mysql异步备份_（5.4）mysql高可用系列——MySQL异步复制（实践）

关键词：mysql复制，mysql异步复制，mysql传统异步复制

【1】实验环境

操作系统：CentOS linux 7.5

数据库版本：5.7.24

数据库架构：主从复制，主库用于生产，从库用于数据容灾和主库备机，采用默认传统的异步复制。

主库IP：192.168.1.201　　端口：3306

从库IP：192.168.1.202　　端口：3306

【2】配置启动主从复制参数及准备工作

其实，为了避免主从切换，建议主从都加上这些参数(唯一不同的就是server_id)

#【2.1】配置my.cnf配置文件

#主库参数# 主库增加参数

[mysqld]

log-error_verbosity=3

log_bin=/mysql/log/3306/mysql-bin #开启binlog

log_bin_index=/mysql/log/3306/mysql-bin.index

binlog_format=row

binlog_rows_query_log_events=on

max_binlog_size=2048#主库修改参数

#bind-address=192.168.1.201bind-address=0.0.0.0

server_id=2013306 #从库设置为2023306expire_logs_days=7#超过7天的binlog清理

innodb_support_xa=1binlog_cache_size=1M

log_bin_trust_function_creators=1#同步存储过程、函数、触发器

innodb_flush_log_at_trx_commit=1sync_binlog=1transaction-isolation=read-committed

#从库参数

relay_log=/mysql/log/3306/relaylog/mysql-relay.log

log-slave-updates=1read_only=1slave-parallel-type=LOGICAL_CLOCKslave-parallel-workers=4master_info_repository=table　　　　#master_info 会记录到 mysql.slave_master_info

relay_log_info_repository=table　　#relay_log 会记录到，mysql.slave_relay_log_info

relay_log_recovery=1slave_skip_errors=ddl_exist_errors

slave_preserve_commit_order=1

#注意，设置完参数之后，必须要记得重启mysql，否则不生效。

#直接可以复制代码

#replication_new

log_bin=/mysql/log/3306/mysql-bin #开启binlog

log_bin_index=/mysql/log/3306/mysql-bin.index

binlog_format=row

binlog_rows_query_log_events=on

max_binlog_size=2048bind-address=0.0.0.0server_id=2013306expire_logs_days=7#超过7天的binlog清理

innodb_support_xa=1binlog_cache_size=1M

log_bin_trust_function_creators=1#同步存储过程、函数、触发器

innodb_flush_log_at_trx_commit=1sync_binlog=1transaction-isolation=read-committed

relay_log=/mysql/log/3306/relaylog/mysql-relay.log

log-slave-updates=1read_only=1slave-parallel-type=LOGICAL_CLOCKslave-parallel-workers=4master_info_repository=table #master_info 会记录到 mysql.slave_master_info

relay_log_info_repository=table #relay_log 会记录到，mysql.slave_relay_log_info

relay_log_recovery=1slave_skip_errors=ddl_exist_errors

slave_preserve_commit_order=1

#【2.2】创建复制通信需要的账户(主从都建)

create user 'rpl'@'192.168.1.%' identified by '123456';

grant replication slave on *.* to 'rpl'@'192.168.1.%';

flush privileges;select user,host from mysql.user;

#使用该账户在从库上对主库进行登录，能登录上才算成功；

#【2.3】核验复制账户是否成功，在主从库上使用该账号登录对方

mysql -u'rpl' -p123456 -h192.168.1.201

#如果不行请核验

(1)网络可以互相ping通

(2)双方端口是否侦听开启：netstat -an|grep 3306

(3)防火墙策略：service iptables stop;(这里只是测试，我就直接关闭了哈)

(4)selinux策略：getenforce(查看状态) setenforce 0 (关闭)

【3】开始主从复制

【3.1】记录主库的binlog日志的position

mysql下操作：(锁表，获取一致性)如果业务正在运行，停机状态的话就无所谓。

flush tables with read lock;

然后show master status;

记录下来，mysql-bin.000001　　154

【3.2】如果是已经有数据的数据库，记得初始化从库(即还原当前状态到从库)

(1)逻辑方式：mysqldump。然后scp拷贝过去。然后登录上从库的mysql，直接把dump的文件加载进去即可。(如果利用历史的 mysqldump 备份，可以查看mysqldump里面记录的Binlog日志文件名及位置)

(2)物理方式：直接拷贝，备份删除从库原有data目录，然后把主库的data目录复制过去，复制到从库后记得 删除/修改 拷贝过来data/下的auto.cnf，否则uuid会一样，导致无法复制。(如果用历史的物理备份，可以直接还原到从苦后，show master查看从库的文件和位置，或者直接去查看Binlog位置也可以)

(3)不管是物理还是逻辑方式，CP或者mysqldump完后，就都可以解锁主库了

(4)在主库的mysql登录下，使用命令，unlock tables;(当业务正在运行，配合上面的锁库解锁)

【3.3】从库复制操作设置

stop slave;

change master to

master_host='192.168.1.201',

master_user='rpl',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=154;

-- 延迟复制参数

-- change master to master_delay=60 -- 这里的60单位是秒，0为不延迟

-- 5.7变成了 SQL_Delay 参数

start slave;

show slave status\G

【3.4】核验

【3.4.1】查看从库状态

主要是确定error字样的位置没有错误

【3.4.2】数据操作同步验证

主库操作：　　　　　　　　　　　　　　　　　　　　　　　　　　查看从库：

　　

成功！

【3.4.3】查看进程(从库上)

【4】相关审查

【4.1】mysql数据库下的系统表

master_info_repository=table　　　　#master_info 会记录到 mysql.slave_master_info

relay_log_info_repository=table　　#relay_log 会记录到， mysql.slave_relay_log_info

【4.2】并行复制(5.7.4)

很明显，ID1 是IO线程，ID2是SQL 线程(抽取relay log日志到mysql应用来)，下面的3-6就是我们的回放/重做 relay log的执行线程。

【4.3】修整

(1)如果都关机了，需要先开主库，再开从库

【5】故障切换

#【5.0】主从不同步

1.表一定要有主键

2.刚开始配置的时候，如果数据异常，重新配置一次

3.从库 停止slave线程

4.主库 reset master;(如果是已经有数据的库，那么需要重新备份恢复到从库进行从库数据初始化，然后执行本文步骤)

5.从库 reset master;reset slave all;

reset slave ; 其实是清理  mysql.slave_master_info(即之前配置的master_log_file等信息)

如果多个从库都需要清理，可以reset slave all;

#【5.1】主从挂了怎么快速切换恢复

1.备份主库上的data目录 mv data data.org

2.从库上scp data目录到主库上 scp -r data 主库:/databases/data/3306/

3.检查my.cnf配置文件，主要保证innodb_log_file_size和innodb_log_files_in_group要和从库设置一致。

4.注意修改data目录里的auto.cnf文件，里面记录的是服务器的uuid，请修改回主库data.org目录下的auto.cnf文件。

5.启动mysql，看错误日志，应该可以正常启动了

6.恢复后的主库上，清除复制关系 mysql> reset slave all;

7.原从库启动数据库，并重做主从 mysql> reset slave all；

8.mysql> change master to ...

#【5.2】一主一从结构迁移从库架构图

具体做法是这样：

研发将 102 的读业务切到主库；

确认 102 MySQL 状态(主要看 PROCESS LIST)，观察机器流量，确认无误后，停止 102 从节点的服务；

103 新建 MySQL 实例，建成以后，停止 MySQL 服务，并且将整个数据目录 mv 到其他地方做备份；

将 102 的整个 mysql 数据目录使用 rsync 拷贝到 103；

拷贝的同时，在 101 授权，使 103 有拉取 binlog 的权限(REPLICATION SLAVE, REPLICATION CLIENT)；

待拷贝完成，修改 103 配置文件中的 server_id，注意不要和 102 上的一致；

在 103 启动 MySQL 实例，注意配置文件中的数据文件路径以及数据目录的权限；

进入 103 MySQL 实例，使用 SHOW SLAVE STATUS 检查从库状态，可以看到 Seconds_Behind_Master 在递减；

Seconds_Behind_Master 变为 0 后，表示同步完成，此时可以用 pt-table-checksum 检查 101 和 103 的数据一致，但比较耗时，而且对主节点有影响，可以和开发一起进行数据一致性的验证；

和研发沟通，除了做数据一致性验证外，还需要验证账号权限，以防业务迁回后访问出错；

做完上述步骤，可以和研发协调，把 101 的部分读业务切到 103，观察业务状态；

如果业务没有问题，证明迁移成功。

参考：