mysql海量数据存储和解决方案_MySQL海量数据存储与优化

一、实现MySQL分库和读写分离

MySQL主从搭建

两组 一主两从架构(6台主机：2主，4从)

第一组：192.168.47.10(主)、192.168.47.11(从)和192.168.47.12(从)

第二组：192.168.47.14(主)、192.168.47.15(从)和192.168.47.16(从)

mysql 版本

修改MySQL的配置文件my.cnf

vim /etc/my.cnf

配置虚拟机的固定IP

vi /etc/sysconfig/network-scripts/ifcfg-ens33

重启网卡ip才能生效

service network restart

主库给从库授权

在MySQL命令行执行如下命令：

#开启复制功能

grant replication slave on *.* to 'root'@'%' identified by '123456';

#开启远程登录功能

grant all privileges on *.* to 'root'@'%' identified by '123456';

刷新权限

flush privileges;

重启服务

service mysql restart

查看同步状态

mysql> show master status;

Empty set (0.00 sec)

将从库的salve地址 指向 master

即在192.168.47.15和192.168.47.16执行同步复制

mysql> change master to master_host='192.168.47.14',master_port=3306,master_user='root',master_password='123456',master_log_file='mysqlbinlog.000001',master_log_pos=869;

注意： master_log_file 指的是主库的当前binlog文件的名称，master_log_pos指的是当前主库binlog的位置。 主库的防火墙必须关闭，才能连接上。 开启从库

1:查看防火状态

systemctl status firewalld

service iptables status

2:暂时关闭防火墙

systemctl stop firewalld

service iptables stop

3:永久关闭防火墙

systemctl disable firewalld

chkconfig iptables off

4:重启防火墙

systemctl enable firewalld

service iptables restart

5:永久关闭后重启

//暂时还没有试过

chkconfig iptables on

查看从库的状态

mysql> show slave status \G;

如果是Slave_SQL_Running：no：

解决办法如下

stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

start slave;

show slave status\G

Slave_IO_Running: No

解决问题的思路：

1. 找到mysql配置的这个文件/etc/my.cnf

2. 在文件中找到mysql错误异常日志文件的路径，我配置的是log-error=/var/log/mysqld.log

3. 编辑/var/log/mysqld.log文件

4. 查看具体异常信息

异常信息：

[ERROR] Slave I/O for channel ‘’: Fatal error: The slave I

/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must

be different for replication to work. Error_code: 1593

问题定位：由于uuid相同，而导致触发此异常

解决方案：把uuid修改即可

另一种原因 ：没能从master读到文件了的内容

Error reading packet from server for channel '': bogus data in log event; the first event 'mysqlbinlog.000001' at 869, the last event read from './mysqlbinlog.000001' at 123, the last byte read from './mysqlbinlog.000001' at 888. (server_errno=1236)

2021-01-28T17:51:49.992665Z 15 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event; the first event 'mysqlbinlog.000001' at 869, the last event read from './mysqlbinlog.000001' at 123, the last byte read from './mysqlbinlog.000001' at 888.', Error_code: 1236

解决方法：

master端：

mysql> flush logs;

Query OK, 0 rows affected (0.02 sec)

mysql> show master status;

+---------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------------+----------+--------------+------------------+-------------------+

| dbmaster-bin.000005 | 120 | | | |

+---------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

记住file和position这两个选项

slave端：

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_log_file ='dbmaster-bin.000005',master_log_pos=120;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

show slave status\G;

再次查看状态就会发现成功了。

验证主从