MySQL Replication 主主配置详细说明

环境配置：
A：192.168.11.11
B：192.168.11.12
mysql> select version(); 
+------------+
| version()  |
+------------+
| 5.5.12-log |
+------------+
CentOS release 5.4 (Final)
主服务器配置
vi /etc/my.cnf
# Replication Master Server (default)
server-id = 1//服务器ID号，整数值，保证唯一标识一台服务器就可以 
log-bin=mysql-bin //打开二进制日志 
binlog-do-db=repl //需要同步的数据库，如果没有本行，即表示同步所有的数据库
binlog-ignore-db=mysql //忽略mysql数据库复制
#主主需加入的部分
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db = test
replicate-ignore-db = mysql
#mysql -uroot -pivgivgivg  //登陆
mysql>grant replication slave on *.* to 'copy'@'192.168.11.12' identified by 'ivgivgivg';//在master机上为slave机添加一同步帐号
mysql>flush privileges; //生效
重启master机的mysql服务：service mysqld restart
#mysql -uroot -pivgivgivg  //登陆
mysql> flush tables with read lock;
mysql>show master status //查看主日志情况
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      107 | test         | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
如果库中已经有数据,可以导出数据,再解锁:
mysqldump -u root -p123456 test > test.sql
mysql>unlock tables; 
注：这里锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次同步完成后，记得解锁。
主2服务器配置
vi /etc/my.cnf
# Replication Slave
server-id=2
log-bin=mysql-bin
replicate-do-db=test
replicate-ignore-db=mysql
#主主需加入的部分
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
binlog-do-db=test
binlog-ignore-db=mysql
#mysql -uroot -pivgivgivg  //登陆
如果之前主mysql有导出的数据,先导入数据到从mysql:
mysql-u root -p123456 test<test.sql
mysql>grant replication slave on *.* to 'copy'@'192.168.11.11' identified by 'ivgivgivg';//在master机上为slave机添加一同步帐号
mysql>flush privileges; //生效
重启master机的mysql服务：service mysqld restart
#mysql -uroot -pivgivgivg  //登陆
mysql>show master status //查看服务器情况
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 | test         | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
主1服务器再配置:
#mysql -uroot -pivgivgivg  //登陆
mysql> change master to master_host='192.168.11.12',master_user='copy',master_password='ivgivgivg',master_log_file='binlog.000005', master_log_pos=107; 
mysql> start slave;//配置连接主2服务器,启动从服务线程
主2服务器再配置:
#mysql -uroot -pivgivgivg  //登陆
mysql> change master to master_host='192.168.11.11',master_user='copy',master_password='ivgivgivg',master_log_file='binlog.000003', master_log_pos=107;//配置连接主1服务器
mysql> start slave;//启动从服务线程
注：master_log_file，master_log_pos由上面主服务器查出的状态值中确定。master_log_file对应File，master_log_pos对应Position。
分别登陆2台数据库，
mysql> show slave status\\G;//查看从服务器状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.11
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: node2-relay-bin.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           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: 107
              Relay_Log_Space: 555
              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: 
 Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR: 
No query specified
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这俩个都是也是就表示正常