Linux-MySQL-主主复制

主主复制

主主复制：两个节点，都可以更新数据，并且互为主从

容易产生的问题：数据不一致；因此慎用

考虑要点：自动增长id

配置一个节点使用奇数id

auto_increment_offset=1   #开始点 auto_increment_increment=2 #增长幅度

另一个节点使用偶数id

auto_increment_offset=2 auto_increment_increment=2

主主复制的配置步骤：

各节点使用一个惟一server_id
都启动binary log和relay log
创建拥有复制权限的用户账号
定义自动增长id字段的数值范围各为奇偶
均把对方指定为主节点，并启动复制线程

实现两个节点主主复制模型

实验环境

机器

centos7 地址10.0.0.7

centso7　　地址10.0.0.77

已安装MySQL5.7数据库

第一个master1主节点

创建日志路径

[root@centos7-liyj ~]#mkdir -p /data/mysql/logbin [root@centos7-liyj ~]#chown -R mysql.mysql /data/mysql/

配置文件添加

[root@centos7-liyj ~]#vim /etc/my.cnf [root@centos7-liyj ~]#cat /etc/my.cnf [mysqld] datadir=/data/mysql skip_name_resolve=1 socket=/data/mysql/mysql.sock         log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid  server-id=7                             #全局唯一ID编号 log-bin=/data/mysql/logbin/mysql-bin　　 #二进制日志路径 auto_increment_offset=1　　　　　　　　　　 #开始点 auto_increment_increment=2　　　　　　　　 #增长幅度  [client] socket=/data/mysql/mysql.sock

重启数据库服务

[root@centos7-liyj ~]#systemctl restart mysqld

记录二进制日志位置

[root@centos7-liyj ~]#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.37-log Source distribution  Copyright (c) 2000, 2022, 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> show  master logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001 |       154 | +------------------+-----------+ 1 row in set (0.00 sec)

创建复制用户并授权

mysql> create user repluser10.0.0.% identified by 123456;  mysql> grant replication slave on *.* to [email protected].%;

第二个master2主节点

创建日志路径

[root@centos7-liyj ~]#mkdir -p /data/mysql/logbin [root@centos7-liyj ~]#chown -R mysql.mysql /data/mysql/

配置文件

[root@centos7-liyj ~]#vim /etc/my.cnf [root@centos7-liyj ~]#cat /etc/my.cnf [mysqld] datadir=/data/mysql skip_name_resolve=1 socket=/data/mysql/mysql.sock         log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid  server-id=77                              #全局唯一ID编号 log-bin=/data/mysql/logbin/mysql-bin       #二进制日志路径 auto_increment_offset=2　　　　　　　　　　　 #开始点 auto_increment_increment=2　　　　　　　　　 #增长幅度  [client] socket=/data/mysql/mysql.sock

重启数据库服务

[root@centos7-liyj ~]#systemctl restart mysqld

登录数据库配置复制同步

mysql> CHANGE MASTER TO     -> MASTER_HOST='10.0.0.7',     -> MASTER_USER='repluser',     -> MASTER_PASSWORD='123456',     -> MASTER_PORT=3306,     -> MASTER_LOG_FILE='mysql-bin.000001',     -> MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.00 sec)  mysql> start slave; Query OK, 0 rows affected (0.00 sec)  mysql> show master logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001 |       154 | +------------------+-----------+ 1 row in set (0.00 sec)

开启复制

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

mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.0.0.7                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 627                Relay_Log_File: centos7-liyj-relay-bin.000002                 Relay_Log_Pos: 793         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: 627               Relay_Log_Space: 1007               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: 7                   Master_UUID: 6eba5dd5-d036-11ec-80f9-0050563aaca0              Master_Info_File: /data/mysql/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:  1 row in set (0.00 sec)

从节点状态信息

在第一个master节点上

mysql> CHANGE MASTER TO     -> MASTER_HOST='10.0.0.77',     -> MASTER_USER='repluser',     -> MASTER_PASSWORD='123456',     -> MASTER_PORT=3306,     -> MASTER_LOG_FILE='mysql-bin.000001',     -> MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.00 sec)

开启复制

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

mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.0.0.77                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 154                Relay_Log_File: centos7-liyj-relay-bin.000002                 Relay_Log_Pos: 320         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: 154               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: 77                   Master_UUID: 8d1ce2f2-d008-11ec-8556-005056399ee0              Master_Info_File: /data/mysql/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:  1 row in set (0.00 sec)  ERROR:  No query specified

主节点状态信息

测试

第一节点master1创建数据库
mysql> create database school; Query OK, 1 row affected (0.00 sec)

第二节点master2查看

第二节点master2创建表  mysql> use school; Database changed mysql> create table student (     -> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,     -> name VARCHAR(20) NOT NULL,     -> age tinyint UNSIGNED,     -> gender ENUM('M','F') default 'M'     -> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.03 sec)  mysql> show tables ; +------------------+ | Tables_in_school | +------------------+ | student          | +------------------+ 1 row in set (0.00 sec)  mysql> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field  | Type                | Null | Key | Default | Extra          | +--------+---------------------+------+-----+---------+----------------+ | id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | | name   | varchar(20)         | NO   |     | NULL    |                | | age    | tinyint(3) unsigned | YES  |     | NULL    |                | | gender | enum('M','F')       | YES  |     | M       |                | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec)  第一节点master1查看 mysql> use school 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_school | +------------------+ | student          | +------------------+ 1 row in set (0.00 sec)  mysql> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field  | Type                | Null | Key | Default | Extra          | +--------+---------------------+------+-----+---------+----------------+ | id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | | name   | varchar(20)         | NO   |     | NULL    |                | | age    | tinyint(3) unsigned | YES  |     | NULL    |                | | gender | enum('M','F')       | YES  |     | M       |                | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)