Slave节点在处理日志时的逻辑是这样的,本地产生的修改事件,写入二进制日志文件,不过解析自中继日志的修改事件,因为不是它自己产生的修改,默认情况下不会写入本地二进制日志文件。启用—log-slave-updates参数,即使是应用中继日志产生的数据库修改,也将会写到本地二进制日志文件中。修改RelaySlave节点的初始化参数文件,在[mysqld]区块中增加log-slave-update参数
停止RelaySlave节点,重启使参数生效
先停止SQL_THREAD线程
RelaySlave > stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
查看当前节点二进制日志文件和位置
RelaySlave > show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[mysql@mysql1 ~]$ mysql_db_shutdown.sh 3307
[mysql@mysql1 ~]$ mysql_db_startup.sh 3307
通过XtraBackup创建Slave节点,要在5分钟内创建成功
将Slave3创建到3308端口
[mysql@mysql1 ~]$ mysql -usystem -p123456 -S /data/mysqldata/3308/mysql.sock
(system@localhost) [(none)]> prompt Slave3 >
Slave3 > change master to
-> master_host='192.168.1.201',
-> master_port=3307,
-> master_user='rep1',
-> master_password='rep1',
-> master_log_file='mysql-bin.000014',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
Slave3 > start slave;
Query OK, 0 rows affected (0.00 sec)
Slave3 > show slave status\G