MySQL 互备份master-master

机器信息

MySQL 5.1.17
A. 192.168.0.2
B. 192.168.0.9

创建用户并授权

A.

01 02 03 04 05 06 07 08 09 10 11

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)

修改配置文件

A.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21

[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

B.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21

[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

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

1	[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 &

查看备份是否成功

1	mysql> SHOW SLAVE STATUS /G

当看到Slave_IO_Running，Slave_SQL_Running 都是Yes，就说明备份成功 ：

1 2	Slave_IO_Running: Yes Slave_SQL_Running: Yes

如下是手动指定Master
A.

01 02 03 04 05 06 07 08 09 10

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)

B.

01 02 03 04 05 06 07 08 09 10

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)

A.

1 2 3 4 5 6 7 8 9

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)

B.

1 2 3 4 5 6 7 8 9

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)

查看各自机器上的IO进程和 SLAVE进程是否都开启。

1	mysql> SHOW SLAVE STATUS /G

释放掉各自的锁

1	mysql> UNLOCK TABLES;