mysql复制部署_Mysql分布式部署 - 主主复制

1，概述

主主复制，即在两台MySQL主机内都可以变更数据，而且另外一台主机也会做出相应的变更。聪明的你也许已经想到该怎么实现了。对，就是将两个主从复制有机合并起来就好了。只不过在配置的时候我们需要注意一些问题，例如，主键重复，server-id不能重复等等。

mysql主主复制.jpg

2，实现过程

2.1，启动和配置mysql_master_a

启动docker容器，172.18.0.21

docker run -it --name mysql_master_a -h mysql_master_a \

-p 23306:3306 --net my_network --ip 172.18.0.21 \

mysql_master:v2 /bin/bash /etc/rc.local

修改配置Mysql配置文件

进入mysql_master_a容器，配置/etc/mysql/mysql.conf.d/mysqld.cnf

server-id=1 #任意自然数n，只要保证两台MySQL主机不重复就可以了。

log-bin=mysql-bin #开启二进制日志

auto_increment_increment=2 #步进值auto_imcrement，一般有n台主MySQL就填n

auto_increment_offset=1 #起始值，一般填第n台主MySQL，此时为第一台主MySQL

replicate-do-db=mall #要同步的数据库，默认所有库

expire_logs_days=7 # 只保存最近7天的bin-log文件，避免文件过多

修改配置之后，要重启Mysql服务

service mysql restart

创建Mysql账号

mysql> Grant all privileges on *.* to 'test'@'%' identified by '123456' with grant option;

mysql> Grant REPLICATION SLAVE on *.* to 'slave1'@'%' identified by '123456';

查看master状态，记录bin-log文件名称和位置

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000004 | 747 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2.2，启动和配置mysql_master_b

启动docker容器，172.18.0.22

docker run -it --name mysql_master_b -h mysql_master_b \

-p 23307:3306 --net my_network --ip 172.18.0.22 \

mysql_master:v2 /bin/bash /etc/rc.local

修改配置Mysql配置文件

进入mysql_master_b容器，配置/etc/mysql/mysql.conf.d/mysqld.cnf

server-id=2

log-bin=mysql-bin

auto_increment_increment=2

auto_increment_offset=2

replicate-do-db=mall

修改配置之后，要重启Mysql服务

service mysql restart

创建Mysql账号

mysql> Grant all privileges on *.* to 'test'@'%' identified by '123456' with grant option;

mysql> Grant REPLICATION SLAVE on *.* to 'slave1'@'%' identified by '123456';

查看master状态，记录bin-log文件名称和位置

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000004 | 747 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2.3，在mysql_master_a和mysql_master_b分别告知另外一个Master信息，包含IP、用户、密码、bin-log文件名称和位置

在mysql_master_a(172.18.0.21)执行

mysql> CHANGE MASTER TO

MASTER_HOST='172.18.0.22',

MASTER_USER='slave1',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=747;

mysql> start slave;

mysql> show slave status\G;

在mysql_master_b(172.18.0.22)执行

mysql> CHANGE MASTER TO

MASTER_HOST='172.18.0.21',

MASTER_USER='slave1',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=747;

mysql> start slave;

mysql> show slave status\G;

3，测试

3.1，测试用例1

在mysql_master_a执行数据库test创建、数据表student创建、数据插入更新删除等操作，确认mysql_master_b是否有对应的数据变化(结果一致)

相反，在mysql_master_b执行类似操作，确认mysql_master_a是否有对应的数据变化(结果一致)

mysql> create database mall;

mysql> CREATE TABLE mall.`student` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> Insert into mall.student(name) values('aaa');

mysql> Insert into mall.student(name) values('bbb');

mysql> delete from mall.student where id = 1;

mysql> update mall.student set name = 'ccc' where id = 2;

mysql> drop table student;

mysql> drop database mall;

3.2，测试用例2

在mysql_master_a和mysql_master_b分别执行多次insert操作

Insert into mall.student(name) values('aaa');

Insert into mall.student(name) values('bbb');

两个Mysql服务的数据一致

mysql> select * from mall.student;

+----+------+

| id | name |

+----+------+

| 5 | aaa |

| 7 | bbb |

| 8 | aaa |

| 10 | bbb |

| 11 | aaa |

| 13 | bbb |

| 15 | aaa |

| 17 | bbb |

| 18 | aaa |

| 20 | bbb |

| 22 | aaa |

| 23 | aaa |

+----+------+

12 rows in set (0.00 sec)

查看student表结构，AUTO_INCREMENT会随着insert自动增加，因此保证id不会存在冲突

(但是，在高并发情况下，不能保证不会冲突，因此，此处设置自增步长是master个数，保证每台master服务器的自增序列不重复，因此不会存在主键冲突)

CREATE TABLE `student` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;

CREATE TABLE `student` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;

4，补充说明

主主复制配置文件中auto_increment_increment和auto_increment_offset只能保证主键不重复，却不能保证主键有序。

当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave status\G信息中有错误提示，可根据错误提示进行更正。

Slave_IO_Running、Slave_SQL_Running不全为YES时，大多数问题都是数据不统一导致。

常见的数据不一致情况，终极更正法：重新执行一遍CHANGE MASTER

两台数据库都存在db数据库，而第一台MySQL db中有tab1，第二台MySQL db中没有tab1，那肯定不能成功。

已经获取了数据的二进制日志名和位置，又进行了数据操作，导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。

stop slave后，数据变更，再start slave。出错。

5，参考