时间：2021-07-01 10:21:17 帮助过：21人阅读
我就是前晚经历了这一次的宝贵教训(两个从的库居然是多了一两个，还要都有不同的库存在)，正苦恼于自己当时的忘记，我就唯有将主从重组，首先在从上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;
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