mysql主从数据库不同步的2种解决方法

今天发现Mysql的主从数据库没有同步,很是疑惑，于是搜索整理了下，接下来介绍解决方法，有感兴趣的朋友可以参考下

今天发现Mysql的主从数据库没有同步 
先上Master库： 
mysql>show processlist; 查看下进程是否Sleep太多。发现很正常。 
show master status; 也正常。 

mysql> show master status; 
+-------------------+----------+--------------+-------------------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+-------------------+----------+--------------+-------------------------------+ 
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema | 
+-------------------+----------+--------------+-------------------------------+ 
1 row in set (0.00 sec) 

再到Slave上查看 

mysql> show slave status\G 
Slave_IO_Running: Yes 
Slave_SQL_Running: No 

可见是Slave不同步 

下面介绍两种解决方法： 
方法一：忽略错误后，继续同步 
该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况 
解决： 
stop slave; 
#表示跳过一步错误，后面的数字可变 
set global sql_slave_skip_counter =1; 
start slave; 
之后再用mysql> show slave status\G 查看： 

mysql> show slave status\G
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 

ok，现在主从同步状态正常了。。。 

方式二：重新做主从，完全同步 
该方法适用于主从库数据相差较大，或者要求数据完全统一的情况 
解决步骤如下： 
1.先进入主库，进行锁表，防止数据写入 
使用命令： 

mysql> flush tables with read lock; 

注意：该处是锁定为只读状态，语句不区分大小写 
2.进行数据备份 
#把数据备份到mysql.bak.sql文件 

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql 

这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失 
3.查看master 状态 

mysql> show master status; 
+-------------------+----------+--------------+-------------------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+-------------------+----------+--------------+-------------------------------+ 
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema | 
+-------------------+----------+--------------+-------------------------------+ 
1 row in set (0.00 sec) 

4.把mysql备份文件传到从库机器，进行数据恢复 
#使用scp命令 

[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/ 

5.停止从库的状态 

mysql> stop slave; 

6.然后到从库执行mysql命令，导入数据备份 

mysql> source /tmp/mysql.bak.sql 

7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项 

change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260; 

8.重新开启从同步 

mysql> stop slave; 

9.查看同步状态 

mysql> show slave status\G
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 

好了，同步完成啦。

注：man mysqldump

1. Stop the slave's SQL thread and get its current status:

                   mysql> STOP SLAVE SQL_THREAD;
                   mysql> SHOW SLAVE STATUS;

            2. From the output of the SHOW SLAVE STATUS statement, the binary log coordinates of the master server from which the new slave
               should start replicating are the values of the Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote those values as
               file_name and file_pos.

            3. Dump the slave server:

                   shell> mysqldump --master-data=2 --all-databases > dumpfile

               Using --master-data=2 works only if binary logging has been enabled on the slave. Otherwise, mysqldump fails with the error
               Binlogging on server not active. In this case you must handle any locking issues in another manner, using one or more of
               --add-locks, --lock-tables, --lock-all-tables, or --single-transaction, as required by your application and environment.

            4. Restart the slave:

                   mysql> START SLAVE;

            5. On the new slave, load the dump file:

                   shell> mysql < dumpfile

            6. On the new slave, set the replication coordinates to those of the master server obtained earlier:

                   mysql> CHANGE MASTER TO
                       -> MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;

               The CHANGE MASTER TO statement might also need other parameters, such as MASTER_HOST to point the slave to the correct master
               server host. Add any such parameters as necessary.