mysql 进阶到高级_MySQL replication 高级进阶

延时从库

解决逻辑损坏问题 ，实际上是让SQL线程慢点回放relay-log，一般企业建议延迟3-6小时，具体看运维对故障的反应时间

mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_DELAY=300;

mysql> start slave;

mysql>show slave status\G;

SQL_Delay: 300 #延时300秒

SQL_Remaining_Delay: 296 #还剩296秒执行relay-log日志

延时从库故障处理逻辑问题

发现主库drop库

停掉SQL线程

模拟SQL线程，回放到relay-log到drop之前的sql语句

找relay-log的起点和终点

起点，停掉SQL线程的位置。终点drop时刻的位置

延时从库恢复思路

监控到数据库逻辑故障

停掉从库的SQL线程，找起点

mysql> stop slave sql_thread;

mysql> show slave status\G;

Relay_Log_File: mysql-relay-bin.000002 #SQL线程回放的relay-log日志

Relay_Log_Pos: 473 #SQL线程回放的relay-log日志的pos号

找relay-log的终点

mysql> show relaylog events in 'mysql-relay-bin.000002';

+------------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql-relay-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |

| mysql-relay-bin.000002 | 123 | Previous_gtids | 6 | 154 | |

| mysql-relay-bin.000002 | 154 | Rotate | 5 | 0 | mysql-bin.000009;pos=154 |

| mysql-relay-bin.000002 | 201 | Format_desc | 5 | 0 | Server ver: 5.7.26-log, Binlog ver: 4 |

| mysql-relay-bin.000002 | 320 | Anonymous_Gtid | 5 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-relay-bin.000002 | 385 | Query | 5 | 307 | create database aa |

| mysql-relay-bin.000002 | 473 | Anonymous_Gtid | 5 | 372 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-relay-bin.000002 | 538 | Query | 5 | 467 | drop database world |

+------------------------+-----+----------------+-----------+-------------+---------------------------------------+

# POS 节点号为relay-log中的pos节点号

# End_log_pos 为binlog中的pos节点号

截取relay-log日志

mysqlbinlog --start-position=473 --stop-position=538 mysql-relay-bin.000002 > /tmp/relay.sql

从库恢复数据

mysql> source /tmp/relay.sql

业务恢复

情况一： 数据库中就跑了一个业务(即就有一个库)，从库替代主库工作

情况二：数据库中跑了多个业务，从库导出故障库，还原生产库

过滤复制

需求

image.png

blog和zh都是测试库，只有wordpre是核心业务，为了减轻主库压力，只复制WordPress库

思路

可以在主库中，让dump_T线程只取WordPress库，即binlog只记录wrodpress库的。

也可以在从库中，让SQL_T只回放wordpress库

主库

vim /etc/my.cnf

[mysqld]

binlog_do_db=wordpress #相当于白名单

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000010 | 154 | wordpress | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

# binlog_do_DB: 只记录哪个库的binlog

#binlog_ignore_DB: 不记录哪个库的binlog

从库

mysql>show slave status\G

# 基于库级别的回放

Replicate_Do_DB:

Replicate_Ignore_DB:

# 基于表级别的回放

Replicate_Do_Table:

Replicate_Ignore_Table:

# 模糊匹配的表 库.t* 表示只匹配这个库下，以t开头的表

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

vim /etc/my.cnf

[mysqld]

replicate_do_db=wordpress

过滤复制另一种架构

image.png

采用读写分离，减轻读的压力

GTID复制配置

环境准备

清空原有环境

准备配置文件 db01 db02 db03

# db01

[root@mysql data]# cat /etc/my.cnf

[mysqld]

basedir=/application/mysql

datadir=/data/mysql/data

user=mysql

port=3306

socket=/tmp/mysql.socket

#secure-file-priv=

server_id=5

log_bin=mysql-bin

gtid_mode=on

enforce_gtid_consistency=true

log_slave_updates=1

[mysql]

socket=/tmp/mysql.socket

gitd_mode=on # 开启gtid

enforce_gtid_consistency=true #强制gtid的一致性

log_slave_updates=1 #slave更新是否写入日志，在多住和高可用环境中必须加入这个参数

# db02

[root@db02 data]# cat /etc/my.cnf

[mysqld]

basedir=/application/mysql

datadir=/data/mysql/data

user=mysql

port=3306

socket=/tmp/mysql.socket

#secure-file-priv=

server_id=7

log_bin=mysql-bin

gtid_mode=on

enforce_gtid_consistency=true

log_slave_updates=1

[mysql]

socket=/tmp/mysql.socket

# db03

[root@db03 ~]# cat /etc/my.cnf

[mysqld]

basedir=/application/mysql

datadir=/data/mysql/data

user=mysql

port=3306

socket=/tmp/mysql.socket

#secure-file-priv=

server_id=6

log_bin=mysql-bin

gtid_mode=on

enforce_gtid_consistency=true

log_slave_updates=1

[mysql]

socket=/tmp/mysql.socket

初始化数据

[root@db02 application]# mysqld --initialize-insecure --basedir=/application/mysql --datadir=/data/mysql/data --user=mysql

构建主从

#master

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

# slave

CHANGE MASTER TO

MASTER_HOST='10.0.0.11',

MASTER_USER='repl',

MASTER_PASSWORD='123',

MASTER_PORT=3306,

MASTER_AUTO_POSITION=1;

start slave;

show slave status\G;

# master_auto_position 开启读取relay-log的最后一个事件，告诉主库这个事件之后给我

GTID复制和普通复制的区别

非GTID

CHANGE MASTER TO

MASTER_HOST='10.0.0.11',

MASTER_USER='repl',

MASTER_PASSWORD='123',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000009',

MASTER_LOG_POS=183134,

MASTER_CONNECT_RETRY=10;

GTID

CHANGE MASTER TO

MASTER_HOST='10.0.0.11',

MASTER_USER='repl',

MASTER_PASSWORD='123',

MASTER_PORT=3306,

MASTER_AUTO_POSITION=1;

在主从复制的环境中，主库发生过的事务，在全局都是惟一的GTID记录的，更方便failover

在change master to的时候就不再需要binlog文件名和pos号码,使用master_auto_position=1

在复制过程中，从库不再需要master.info文件，master信息仍然会记录在这个稳健者中。它会直接读取最后一个relay.log的GTID号

mysqldump过程中，默认会将备份中包含的事务操作，以下方式set @@global.GTID_purged=server-uuid:TID,告诉从库我已有以上事务，你直接恢复即可。不用去主库重新拿备份文件中包含的事务--set-gtid-purge=ON(auto默认) 是自动打开的

GTID 从库写入错误

查看监控信息:

Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default database: 'oldboy'. Query: 'create database oldboy'

Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3

Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,

7ca4a2b7-4aae-11e9-859d-000c298720f6:1

注入空事物的方法：

stop slave;

set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';

begin;commit;

set gtid_next='AUTOMATIC';

这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。

最好的解决方案：重新构建主从环境

半同步(了解)

解决主从复制数据一致性问题

主从数据不一致的原因

当IO线程拿到数据到TCP/IP缓存时，从库宕机，导致数据没有写入成功。从库在启动会导致主从数据不一致

解决办法(数据库层面的ACK)

从库relay log 落地，IO线程会返回一个ACK，主库的ACK_reciver收到ack，才会写入事务成功，如果超过10秒，ACK没有返回，会自动切换为异步复制