mysql 5.6 主主复制_MySQl5.6主主复制配置

实现原理

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

安装环境

server-1 IP：192.168.4.58

server-2 IP：192.168.4.88

操作系统CentOS6.8 应用程序MySQL 5.6.40

MySQL安装步骤

配置步骤

1、修改配置文件

server-1(IP：192.168.4.58)

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

server-id=58 #任意自然是n，只要保证两台MySQl主机不重复就行

relay-log=relay-mysql #启用中继日志

relay-log-index=relay-mysql.index

auto-increment-increment=2 #步进值auto_imcrement 一般有n台主MySQL就填n

auto-increment-offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL

server-2(IP：192.168.4.88)

server-id=88

log-bin=mysql-bin

relay-log=relay-mysql

relay-log-index=relay-mysql.index

auto-increment-increment=2

auto-increment-offset=2

注：配置好后重启MySQL

2、在两台服务器上互相创建具有复制权限的用户

mysql> GRANT REPLICATION SLAVE ON . TO 'backup'@'192.168.4.88' IDENTIFIED BY '123456';

mysql> FLUSH PRIVILEGES;

3、如果此时两台服务器均为新建立，且无其它写入操作，各服务器只需记录当前自己二进制日志文件及事件位置，以之作为另外的服务器复制起始位置即可

查看server-1(192.168.4.58)二进制日志名和位置

mysql> show master status\G;

1. row

File: mysql-bin.000002

Position: 420

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

查看server-1(192.168.4.88)二进制日志名和位置

mysql> show master status\G;

1. row

File: mysql-bin.000002

Position: 420

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

4、各服务器接下来指定对另一台服务器为自己的主服务器即可

告知二进制文件名及位置

服务器server-1( 192.168.4.58)

mysql> change master to master_host='192.168.4.88',master_user='backup',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=420;

mysql> start slave; # 启动从服务器

服务器server-2( 192.168.4.58)

mysql> change master to master_host='192.168.4.58',master_user='backup',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=420;

mysql> start slave; # 启动从服务器

5、查看主从复制是否成功：

服务器server-1( 192.168.4.58)

mysql> show slave status\G;

1. row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.4.88

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 420

Relay_Log_File: relay-mysql.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 420

Relay_Log_Space: 452

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 88

Master_UUID: 7e7ddde3-4a3b-11e8-8195-000c29f5e0c1

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

服务器server-2( 192.168.4.58)

mysql> show slave status\G;

1. row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.4.58

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 420

Relay_Log_File: relay-mysql.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 420

Relay_Log_Space: 452

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 58

Master_UUID: 65930153-383f-11e8-8c4e-000c29138f00

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

当看到Slave_IO_Running: YES；Slave_SQL_Running: YES才表明状态正常

测试结果

在server-1 192.168.4.58上创建

mysql> create database test_db;

mysql> use test_db;

mysql> CREATE TABLE test_tb(id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,name CHAR(30));

在server-2 192.168.4.58上查看结果：

mysql> show tables;

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

| Tables_in_test_db |

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

| test_tb |

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

server-2 192.168.4.88 自动创建test_db数据库和 test_tb表，即主从复制成功。

再验证主主复制

在server-1 192.168.4.58插入数据：

mysql> INSERT INTO test_tb (name) VALUES ('11'),('22'),('33');

在server-2 192.168.4.88插入数据：

mysql> INSERT INTO test_tb (name) VALUES ('88'),('88'),('88');

分别查看两个服务器数据：

mysql> SELECT * FROM test_tb;

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

| id | name |

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

| 1 | 11 |

| 3 | 22 |

| 5 | 33 |

| 6 | 88 |

| 8 | 88 |

| 10 | 88 |

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