MySQL 主从复制
主从复制前提
- Master 和 Slave 时间同步
- 至少 2 台以上实例，需要有角色划分的标识，server_id
- Master 需要开启 Binlog
- Master 建立专用复制用户
- Slave 提前录入Master 的大部分数据
- Slave 确认复制起点
- 开启专用复制线程（主库 Dump 线程；从库 IO 线程，SQL 线程）
主从复制原理
文字描述：
主库配置：
# 主库配置 server_id ，开启 bin-log （/etc/my.cnf）
# 主库创建授权主从复制用户（grant replication slave）
# 获取主库的 binlog 信息（file，position）
# 导出主库数据（mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql）
从库配置：
# 从库配置 server_id
# 确认使用主库的主从复制用户可以连接主库
# 导入主库数据（mysql < full.sql）
# 配置主从（change master to），需要 Master节点 IP，用户，端口，binlog-file，binlog-pos
# binlog 配置信息可以从 full.sql 中 22 行获得
工作原理：
# 1. Start slave 之后，从库开启 IO线程 和 SQL线程
# 2. 主库开启 Dump线程
# 3. IO线程 向 Dump线程 发起询问，询问是否有新的数据
# 4. Dump线程 被询问，查找是否有新数据，若有返回给 IO线程
# 5. IO线程 拿到数据，写入TCP缓存
# 6. TCP缓存拿到数据，写入relay-log，并返回给 IO线程一个 ACK报文
# 7. IO线程 收到 ACK报文，会记录当前位置到 master.info（binlog 位置点）
# 8. SQL线程 会主动读取 relay-log，执行主库执行的sql语句
# 9. 执行后，将执行到的位置点，记录到 relay-log.info（relay-log 位置点）
涉及的文件 & 线程
Master 主库
Binlog：主库执行的 SQL语句
Binlog_Dump 线程：用来接收从库的请求，并投递 Binlog 给从库
mysql> show processlist;
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 2 | rep | 172.16.1.122:44504 | NULL | Binlog Dump | 282 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 3 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
Slave 从库
Relaylog：中继日志，记录从主库接收的 binlog
master.info：连接主库的信息，以及记录主库 binlog 信息，会随着同步进行更新
relay-log.info：记录sql线程执行到了那里，下次从哪里开始执行
I/O 线程：连接主库，请求 Binlog，接收 Binlog，等待主库发送 Binlog 事件
SQL 线程：读取执行 Relaylog，等待 I/O 线程更新 Relaylog，实质上就是执行从主库接收的 Binlog 事件（Relaylog 中记录着 Binlog 事件）
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 3 | system user | | NULL | Connect | 1915 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 1635 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 6 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
主从复制的搭建
主库配置
配置 /etc/my.cnf
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/service/mysql/data/mysql-bin
[root@db03 ~]# /etc/init.d/mysqld start
建立专用复制用户
mysql> grant replication slave on *.* to rep@‘172.16.1.%‘ identified by ‘123‘;
Query OK, 0 rows affected (0.03 sec)
查看 Binlog 信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 326 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
导出主库全部数据
[root@db03 data]# mysqldump -uroot -p -A --master-data=2 --single-transaction > /tmp/full.sql
[root@db03 data]# scp /tmp/full.sql 172.16.1.52:/tmp/
从库配置
配置 /etc/my.cnf
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
[root@db02 ~]# /etc/init.d/mysqld start
验证专用复制用户
[root@db02 ~]# mysql -urep -p -h172.16.1.53
导入主库全部数据
[root@db02 ~]# mysql -uroot -p123 < /tmp/full.sql
配置主从信息
mysql> change master to
-> master_host=‘172.16.1.121‘,
-> master_user=‘rep‘,
-> master_password=‘123‘,
-> master_log_file=‘mysql-bin.000005‘,
-> master_log_pos=640;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
开启从库线程
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
检验是否成功
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主从数据库出错
I/O 线程出错
mysql> show slave status\G
Slave_IO_Running: No
Slave_SQL_Running: Yes
mysql> show slave status\G
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
# 排查思路
1.网络
[root@db02 ~]# ping 172.16.1.53
2.端口
[root@db02 ~]# telnet 172.16.1.53 3306
3.防火墙
4.主从授权的用户错误
5.反向解析
skip-name-resolve
6.UUID或server_id相同
SQL 线程出错
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
# 原因：
1.主库有的数据，从库没有
2.从库有的数据，主库没有
# 处理方式三：正解
重新同步数据，重新做主从