Mysql数据库的主从同步，实现读写分离详细配置说明以及错误解决办法

本文链接：https://blog.youkuaiyun.com/weixin_42784553/article/details/105111816

利用主从数据库来实现读写分离，从而分担主数据库的压力。在多个服务器上部署mysql，将其中一台认为主数据库，而其他为从数据库，实现主从同步。其中主数据库负责主动写的操作，而从数据库则只负责主动读的操作（slave从数据库仍然会被动的进行写操作，为了保持数据一致性），这样就可以很大程度上的避免数据丢失的问题，同时也可减少数据库的连接，减轻主数据库的负载。
在这里插入图片描述

在上面的模型中，Mysql-A就是主服务器，即master，Mysql-B就是从服务器，即slave。

在Mysql-A的数据库事件（例如修改数据库的sql操作语句），都会存储到日志系统A中，在相应的端口（默认3306）通过网络发送给Mysql-B。Mysql-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库Mysql-B中完成。

日志系统A，是MYSQL的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即bin log，注意MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全。

日志系统B，不是二进制日志，由于它是从MYSQL-A的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即relay log。

通过上面的机制，可以保证Mysql-A和Mysql-B的数据库数据一致，但是时间上肯定有延迟，即Mysql-B的数据是滞后的。因此，会出现这样的问题，Mysql-A的数据库操作是可以并发的执行的，但是Mysql-B只能从relay log中一条一条的读取执行。若Mysql-A的写操作很频繁，Mysql-B很可能就跟不上了。

主从同步复制有以下几种方式：

（1）同步复制，master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。
（2）异步复制，master只需要完成自己的数据库操作即可，至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。
（3）半同步复制，master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MYSQL引入的。

实现的mysql5.7数据库的主从同步配置，从而实现读写分离操作

master主服务器的配置

配置文件my.cnf的修改（系统不一致文件可能名字位置不同）

[root@localhost mysql]# vim /etc/my.cnf
# 不同的系统名字位置不同 /etc/mysql/mysql.conf.d/mysqld.cnf 
#在[mysqld]中添加：
server-id=1
log_bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=test
#备注：
#server-id 服务器唯一标识。
#log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
#binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
#binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

创建从服务器的用户和权限

#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password:
#创建来访者用户及host
mysql> create user 'masterbackup'@'192.168.139.%' identified by '123456';
#创建从数据库的masterbackup用户和权限
mysql> grant replication slave on *.* to masterbackup@'192.168.17.%' identified by '123456';
#备注
#192.168.17.%通配符，表示0-255的IP都可访问主服务器，正式环境请配置指定从服务器IP
#若将 192.168.17.% 改为 %，则任何ip均可作为其从数据库来访问主服务器
 
#退出mysql
mysql> exit;

重启mysql服务

[root@localhost mysql]# service mysql restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

查看主服务器状态


#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password:
 
#查看主服务器状态
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 | test         |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

这些数据需要保存在从服务器上配置

slave从服务器的配置

配置文件my.cnf的修改

[root@localhost mysql]# vim /etc/my.cnf

#在[mysqld]中添加：
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#replicate-do-db=test
#备注：
#server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
#relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
#replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
#replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

MySQL relay log 详细参数解释

https://blog.51cto.com/douya/1788753

2.重启mysql服务

[root@localhost mysql]# service mysql restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

连接master主服务器


#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password:
 
#连接master主服务器
mysql> change master to master_host='192.168.17.130',master_port=3306,master_user='masterbackup',master_password='123456',master_log_file='master-bin.000001',master_log_pos=154;
#备注：
#master_host对应主服务器的IP地址。
#master_port对应主服务器的端口。
#master_log_file对应show master status显示的File列：master-bin.000001。
#master_log_pos对应show master status显示的Position列：154。

启动slave数据同步

#启动slave数据同步
mysql> start slave;

#停止slave数据同步（若有需要）
mysql> stop slave;

查看slave信息

mysql> show slave status\G;

在这里插入图片描述
Slave_IO_Running和Slave_SQL_Running都为yes，则表示同步成功。

主从服务器不同步解决方法

这里有主从同步出现各种错误的详细解决办法和模拟实例说明

https://blog.51cto.com/13407306/2067333

以下介绍最简单的两种

在从数据库中，使用SET全局sql_slave_skip_counter来跳过事件，跳过这一个错误，然后执行从下一个事件组开始。

#在从数据库上操作
mysql > stop slave;
mysql > set global sql_slave_skip_counter=1;
mysql > start slave;

重新做主从，完全同步

1--master主库上操作
mysql> flush tables with read lock;  //进行锁表，防止数据写入。注意该处是锁定为只读状态，语句不区分大小写
#mysqldump --lock-all-tables --all-databases --flush-logs --master-data=2 > /root/allsql.sql   //主库完全备份(如果是指定库同步，就备份指定库)，注意数据库备份一定要定期进行，确保数据万无一失
mysql> show master status;      //查看master状态，注意log file和pos节点，slave同步会用到
# scp mysql.bak.sql root@192.168.1.102:/tmp/   //把备份文件传到slave从库机器，进行数据恢复
#备注
#master_log_file和master_log_pos可能会不同，需要在主数据库中show master status来查看

2--slave从库操作
mysql> stop slave;
mysql> source /tmp/mysql.bak.sql
mysql> change master to master_host = '192.168.1.101', master_user = 'slave', master_port=3306.......；#配置和之前从库配置一样
mysql> start slave;
mysql> show slave status\G 
#结果yes成功
.......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#解决完了把主库解锁
mysql> UNLOCK TABLES