Mycat我爱编程

Mysql分布式部署 - 主主复制

2018-02-11  本文已影响9人  红薯爱帅

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配置文件

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文件,避免文件过多
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配置文件

server-id=2
log-bin=mysql-bin

auto_increment_increment=2
auto_increment_offset=2

replicate-do-db=mall
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> 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> 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> 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

Insert into mall.student(name) values('aaa');
Insert into mall.student(name) values('bbb');
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)
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,补充说明

5,参考

上一篇下一篇

猜你喜欢

热点阅读