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  查看:

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> start slave;

9.查看同步状态

mysql> show slave status\G  查看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

10、回到主库并执行:UNLOCK TABLES;

 

好了,同步完成啦。

======================================================================

-- ===================================方法一:跳过错误数===================================

show slave status;

stop slave;

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1000;

start slave;

-- ===================================方法二:重新设置主从===================================

-- master 执行获取 master_log_file 和 master_log_pos

show master status;

-- slave 执行

stop slave;

change master to master_host="129.204.194.53", master_user="root", master_password="root", master_port=3306, master_log_file="mysql-bin.000018", master_log_pos=244160466;

start slave;

======================================

查看server_id、server_uuid

show variables like 'server_id';

show variables like 'server_uuid';

======================================

SET GLOBAL log_bin_trust_function_creators =0;

stop slave;

change master to master_host="129.204.194.53", master_user="root", master_password="SJHT...999)))", master_port=3306, master_log_file="mysql-bin.000082", master_log_pos=122770;

start slave;

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000081',MASTER_LOG_POS=154;

change master to master_log_file='mysql-bin.000081',master_log_pos=10034;

show variables like 'server_id';

show slave status;

show variables like 'server_uuid';

stop slave;

reset slave;

start slave;

stop slave;

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

start slave;

show slave status;

show variables like 'log_bin_trust_function_creators';

SET GLOBAL log_bin_trust_function_creators = 0;

CREATE FUNCTION `getChildLst`(rootId VARCHAR(32))

RETURNS varchar(1024)

READS SQL DATA

BEGIN

DECLARE sTemp VARCHAR(1024);

DECLARE sTempChd VARCHAR(1024);

SET sTemp = '$';

SET sTempChd =cast(rootId as CHAR);

WHILE sTempChd is not null DO

SET sTemp = concat(sTemp,',',sTempChd);

SELECT group_concat(id) INTO sTempChd FROM sys_org where FIND_IN_SET(parent_id,sTempChd)>0;

END WHILE;

RETURN sTemp;

END

show variables like '%relay%';
————————————————
版权声明:本文为CSDN博主「王晓茵」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_33784667/article/details/113306204

posted @ 2021-04-09 09:43  zhouyuqiang  阅读(154)  评论(0编辑  收藏  举报