MYSQL架构之MySQL Dual-Master双向同步
简介
其实与Master-Slave同步方式并无太大的不同，只是双方相互为对方的主从服务器。并且可以扩展到多台服务器。如下图
搭建环境
操作系统： CentOS 6.4 64位
MySQL版本：5.1.50
MySQL（1）IP地址：192.168.100.60
MySQL（2）IP地址：192.168.100.61
MySQL安装
此步骤从略，确保两台测试机器的my.cnf相同，安装后能成功启动即可。
Replication配置
MySQL(1)配置
创建账号
mysql> grant replication slave on *.* to 'repl'@'192.168.100.61' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改my.cnf
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
socket = /usr/local/mysql/tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/dbdata
tmpdir = /tmp
log-bin = master-bin
log-bin-index = master-bin.index
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset = 1
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
server-id = 1
重启mysqld
/etc/init.d/mysqld restart
查看二进制文件名与位置偏移值
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 106 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注意：
记录以上两个值，等会启动复制要用到
MySQL(2)配置
创建账号
mysql> grant replication slave on *.* to 'repl'@'192.168.100.60' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
修改my.cnf
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
socket = /usr/local/mysql/tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/dbdata
tmpdir = /tmp
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset = 2
log-bin = master-bin
log-bin-index = master-bin.index
server-id = 2
重启mysqld
/etc/init.d/mysqld restart
查看二进制文件名与位置偏移值
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 106 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注意：
记录以上两个值，等会启动复制要用到
启动MySQL(1) Replication
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.100.61',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = '123456',
-> MASTER_LOG_FILE = 'master-bin.000003',
-> MASTER_LOG_POS = 106;
Query OK, 0 rows affected (0.18 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看slave状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.61
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 252
Relay_Master_Log_File: master-bin.000003
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: 106
Relay_Log_Space: 407
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:
1 row in set (0.00 sec)
启动启动MySQL(1)Replication
mysql> change master to
-> MASTER_HOST = '192.168.100.60',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = '123456',
-> MASTER_LOG_FILE = 'master-bin.000003',
-> MASTER_LOG_POS = 106;
Query OK, 0 rows affected (0.11 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看slave状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.60
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 252
Relay_Master_Log_File: master-bin.000003
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: 106
Relay_Log_Space: 407
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:
1 row in set (0.00 sec)
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
socket = /usr/local/mysql/tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/dbdata
tmpdir = /tmp
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset = 1
log-bin = master-bin
log-bin-index = master-bin.index
Replication测试
MySQL(1)
示例：在MySQL(1)服务器创建example库
mysql> create database example;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| example |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
查看MySQL(2)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| example |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
MySQL(2)
示例：在MySQL(2)服务器创建example1库
mysql> create database example1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| example |
| example1 |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
查看MySQL(1)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| example |
| example1 |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
测试成功