环境配置：
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
这俩个都是也是就表示正常
