12、如何在已有的数据库中无损主主备份？

MySQL四种复制方式

1.异步复制（ asynchronous ）
- 搭建简单，使用非常广泛，从mysql 诞生之初，就产生了这种架构，性能非常好，非常成熟。但这种架构是异步，所以有数据丢失的风险。
2.全同步复制（ fully synchronous ）
- 保证数据安全，不丢失数据，损失性能。
3.传统半同步复制（ Semi synchronous ）rpl_semi_sync_master_wait_point=after_commit
- 性能，功能都介于异步和全同步之间。从mysql5.5开始诞生，目的是为了折中上述两种架构的性能及优缺点。
4.无损复制，增强版的半同步复制（ lossless replication ） rpl_semi_rsync_master_wait_point=after_sync
- 数据零丢失，性能好，mysql5.7诞生。

推荐使用：对性能要求较高的推荐使用异步复制，如果运行的金融类业务推荐使用增强半同步复制，并使用ROW+GTID+5.7以上

主主备份

这里直接使用增强班GTID模式，无损复制模式

主1数据库 aaaa,复制到主2服务器
主2数据库 bbbb,复制到主1服务器
环境： MYSQL:5.7

1、主主配置

主一 my.cnf

[mysqld]

user=mysql
#basedir = /var/lib/mysql # 安装路径
datadir = /var/lib/mysqld  # 数据存储路径
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
symbolic-links=0
log-error=/var/log/mysqld.log


# 开启复制
server-id = 1


# 设置同步的数据库
binlog_do_db = aaaa   # 只复制 master 一个数据库  

# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql,information_schema,sys,performance_schema

# binlog 日志
log_bin = db-binlog  # 开启二进制日志
log_bin_index = db-binlog.index
binlog-format = row   # 默认为mixed混合模式，更改成row复制，为了数据一致性
binlog_rows_query_log_events = on
log-slave-updates = 1 # 从库binlog才会记录主库同步的操作日志
skip-slave-start = 1  # 跳过slave复制线程
sync_binlog = 1       # 控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失)


# 中继日志
relay_log = db-relay.log
relay_log_index = db-relay.index

# 开启gtid模式
gtid-mode = on        # 开启gtid模式
enforce-gtid-consistency = on # 强制gtid一致性，开启后对特定的create table不被支持



## 开启增强半复制
plugin_dir="/usr/lib64/mysql/plugin" # 插件位置 
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave-semisyc_slave.so"
loose_rpl_semi_sync_master_enabled=1
loose_rpl_semi_sync_slave_enabled=1
loose_rpl_semi_sync_master_timeout=5000
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count=1



# 不配置binlog_group_commit从库无法做到基于事物的并行复制。
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10



# 为了数据安全配置
innodb_flush_log_at_trx_commit=1
transaction-isolation=read-committed



## 从配置
slave-parallel-workers=10 # 最大线程10
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

# 如果主没有配置哪些数据库同步，哪些数据可不同步，在从上面也可以配置
#replicate-do-db=test
#replicate-wild-do-table = test.user  # 当只针对某些库的某张表进行同步时,多个不同库表，可以配置将上下两行多配置几个
#replicate-ignore-db=mysql,information_schema,sys,performance_schema # 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）


# 主主配置
auto_increment_offset = 1     # 主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 2  # 主主同步中，用来错开自增值, 防止键值冲突


[client]
socket=/var/run/mysqld/mysqld.sock

#备注：
# server-id 服务器唯一标识。
# log-bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
# binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
# binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
# 其中需要注意的是，binlog_do_db 和 binlog_ignore_db 为互斥选项，一般只需要一个即可。

主二 my.cnf

[mysqld]

user=mysql
#basedir = /var/lib/mysql # 安装路径
datadir = /var/lib/mysqld  # 数据存储路径
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
symbolic-links=0
log-error=/var/log/mysqld.log


# 开启复制
server-id = 2


# 设置同步的数据库
binlog_do_db = bbbb   # 只复制 bbbb 一个数据库  

# binlog 日志
log_bin = db-binlog  # 开启二进制日志
log_bin_index = db-binlog.index
binlog-format = row   # 默认为mixed混合模式，更改成row复制，为了数据一致性
binlog_rows_query_log_events = on
log-slave-updates = 1 # 从库binlog才会记录主库同步的操作日志
skip-slave-start = 1  # 跳过slave复制线程




# 中继日志
relay_log = db-relay.log
relay_log_index = db-relay.index

# 开启gtid模式
gtid-mode = on        # 开启gtid模式
enforce-gtid-consistency = on # 强制gtid一致性，开启后对特定的create table不被支持



## 开启增强半复制
plugin_dir="/usr/lib64/mysql/plugin" # 插件位置 
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave-semisyc_slave.so"
loose_rpl_semi_sync_master_enabled=1
loose_rpl_semi_sync_slave_enabled=1
loose_rpl_semi_sync_master_timeout=5000
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count=1



# 不配置binlog_group_commit从库无法做到基于事物的并行复制。
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10


# 为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit=1
transaction-isolation=read-committed



## 从配置
slave-parallel-workers=10 # 最大线程10
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON



[client]
socket=/var/run/mysqld/mysqld.sock

#备注：
# server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
# relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
# replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
# replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
# 其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

2、创建备份专用用户

172.17.0.3 是从 IP 地址，表示只允许此IP地址进行复制

# 主一
mysql> grant replication slave on *.* to 'repl'@'172.17.0.3' identified by '123456';
mysql> flush privileges;

# 主二
mysql> grant replication slave on *.* to 'repl'@'172.17.0.2' identified by '123456';
mysql> flush privileges;

3、设置全局锁

# 主一
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| aaaa               | ----> 只备份 aaaa               t 
+--------------------+
5 rows in set (0.00 sec)


# 设置全局锁 (后面需要解锁)，注意不要退出 mysql 终端，退出失效全局锁
#mysql> set global super_read_only=ON;
#mysql > set global read_only =1;     # 或者 set global read_only=ON;
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)


# 查看binlog位置 (后面会用到)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                  |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| db-binlog.000005 |      648 | aaaa         |                  | 9a81b0d0-ff9a-11ed-8e47-0242ac110003:1-3,
e5588990-ff99-11ed-8749-0242ac110002:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+

# 主二类似，同意执行

4、先全备份

# 主一：备份
mysqldump -uroot -p123456 test  |gzip -9 > test.db.gz

# 主二: 导入
zcat test.db.gz |mysql -uroot -p123456 test

# 主二复制到主一，类似
# 核实信息(略)

5、再主主备份

主一： 172.17.0.2
主二： 172.17.0.3

mysql> CHANGE MASTER TO \
MASTER_HOST='172.17.0.2', \
MASTER_PORT=3306, \
MASTER_USER='repl', \
MASTER_PASSWORD='123456', \
MASTER_AUTO_POSITION=1;


mysql> start slave;
mysql> show slave status\G;

----
    Read_Master_Log_Pos: 763            -> 位置
           Relay_Log_File: d3a0b765cda7-relay-bin.000002
            Relay_Log_Pos: 320
    Relay_Master_Log_File: mysql-bin.000001
         Slave_IO_Running: Yes            -> IO线程
        Slave_SQL_Running: Yes            -> SQL线程
----


# 暂停复制 stop slave;

6、释放全局锁

# 主
unlock tables;

小结：这样主从配置完成了

从服务器维护

1、先记录位置

mysql> stop slave;

2、再开启复制


# 维护完后
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
 
mysql> CHANGE MASTER TO \
MASTER_HOST='172.17.0.2', \
MASTER_PORT=3306, \
MASTER_USER='repl', \
MASTER_PASSWORD='123456', \
MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
 
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)


mysql> show slave status\G;
----
    Read_Master_Log_Pos: 2835    ---> 发现位置变动，说明在从数据库维护期间，主数据库有新数据写入
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
----

主主切换

主备正常切换以及主库宕机备库切换两个场景，掌握正确的切换流程，可以有效避免切换过程中可能出现的数据不一致问题以及提高整体切换的时间

正常切换

主备正常切换，此场景主要是针对在主备同步复制正常的情况下进行的主备切换，例如：灾备演练，计划性的主备切换。

1、切断应用对主库的写流量
2、主库备库设置只读
3、查看备库复制进程状态
4、比对主备两边的GTID是否一致
5、从库停掉复制进程并清空主从信息
6、从库关闭只读开启读写，转为新主库
7、主库设置执行新主库的复制链路，转为新备库，完成主从切换
8、应用流量切向新主库

主库备库设置只读


mysql> set global read_only=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global super_read_only=ON;
Query OK, 0 rows affected (0.00 sec)



# 确认Slave_IO_Running，Slave_SQL_Running状态为YES，Seconds_Behind_Master为0
mysql> show slave status\G;
---
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Seconds_Behind_Master: 0
---

比对主备两边的GTID是否一致

可通过GTID_SUBSET函数进行比对； SELECT GTID_SUBSET(master_gtid_executed, slave_gtid_executed);
若在master_gtid_executed中的GTID，也存在slave_gtid_executed中，则返回true(1)，否则返回false(0)
返回1，代表主库GTID已经在从库完成执行过，两边是一致的
返回0，代表主库GTID已经未在从库完成执行过，两边是不一致的

mysql> select @@global.gtid_executed;  # 主查看
+------------------------------------------------------------------------------------+
| @@global.gtid_executed                                                             |
+------------------------------------------------------------------------------------+
| 83eb349f-fea5-11ed-ba66-0242ac110002:1-8,
9b633c56-fea5-11ed-b82c-0242ac110003:1-8 |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> select @@global.gtid_executed;  # 从查看
+------------------------------------------------------------------------------------+
| @@global.gtid_executed                                                             |
+------------------------------------------------------------------------------------+
| 83eb349f-fea5-11ed-ba66-0242ac110002:1-8,
9b633c56-fea5-11ed-b82c-0242ac110003:1-8 |
+------------------------------------------------------------------------------------+


# 可通过GTID_SUBSET函数进行比对； SELECT GTID_SUBSET(master_gtid_executed, slave_gtid_executed);
mysql> SELECT GTID_SUBSET('83eb349f-fea5-11ed-ba66-0242ac110002:1-8,9b633c56-fea5-11ed-b82c-0242ac110003:1-8','83eb349f-fea5-11ed-ba66-0242ac110002:1-8,9b633c56-fea5-11ed-b82c-0242ac110003:1-8');
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GTID_SUBSET('83eb349f-fea5-11ed-ba66-0242ac110002:1-8,9b633c56-fea5-11ed-b82c-0242ac110003:1-8','83eb349f-fea5-11ed-ba66-0242ac110002:1-8,9b633c56-fea5-11ed-b82c-0242ac110003:1-8') |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                    1 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

从库关闭只读开启读写，转为新主库

172.17.0.2 原主
172.17.0.3 原从

# 原从库操作
mysql> stop slave;
mysql> reset slave all;    #从库停掉复制进程并清空主从信息

mysql> set global read_only=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> set global super_read_only=OFF;      # 从库关闭只读模式
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'172.17.0.2' identified by '123456';
mysql> flush privileges;      #从库创建复制用户

# 原主库操作； 设置执行新主库的复制链路，转为新备库，完成主从切换
mysql> CHANGE MASTER TO \
MASTER_HOST='172.17.0.3', \
MASTER_PORT=3306, \
MASTER_USER='repl', \
MASTER_PASSWORD='123456', \
MASTER_AUTO_POSITION=1;

mysql> show slave;
mysql> show slave status\G;

应用流量切向新主库

Previous11、如何优雅备份MySQL？Next13、PXC集群

Last updated 11 months ago