监控方法
方法一：有没有延时 Seconds_Behind_Master: 0 方法二： 主库： mysql> show master status ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 151847 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 从库： [root@db01 data]# mysql -S /tmp/mysql3308.sock -uroot -p123 -e "show slave status \G"|grep "Master_Log" 已经拿到的主库日志量(master.info)：判断传输有没有延时 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 151847 已经执行的主库日质量(relay-log.info): 判断回放有没有延时 Relay_Master_Log_File: mysql-bin.000004 Exec_Master_Log_Pos: 141847 计算主从复制延时日志量。
导致延时的主要原因
外部因素 网络延时 主从硬件、参数等不一致 从库太多。 主库压力大。
主库 1） binlog记录不及时。 mysql> select @@sync_binlog; +---------------+ | @@sync_binlog | +---------------+ | 1 | +---------------+ 参数说明： 1 ：每次事务提交都立即刷新binlog到磁盘。 0 ：由操作系统决定，什么刷新磁盘。 （2） DUMP线程串行工作。 大事务、并发事务高、DDL 解决办法： 5.6版本加入GTID复制模式，但手工配置。DUMP在传输日志时可以并发。 5.7版本GTID做了增强，不手工开启也自动维护匿名的GTID信息。 （3）怎么判断是主库导致的延时？ 主库： mysql> show master status ; 从库： mysql -S /tmp/mysql3308.sock -uroot -p123 -e "show slave status \G"|grep "Master_Log" 从库方面 # IO线程： 从库IO比较慢。relay 落地慢。可以将realy放到 SSD # SQL 线程：串行回放。 主库可以并行事务，从库SQL线程串行回放。 所以：并发事务高、大事务、DDL 解决方法： 5.6 版本：开启GTID后，可以多SQL线程，只能针对不同的库的事务进行并行SQL恢复。 5.7 版本：做了增强，基于逻辑时钟的并行回放。MTS。 5.7 的从库并发配置方法。 gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 master_info_repository=TABLE relay_log_info_repository=TABLE # 已经执行的主库日质量(relay-log.info): 判断回放有没有延时 Relay_Master_Log_File: mysql-bin.000004 Exec_Master_Log_Pos: 141847
过滤复制
主库实现 binlog_do_db 白名单 binlog_ignore_db 黑名单 说明：是否记录binlog日志来控制过滤。 7.2 从库实现 ****** 实现方法： IO线程不做限制。 SQL线程回放时，选择性回放。 参数： replicate_do_db=world replicate_do_db=oldboy replicate_ignore_db= replicate_do_table=world.city replicate_ignore_table= replicate_wild_do_table=world.t* replicate_wild_ignore_table= 配置方法： 方法一： 修改配置文件并重启 vim /data/3308/my.cnf replicate_do_db=world replicate_do_db=oldboy systemctl restart mysqld3308 方法二： STOP SLAVE SQL_THREAD; CHANGE REPLICATION FILTER REPLICATE_DO_DB = (oldguo, oldboy); START SLAVE SQL_THREAD;
延时从库的应用
配置方法： mysql>stop slave; mysql>CHANGE MASTER TO MASTER_DELAY = 300; mysql>start slave; mysql> show slave status \G SQL_Delay: 300 SQL_Remaining_Delay: NULL 思路 ： 主库发生了逻辑损坏（DROP，truncate）时，可以使用延时从库快速恢复数据。 2小时延时 10：00 做的drop database A; 1. 及时监控故障：主库 10:05发现故障，从库此时8:05数据状态 2. 立即将从库的SQL线程关闭。需要对A业务挂维护页。 3. 停止所有线程。 4. 在延时从。恢复A库数据 手工模拟SQL线程工作，直到drop之前位置点。 SQL线程上次执行到的位置------> drop之前 relay.info ----> 分析drop位置点 ---> 截取relaylog日志----> source
故障模拟及恢复
故障模拟： create database delaydb charset utf8mb4; use delaydb; create table t1(id int); insert into t1 values(1),(2),(3); commit; drop database delaydb; 截取日志： 起点: SQL上次执行到的位置点， Relay_Log_File: db01-relay-bin.000004 Relay_Log_Pos: 320 终点：drop 之前 db01-relay-bin.000004 | 1006 | Query | 7 | 152967 | drop databas [root@db01 tmp]# mysqlbinlog --start-position=320 --stop-position=1006 /data/3309/data/db01-relay-bin.000004 >/tmp/bin.sql mysql> reset slave all; mysql> set sql_log_bin=0; mysql> source /tmp/bin.sql; mysql> set sql_log_bin=1; mysql> show tables; +-------------------+ | Tables_in_delaydb | +-------------------+ | t1 | +-------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+
GTID复制
清理环境 pkill mysqld \rm -rf /data/mysql/data/* \rm -rf /data/binlog/* mkdir -p /data/mysql/data /data/binlog chown -R mysql.mysql /data/* 准备配置文件 主库db01： mv /etc/my.cnf /tmp cat > /etc/my.cnf <<EOF [mysqld] basedir=/data/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=51 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db01 [\\d]> EOF slave1(db02)： mv /etc/my.cnf /tmp cat > /etc/my.cnf <<EOF [mysqld] basedir=/data/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=52 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db02 [\\d]> EOF slave2(db03)： mv /etc/my.cnf /tmp cat > /etc/my.cnf <<EOF [mysqld] basedir=/data/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=53 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db03 [\\d]> EOF
初始化数据
/data/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
启动数据库
/etc/init.d/mysqld start
构建主从：
master:51slave:52,53
51主库操作
grant replication slave on *.* to repl@'10.0.1.%' identified by '123';
52/53从库操作
change master to master_host='10.0.1.121', master_user='repl', master_password='123' , MASTER_AUTO_POSITION=1; start slave; show slave status \G Slave_IO_Running: Yes Slave_SQL_Running: Yes
