6.MySQL主从同步 、 主从同步模式
6.MySQL主从同步 、 主从同步模式
主从同步原理
(1)Master，记录数据更改操作
①启用binlog日志
②启用binlog日志格式
③设置server_id
(2)Slave运行2个线程
①Slave_IO: 复制master主机binlog日志文件里的SQL到本机的relay-log文件里
②Slave-SQL: 执行本机relay-log文件里的SQL语句，重现Master的数据操作
③relay-log中继日志文件：只要指定为从服务器，自动开启此文件
在主从同步的过程中，主库会将所有的操作事件记录在 binlog 中，从库通过开启一个 I/O 线程保持与主库的通信，并在一定时间间隔内探测 binlog 日志文件是否发生改变。如果 binlog 日志发生了变化，主库生成一个 binlog dump 线程向从库 I/O 线程传送 binlog。从库上的 I/O 线程将 binlog 复制到自己的 relay log 中。最终由从库中的 SQL 线程读取 relay log 中的事件重放到从库上
构建主从同步
(1)确保数据相同：从库必须要有主库上的数据
(2)配置主服务器：启用binlog日志、授权用户，查看当前正在使用的日志
(3)配置从服务器：设置server_id,指定主库信息
(4)测试配置：客户端连接主库写入数据，在从库上也能查询到
| 文件名 | 说明 |
|---|---|
| Master.info | 主库信息 |
| Relay-log.info | 中继日志信息 |
| 主机名-relay-bin.xxxxxx | 中继日志 |
| 主机名-relay-bin.index | 中继日志索引文件 |
配置主服务器
修改/etc/my.cnf配置，重新启动MySQL服务程序
vim /etc/my.cnf
[mysqld]
log_bin=日志名 //启用binlog日志，并指定文件名前缀
server_id=id值 //指定服务器ID号
binlog_format=”mixed” //指定binlog日志类型
......
重启mysql服务：
systemctl restart mysqld.service
新建备份用户，授予复制权限
需要的权限为REPLICATION SLAVE，允许其从Slave服务器访问：
grant replication slave ON *.* TO 授权用户名@'从库地址' IDENTIFIED BY '密码';
检查Master服务器的同步状态
show master status\G
*************************** 1. row ***************************
File: dbsvr1-bin.000001 //记住当前的日志文件名
Position: 154 //记住当前的位置
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
验证是否为从库
show slave status 空则不是
配置从服务器
修改/etc/my.cnf配置，指定服务器ID号、允许日志同步：
vim /etc/my.cnf
[mysqld]
log_bin=dbsvr2-bin //启动SQL日志，并指定文件名前缀
server_id = 20 //指定服务器ID号，不要与Master的相同
.. ..
配置完成后，重启mysql服务：
systemctl restart mysqld.service
指定主库信息
change master to
master_host='主库ip地址',
master_user='主库授权的用户名',
master_password='授权密码',
master_log_file='主库日志文件名', //对应Master的日志文件
master_log_pos=主库偏移量; //对应Master的日志偏移位置(偏移量)
启动 SLAVE进程
start slave; //启动进程，stop slave，停止进程
注意：一旦启用SLAVE复制，当需要修改MASTER信息时，应先执行STOP SLAVE停止复制，然后重新修改、启动复制。
检查Slave服务器的同步状态
确认其中的IO线程、SQL线程正常运行，才能成功同步：
show slave status\G #显示当前服务器从库的状态
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.1
Master_User: replicater
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dbsvr1-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: db2-relay-bin.000003 #中继日志文件
Relay_Log_Pos: 321
Relay_Master_Log_File: dbsvr1-bin.000001
Slave_IO_Running: Yes //IO线程应该已运行
Slave_SQL_Running: Yes //SQL线程应该已运行
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 2490
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0 #IO错误显示的地方
Last_IO_Error:
Last_SQL_Errno: 0 #SQL错误显示的地方
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 2d4d8a11-27b7-11e7-ae78-52540055c180
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
错误分析
若START SLAVE直接报错失败，请检查CHANGE MASTER相关设置是否有误，纠正后再重试；若IO线程或SQL线程有一个为“No”，则应检查服务器的错误日志，分析并排除故障后重启主从复制。
Slave_IO_Running错 #从服务器指定主服务器的IP以及最初改主配置文件等打错了，细心寻找差错，找到某个之后直接
stop slave；
change master to 选项=‘值’,选项=‘值’...; #哪个错了改哪个，一个错了就改一个
start slave；
Slave_SQL_Running错 #最初主从数据库的数据不一致
从服务器相关文件
cat /var/lib/mysql/master.info //主库信息
主机名-relay-bin.00001 //中继日志文件 默认保留两个最新的
主机名-relay-bin.index //中继日志文件的索引文件
relay-log.info //中继日志信息
同步部分数据库
主库配置选项：对所有从服务器有效
| 选项 | 用途 |
|---|---|
| Bin_do_db=想同步的库名，多个库用逗号空格间隔 | 设置master对哪些库记日志 |
| Binlog_ignore_db=不想同步的库名 | 设置master对哪些库不记日志 |
从库配置选项
| 选项 | 用途 |
|---|---|
| log_slave_updates | 记录从库更新，允许链式复制(A-B-C),作主从从用 |
| relay_log=日志文件的名字 | 指定文件日志文件名 |
| replicate_do_db=库名1,库名2 | 仅复制指定库，其他库将被忽略，此选项可设置多条(省略时复制所有库) |
| replicate_ignore_db=test | ignore-db与do-db只需选用其中一种 |
| 不复制哪些库，其他库将被忽略 |
主-从-从
主 192.168.4.51 从 192.168.4.52 从 192.168.4.53
配置主服务器192.168.4.51
vim /etc/my.cnf
[mysqld]
log_bin=db51 //启用binlog日志，并指定文件名前缀
server_id=51 //指定服务器ID号
binlog-format="mixed" // 指定binlog日志格式
systemctl restart mysqld
确保/var/lib/mysql下面有两个文件：
/var/lib/mysql/db51.000001 /var/lib/mysql/db51.index
查看主服务正在使用的日志信息
show master status;
对从库52上的用户进行授权
grant replication slave on *.* to 用户52@"192.168.4.52" identified by "密码";
配置从服务器192.168.4.52
对用户53用户进行授权
grant replication slave on *.* to 用户53@"192.168.4.53" identified by "密码";
vim /etc/my.cnf
[mysqld]
server_id=52
log-bin=db52
binlog-format="mixed"
log_slave_updates //允许级联复制
systemctl restart mysqld
确保/var/lib/mysql下面有两个文件：
/var/lib/mysql/db52.000001 /var/lib/mysql/db52.index
查看正在使用的日志信息
show master status;
change master to
master_host="192.168.4.51",
master_user="yaya",
master_password="123456",
master_log_file="db51.000002",
master_log_pos=437;
Query OK, 0 rows affected, 2 warnings (0.43 sec)
start slave;
show slave status \G; 确认其中的IO线程、SQL线程正常运行
配置从从服务器192.168.4.53
mysql -h192.168.4.52 -uuser53 -p654321
vim /etc/my.cnf
validate_password_policy=0
validate_password_length=6
server_id=53
systemctl restart mysqld
mysql -uroot -p123456
change master to
master_host="192.168.4.52",
master_user="user53",
master_password="654321",
master_log_file=" db52.000001",
master_log_pos=154;
start slave;
show slave status\G
配置半同步复制模式
异步复制：主库执行完成后，立即将结果返回给客户端，并不关心从库是否已经接收并处理，客户端体度高
全同步模式：当主库执行完一次事务，且所有从库都执行了该事物才返回给客户端，用户体验度差
半同步复制: 介于异步和全同步之间，主库在执行完一次事物后，等待至少一个从库接收到并写到relay log中才返回给客户端
1) 查看是否允许动态加载模块默认允许
mysql> show variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
如果为no，修改主配置文件/etc/my.cnf,加一句have_dynamic_loading=yes即可
2）加载插件，用户需有SUPER权限（加载模块）
主库上面操作：
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
从库上面操作：
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
查看模块是否安装成功：
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+------------------- ---+-------------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------------- --+-------------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+--------------------- -+-------------- ----+
3）启用半同步复制
// 在安装完插件后，半同步复制默认是关闭的
主库上面执行：
mysql> set global rpl_semi_sync_master_enabled = 1;
从库上面执行：
mysql> set global rpl_semi_sync_slave_enabled = 1;
查看半同步复制模式是否启用,主库和从库都要操作：
mysql> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_slave_enabled | ON |
+------------------------------+-------+
4）永久启用半同步复制 （推荐）
主库配置
vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
从库配置
vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
在高可用架构下，master和slave需同时启动，以便在切换后能继续使用半同步复制
[root@master51 ~]# vim /etc/my.cnf
[mysqld]
plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1