Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

本文转载自https://github.com/sxyx2008/DevArticles

Ubuntu 12.04.5

MySQL 5.5.47

master1 192.168.64.131

master2 192.168.64.132

1. 修改mysql配置文件、创建帐号并授权

1.1 修改master1上mysql配置文件my.conf

[mysqld]
server-id           = 131				#数据库ID
log_bin             = /var/log/mysql/mysql-bin.log	#启用二进制日志 如果没有var/log/mysql这个目录,则需要创建.
#binlog-do-db		= tudou1			#需要同步的数据库,这里同步tudou1和tudou2两个数据库
#binlog-do-db		= tudou2
binlog-ignore-db	= mysql				#忽略同步的数据库
log-slave-updates						#把从库的写操作记录到binlog中 (缺少之后,双主创建失败)
expire_logs_days	= 365				#日志文件过期天数,默认是 0,表示不过期
auto-increment-increment= 2				#设定为主服务器的数量,防止auto_increment字段重复
auto-increment-offset	= 1				#自增长字段的初始值,在多台master环境下,不会出现自增长ID重复
创建帐号密码并授权
GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'192.168.64.132' IDENTIFIED BY 'repuser';
FLUSH PRIVILEGES;
在192.168.64.132测试repuser是否能登录192.168.64.131上的数据库
ubuntu@192.168.64.132:~/apps$ mysql -urepuser -prepuser -h192.168.64.131

1.2 修改master2上mysql配置文件my.conf

[mysqld]
server-id           = 132				#数据库ID
log_bin             = /var/log/mysql/mysql-bin.log	#启用二进制日志 如果没有var/log/mysql这个目录,则需要创建.
#binlog-do-db		= tudou1			#需要同步的数据库,这里同步tudou1和tudou2两个数据库
#binlog-do-db		= tudou2
binlog-ignore-db	= mysql				#忽略同步的数据库
log-slave-updates						#把从库的写操作记录到binlog中 (缺少之后,双主创建失败)
expire_logs_days	= 365				#日志文件过期天数,默认是 0,表示不过期
auto-increment-increment= 2				#设定为主服务器的数量,防止auto_increment字段重复
auto-increment-offset	= 1				#自增长字段的初始值,在多台master环境下,不会出现自增长ID重复
创建帐号密码并授权
GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'192.168.64.131' IDENTIFIED BY 'repuser';
FLUSH PRIVILEGES;
在192.168.64.131测试repuser是否能登录192.168.64.132上的数据库
ubuntu@192.168.64.131:~/apps$ mysql -urepuser -prepuser -h192.168.64.132

注意:

  • log-slave-updates 表示把从库的写操作记录到binlog中,缺少之后,双主创建失败。双主同步时该项必须有

  • binlog-do-db 表示需要同步的数据库可出现多个,上述配置中注释掉了,若开启该配置项则格式见上述配置

  • binlog-ignore-db 表示忽略同步的数据库

2. 配置双主同步

查看master状态

master1中

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

master2中

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

设置master1从master2同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.64.132',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='repuser',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=107;
mysql> SHOW SLAVE STATUS\G
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

如出现以下两项,则说明配置成功!

           Slave_IO_Running: Yes
           Slave_SQL_Running: Yes

设置master2从master1同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.64.131',MASTER_PORT=3306,MASTER_USER='repuser',MASTER_PASSWORD='repuser',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=107;
mysql> SHOW SLAVE STATUS\G
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

如出现以下两项,则说明配置成功!

           Slave_IO_Running: Yes
           Slave_SQL_Running: Yes

3 双主同步测试

进入master1 mysql 数据库

mysql>  create database crm;
Query OK, 1 row affected (0.00 sec)

mysql>  use crm;
Database changed
mysql>  create table employee(id int auto_increment,name varchar(10),primary key(id));
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into employee(name) values('a');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into employee(name) values('b');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into employee(name) values('c');
Query OK, 1 row affected (0.06 sec)

mysql>  select * from employee;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  5 | c    |
+----+------+
3 rows in set (0.00 sec)

进入master2,查看是否有crm这个数据库和employee表。

mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crm                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql>  use crm;
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_crm |
+---------------+
| employee      |
+---------------+
1 row in set (0.00 sec)

mysql>  select * from employee;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  5 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql>  insert into employee(name) values('d');
Query OK, 1 row affected (0.00 sec)

mysql>  select * from employee;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  5 | c    |
|  7 | d    |
+----+------+
4 rows in set (0.00 sec)

在master1的中查看是否有刚刚在master2中插入的数据。

mysql>  select * from employee;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  5 | c    |
|  7 | d    |
+----+------+
4 rows in set (0.00 sec)
@jiuburangshi

This comment has been minimized.

Copy link

@jiuburangshi jiuburangshi commented Mar 21, 2017

nice, thank you

@martin-sun

This comment has been minimized.

Copy link

@martin-sun martin-sun commented Jun 27, 2018

有一个错误:
修改master2上mysql配置文件my.conf中 ,auto-increment-offset 应该是 2 而不是1
auto-increment-offset = 2

@wangy8961

This comment has been minimized.

Copy link

@wangy8961 wangy8961 commented Oct 25, 2018

有一个错误:
修改master2上mysql配置文件my.conf中 ,auto-increment-offset 应该是 2 而不是1
auto-increment-offset = 2

you are right

@garywu520

This comment has been minimized.

Copy link

@garywu520 garywu520 commented Nov 28, 2018

请问,如果master1和master2的position号不一致,怎么弄呢?

@15879883242

This comment has been minimized.

Copy link

@15879883242 15879883242 commented Dec 1, 2018

请问,如果master1和master2的position号不一致,怎么弄呢?

不一样也没有关系的,使用show slave status\G 显示出来是双yes就没问题

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment