mysql8主从和主主复制实战
本文将实战搭建一个mysql8的双主双从的架构,通过4个单机实现主从和主主复制。
单机环境:centos7 x64+mysql8,单机部署可参考我的另一个文章centos7 x64+mysql8生产环境部署
双主双从架构:
1.mysql服务器列表
名称 | ip地址 |
---|---|
mysql1-m1(master1) | 192.168.1.181 |
mysql1-s1(slave1) | 192.168.1.182 |
mysql1-m2(master2) | 192.168.1.183 |
mysql1-s2(slave2) | 192.168.1.184 |
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 status\G;
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 status\G;
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 |
+----+--------------------+