對于已經做完mysql主從發現show slave status\G;檢視mysql-bin-0000XX,slave等這些資訊一緻時候很開心，但當你在主上删除一個庫裡面一個表再到從上檢視才發現資訊不能同步一緻的結果令人很郁悶...
<a href="http://s2.51cto.com/wyfs02/M00/7D/15/wKiom1bfxojzFT-mAABKXPq0Oj0475.png" target="_blank"></a>
<a href="http://s2.51cto.com/wyfs02/M00/7D/13/wKioL1bfxwvDZysyAABM4D8p3ec591.png" target="_blank"></a>
我就是前晚經曆了這一次的寶貴教訓(兩個從的庫居然是多了一兩個，還要都有不同的庫存在)，正苦惱于自己當時的忘記，我就唯有将主從重組，首先在從上stop slave後将和主的不一樣的庫删除掉，分别再進入主從裡面确認兩邊的庫資訊一樣（這個要對照清楚，不然你又再做好主從才發現有錯漏時候又要将主從重組),确認無誤後再做主從各自的操作，今天，我終于将主從重組成功後檢視發現主從的資料庫資訊終于可以一緻了
（當時心裡很緊張會不會成功重新同步了)
主：directory 從1：slave1 從2：slave2
[root@directory ~]# mysql -S /tmp/mysql.sock -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.72-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
| information_schema |
| db2 |
| discuz |
| mysql |
4 rows in set (0.06 sec)
mysql> use db2;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_db2 |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| tb2 |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
23 rows in set (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000007 | 106 | db2 | |
1 row in set (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> unlock tables;
mysql> show processlist;
+----+------+---------------------+------+-------------+------+----------------- -----------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
| 1 | root | localhost | db2 | Query | 0 | NULL | show processlist |
| 2 | repl | 192.168.0.141:54535 | NULL | Binlog Dump | 262 | Has sent all bin log to slave; waiting for binlog to be updated | NULL |
| 3 | repl | 192.168.0.140:34429 | NULL | Binlog Dump | 142 | Has sent all bin log to slave; waiting for binlog to be updated | NULL |
3 rows in set (0.00 sec)
mysql> drop table help_keyword;
Query OK, 0 rows affected (0.01 sec)
22 rows in set (0.01 sec)
mysql> drop table help_relation;
[root@slave1 ~]# mysql -S /tmp/mysql.sock -p123456
Your MySQL connection id is 4
Server version: 5.1.51-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
mysql> stop slave;
mysql> reset slave;
Query OK, 0 rows affected (0.04 sec)
mysql> change master to master_host='192.168.0.8',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=106;
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.8
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 106
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db2
Replicate_Ignore_DB:
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: 106
Relay_Log_Space: 400
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:
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> show tables
-> ;
| help_category |
23 rows in set (0.00 sec)
| help_category |
22 rows in set (0.00 sec)
PS:從庫多出一個help_category 的表，是因為之前主從資料庫不一緻時候，在主删除了一個表來嘗試導緻的.現在其餘的表再删除就可以同步了
本文轉自wsw26 51CTO部落格，原文連結：http://blog.51cto.com/wsw26/1749125，如需轉載請自行聯系原作者
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)