MySQL数据库复制技术 Part 2 : 主从复制

1 MySQL数据库主从复制

        MySQL数据库主从复制技术是将数据库更新的binlog日志发送至从库服务器，从库数据库线程读取日志中的SQL语句并应用到MySQL数据库中，进而实现主从复制。

1.1 企业应用场景

• 从库作为主库的实时备份
• 主从数据库实现读写分离，从库实现负载均衡
• 应用重要等级划分，一对一配置从库

1.2 主从复制关键点

• 主从复制是异步的逻辑SQL复制
• 主库binlog线程，从库I/O线程和SQL线程
• MySQL 5.6，从库的SQL线程可以有多个
• 主库、从库均需要开启binlog
• 多从库时，server-id不能相同
• binlog记录数据库内容更改的SQL语句

2 MySQL数据库主从复制配置

2.1 基础环境

角色	hostname	eth0（业务）	eth1（主从同步）
master	node1	10.11.12.1	172.16.33.1
slave	node2	10.11.12.2	172.16.33.2

2.2 主库配置

--- 开启binlog，配置server-id

[mysqld]

server_id = 1
log_bin = /app/mysql5.7/logs/ocean-bin

--- 核验配置文件

egerp "server_id | log_bin" /etc/my.cnf

--- 重启数据库

/etc/init.d/mysqld restart

--- 核验数据库参数

mysql -e "show variables like 'log_bin';"

mysql -e "show variables like 'server_id';"

--- 主库创建数据同步的账号

grant replication slave on *.* to 'rep'@'10.11.12.%' identified by 'root1234';

flush privileges;

--- 核验账号

SELECT user,host FROM mysql.user;

SHOW GRANTS FOR rep@'10.11.12.%';

--- 锁表只读（会话窗口勿关闭）

flush table with read lock;

show variables like '%timeout%';

--- 查看主库状态

show master status;

--- 备份主库数据

mysqldump -A -B | gzip > /bak/ocean_$(date +%F).sql.gz

2.3 从库配置 

--- 开启binlog，配置server-id

[mysqld]

server_id = 2
#log_bin = /app/mysql5.7/logs/ocean-bin

--- 核验配置文件

egerp "server_id | log_bin" /etc/my.cnf

--- 重启数据库

/etc/init.d/mysqld restart

--- 核验数据库参数

mysql -e "show variables like 'log_bin';"

mysql -e "show variables like 'server_id';"

--- 备库恢复数据

zcat /bak/ocean_2019-04-01.sql.gz | mysql

--- 配置主从参数

CHANGE MASTER TO MASTER_HOST='10.11.12.1',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='root1234',MASTER_LOG_FILE='ocean-bin.000006',MASTER_LOG_POS=668;

--- 核验master.info中的配置信息

more /app/mysql5.7/data/master.info

--- 启动从库，开启复制

START SLAVE;

--- 查看主从复制状态

show slave status\G;

mysql -e "show slave status\G;"

--- 查看主从复制关键参数

mysql -e "show slave status\G;" | egrep "IO_Running | SQL_Running | _Behind_Master"

• Slave_IO_Running：I/O线程读取binlog日志，写入从库的中继日志（relay-log）
• Slave_SQL_Running：SQL线程读取中继日志的数据，转换成SQL语句应用到从库
• Seconds_Behind_Master：从库的延迟时间（秒为单位）

2.4 小结

1. 数据库操作系统环境一致，关闭selinux、iptables
2. 配置my.cnf，主库配置binlog、从库注释binlog，server-id不能相同
3. 主库创建同步账户，授权replication slave
4. 整库锁表（flush table with read lock），查看binlog位置
5. 开启新session备份数据
6. 主库解锁（unlock table）
7. 从库恢复数据
8. 主库查看binlog位置（show master status\G;），从库配置参数（change master to ...）
9. 从库开启复制（start slave;）
10. 从库检查复制状态（show slave status\G;）

附录

        查看MySQL线程

show processlist\G;

        主库I/O线程 

I/O线程	说明
Sending binlog event to slave	读取binlog，发送至从服务器
Finished reading one binlog;switching to next binlog	完成binlog数据读取，正在打开下一个binlog
Has sent all binlog to slave;waiting for binlog to be updated	完成binlog更新数据的读取和发送，等待binlog更新数据
Waiting to finalize termination	线程停止

        从库I/O线程

I/O线程	说明
Connecting to master	尝试连接主服务器
Checking master version	成功连接主库的临时状态
Registering slave on master
Requesting binlog dump	发出请求，索取binlog
Waiting to reconnect after a failed binlog dump request	binlog转储失败，线程进入睡眠状态
Reconnecting after a failed binlog dump request	尝试重连主库，索取binlog
Waiting for master to send event	线程连接主库，等待binlog数据
Queueing master event to the relay log	读取binlog更新，写入relay-log
Waiting to reconnect after a failed master event read	连接中断，线程尝试重连
Reconnecting after a failed master event read	尝试重连主库，等待binlog数据

        从库SQL线程

SQL线程	说明
Reading event from the relay log	读取relay-log，处理事务
Has read all relay log;waiting for the slave I/O thread to update it	处理所有事务，等待I/O线程写入数据库
Waiting for slave mutex on exit	线程停止

 

 

 

相关链接

MySQL数据库复制技术 Part 1 : 复制技术介绍

MySQL数据库复制技术 Part 3 : 级联复制

MySQL数据库复制技术 Part 4 : 双主复制

MySQL数据库复制技术 Part 5 : 半同步复制

MySQL数据库复制技术 Part 6 : GTID复制