mysql8主从和主主复制实战

本文将实战搭建一个mysql8的双主双从的架构，通过4个单机实现主从和主主复制。

单机环境：centos7 x64+mysql8，单机部署可参考我的另一个文章centos7 x64+mysql8生产环境部署

双主双从架构：

1.mysql服务器列表

2.mysql服务器主从复制搭建

根据双主双从架构图，首先搭建两套主从复制，即master1->slave1和master2->slave2。

• MySQL 主从复制原理 ：当master发生增删改数据更新事件后，记录到binary log，并创建一个binlog dump thread，把binary log的内容发送到slave，此时slave启动并发起连接，连接到主库创建一个I/O线程，读取主库传过来的binary log内容写入到relay log，同时创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db。

• master1和master2配置

  //修改master1和master2的mysql配置文件 [root@localhost ~]#vi /etc/my.cnf  //master1内容如下： [mysqld] #服务器唯一ID  server-id=1 #启用二进制日志，设置名称为mysql-bin log-bin=mysql-bin #设置你要复制的数据库名，一会我们用testdb和orders测试 binlog-do-db=testdb binlog-do-db=orders #设置logbin格式，即在主从复制时采取的模式 #STATEMENT:根据SQL复制，当sql语句中存在函数时，如now()会造成数据不一致； #ROW:根据表行数据变化复制，但当表结构发生变化或批量操作数据时，该表每一条记录都会记录到日志中； #MIXED:是以上两种模式的混合使用，会自动在不同情况选择合适的复制模式，但不支持mysql系统变量。 binlog-format=STATEMENT #主主复制配置，这里顺便一起写入 #开启主作为从数据库的时候，要更新二进制日志文件  log-slave-updates #表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535  auto-increment-increment=2 #表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535  auto-increment-offset=1  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  //master2内容如下： [mysqld] server-id=3 log-bin=mysql-bin binlog-do-db=testdb binlog-do-db=orders binlog-format=STATEMENT  log-slave-updates auto-increment-increment=2 auto-increment-offset=2  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

• slave1和slave2配置

  //修改slave1和slave2的mysql配置文件 [root@localhost ~]#vi /etc/my.cnf  //slave1内容如下： [mysqld] #服务器唯一ID  server-id=2 #启用中继日志，设置名称为mysql-relay relay-log=mysql-relay  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  //slave2内容如下： [mysqld] #服务器唯一ID  server-id=4 #启用中继日志，设置名称为mysql-relay relay-log=mysql-relay  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

• 重启服务

  //分别重起4台机器上的mysql服务 [root@localhost ~]#systemctl restart mysqld 

• 在master1和master2上分别建立主从复制帐户slave并授权

  //登陆mysql [root@localhost ~]#mysql -uroot -p  //创建新用户slave，因为这里是远程连接故需要修改连接加密方式 mysql>CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave123456';  //授权 mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';  //更新权限表 mysql>flush privileges; 

• 查询master1和master2状态

  //如果是新装，则master1和master2状态相同，如若不同则记住各自的File和Position，且再勿操作。 mysql>show master status;  +------------------+----------+---------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+---------------+------------------+-------------------+ | mysql-bin.000001 |      155 | testdb,orders |                  |                   | +------------------+----------+---------------+------------------+-------------------+ 1 row in set (0.00 sec) 

• 在slave1和slave2配置要复制的主机

  //分别登陆slave1和slave2的mysql [root@localhost ~]#mysql -uroot -p  //slave1执行 mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.181',master_port=3306,master_user='slave',master_password='slave123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=155;  //slave2执行 mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.183',master_port=3306,master_user='slave',master_password='slave123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=155;  //分别启动slave1和slave2复制功能 mysql>start slave;  //分别查看slave1和slave2状态，若Slave_IO_Running和Slave_SQL_Running是Yes则主从复制成功 mysql>show slave statusG; 

3.mysql服务器主主复制

搭建主从复制后，勿操作master1和master2库，我们在搭建过程已写入了主主复制的配置，其生效只需要设置master1和master2互相复制即可 ：

//分别登陆master1和master2的mysql [root@localhost ~]#mysql -uroot -p  //master1执行 mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.183',master_port=3306,master_user='slave',master_password='slave123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=155;  //master2执行 mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.181',master_port=3306,master_user='slave',master_password='slave123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=155;  //分别启动master1和master2复制功能 mysql>start slave;  //分别查看master1和master2状态，若Slave_IO_Running和Slave_SQL_Running是Yes则主主复制成功 mysql>show slave statusG; 

4.过程中出错了怎么办？

开始测试了，但发现不复制，一般按照以上步骤不会出现问题，但你架设环境用虚拟机克隆4个主机的话，需要修改mysql实例的server-uuid，重置复置功能，再重新设置即可解决。

• 修改mysql实例的server-uuid均不相同

[root@localhost ~]#vi /usr/local/mysql/data/auto.cnf [auto] server-uuid=3b4bd0c8-53d4-11ea-bb92-000c296f187c 

• 重置复制功能

//登陆mysql [root@localhost ~]#mysql -uroot -p  //停止复制功能 mysql>stop slave;  //重置复制功能 mysql>reset master;  

• 顺便改个hostname

[root@localhost ~]#vi /etc/hostname 

5.测试

• 在master1创建testdb库，会发现其他3个主机也同时创建了testdb库。

mysql>CREATE DATABASE testdb; 

• 在master2创建orders库，会发现其他3个主机也同时创建了orders库。

mysql>CREATE DATABASE orders; 

• 在master1的testdb库创建mytbl表，则其他3个主机的testdb库也同时创建了mytbl表。

mysql>use testdb mysql>DROP TABLE IF EXISTS `mytbl`; CREATE TABLE `mytbl` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `name` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 

• 在master1的testdb库mytbl表插两个值，再到master2的testdb库mytbl表插一个值。

//master1 mysql>use testdb mysql>insert into mytbl(`name`) values('test1') mysql>insert into mytbl(`name`) values('test2') 

此时，4个库表mytbl均插入了两个记录，如下图：

//master2 mysql>use testdb mysql>insert into mytbl(`name`) values('test3') 

此时，4个库表mytbl均有三个记录，如下图：

id的不连续和我们主主配置的auto_increment_offset和auto_increment_increment参数相关。

• 系统变量测试(出现数据不一致)

//master1或master2 mysql>use testdb mysql>insert into mytbl(name) values(@@hostname); 

会发现4个库里的name值不一致了，其name字段值分别取的各自主机名。因此，在写程序过程中，尽量不用mysql的系统函数或查出后代入。

//master1 mysql> select * from mytbl; +----+--------------------+ | id | name               | +----+--------------------+ |  1 | test1              | |  3 | test2              | |  4 | test3              | |  5 | localhost.mysql-m1 | +----+--------------------+  //master2 mysql> select * from mytbl; +----+--------------------+ | id | name               | +----+--------------------+ |  1 | test1              | |  3 | test2              | |  4 | test3              | |  5 | localhost.mysql-m2 | +----+--------------------+  //slave1 mysql> select * from mytbl; +----+--------------------+ | id | name               | +----+--------------------+ |  1 | test1              | |  3 | test2              | |  4 | test3              | |  5 | localhost.mysql-s1 | +----+--------------------+  //slave2 mysql> select * from mytbl; +----+--------------------+ | id | name               | +----+--------------------+ |  1 | test1              | |  3 | test2              | |  4 | test3              | |  5 | localhost.mysql-s2 | +----+--------------------+