十三、MySQL 主从复制

---

一、MySQL 主从复制

1. 主从复制原理

1. 主库 有一个 工作线程 I/O dump thread（转储线程）。
2. 从库 有两个工作线程 I/O thread 和 SQL thread。
3. 主库 通过 I/O dump thread 给 从库 I/O thread 传送 binlog 日志。

• 主从同步过程中：

3. 主库 把接收的 SQL 请求，记录到自己的 binlog 日志中。
4. 从库 的 I/O thread 去请求主库的 binlog 日志，并将 binlog 日志写到 中继日志 中。
5. 从库 的 SQL thread 重做 中继日志 中的 SQL 语句。
   

---

2. 主从复制前提

1. 主 DBserver 和 从 DBserver 数据库的版本需一致。
2. 主 DBserver 和 从 DBserver 数据库数据名称需一致。
3. 主 DBserver 需开启二进制日志。
4. 主 DBserver 和 从 DBserver 的 server_id 都必须唯一。

---

二、MySQL 主服务配置

1. 修改 my.cnf 文件【主】

[mysqld]
# 主服务器唯一ID（一般取IP最后一段）
server-id=101
# 启用二进制日志（文件名称）
log-bin=mysql-bin

# 需要同步的数据库
binlog-do-db=db1
# 不需要同步的数据库
binlog-ignore-db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys

# 设置 binlog 日志格式（MySQL 默认采用 statement，建议使用 mixed）
binlog_format=MIXED

---

1. MySQL 复制主要有三种方式

1. 基于 SQL 语句的复制（SBR，statement-based replication），对应的 binlog 格式 STATEMENT。
2. 基于 行的复制（RBR，row-based replication），对应的 binlog 格式 ROW。
3. 混合模式复制（MBR，mixed-based replication），对应的 binlog 格式 MIXED。

---

2. binlog_format

1. STATEMENT 模式（SBR）

1. 每一条修改数据的 SQL 语句，会记录到 binlog 中。
2. 优点：并不需要记录每一条 SQL 语句和 每一行的数据变化，减少了 binlog 日志量，节约 IO，提高性能。
3. 缺点：在某些情况下会导致（master-slave）中的数据不一致。
   如：sleep()、last_insert_id()、user-defined、functions(udf) 等函数。

2. ROW 模式（RBR）

1. 不记录每条 SQL 语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。
2. 而且不会出现某些特定情况下的 存储过程、function、trigger 的调用和触发，无法被正确复制的问题。
3. 缺点：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨。

---

3. MIXED 模式（MBR）

1. 以上两种模式的混合使用，MySQL 会根据执行的 SQL 语句，选择日志的保存方式。
2. 一般的复制使用 STATEMENT 模式保存 binlog 日志。
3. 对于 STATEMENT 模式无法复制的操作，使用 ROW 模式保存 binlog 日志。

---

2. 重启 Master 主服务

service mysqld restart
systemctl restart mysqld

---

3. 创建连接帐户并授权

1. 一般不用 root 帐号，% 表示所有客户端都可以连，只需要帐号密码正确。
2. 此处可用具体 客户端IP 代替，如：192.168.1.137 加强安全。

# 主库 创建用户及授权
# CREATE USER 'slave1'@'172.20.0.3' IDENTIFIED BY '123456';

# 建立 slave 帐户
mysql>GRANT FILE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
# 授权 slave 帐户
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%' identified by '123456'; 
mysql>GRANT ALL ON test.* TO 'test'@'%';
# 刷新权限
mysql>FLUSH PRIVILEGES;

# 查看 MySQL 当前有哪些用户
mysql>SELECT user,host FROM mysql.user;

---

4. 查看 Master 主服务状态

mysql>show master status \G;

# 删除所有 index file 中记录的 binlog 文件
# 将日志索引文件清空，创建一个新的日志文件，通常仅用于第一次 搭建主从关系时的主库。
mysql>reset master

---

三、MySQL 从服务配置

1. 修改从 mysqld.cnf 文件

[mysqld]
# 从服务器唯一ID（一般取IP最后一段）
server-id=102
relay-log=mysql-relay

---

2. 配置 从服务 连接 主服务

show master status;

• 查看数据库状态
  

# 从库 创建连接 主库的 IP、账户、密码、日志文件、位置
mysql>change master to master_host='192.168.1.137', master_port=3306, master_user='slave', master_password='123456', master_log_file='mysql-bin.002142', master_log_pos=385963

# master_port 服务器端口号（无引号）。
# master_user 执行同步操作的数据库账户。
# master_log_file 通过`show master status;`中看到的`File`的值。
# master_log_pos 通过`show master status;`中看到的`Position`的值（无单引号）。

---

3. 启动 Slave 从服务

mysql>start slave; 
# 不带任何参数，表示同时启动 I/O线程 和 SQL线程。
# I/O线程 从主库读取`binlog`，并存储到`relaylog`中继日志文件中。
# SQL线程 读取中继日志，解析后在从库重放。

# 完成停止 I/O线程 和 SQL线程 的操作。
mysql>stop slave;

---

4. 查看 Slave 从服务状态

mysql>show slave status \G;
# 有两个 yes 才 OK，假如有 No 或者 Connecting，请看下方日志 或者 找到 MySQL 日志文件查看具体问题。
# Slave_IO_Running: Yes（此状态必须 YES）
# Slave_SQL_Running: Yes（此状态必须 YES）
# 注意：Slave_IO 及 Slave_SQL 进程必须正常运行（即 YES 状态）否则都是错误的状态（如：其中一个 NO 均属错误）

---

1. Slave_IO_Running = No

mysql>show master status;
# master_log_file=File, 
# master_log_pos=Position;

---

2. Slave_SQL_Running = No

# 一般是事务回滚造成的
mysql>stop slave;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>start slave;

---

3. 错误：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

1. 原因：MySQL 是克隆的系统，所以 MySQL 的 uuid 是一样的，所以需要修改。
2. 解决：删除 /var/lib/mysql/auto.cnf 文件，重新启动服务。

---