mysql数据库双向同步的设置

2012-7-19 10:14:12 来源:网络转载 浏览:695
如果你的两台服务器上都装有Mysql数据库,但是有时候需要将A机器里面的数据库整合到B里面或许就略显有些麻烦,也许当您看了这篇文章就能做到了—mysql数据库双向同步的设置,它可以实时的更新被同步的数据库中的信息。
环境:
Master server: 10.224.194.239
Slave server: 10.224.194.237
步骤:
1.分别在Master/Slaver mysql db 创建backup user:
GRANT FILE ON *.* TO backup@10.224.194.239 IDENTIFIED BY 'pass'; GRANT REPLICATION SLAVE ON *.* TO backup@10.224.194.239 IDENTIFIED BY 'pass';  GRANT FILE ON *.* TO backup@10.224.194.237 IDENTIFIED BY 'pass'; GRANT REPLICATION SLAVE ON *.* TO backup@10.224.194.237 IDENTIFIED BY 'pass';
2.在Master server配置/etc/my.cf 文件:
server-id = 1 binlog-do-db=test binlog-ignore-db = mysql  replicate-do-db=test replicate-ignore-db = mysql  master-host=10.224.194.237 master-user=backup master-password=pass master-port=3306 master-connect-retry=60  slave-skip-errors=all
3.在Master server配置/etc/my.cf 文件:
server-id = 2  binlog-do-db=test binlog-ignore-db = mysql  replicate-do-db=test replicate-ignore-db = mysql  master-host=10.224.194.239 master-user=backup master-password=pass master-port=3306 master-connect-retry=60  slave-skip-errors=all
4.重启mysql数据库,验证命令如下:
查看Master状态
show master status;
mysql> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysqllog.000003 |      301 | test         | mysql            |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看Slave状态
show slave status \G;
mysql> show slave status \G;
*************************** 1. row ***************************              
Slave_IO_State: Reconnecting after a failed master event read                 
Master_Host: 10.224.194.239                 
Master_User: backup                 
Master_Port: 3306               
Connect_Retry: 60             
Master_Log_File: mysqllog.000003         
Read_Master_Log_Pos: 301              
Relay_Log_File: mysqlgsb-relay-bin.000082               
Relay_Log_Pos: 348       
Relay_Master_Log_File: mysqllog.000003            
Slave_IO_Running: No           
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: 301             
Relay_Log_Space: 650             
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: NULLMaster_SSL_Verify_Server_Cert: No               
Last_IO_Errno: 0               
Last_IO_Error:               
Last_SQL_Errno: 0              
Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified
查看同步进程:
mysql> show processlist \G;
*************************** 1. row ***************************     
Id: 1   
User: system user   
Host:      
db: NULL Command: Connect   
Time: 4186  
State: Waiting for master to send event   
Info: NULL
*************************** 2. row ***************************     
Id: 2   
User: system user   
Host:      
db: NULL Command: Connect   
Time: 3745  
State: Has read all relay log; waiting for the slave I/O thread to update it   
Info: NULL
*************************** 3. row ***************************     
Id: 5   
User: root   
Host: mysqlpri.webex.com:28293     
db: NULL Command: Query   
Time: 0   State: NULL   
Info: show processlist
*************************** 4. row ***************************     
Id: 6   
User: backup   
Host: 10.224.194.237:41729     
db: NULL Command: Binlog Dump   
Time: 135  
State: Has sent all binlog to slave;
waiting for binlog to be updated   
Info: NULL 4 rows in set (0.00 sec) 
ERROR:  No query specified
(0)
(0)