主从复制高级进阶： 1、延时从库 ****** 方便我们处理，逻辑损坏，比如，误操作，drop database xy; SQL 线程延时：数据已经写入了relaylog中了，SQL线程"慢点"运行 一般企业建议3----6小时，具体看公司运维人员对故障的反应时间 登录slave stop slave; change master to master_delay=300; #单位：秒 start slave; show slave status\G SQL_Delay：300 SQL_Remainning_Delay：NULL ##最近的一个日志，还差多少秒，执行。 1.2、延时从库处理逻辑故障 1.2.1、延时从库的恢复思路 (1)、监控到数据库逻辑故障 (2)、停从库SQL线程，记录SQL线程已经 回放 的位置点(截取日志起点)。 stop slave sql_thread; show slave status\G Relay_Log_File: dba01-relay-bin.000002 Relay_Log_Pos: 320 (3)、截取relaylog 起点： show slave status\G Relay_Log_File: Relay_Log_Pos: 终点： drop 之前的位置点 show realylog events in 'dba01-relay-bin.000002' 进行截取 (4)、模拟SQL线程回放日志 从库 source (5)、恢复业务 情况一：只有一个库 从库替代主库工作 情况二：多个库，只删除了一个库或其中几个库。 从库导出故障库，还原到主库中。 1.2.2、故障演练 主库： create database delay charset utf8mb4; use delay; create table t1(id int); insert into t1 values(1),(2),(3); commit; drop database delay; 从库： (1)、停止SQL线程，获取relay的位置点 stop slave sql_thread; Relay_Log_File: dba01-relay-bin.000002 Relay_Log_Pos: 320 (2)、找到relaylog的截取 终点 show relaylog events in 'dba01-relay-bin.000002';(3)、截取日志 cd /data/mysql/data mysqlbinlog --skip-gtids --start-position=320 --stop-position=992 dba01-relay-bin.000002 >/tmp/delay.sql (4)、恢复relaylog到从库 set sql_log_bin=0; source /tmp/delay.sql;
(5)、恢复业务 从库备份delay mysqldump -uroot -p -B delay -R -E --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction >/tmp/master_delay.sql scp -r /tmp/master_delay.sql root@192.168.189.128:/tmp 主库上导入数据 set sql_log_bin=0; source /tmp/master_delay.sql;
commit; set sql_log_bin=1; 从库： drop database delay; start slave sql_thread; show slave status\G
set sql_log_bin=1; 2、过滤复制 ***** /etc/my.cnf 主库：实现过滤，这种方法很少用。 binlog_do_db=world #白名单，只记录这个数据库的日志 binlog_ignore_db=xy #黑名单，不记录 xy 库， 多个库，要写多行。不管白名单和黑名单 一般只选一个。 从库：实现过滤，控制SQL线程，回不回放这些库 replicate_do_dB= #库级别，白名单 库级别用的最多 replicate_ignore_dB= #库级别，黑名单 replicate_do_table= #表级别，白名单 replicate_ignore_table= #表级别，黑名单 replicate_wild_do_table= #表级别，模糊，可以支持，那个库下面的 t*开头的表，可以用通配符 白名单 replicate_wild_ignore_table= #表级别，模糊，可以支持，那个库下面的 t*开头的表，可以用通配符 黑名单 例子：只回放repl 库 从库 vim /etc/my.cnf replicate_do_db=repl 保存，退出。
3、GTID *****
3.2.1、GTID核心参数 重要参数： git-mode=on ##开启gitd，否则就是普通的复制 enforce-gtid-consistency=true ## 强制GTID 一致性 log-slave-updates=1 # slave 更新是否记录日志，强制从库刷新binlog，在MHA高可用，双主环境中，必须要加。 准备3台干净的mysql 服务器 dba02---192.168.189.133 主库 dba03---192.168.189.134 从库 dba04---192.168.189.135 从库 /etc/my.cnf [mysqld] user=mysql basedir=/application/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=8 #从库的server_id 比主库的大 log_bin=/data/binlog/mysql-bin binlog_format=row log_error=/application/mysql/mysqlerr.log port=3306 secure_file_priv="" #允许将查询导出文件 #开启GTID gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 ##开启慢日志 slow_query_log=1 slow_query_log_file=/data/mysql/slow.log long_query_time=2 log_queries_not_using_indexes [mysql] socket=/tmp/mysql.sock prompt=dba02 [\\d]> ##登录mysql看到的提示符，其他的 dba03 dba04 [\\d]----> 显示库 搭建主从： 主库上创建复制用户 grant replication slave on *.* to repl@'192.168.%' identified by 'repl520'; 从库上面：配置master change master to master_host='192.168.189.133',master_user='repl',master_password='repl520',master_auto_position=1; start slave; master_auto_position=1 ## 读取relaylog最后一个事务的GTID， GTID复制和普通复制的区别： (1)、在主从复制环境中，主库发生 过的事务，在全局都是由唯一GTID记录的，更方便Failover (2)、GTID额外的参数三个 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 (3)、change master to 的时候不再需要binlog文件和position号，直接master_auto_position=1; (4)、在GTID复制 过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的GTID号 (5)、在mysqldump 备份时，默认会将备份中包含的事务操作，以以下方式 SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11'; 告诉从库，我的备份中已经有以上事务了，你就不用运行了，直接从下一个GTID开始请求binlog就可以了。 在生产环境，已经有数据的情况下，并且开启了gtid，构建主从， 先备份主库数据，再把这些备份，恢复到从库，再开启主从 这时mysqldump 备份的时候不能加 --set-gtid-purged=off 4、半同步 *** 用得少，性能极差 作用：为了解决主从复制数据一致性问题，也就是主从复制之间的数据安全问题。 主库的dump线程要等到从库的IO线程将日志全部写到磁盘上的relaylog，并传回ack信号，才认为 5.5版本以后，多了一个插件，来控制IO线程，必须要把relaylog写到磁盘，并传回ack，主库的dump线程，才认为传完了，这个事务才正常提交成功，否则，会阻塞主库的事务提交。 半同步还有缺陷，过了超时时间间（默认为10秒），就还是会切换成异步同步， 与传统复制的区别： ACK，从库relaylog落地，从库的IO线程会返回一个ACK_reciver ，主库事务才能提交， 如果主库一直没有收到从库IO线程返回的ACK_reciver，超过10秒钟（默认时间10秒）就会切换为异步复制。