Linux---MySQL主从配置相关报错合集

Love The Way You Lie 2022-01-10 20:07 260阅读 0赞

**mysql 数据同步 出现Slave\_IO\_Running:No或者Slave\_Sql\_Running:No问题的解决方法小结**

mysql主从复制经常会遇到错误而导致slave端复制中断这个时候一般就需要人工干预跳过错误才能继续。

如果是slave\_io\_running no了那么就我个人看有三种情况一个是网络有问题连接不上第二个是有可能my.cnf有问题配置文件怎么写就不说了网上太多了最后一个是授权的问题slave上没有权限读master上的数据replication slave和file权限是必须的。

一旦io为no了先看err日志看看报什么错很可能是网络也有可能是包太大收不了这个时候主备上改max\_allowed\_packet这个参数。 max\_allowed\_packet=1024M 可在 vi /etc/my.cnf中的\[mysqld\]中添加

如果是slave\_sql\_running no了那么也有两种可能一种是slave机器上这个表中出现了其他的写操作就是程序写了这个是会有问题的还有一种占绝大多数可能的是slave进程重启事务回滚造成的这也是mysql的一种自我保护的措施像关键时候只读一样。

跳过错误有两种方式

1.跳过指定数量的事务

mysql>slave
    stop;
    mysql>SET
    GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳过一个事务
    mysql>slave
    start

其中SQL\_SLAVE\_SKIP\_COUNTER=N;再开一下slave就可以了这个全局变量赋值为N的意思是

This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.

This statement is valid only when the slave thread is not running. Otherwise, it produces an error.

2.修改mysql的配置文件通过slave\_skip\_errors参数来跳所有错误或指定类型的错误

vi /etc/my.cnf

[mysqld]
    slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
    slave-skip-errors=all #跳过所有错误

相关错误实例

1、出现错误提示、

Slave I/O: error connecting to master 'backup@192.168.1.x:3306' - retry-time: 60 retries: 86400, Error\_code: 1045

解决方法

检查网络密码以及权限相关的设置是否有误

2、出现错误提示

Error reading packet from server: File '/home/mysql/mysqlLog/log.000001' not found (Errcode: 2) ( server\_errno=29)

解决方案

由于主服务器运行了一段时间产生了二进制文件而slave是从log.000001开始读取的删除主机二进制文件包括log.index文件。

3、错误提示如下

Slave SQL: Error 'Table 'xxxx' doesn't exist' on query. Default database: 't591'. Query: 'INSERT INTO \`xxxx\`(type,post\_id,browsenum) SELECT type,post\_id,browsenum FROM xxxx WHERE hitdate='20090209'', Error\_code: 1146

解决方法

由于slave没有此table表添加这个表使用slave start 就可以继续同步。

4、错误提示如下

Error 'Duplicate entry '1' for key 1' on query. Default database: 'movivi1'. Query: 'INSERT INTO \`v1vid0\_user\_samename\` VALUES(null,1,'123','11','4545','123')'

Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'club'. Query: 'INSERT INTO club.point\_process ( GIVEID, GETID, POINT, CREATETIME, DEMO ) VALUES ( 0, 4971112, 5, '2010-12-19 16:29:28','

1 row in set (0.00 sec)

Mysql > Slave status\\G;

显示Slave\_SQL\_Running 为 NO

解决方法

Mysql > stop slave;
    Mysql > set global sql_slave_skip_counter =1 ;
    Mysql > start slave;

5、错误提示如下

\# show slave status\\G;

Master\_Log\_File: mysql-bin.000029

Read\_Master\_Log\_Pos: 3154083

Relay\_Log\_File: c7-relay-bin.000178

Relay\_Log\_Pos: 633

Relay\_Master\_Log\_File: mysql-bin.000025

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: No

Replicate\_Do\_DB: club

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 1594

Last\_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 1010663436

这个问题原因是主数据库突然停止或问题终止更改了mysql-bin.xxx日志slave服务器找不到这个文件需要找到同步的点和日志文件然后chage master即可。

解决方法

change master to

master\_host='211.103.156.198',

master\_user='mysql,

master\_password='password

master\_port=3306,

master\_log\_file='mysql-bin.000025',

master\_log\_pos=1010663436;

6、错误提示如下

Error 'Unknown column 'qdir' in 'field list'' on query. Default database: 'club'. Query: 'insert into club.question\_del (id, pid, ques\_name, givepoint, title, subject, subject\_pid, createtime, approve, did, status, intime, order\_d, endtime,banzhu\_uid,banzhu\_uname,del\_cause,qdir) select id, pid, ques\_name, givepoint, title, subject, subject\_pid, createtime, approve, did, status, intime, order\_d, endtime,'1521859','admin0523','无意义回复',qdir from club.question where id=7330212'

1 row in set (0.00 sec)

这个错误就说club.question\_del 表里面没有qdir这个字段 造成的加上就可以了~

在主的mysql 里面查询 Desc club.question\_del

在 错误的从服务器上执行 alter table question\_del add qdir varchar(30) not null;

7、错误提示如下

Slave\_IO\_Running: NO

这个错误就是IO 进程没连接上 想办法连接上把 把与主的POS 号和文件一定要对然后重新加载下数据。具体步骤

slave stop;
    change master to master_host='IP地址',master_user='club',master_password='mima
    ',master_log_file='mysqld-bin.000048',MASTER_LOG_POS=396549485;

注master\_log\_file='mysqld-bin.000048',MASTER\_LOG\_POS=396549485;是从主的上面查出 来的

show master status\\G;

LOAD DATA FROM MASTER;

load data from master

slave start;

8、错误提示如下

Error
    reading packet from server:
     
    Access denied; you need the REPLICATION SLAVE privilege for this operation (
    server_errno=1227)

【解决办法】主服务器给的复制权限不够重新赋予权限。

>
    grant replication slave on *.* to 'mysql'@'master IP' identified by 'password'
    >
    flush privileges

转载于:https://blog.51cto.com/ezioauditor/1752450