MySQL replication 高级进阶

2020-04-05 本文已影响0人麟之趾a

延时从库

解决逻辑损坏问题，实际上是让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没有返回，会自动切换为异步复制

MySQL replication 高级进阶

延时从库

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

发现主库drop库

延时从库恢复思路

过滤复制

需求

思路

主库

从库

过滤复制另一种架构

GTID复制配置

环境准备

GTID复制和普通复制的区别

非GTID

GTID

GTID 从库写入错误

半同步(了解)

主从数据不一致的原因

解决办法（数据库层面的ACK）

猜你喜欢

热点阅读