搭建MySQL Dual Master环境

By banping On 2011/03/19 · 2 Comments · In MySQL

搭建Dual Master其实和Msater Slave是一样的，就是反过去再做一遍而已，互为主从。

1、修改配置文件，一般要注意以下几个参数：

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

replicate-ignore-db = mysql

replicate-ignore-db = performance_schema

replicate-ignore-db = information_schema

binlog_format=mixed

server-id = 1

其中replicate开头的几个参数只影响从库。

2、建立一个专门的用于复制的用户，锁主库表

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'%' identified by 'slave';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

3、导数据

mysql> show master status;

+------------+----------+--------------+---------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------+----------+--------------+---------------------------------------------+

| bin.000010 | 190034 | | mysql,information_schema,performance_schema |

+------------+----------+--------------+---------------------------------------------+

1 row in set (0.00 sec)

[root@localhost 3306]# bin/mysqldump rtdb --master-data -u root -p>/tmp/rtdb3306.db;

这个master-data参数就是在导出的文件里显示CHANGE MASTER TO的信息，可以注释掉以后手工执行，也可以看到这里的信息和刚才show master status的输出是一致的：

[root@localhost 3306]# vi /tmp/rtdb3306.db

-- CHANGE MASTER TO MASTER_LOG_FILE='bin.000010', MASTER_LOG_POS=190034;

4、释放表

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

5、启动从库

[root@localhost 3308]# bin/mysqld_safe --user=mysql --skip-slave-start &

[1] 21925

[root@localhost 3308]# 110319 15:11:08 mysqld_safe Logging to '/home/mysql/3308/mysql_error.log'.

110319 15:11:08 mysqld_safe Starting mysqld daemon with databases from /home/mysql/3308/data

6、导入数据

[root@localhost 3308]# bin/mysql rtdb -u root -p</tmp/rtdb3306.db

7、登入从库修改master信息，如果报错，先删掉data目录下的master.info和relay-log.info

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.0.35',

-> MASTER_PORT = 3308,

-> MASTER_USER='repl',

-> MASTER_PASSWORD='rtslave',

-> MASTER_LOG_FILE='bin.000010',

-> MASTER_LOG_POS=190034;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

8、查看show slave status\G输出，注意2个线程的状态

9、反过来再做一遍

10、在两边insert数据进行测试