mysql复制被阻塞_MySQL复制常见问题

master、slave同步状态

Slave_IO_Running：Slave从Master上读取log_bin，并写入Slave中继日志Relay_Log_File

Slave_SQL_Running：负责读取并且执行中继日志中的log_bin

单独停止IO、SQL线程

stop slave io_thread

stop slave sql_thread

关闭log_bin写入

mysql> set global sql_log_bin=OFF;

设置后，insert、update、delete等语句就不会再写入log_bin文件，但是用flush logs仍然会生成新的log_bin文件。

问题适用环境：配置单机多实例，从继承了log_bin参数，同样生成log_bin日志。

------------------------------------------------------------------

用户权限导致同步失败

错误：

Last_IO_Error: error connecting to master 'repli@192.168.60.134:3306' - retry-time: 60  retries: 86400

分析：测试，在slave端，用repli用户连接master。看能否连接master，是否存在权限问题

------------------------------------------------------------------

错误语句，导致数据不同步

错误：

Error 'Operation DROP USER failed for 'repli'@'%'' on query. Default database: ''. Query: 'drop user repli@'%''

分析：

跳过来自master的语句

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n

如果来自主服务器的更新语句不使用AUTO_INCREMENT或LAST_INSERT_ID()，n值应为1

否则，值应为2。

原因是使用AUTO_INCREMENT或LAST_INSERT_ID()的语句需要从二进制日志中取两个事件

处理(暂时理解是两个SQL语句，或许两个事务)：

stop slave;

set global sql_slave_skip_counter = 1

------------------------------------------------------------------

OLTP，减小主从数据差距

在负载较低的时候暂时阻塞主数据库的更新，强制主从数据库更新同步。这样会造成客户的体验效果。

在master上执行

mysql> flush tables with read lock;

再执行更新操作，会提示错误

mysql>  create table t1 (a int not null primary key , b varchar(100));

ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

记录log_bin和Position

mysql> show master status\G

*************************** 1. row ***************************

File: ora01-bin.000022

Position: 1289

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

在Slave上查看同步状态

mysql>show slave status\G

数据同步后，在master上执行

mysql>unlock tables;

---------------------------------------------------------------------

查看从服务器的复制进度

通过SHOW PROCESSLIST列表中的Slave_SQL_Running线程的Time值得到，它记录了从服务器当前执行的SQL时间戳与系统时间之间的差距，单位是秒

MySQL复制的机制是执行master传输过来的二进制日志，二进制日志中的每个语句通过设置时间戳来保证执行时间和顺序的正确性，所以每个语句执行之前都会首先设置时间戳，而通过查询这个进程的Time就可以知道最后设置的时间戳和当前时间的差距。

测试：

在master上插入一条带有时间戳的语句

mysql> insert into t select 4,now();

在slave上停止slave_io_running线程，停止写slave的中继日志。

过2分30秒左右，查看结果如下

mysql> show full processlist\G

*************************** 2. row ***************************

Id: 4

User: system user

Host:

db: NULL

Command: Connect

Time: 446

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

2 rows in set (0.00 sec)

mysql> select now()\G

*************************** 1. row ***************************

now(): 2011-05-24 19:56:07

1 row in set (0.00 sec)

mysql> select * from t\G

*************************** 4. row ***************************

a: 4

b: 2011-05-24 19:48:27

4 rows in set (0.00 sec)

19:56:07-19:48:27约等于446秒

-----------------------------------------------------------------------

slave上的master.info和relay-log.info文件

IO线程更新master.info文件

SQL线程更新relay-log.info文件

在master上没有这两个文件，如果slave切master需要删除。

文件中的行和SHOW SLAVE STATUS显示的列的对应关系为：

master.info文件：

行 描述

1 文件中的行号

2 Master_Log_File

3 Read_Master_Log_Pos

4 Master_Host

5 Master_User

6 密码(不由SHOW SLAVE STATUS显示)

7 Master_Port

8 Connect_Retry

9 Master_SSL_Allowed

10 Master_SSL_CA_File

11 Master_SSL_CA_Path

12 Master_SSL_Cert

13 Master_SSL_Cipher

14 Master_SSL_Key

relay-log.info文件：

行 描述

1 Relay_Log_File

2 Relay_Log_Pos

3 Relay_Master_Log_File

4 Exec_Master_Log_Pos

当备份从服务器的数据时，你还应备份这两个小文件以及中继日志文件。它们用来在恢复从服务器的数据后继续进行复制。如果丢失了中继日志但仍然有relay-log.info文件，你可以通过检查该文件来确定SQL线程已经执行的主服务器中二进制日志的程度。然后可以用Master_Log_File和Master_LOG_POS选项执行CHANGE MASTER TO来告诉从服务器重新从该点读取二进制日志。当然，要求二进制日志仍然在主服务器上。所以最好建议将自动删除中继日志的特性关闭，手工写shell角本来防止空间满的问题。

服务器认为master.info的优先级比配置文件my.cnf高，

第一次启动slave时，master.info不存在，它从my.cnf中读取选项值，然后把它们保存在master.info中。

下次重启slave时，它只读取master.info的内容，而不会读取my.cnf中的选项值。

想要使用不同的选项值，可以删除master.info后重启slave，或者使用CHANGE MASTER TO语句(推荐)重置选项值。

源文档

-----------------------------------------------------------------------

slave-skip-errors

在复制过程中，slave可能会遇到执行BINLOG中的SQL出错的情况(比如主键冲突)，默认情况下，从服务器将会停止复制进程，不再进行同步，等待用户介入处理。这种问题如果不能及时发现，将会对应用或者备份产生影响。此参数的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，这样当复制过程中遇到定义中的错误号时，便可以自动跳过，直接执行后面的SQL语句，以此来最大限度地减少人工干预。此参数可以定义多个错误号，或者通过定义成all跳过全部的错误。具体语法如下：

--slave-skip-errors=[err_code1,err_code2,... | all]

如果从数据库主要是作为主数据库的备份，那么就不应该使用这个启动参数，设置不当，很可能造成主从数据库的数据不同步。但是，如果从数据库仅仅是为了分担主数据库的查询压力，且对数据的完整性要求不是很严格，那么这个选项的确可以减轻数据库管理员维护从数据库的工作量。