主从复制（双主复制）
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.cnfMASTER1:
[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=1MASTER2:
[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本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。