本帖最后由 wsw13640218682 于 2016-3-9 14:40 编辑
对于已经做完mysql主从发现show slave status\G;查看mysql-bin-0000XX,slave等这些信息一致时候很开心，但当你在主上删除一个库里面一个表再到从上查看才发现信息不能同步一致的结果令人很郁闷...
我就是前晚经历了这一次的宝贵教训(两个从的库居然是多了一两个，还要都有不同的库存在)，正苦恼于自己当时的忘记，我就唯有将主从重组，首先在从上stop slave后将和主的不一样的库删除掉，分别再进入主从里面确认两边的库信息一样（这个要对照清楚，不然你又再做好主从才发现有错漏时候又要将主从重组),确认无误后再做主从各自的操作，今天，我终于将主从重组成功后查看发现主从的数据库信息终于可以一致了{:4_102:}（当时心里很紧张会不会成功重新同步了)
主：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;
Query OK, 0 rows affected (0.00 sec)
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> 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> drop table help_keyword;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------------------+
| Tables_in_db2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| 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 |
+---------------------------+
22 rows in set (0.01 sec)
mysql> drop table help_relation;
Query OK, 0 rows affected (0.00 sec)
[root@slave1 ~]# mysql -S /tmp/mysql.sock -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
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
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
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;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
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> use db2;
Database changed
mysql> show tables
-> ;
+---------------------------+
| Tables_in_db2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| 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.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_db2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| 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 |
+---------------------------+
22 rows in set (0.00 sec)
PS:从库多出一个help_category 的表，是因为之前主从数据库不一致时候，在主删除了一个表来尝试导致的.现在其余的表再删除就可以同步了
对于已经做完mysql主从发现show slave status\G;查看mysql-bin-0000XX,slave等这些信息一致时候很开心，但当你在主上删除一个库里面一个表再到从上查看才发现信息不能同步一致的结果令人很郁闷...
我就是前晚经历了这一次的宝贵教训(两个从的库居然是多了一两个，还要都有不同的库存在)，正苦恼于自己当时的忘记，我就唯有将主从重组，首先在从上stop slave后将和主的不一样的库删除掉，分别再进入主从里面确认两边的库信息一样（这个要对照清楚，不然你又再做好主从才发现有错漏时候又要将主从重组),确认无误后再做主从各自的操作，今天，我终于将主从重组成功后查看发现主从的数据库信息终于可以一致了{:4_102:}（当时心里很紧张会不会成功重新同步了)
主：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;
Query OK, 0 rows affected (0.00 sec)
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> 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> drop table help_keyword;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------------------+
| Tables_in_db2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| 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 |
+---------------------------+
22 rows in set (0.01 sec)
mysql> drop table help_relation;
Query OK, 0 rows affected (0.00 sec)
[root@slave1 ~]# mysql -S /tmp/mysql.sock -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
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
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
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;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
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> use db2;
Database changed
mysql> show tables
-> ;
+---------------------------+
| Tables_in_db2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| 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.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_db2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| 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 |
+---------------------------+
22 rows in set (0.00 sec)
PS:从库多出一个help_category 的表，是因为之前主从数据库不一致时候，在主删除了一个表来尝试导致的.现在其余的表再删除就可以同步了
0
我记得这个好像也有集成模块可以弄的吧...之前看到过，但是不太清楚具体的
wsw13640218682 发表于 2016-3-11 19:30
恩，是的..重要是看清楚，不过主从数据不同步的故障，我想这个也是可以作为一个解决办法吧
我记得这个好像也有集成模块可以弄的吧...之前看到过，但是不太清楚具体的
0
你好，问下这里配置的一主多从，其实和配置一主一从对比需要注意的地方，就是多个从都要先保证其中多个从的数据库、表都要一样了。才能完成同步是么。这样我就可以直接将主的那个数据库备份出来，然后分别导入到三个从上的mysql上去就保证一样了。配置啥的同配置一主一丛是吧。
0
无论一主一从还是一主多从的表是需要一致，方法不单单只是我那种的，觉得适合自己的就好，所以你说的主备份出来然后导入到从也是可以..
hmh 发表于 2016-7-14 01:15
你好，问下这里配置的一主多从，其实和配置一主一从对比需要注意的地方，就是多个从都要先保证其中多个从的 ...
无论一主一从还是一主多从的表是需要一致，方法不单单只是我那种的，觉得适合自己的就好，所以你说的主备份出来然后导入到从也是可以..
编辑回复