MYSQL-mysql常见主从复制技术实现全过程(M-S、M-M、M-S-S)

傷城~ 2022-12-09 05:16 189阅读 0赞

### 文章目录 ###

*  MYSQL-mysql常见主从复制技术实现全过程
 *   *  1. mysql主从同步（M-S）
     *  2. mysql双向主从同步复制（M-M）
     *  3. 部署MSS模型
     *  4. mysql主从同步复制常见报错
     *   *  4.1 因主从库的数据不一致导致从库同步复制停止
         *  4.2 从库对应的二进制主库日志（Master\_Log\_File）与主库上的二进制日志文件不一致

# MYSQL-mysql常见主从复制技术实现全过程 #

## 1. mysql主从同步（M-S） ##

**主从同步原理：**

![img][]

**实验环境：**

<table> 
 <thead> 
  <tr> 
   <th>主机名</th> 
   <th>IP地址</th> 
   <th>mysql版本</th> 
   <th>主从复制的身份</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>master</td> 
   <td>192.168.8.198</td> 
   <td>mysql5.7.31</td> 
   <td>主Master</td> 
  </tr> 
  <tr> 
   <td>slave1</td> 
   <td>192.168.8.197</td> 
   <td>mysql5.7.31</td> 
   <td>从Slave</td> 
  </tr> 
 </tbody> 
</table>

**在master上创建测试数据：**

# 创建用于主从同步的数据库
    [root@master ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.31 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> create database test;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use test;
    Database changed
    mysql> create table emp(id int primary key,name varchar(20));
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> insert into emp values(1,'haha');
    Query OK, 1 row affected (0.05 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    +----+------+
    1 row in set (0.00 sec)

**配置master为mysql主：**

# 添加以下内容，表示该mysql服务器为主，标识符为1，启动二进制日志，可同步test数据库，不可同步mysql数据库。
    [root@master ~]# vim /etc/my.cnf
    [mysql]
    default-character-set=utf8
    
    [mysqld]
    default-storage-engine=INNODB
    character_set_server=utf8
    
    log-bin=mysql-bin-master
    server-id=1
    binlog-do-db=test
    binlog-ignore-db=mysql
    
    [root@master ~]# systemctl restart mysqld
    
    # set global validate_password_policy=0; 密码策略等级
    # set global validate_password_length=1; 密码长度最小值
    
    # 创建一个slave用户，可以在从服务器使用该用户连接主服务器，并且授予replication slave的权限。
    [root@master ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.31-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> grant replication slave on *.* to slave@192.168.8.197 identified by 'Abong123.';
    Query OK, 0 rows affected, 1 warning (0.05 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> exit
    Bye

**查看主mysql的状态：**

[root@master ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.31-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show master status;    # 可以看到主服务器的同步复制信息
    +-------------------------+----------+--------------+------------------+
    | File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +-------------------------+----------+--------------+------------------+
    | mysql-bin-master.000001 |      602 | test         | mysql            |
    +-------------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
    mysql> exit
    Bye
    # mysql> show binlog events\G;
    # 也可以使用以上命令查看主服务器信息。
    
    # 可以看到，主服务器上已经产生日志信息了。
    [root@master ~]# ll /var/lib/mysql/mysql-bin-master.*
    -rw-r-----. 1 mysql mysql 602 9月  19 20:50 /var/lib/mysql/mysql-bin-master.000001
    -rw-r-----. 1 mysql mysql  26 9月  19 20:45 /var/lib/mysql/mysql-bin-master.index

**在进行主从同步之前，要保证主从数据库的一致性。这里将要同步复制的test数据库导出，然后再导入到从库中。**

[root@master ~]# mysqldump -uroot -pAbong123. test > test.sql
    [root@master ~]# scp test.sql root@192.168.8.197:/root/
    root@192.168.8.197's password: 
    test.sql                                    100% 1831   407.4KB/s   00:00

**在从服务器上使用slave用户连接主库时，可以先检查主库的3306端口号是否开放。**

[root@master ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
    success
    [root@master ~]# firewall-cmd --reload
    success
    [root@master ~]# netstat -antup | grep mysql
    tcp6       0      0 :::3306                 :::*                    LISTEN      9526/mysqld    
    [root@master ~]# firewall-cmd --list-port
    3306/tcp

**使用slave用户连接主库：**

[root@slave1 ~]# mysql -uslave -p -h 192.168.8.198
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7
    Server version: 5.7.31-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;    # 由于slave用户仅有复制权限，故无法看到主库上的其他数据库。
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> exit
    Bye
    
    
    # 从库上创建test数据库，并导入复制前的数据
    [root@slave1 ~]# mysql -uroot -pAbong123.
    mysql> create database test;
    Query OK, 1 row affected (0.05 sec)
    
    mysql> exit
    Bye
    [root@slave1 ~]# mysql -uroot -pAbong123. test < test.sql
    [root@slave1 ~]# mysql -uroot -pAbong123.
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test               |
    +--------------------+
    5 rows in set (0.00 sec)

**配置从库：**

[root@slave1 ~]# vim /etc/my.cnf
    [mysqld]
    server-id=2
    [root@slave1 ~]# systemctl restart mysqld
    
    # 在从库上配置主库信息，用于连接主库
    [root@slave1 ~]# mysql -uroot -p
    Enter password: 
    mysql> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    # 修改从库所对应的主库信息。
    mysql> change master to master_host='192.168.8.198',master_port=3306,master_user='slave',master_password='Abong123.',master_log_file='mysql-bin-master.000001',master_log_pos= 602;
    Query OK, 0 rows affected, 2 warnings (0.08 sec)
    #master_host 【主服务器的IP地址】
    #master_port 【主服务器的端口】
    #master_log_file 【show master status显示的File列：mysql-bin-master.000001】
    #master_log_pos 【show master status显示的Position列：602】
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    # 查看从库状态的时候，没连上有一个报错。说是mysql服务器ID冲突了。
    mysql> show slave status\G;
                 Slave_IO_Running: No
                Slave_SQL_Running: Yes
                    Last_IO_Error: 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.

**检查并修复mysql服务器ID冲突的问题：**

可能是因为**克隆虚拟机**导致的。

# 主库
    mysql> show variables like 'server_id';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 1     |
    +---------------+-------+
    1 row in set (0.07 sec)
    # 从库
    mysql> show variables like 'server_id';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 2     |
    +---------------+-------+
    1 row in set (0.08 sec)
    
    # uuid真的冲突了！！！
    [root@master ~]# cat /var/lib/mysql/auto.cnf
    [auto]
    server-uuid=30e19a3e-f1de-11ea-99f9-000c29ebd724
    [root@slave1 ~]# cat /var/lib/mysql/auto.cnf 
    [auto]
    server-uuid=30e19a3e-f1de-11ea-99f9-000c29ebd724
    
    # 移除从库的uuid文件，然后重启mysql服务生成新的uuid文件。
    # 可以看到，和主库的不一样了。
    [root@slave1 ~]# mv /var/lib/mysql/auto.cnf /tmp/
    [root@slave1 ~]# systemctl restart mysqld
    [root@slave1 ~]# cat /var/lib/mysql/auto.cnf 
    [auto]
    server-uuid=7be7fc33-fa7c-11ea-bf9e-000c29aab0d2

**再次查看从库的连接情况：**

# 可以看到已经连上了，而且没有报错。
    mysql> show slave status\G;
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

**在主库上查看主从复制情况：**

mysql> show processlist\G;
    *************************** 1. row ***************************
         Id: 13
       User: slave
       Host: 192.168.8.197:48976
         db: NULL
    Command: Binlog Dump
       Time: 175
      State: Master has sent all binlog to slave; waiting for more updates
       Info: NULL

**至此，mysql主从同步复制已经部署完毕了。以下测试下结果：**

# 主库插入数据
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql> insert into emp values(2,'xixi');
    Query OK, 1 row affected (0.06 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    +----+------+
    2 rows in set (0.00 sec)
    
    # 可以看到从库也有数据了。
    mysql> use test;
    Database changed
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    +----+------+
    2 rows in set (0.00 sec)

**注意：由于主库被删，从库也会对应被删除，故定期对主库进行备份是必须的。**

## 2. mysql双向主从同步复制（M-M） ##

<table> 
 <thead> 
  <tr> 
   <th>host</th> 
   <th>IP地址</th> 
   <th>mysql版本</th> 
   <th>主从复制的身份</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>master1</td> 
   <td>192.168.8.198</td> 
   <td>mysql5.7.31</td> 
   <td>主master1，从slave1</td> 
  </tr> 
  <tr> 
   <td>master2</td> 
   <td>192.168.8.197</td> 
   <td>mysql5.7.31</td> 
   <td>主master2，从slave2</td> 
  </tr> 
 </tbody> 
</table>

由于主从同步复制是通过主上的二进制日志来实现的，这里先**清空下之前主从同步所产生的二进制日志**。

[root@master1 ~]# ll /var/lib/mysql/mysql-bin-master.*
    -rw-r-----. 1 mysql mysql 864 9月  20 17:10 /var/lib/mysql/mysql-bin-master.000001
    -rw-r-----. 1 mysql mysql 154 9月  20 17:10 /var/lib/mysql/mysql-bin-master.000002
    -rw-r-----. 1 mysql mysql  52 9月  20 17:10 /var/lib/mysql/mysql-bin-master.index
    [root@master1 ~]# mysql -uroot -pAbong123.
    
    mysql> reset master;     # 清空原先的master二进制日志
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show master status\G;     # 可以看到主库的情况。File和Position
    *************************** 1. row ***************************
                 File: mysql-bin-master.000001
             Position: 154
         Binlog_Do_DB: test
     Binlog_Ignore_DB: mysql
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified
    
    mysql> exit
    Bye
    # 查看下二进制日志的情况，可以看到是使用二进制日志文件mysql-bin-master.000001写入日志。
    [root@master1 ~]# ll /var/lib/mysql/mysql-bin-master.*
    -rw-r-----. 1 mysql mysql 154 9月  20 17:18 /var/lib/mysql/mysql-bin-master.000001
    -rw-r-----. 1 mysql mysql  26 9月  20 17:18 /var/lib/mysql/mysql-bin-master.index

**配置主master1、从slave2：**

①修改`/etc/my.cnf`文件为主`master1`，启动二进制日志。

[root@master1 ~]# vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin-master   # 开启二进制日志，且二进制文件前缀名为mysql-bin-master
    server-id=1     # 主master的id
    binlog-do-db=test    # 同步的数据库
    binlog-ignore-db=mysql   # 不同步的数据库
    [root@master1 ~]# systemctl restart mysqld

②创建slave用户，允许在其他主机登录，并授予复制权限。

[root@master1 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> grant replication slave on *.* to slave@192.168.8.197 identified by 'Abong123.';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> exit
    Bye

③从库测试slave用户登录主库情况

[root@master1 ~]# firewall-cmd --list-port
    3306/tcp
    [root@master2 ~]# mysql -uslave -p -h 192.168.8.198
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.7.31-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> exit
    Bye

④配置从库信息，并在从库上修改指向主库的信息，检查slave状态。

[root@master2 ~]# vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin-master
    server-id=2
    binlog-do-db=test
    binlog-ignore-db=mysqs
    
    
    # file mysql-bin-master.000001
    # position 154
    mysql> stop slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> change master to master_host='192.168.8.198',master_port=3306,master_user='slave',master_password='Abong123.',master_log_file='mysql-bin-master.000001',master_log_pos=154;
    Query OK, 0 rows affected, 2 warnings (0.06 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show slave status\G;   #可以看到从库已经连上主库了。
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

**配置主master2、从slave1：**

①修改/etc/my.cnf文件为主master2，启动二进制日志。

[root@master2 ~]# cat /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin-master
    server-id=2
    binlog-do-db=test
    binlog-ignore-db=mysql
    [root@master2 ~]# systemctl restart mysqld

②创建slave用户，允许在其他主机登录，并授予复制权限。

[root@master2 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> grant replication slave on *.* to slave@192.168.8.198 identified by 'Abong123.';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> exit
    Bye

③从库测试slave用户登录主库情况

# 开放master2上的3306端口，允许被访问。
    [root@master2 ~]# firewall-cmd --list-port
    
    [root@master2 ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
    success
    [root@master2 ~]# firewall-cmd --reload 
    success
    [root@master2 ~]# firewall-cmd --list-port
    3306/tcp
    
    
    # 在master1上测试连接
    [root@master1 ~]# mysql -uslave -p -h 192.168.8.197
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.31-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    +--------------------+
    1 row in set (0.05 sec)
    
    mysql> exit
    Bye

④从库上修改指向主库的信息，并检查slave状态。

# 在master2主库上查看主库信息
    [root@master2 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> show master status\G;
    *************************** 1. row ***************************
                 File: mysql-bin-master.000001
             Position: 602
         Binlog_Do_DB: test
     Binlog_Ignore_DB: mysql
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)

在master1上进行相应的配置：

[root@master1 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> change master to master_host='192.168.8.197',master_port=3306,master_user='slave',master_password='Abong123.',master_log_file='mysql-bin-master.000001',master_log_pos=602;
    Query OK, 0 rows affected, 2 warnings (0.11 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show slave status\G;
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

至此，master1和master2已经互为主从。测试下数据同步复制情况：

# master1上写入数据
    [root@master1 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    +----+------+
    2 rows in set (0.00 sec)
    
    mysql> insert into emp values(3,'ohoh');
    Query OK, 1 row affected (0.06 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    +----+------+
    3 rows in set (0.00 sec)
    
    # master2检测是否同步数据，发现并没有同步。查看slave的状态会发现没连上。
    [root@master2 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> use test;
    Database changed
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    +----+------+
    2 rows in set (0.00 sec)
    
    mysql> show slave status\G;
                 Slave_IO_Running: No
                Slave_SQL_Running: No
    ===========================================
    # 从master1上获取master信息
    mysql> show master status\G;
    *************************** 1. row ***************************
                 File: mysql-bin-master.000001
             Position: 154
         Binlog_Do_DB: test
     Binlog_Ignore_DB: mysql
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified
    
    # master2重启配置salve,启动slave的时候报错，使用reset slave命名重置slave即可启动。
    mysql> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> change master to master_host='192.168.8.198',master_port=3306,master_user='slave',master_password='Abong123.',master_log_file='mysql-bin-master.000001',master_log_pos=154;
    Query OK, 0 rows affected, 2 warnings (0.07 sec)
    
    mysql> start slave;
    ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
    mysql> reset slave;
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> show slave status\G;   # 可以看到，从库重新连上主库了。
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

**注意：主从同步，若数据存在不一致，即当主库执行删除操作，而从库不能执行删除操作，那从库会报错并停止数据同步复制。可以认为是一种同步复制堵塞。**

**修复同步复制堵塞：**

# 首先修复主从同步连接：
    # 查看主库信息
    mysql> show master status\G;
    *************************** 1. row ***************************
                 File: mysql-bin-master.000001
             Position: 678
         Binlog_Do_DB: test
     Binlog_Ignore_DB: mysql
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)
    
    # 在从库上修改对应的主库信息
    mysql> stop slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> reset slave;
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> change master to master_host='192.168.8.198',master_port=3306,master_user='slave',master_password='Abong123.',master_log_file='mysql-bin-master.000001',master_log_pos=678;
    Query OK, 0 rows affected, 2 warnings (0.07 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    mysql> show slave status\G;
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                
                
    # 手动同步两个数据库，使得数据保持一致。
    # 备份test数据库
    [root@master1 ~]# mysqldump -uroot -p test > test.sql
    Enter password: 
    [root@master1 ~]# ll test.sql
    -rw-r--r--. 1 root root 1864 9月  20 21:12 test.sql
    [root@master1 ~]# scp test.sql root@192.168.8.197:/root/
    root@192.168.8.197's password: 
    test.sql                                         100% 1864   605.5KB/s   00:00  
    
    
    [root@master2 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> drop database test;
    Query OK, 1 row affected (0.06 sec)
    
    mysql> create database test;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> exit
    Bye
    [root@master2 ~]# mysql -uroot -p test < test.sql
    Enter password:

再来测试一遍：

# master1上删除数据：
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  4 | abab |
    |  5 | lala |
    +----+------+
    4 rows in set (0.00 sec)
    
    mysql> delete from emp where id=5;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  4 | abab |
    +----+------+
    3 rows in set (0.00 sec)
    
    # 在master2上也可以看到数据被删除了。
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  4 | abab |
    +----+------+
    3 rows in set (0.00 sec)
    
    
    # master2上插入数据
    mysql> insert into emp values(3,'ohoh');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    +----+------+
    4 rows in set (0.00 sec)
    
    
    # master1上也可以看到有数据插入
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    +----+------+
    4 rows in set (0.00 sec)

从上面的实验结果可以看出，主主同步已经测试成功了。

mysql双主的架构，并非是负载均衡，只能说两台mysql服务器都可以提供准确的数据，也可以进行写操作。

## 3. 部署MSS模型 ##

<table> 
 <thead> 
  <tr> 
   <th>主机名</th> 
   <th>IP地址</th> 
   <th>mysql版本</th> 
   <th>主从复制的身份</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>master</td> 
   <td>192.168.8.198</td> 
   <td>mysql5.7.31</td> 
   <td>master</td> 
  </tr> 
  <tr> 
   <td>slave1</td> 
   <td>192.168.8.197</td> 
   <td>mysql5.7.31</td> 
   <td>slave中继</td> 
  </tr> 
  <tr> 
   <td>slave2</td> 
   <td>192.168.8.199</td> 
   <td>mysql5.7.31</td> 
   <td>slave</td> 
  </tr> 
 </tbody> 
</table>

**配置主库：**

# 在主库上创建中继用户repl，并授予replication slave权限，允许在192.168.8.网段的主机上登录。
    [root@master ~]# mysql -uroot -p
    Enter password: 
    
    mysql> grant replication slave on *.* to repl@'192.168.8.%' identified by 'Abong123.';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> exit
    Bye
    
    # 将主库的数据导出并导入到中继库和从库
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | emp            |
    +----------------+
    1 row in set (0.00 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    +----+------+
    4 rows in set (0.01 sec)
    
    [root@master ~]# mysqldump -uroot -p test > test.sql
    Enter password: 
    [root@master ~]# ll test.sql 
    -rw-r--r--. 1 root root 1864 9月  22 19:41 test.sql
    
    # 编辑主库的my.cnf文件：
    [root@master ~]# vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin-master
    server-id=1
    binlog-do-db=test
    binlog-ignore-db=mysql
    sync-binlog=1
    binlog-format=row
    
    # 重启mysqlf服务
    [root@master ~]# systemctl restart mysqld
    
    # 将数据库发送到中继库和从库上
    [root@master ~]# scp test.sql 192.168.8.197:/root/
    [root@master ~]# scp test.sql 192.168.8.199:/root/

**配置中继库：**

# 中继库导入数据库：
    [root@slave1 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> create database test;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> exit
    Bye
    
    [root@slave1 ~]# mysql -uroot -p test < test.sql 
    Enter password: 
    
    # 编辑中继库的my.cnf文件
    [root@slave1 ~]# vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin-slave1
    server-id=2
    log-slave-updates=1
    binlog-format=row
    [root@slave1 ~]# systemctl restart mysqld
    
    # 修改中继库的master配置，指向主库
    mysql> show master status\G;  # 主库配置
    *************************** 1. row ***************************
                 File: mysql-bin-master.000001
             Position: 154
         Binlog_Do_DB: test
     Binlog_Ignore_DB: mysql
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)
    
    
    # 中继库配置
    mysql> stop slave;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> reset slave;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> change master to master_host='192.168.8.198',master_port=3306,master_user='repl',master_password='Abong123.',master_log_file='mysql-bin-master.000001',master_log_pos=154;
    Query OK, 0 rows affected, 2 warnings (0.03 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show slave status\G;
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
          
    # 在中继库上创建中继用户repl，并授予replication slave权限，允许在192.168.8.199(从库)上登录。
    mysql> grant replication slave on *.* to repl@192.168.8.199 identified by 'Abong123.';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> exit
    Bye
    
    此时，主库和中继库已经建立了同步复制关系。

**配置从库：**

# 从库导入数据库
    [root@slave2 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> create database test;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> exit;
    Bye
    
    [root@slave2 ~]# mysql -uroot -p test < test.sql 
    Enter password: 
    
    # 修改my.cnf文件
    [root@slave2 ~]# vim /etc/my.cnf
    server-id=3
    log-bin=mysql-bin-slave2
    binlog-format=row
    
    # 重启服务
    [root@slave2 ~]# systemctl restart mysqld
    
    #开放从库的3306端口号
    [root@slave2 ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
    success
    [root@slave2 ~]# firewall-cmd --reload
    success
    [root@slave2 ~]# firewall-cmd --list-ports 
    3306/tcp
    
    
    # 修改从库中的master配置，指向中继库
    mysql> change master to master_host='192.168.8.197',master_port=3306,master_user='repl',master_password='Abong123.',master_log_file='mysql-bin-slave1.000001',master_log_pos=601;
    Query OK, 0 rows affected, 2 warnings (0.02 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show slave status\G;
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    
    至此，中继库和从库之间也建立了主从复制关系，但中继库并不进行写操作。

**测试：在主库上插入一条数据，看从库上是否会同步该数据。**

# 主库上插入数据
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | emp            |
    +----------------+
    1 row in set (0.00 sec)
    
    mysql> desc emp;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   | PRI | NULL    |       |
    | name  | varchar(20) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    +----+------+
    4 rows in set (0.00 sec)
    
    mysql> insert into emp values(5,'lala');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    |  5 | lala |
    +----+------+
    5 rows in set (0.00 sec)
    
    # 中继库也有记录
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    |  5 | lala |
    +----+------+
    5 rows in set (0.00 sec)
    
    # 从库也有记录
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    |  5 | lala |
    +----+------+
    5 rows in set (0.00 sec)

**为了让中继库不产生数据，仅有从库产生同步复制数据，可以对中继库中的表进行设置：**

# 中继库操作：
    mysql> set sql_log_bin=off;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> alter table test.emp ENGINE=blackhole;
    Query OK, 5 rows affected (0.02 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    mysql> set sql_log_bin=on;
    Query OK, 0 rows affected (0.00 sec)

**再测试一遍：**

# 主库插入数据
    mysql> insert into emp values(6,'nene');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    |  5 | lala |
    |  6 | nene |
    +----+------+
    6 rows in set (0.00 sec)
    
    # 中继库无数据
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from emp;
    Empty set (0.00 sec)
    
    # 从库有同步数据
    mysql> select * from emp;
    +----+------+
    | id | name |
    +----+------+
    |  1 | haha |
    |  2 | xixi |
    |  3 | ohoh |
    |  4 | abab |
    |  5 | lala |
    |  6 | nene |
    +----+------+
    6 rows in set (0.00 sec)

## 4. mysql主从同步复制常见报错 ##

### 4.1 因主从库的数据不一致导致从库同步复制停止 ###

**方法1：从库跳过指定数量的事务【适用从库较少的操作】**

# 从库发现主从同步停了
    mysql> show slave status\G;
    
    # 主库上查看日志
    mysql> show master status\G;
    *************************** 1. row ***************************
                 File: mysql-bin-master.000001
             Position: 678
         Binlog_Do_DB: test
     Binlog_Ignore_DB: mysql
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)
    
    mysql> show binlog events in 'mysql-bin-master.000001' from 678\G;
    Empty set (0.00 sec)
    
    # 从库跳过这些事务
    mysql> stop slave;
    mysql> set global sql_slave_skip_counter=2;   # 跳过2个事务，即这些事务主库上做了，但是从库上做不了，就会堵塞，所以要跳过。
    mysql> start slave;
    mysql> show slave status\G;   # 再次查看是否连接上

**方法2：主库关闭二进制日志再执行sql语句，执行完再开启二进制日志【适用从库较多的操作】**

# 关闭bin-log
    mysql> set sql_log_bin=off;
    # 开启bin-log
    mysql> set sql_log_bin=off;

### 4.2 从库对应的二进制主库日志（Master\_Log\_File）与主库上的二进制日志文件不一致 ###

# 在从库上该命令调整。
    change master to master_host='192.168.8.197',master_port=3306,master_user='repl',master_password='Abong123.',master_log_file='mysql-bin-slave1.000001',master_log_pos=601;

[img]: /images/20221123/4dc2c83b92bc40a0832a304a2553f898.png