mysql 主从复制 双主_主从复制（双主复制）

MySQL双主复制，即互为Master-Slave(只有一个Master提供写操作)

双主复制可以实现数据库服务器的热备，结合Keepalived实现动态切换，实现了双主对外的统一接口以及自动检查、失败切换机制。联合使用，可以实现MySQL数据库的高可用方案。(在本文仅做双主复制的部署)

环境：CentOS 6.5

MASTER1:192.168.81.11

MASTER2:192.168.81.12

分别修改Master1和Master2 的配置文件

[root@localhost ~]# vim /usr/local/mysql/my.cnf

MASTER1:

[mysqld]

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

server-id=1 #服务器id(不能相同)

expire-logs-days=100 #自动清理100天前的日志

replicate-do-db=test

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

auto-increment-increment=2

auto-increment-offset=1

MASTER2:

[mysqld]

log-bin=mysql-bin

server-id=2

expire-logs-days=100

replicate-do-db=test

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

auto-increment-increment=2

auto-increment-offset=2

character-set-server=utf8

重新启动数据库

[root@localhost ~]# service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

进入数据库配置双主同步

[root@localhost ~]# mysql -u root -p

Enter password:

MASTER1：

mysql> grant replication slave on *.* to slave@'192.168.81.12' identified by 'ibelieveicanfly';

Query OK, 0 rows affected (0.05 sec)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 | 336 | | mysql,information_schema | |

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

1 row in set (0.00 sec)

MASTER2：

mysql> grant replication slave on *.* to slave@'192.168.81.11' identified by 'ibelieveicanfly';

Query OK, 0 rows affected (0.05 sec)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 | 336 | | mysql,information_schema | |

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

1 row in set (0.00 sec)

MASTER1：

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to

-> master_host='192.168.81.12',

-> master_user='slave',

-> master_password='ibelieveicanfly',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=336;

Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

MASTER2:

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> change master to

-> master_host='192.168.81.11',

-> master_user='slave',

-> master_password='ibelieveicanfly',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=336;

Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> start slave;

Query OK, 0 rows affected (0.05 sec)

查看是否同步成功(出现双yes即为成功)

mysql> show slave status\G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes