centos7上mysql 8.0.25版本master-master架构的搭建

1 mysql的安装

分别在canal1和canal2两台服务器进行mysql的安装，安装的版本为8.0.25

安装的方式可以参考我的博客centos7上安装mysql8.0.25

2 master-master架构的配置 2.1 编辑/etc/my.cnf

其中canal1的内容如下，canal2只是server-id = 2不同，其它一样

[mysqld]
server-id = 1    
                 
# 开启binlog, 日志名前缀为mysql-bin				 
log_bin = mysql-bin  
binlog_format = row
# 默认为0，表示不做强制性的磁盘刷新；此时表示每n条事务，进行一次磁盘刷新
sync_binlog = 100

# 默认值为0，表示不自动删除
binlog_expire_logs_seconds = 604800 


# 要同步的数据库
# binlog-do-db = test

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

然后重启canal1和canal2上的mysql

2.2 创建slave用户并设置权限

canal1和canal2进行的操作一样

[root@canal1 ~]# 
[root@canal1 ~]# mysql -h 192.168.23.3x -u root -pRoot_123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> create user 'slave'@'%' identified with mysql_native_password by 'Slave_123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'slave';
+-----------------------------------------------+
| Grants for slave@%                            |
+-----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `slave`@`%` |
+-----------------------------------------------+
1 row in set (0.03 sec)

mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| mysql-bin.000001 |      820 |              | mysql,information_schema,performation_schema,sys |                   |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
1 row in set (0.01 sec)

mysql> 

如果想重置binlog，可以使用命令reset master

2.3 slave连接master，并开启同步

以下是canal1的; canal2因为连接的master是canal1, 所以参数master_host='canal1', 其他步骤都是一样的

mysql> 
mysql> change master to
    -> master_host='canal2',
    -> master_user='slave',
    -> master_password='Slave_123',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=820;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> 
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: canal2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 820
               Relay_Log_File: canal1-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_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_Master_Log_Pos: 820
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 3f5b2e78-d2eb-11eb-a61e-000c299936b3
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> 

当看到Slave_IO_Running: Yes和Slave_SQL_Running: Yes时，表示同步成功

2.4 测试

在canal1上执行的操作

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> use db1;
Database changed
mysql> 
mysql> create table tb1_1(
    -> idA int,
    -> nameA varchar(32),
    -> scoreA decimal(5,2)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb1_1 values(1,'1',1.1),(2,'2',2.2),(3,'3',3.3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> 

在canal2上执行的操作

mysql> 
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> use db2;
Database changed
mysql> create table tb2_1(
    -> idB int,
    -> nameB varchar(32),
    -> scoreB decimal(5,2)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb2_1 values(1,'1',1.1),(2,'2',2.2),(3,'3',3.3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

在canal2上执行的show databases命令，可以看到在canal1上创建的数据库db1也出现了，说明同步成功；

如果在canal1上执行show databases结果也是一样的

2.5 mysql查询binlog的命令

mysql> 
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.02 sec)

mysql> 
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> 
mysql> show binlog events;
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                       |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |    4 | Format_desc    |         1 |         125 | Server ver: 8.0.25, Binlog ver: 4                                                                                          |
......省略部分......
| mysql-bin.000001 | 3334 | Rotate         |         1 |        3381 | mysql-bin.000002;pos=4                                                                                                     |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
37 rows in set (0.00 sec)

2.6 在一个mysql实例运行一段时间之后在建立master-master架构的操作步骤

刚刚我们的两个mysql实例，都是在完全没有数据的情况下，进行数据同步的；但实际情况中，往往是在一个mysql实例(canal1)运行一段时间之后，再进行master-master(canal1和canal2)架构的搭建

此时部署的步骤大致如下：

1. canal1开启binlog日志(可参考上面的开启binlog)
2. 将数据用mysqldump进行全量导出

[root@canal1 ~]# 
[root@canal1 ~]# mysqldump -u root -pRoot_123 --databases db1 db2 --flush-logs --lock-all-tables --master-data=1 > /root/test.sql
[root@canal1 ~]#

因为上面的命令的--master-data=1，会将binlog刷新后的binlog文件名和position输出到test.sql文件中，如下所示： 3. 将上面截图的那行注释，我们只需要得到它的信息，后面在chage master to命令时手动输入 4. 将test.sql数据全部导入canal2中 5. 开启canal2的binlog, 然后重启canal2的mysql, 后面就可以参考步骤2.2进行后面的同步操作了