MySQL 互备份master-master

机器信息

MySQL 5.1.17
A. 192.168.0.2
B. 192.168.0.9

创建用户并授权

A.
[sql]
mysql> GRANT REPLICATION SLAVE, FILE on *.* TO 'slave_2'@'192.168.0.9' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
B.
mysql> GRANT REPLICATION SLAVE, FILE on *.* TO 'slave_9'@'192.168.0.2' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
[/sql]

修改配置文件

A.
[bash highlight="10,12-14"]
[mysqld]
server-id = 1
user = mysql
log_bin = mysql-bin
binlog_do_db = example
binlog_ignore_db = mysql
binlog_ignore_db = test
replicate_do_db = example
replicate_ignore_db = mysql
replicate_ignore_db = test
log_slave_updates
slave_skip_errors = all
sync_binlog = 1
auto_increment_increment = 2
auto_increment_offset = 1
master_host = 192.168.0.9
master_port = 3306
master_user = slave_9
master_password = 123456
master_connect_retry = 60
report_host = 192.168.0.5
[/bash]

B.
[bash highlight="10,12-14"]
[mysqld]
server-id = 2
user = mysql
log_bin = mysql-bin
binlog_do_db = example
binlog_ignore_db = mysql
binlog_ignore_db = test
replicate_do_db = example
replicate_ignore_db = mysql
replicate_ignore_db = test
log_slave_updates
slave_skip_errors = all
sync_binlog = 1
auto_increment_increment = 2
auto_increment_offset = 1
master_host = 192.168.0.2
master_port = 3306
master_user = slave_2
master_password = 123456
master_connect_retry = 60
report_host = 192.168.0.5
[/bash]

启动MySQL服务，在A和B上执行如下相同的步骤

[bash]
[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/log/localhost.localdomain.pid --skip-external-locking &
[/bash]

查看备份是否成功

[sql]
mysql> SHOW SLAVE STATUS \G
[/sql]

当看到Slave_IO_Running，Slave_SQL_Running 都是Yes，就说明备份成功 ：
[bash]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[/bash]



如下是手动指定Master
A.
[sql]
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 520
Binlog_Do_DB: example
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)
[/sql]

B.
[sql]
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 519
Binlog_Do_DB: example
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)
[/sql]

A.
[sql]
mysql> CHANGE MASTER TO
-> master_host='192.168.0.9',
-> master_user='slave_9',
-> master_password='123456',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=519;
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
[/sql]

B.
[sql]
mysql> CHANGE MASTER TO
-> master_host='192.168.0.2',
-> master_user='slave_2',
-> master_password='123456',
-> master_log_file='mysql-bin.000004',
-> master_log_pos=520;
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
[/sql]


查看各自机器上的IO进程和 SLAVE进程是否都开启。
[sql]
mysql> SHOW SLAVE STATUS \G
[/sql]

释放掉各自的锁
[sql]
mysql> UNLOCK TABLES;
[/sql]