MySQL雙主複制，即互為Master-Slave(隻有一個Master提供寫操作)
雙主複制可以實作資料庫伺服器的熱備，結合Keepalived實作動态切換，實作了雙主對外的統一接口以及自動檢查、失敗切換機制。聯合使用，可以實作MySQL資料庫的高可用方案。(在本文僅做雙主複制的部署)
環境：CentOS 6.5
MASTER1:192.168.81.11
MASTER2:192.168.81.12
分别修改Master1和Master2 的配置檔案
[[email protected] ~]# 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
重新啟動資料庫
[[email protected] ~]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
進入資料庫配置雙主同步
[[email protected] ~]# mysql -u root -p
Enter password:
MASTER1：
mysql> grant replication slave on *.* to [email protected]'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 [email protected]'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