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 小结
- 数据库操作系统环境一致,关闭selinux、iptables
- 配置my.cnf,主库配置binlog、从库注释binlog,server-id不能相同
- 主库创建同步账户,授权replication slave
- 整库锁表(flush table with read lock),查看binlog位置
- 开启新session备份数据
- 主库解锁(unlock table)
- 从库恢复数据
- 主库查看binlog位置(show master status\G;),从库配置参数(change master to ...)
- 从库开启复制(start slave;)
- 从库检查复制状态(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 | 线程停止 |
相关链接