MySQL 主从复制与读写分离

一、概述和原理

主从复制就是把主库的 DDL 和 DML 操作通过二进制日志传到从库，从库把这些日志重新执行一遍（也叫重做），最后从库的数据和主库保持一致。

MySQL 支持一台主库同时向多台从库复制，从库也可以再作为别的从库的主库，这样就串成了链状复制。

主要好处就三条：主库出问题能快速切到从库继续服务、实现读写分离降低主库压力、备份可以在从库上做不影响主库。

要注意的是：数据同步是可能存在延迟的，总览就是这张图：

原理不复杂，还是基于二进制日志做的数据同步，而二进制日志记录的就是数据库、表、数据的变更：

从上图看，复制分三步：

1. Master 主库在事务提交时，把数据变更记录在二进制日志文件 Binlog 中
2. 从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log
3. slave 重做中继日志中的事件，把改变反映到它自己的数据上

二、主库配置

1. 初始化配置

先说个前提：不同的 LTS 版本之间复制是完全可以的，常见的情况就是主库版本低、从库版本高。

两台服务器的防火墙配置

下面这张图是经典的内网两台机器（192.168.200.200 主、192.168.200.201 从）的放行方式：

我这台是 Ubuntu 22.04，先按装包列表把 MySQL 装上：

# 在22.04操作
dpkg -i mysql-common_9.7.1-1ubuntu22.04_amd64.deb \
       mysql-community-client-plugins_9.7.1-1ubuntu22.04_amd64.deb \
       mysql-community-client-core_9.7.1-1ubuntu22.04_amd64.deb \
       mysql-community-client_9.7.1-1ubuntu22.04_amd64.deb \
       mysql-client_9.7.1-1ubuntu22.04_amd64.deb \
       mysql-community-server-core_9.7.1-1ubuntu22.04_amd64.deb \
       mysql-community-server_9.7.1-1ubuntu22.04_amd64.deb \
       mysql-server_9.7.1-1ubuntu22.04_amd64.deb

# mysql 安装、初始化、配置完了之后
# 在两个服务器指定3306/tcp规则

root@VM-4-16-ubuntu:~# ufw allow 3306/tcp
Rule added
Rule added (v6)

# 查看状态

root@VM-4-16-ubuntu:~# ufw status
Status: active

To                         Action      From
--                         ------      ----
22/tcp                     ALLOW       Anywhere                  
80/tcp                     ALLOW       10.1.0.12                 
80/tcp                     ALLOW       10.1.0.0/16               
3306/tcp                   ALLOW       Anywhere                  
22/tcp (v6)                ALLOW       Anywhere (v6)             
3306/tcp (v6)              ALLOW       Anywhere (v6)  

# 如果是指定IP
sudo ufw allow from 192.168.1.100 to any port 3306 proto tcp

# 删去防火墙规则
# 1. 看规则编号
sudo ufw status numbered

# 2. 删
sudo ufw delete [编号]

顺手把 Ubuntu 的 ufw 和 CentOS 的 firewalld 放一起对着记，两边干的是同一件事，命令完全不一样：

两个小坑记一下：firewalld 加了 --permanent 之后必须 firewall-cmd --reload 才生效；ufw 装完默认是关的，不 sudo ufw enable 的话光 allow 没意义。

2. 主库配置和高版本命令

主库这边就两个参数，server-id 要唯一，read-only=0 是可读写：

# 在LTS-22.04配置主库参数
vim /etc/mysql/conf.d/master.cnf

[mysqld]

server-id=1
read-only=0 # 读写（只约束普通用户，root 不受限制）

systemctl restart mysql
# 不报错就配置成功

注意 read-only 管的是普通用户，root 照样能写；真想连 root 都锁住，才用 super-read-only=1。

然后是给从库准备一个专门用来拉 binlog 的账号：

从库的 I/O 线程会向主库发起连接请求，这个权限就是主库用来验证"允不允许这台从库来拉我的日志"的。

拉取主库的 binlog，权限肯定要在主库上配！

# 创建从库连接主库的账号
create user 'slave'@'%' identified by '密码';
# replication 译为复制
# replication slave 是主从复制的核心权限
grant replication slave on *.* to 'slave'@'%';

# 查看二进制日志坐标
show master status; # 旧命令，8.4 之前可用
show source status; # 旧命令，8.4 之前可用

mysql> show binary log status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 |      693 |              |                  | ed8aa148-a1bf-11f1-977c-52540025d746:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+

# file代表写入的日志文件（当前）
# Position 指代位置，标明了从库读取主库的二进制日志位置
# 下一块细说

主库配置完，暂时别再执行增删改和 DDL 了，不然坐标又变了。

MySQL 8.4 之后一堆复制相关的命令都改名了，这张表对一下：

三、从库配置

1. 从库参数和启动复制

从库的 server-id 必须和主库不一样，read-only=1 把这台机器锁成只读：

vim /etc/mysql/conf.d/slave.cnf

[mysqld]

server-id=2 # 该值设置唯一
read-only=1 # 只读（只约束普通用户，root 不受限制）

# 如果想对root也设置只读：
super-read-only=1

systemctl restart mysql

# 设置主库的相关配置
# mysql 8.0.23 及以上版本
change replication source to source_host='xxx.xxx.xxx.xxx', source_user='xxx', source_password='xxx', source_log_file='xxx', source_log_pos=xxx;

# mysql 8.0.23 之前的版本
change master to master_host='xxx.xxx.xxx.xxx', master_user='xxx', master_password='xxx', master_log_file='xxx', master_log_pos=xxx;

# 从库配置

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='1.14.137.231',
    SOURCE_USER='slave',
    SOURCE_PASSWORD='2T:u:ujUT-sTn7m',
    SOURCE_LOG_FILE='binlog.000004',
    SOURCE_LOG_POS=198;

# 开启同步操作（在从库执行，因为是从库拉主库）
# mysql 8.0.22 及之后版本
start replica;

# mysql 8.0.22 之前版本
start slave;

mysql> start replica;
Query OK, 0 rows affected (0.032 sec)
# 主从复制启动

新旧参数名也是对应的，改命令名的时候照这张表换：

云服务器的安全组也要放行，主库可以只允许指定 IP 访问 3306：

还有一条最容易写错的：SOURCE_LOG_FILE 只能写主库配置时看到的那个序号，比如主库 show binary log status 看到的是 binlog.000004，从库就得写 000004，写别的文件名从库会去拉一个不存在的文件，直接连不上：

2. 查看复制状态和报错

# 查看主从同步状态
# mysql 8.0.22 及之后版本
show replica status\G; # 表太大了列转为行查看

# mysql 8.0.22 之前版本
show slave status\G;

mysql> SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 1.14.137.231
                  Source_User: slave
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000004
          Read_Source_Log_Pos: 198
          # 中继日志
               Relay_Log_File: VM-4-16-ubuntu-relay-bin.000002
                Relay_Log_Pos: 325
        Relay_Source_Log_File: binlog.000004
        # 必须都是yes
           Replica_IO_Running: Yes # 读取二进制日志、写入中继日志
          Replica_SQL_Running: Yes # 读取中继日志、同步数据
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 198
              Relay_Log_Space: 545
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: Yes
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
# 这里可以看报错，为什么连不上等等
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: ed8aa148-a1bf-11f1-977c-52540025d746
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 907cd40a-a108-11f1-9f2b-5254008757a3:1-11
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.000 sec)

ERROR: 
No query specified

最后那个 ERROR: No query specified 是用 \G 结尾没写分号导致的，不影响结果，忽略就行。

关键就看四个地方：Replica_IO_Running 和 Replica_SQL_Running 必须都是 Yes，Seconds_Behind_Source 是 0，Last_IO_Errno / Last_SQL_Errno 都是 0。连不上的原因基本都写在 Last_IO_Error 里。

四、主从配置排障

我这套环境：主库 IP 是 1.14.137.231，从库就是本机 VM-4-16-ubuntu，两边都是 MySQL 9.7.1（主库是 Ubuntu 24.04 的编译包，从库是 Ubuntu 22.04 的编译包）。一开始的现象是从库 Replica_IO_Running: Connecting，连不上主库，报的是：

Can't connect to MySQL server on '1.14.137.231:3306' (111)

排查是一层一层剥开的：

具体每个错误对上了哪个原因：

最后验证的时候，这几个字段是对的才算成了：

这次踩下来值得记的几条：

1. 跨 LTS 版本复制从高主低的时候，从库要能兼容主库的文件格式
2. server-id 必须唯一，所有参与复制的节点都不能一样
3. bind-address 必须是 0.0.0.0 或者 *，写 127.0.0.1 外部根本连不进来
4. 云安全组优先级高于系统防火墙，ufw 放行不等于安全组放行，两头都得查
5. 主库一重启 binlog 坐标就变，每次配复制前都要重新 SHOW BINARY LOG STATUS
6. 权限要分两类：REPLICATION SLAVE 是复制必需的，REPLICATION CLIENT 是查看状态才要的
7. 密码里有特殊字符时注意引号，双引号比单引号省事

从版本不兼容、server-id 冲突、网络不通、权限不足一路排到坐标过期，最后用正确的坐标 binlog.000004:198 重配复制，主从才同步上。八个问题里最坑的是坐标过期，前六个都改对了只要坐标是旧的，一样连不上。

五、测试方法和小结

在主库做数据增删改加 DDL，然后去从库查，能看到就说明通了（这里就不演示了）。

这样配出来的主从，是从当前二进制日志往后的数据才同步到从库的。想同步之前的日志数据，得先把主库的数据导成 sql 脚本，在从库上执行一遍，保证两边数据一致之后再开同步。

小结就是这一张：

六、读写分离

先说清楚：读写分离是建立在 binlog 主从复制之上的，从库先得有一份能用的数据。另外 MyCat 这个项目社区已经停止活跃了，知道思路就行。

1. 一主一从

读操作连从节点，写操作连主节点 —— 道理简单，但要是让应用程序自己判断，就得不断切换数据源，很麻烦：

所以中间加一层代理，应用程序只连 MyCat，操作先到 MyCat，再由 MyCat 分发到后面的数据库：

主要用的就是 MyCat 配置里的 writeHost 和 readHost：增删改走主库，查询走从库，读写分离就成了：

2. 一主一从的准备

先在主库建库建表塞几条数据，去从库看能不能看到：

create database test_repl;
use test_repl;
create table t1 (id int, name varchar(20));
insert into t1 values (1, 'hello');
insert into t1 values (2, 'world');

新旧命令还是那张表，配复制的时候对照着用：

如果主库那边出错，很可能直接把主从复制带挂，修复办法就是把从库的数据清掉重新配一遍，比如 sudo rm -rf /var/lib/mysql/*。但这一步会把从库上所有数据都清空（连系统库一起），删之前一定确认这台就是从库，而且之前同步的数据丢了就只能靠 sql 脚本找回来。

3. 一主一从的读写分离

具体的配置详见视频，实际工作里用得很少，这里就不写了。