提高mysql主从复制_MySQL主从复制的一些心得体会

MySQL复制

MySQL复制支持单向，异步复制。通过一台主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。MySQL主从复制是异步进行的。同步需要版本为5.5,使用google提供的插件来实现。

MySQL主从复制操作很灵活既可以实现整个服务的级别的复制，也可以实现对某个库，甚至某个数据库中的指定的某个对象进行复制。

MySQL主从复制应用场景

提高性能。通过一(多)主多从的部署方案，将涉及数据写的操作放在Master端操作，而将数据读的操作分散到众多的Slave当中。降低了Master的负载，提高数据写入的响应效率；多台从服务器提供读，分担了请求，提高了读的效率。

数据安全。数据复制到Slave节点，即使Master宕机，Slave节点还保存着完整数据。

数据分析。将数据分析，放在slave上。

主从复制的原理

MySQL的Replication是一个异步的复制过程，从一个MySQL实例(Master)复制到另一台MySQL实例上。在Master和Slave之间复制的整个过程主要由3个线程完成，其中两个线程(SQL线程和IO线程)在Slave端，另外一个线程(IO线程)在Master端。

要实现主从复制，首先要在Master端打开Binary Log功能。因为整个复制过程实际上就是Slave从Master上获取二进制日志，然后在自己身上完全按照产生的顺序一次执行日志中记录的各种操作的过程。

复制的具体过程如下：

MySQL主从复制原理图

Slave的IO线程连上Master，并请求日志文件指定位置(或从开始的日志)之后的日志的内容。

Master接收到来自Slave的IO线程请求后，负责复制IO线程根据请求的信息读取指定日志之后的日志信息，返回给Slave端的IO线程。返回信息中除了日志所包含的信息，还包含了包括本次返回的信息在Master端的Binary Log文件的名称和位置。

Slave的IO线程接受到信息后，将日志内容一次写入Slave端的Relay Log文件(mysql-relay-bin.xxxx)的末端，并将读取到的Master端的bin-log的文件和位置记录到master-info文件中，以便在下一次读取时能够清楚地告诉Master，下次从bin-log哪个位置开始往后的日志内容。

Slave的SQL线程检测检测到Relay Log中更新内容后，会马上解析该Log文件中的内容，还原成在Master端真实执行时的可执行的SQL语句，并执行这些SQK语句。实际上Master和Slave执行同样的语句。

Binary Log 记录方式

Row Level

Binary Log会记录成每一行数据被修改的形式，然后在Slave端再对相同的数据进行修改。

优点：在Row Level模式下，Binnary Log可以不记录执行的Query语句的上下文相关信息，只要记录哪一行修改了，修改成什么样子。Row Level会详细的记录下每一行数据的修改细节，而且不会出现某个特定情况下的存储过程，或Function，以及Trigger的调用和触发无法被正确复制问题。

缺点：产生大量的日志内容。

Statment Level

每一条会修改的SQL语句都会记录到Master的Binnary中。Slave端在复制的时候，SQL线程会解析成和原来Master端执行过相同的SQL语句，并再次执行。

优点：首先，解决了Row Level下的缺点，不须要记录每一行的数据变化，减少了Binnary Log日志量，节约了IO成本，提高了性能。

缺点：由于它是记录的执行语句，为了让这些语句在Slave端也能正确执行。那么它还必须记录每条语句在执行时的一些相关信息，即上下文信息，以保证所有语句在Slave端被执行的时候能够得到和在Master端执行时相同的结果。另外，由于MySQL发展比较快，很多新功能不断加入，使得MySQL复制遇到了不小的挑战，复制时设计的内容岳父在，越容易出bug。在Statement Level下，目前已发现不少的情况下会造成MySQL的复制问题。主要是在修改数据使用了某些特定的函数货功能后，出现，比如：Sleep()函数在有些版本中就不能正确的复制，在存储过程中使用了last_insert_id()函数，可能会使Slave和Master的到不一致的ID，等等。

Mixed Level

在Mixed模式下，MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。除了MySQL认为通过Statement方式可能造成复制过程中Master和Slave之间产生不一致数据。(如特殊Procedure和Funtion的使用，UUID()函数的使用等特殊情况)时，它会选择ROW的模式来记录变更之外，都会使用Statement方式。

mysql主从延时

有很多种原因会造成mysql的主从延时，通过查看Seconds_Behind_Master:的值来确定是否存在延迟问题，该值越大，延迟现象越明显。众所周知备库relay-log和主库的bin-log里的内容一样，真正和主库有关两的是io_thread，当主库I/O负载很大或网络阻塞时，io_thread不能及时复制binlog，而sql_thread直能跟上io_thread的脚步，这时seconds_behind_master的值是0，实际上却不是，这时用该值作为延迟参考则不准。我们可以使用pt-heartbeat来监控延时现象。该工具定期在主库上不断写入时间戳，通过主从复制同步到从库中去，此时从库服务器的时间和主库保持一致，通过比较从服务器上同步过来的主库时间戳和从库系统时间戳的差值的大小来确定是否存在延迟现象。

主从延迟的主要原因是：主库多线程并发更新，从库单线程串行更新。解决方法：将从库变为多线程更新，可以使用mysql-transfer：是一个基于mysql的patch，用来加速主从同步速度。

一般来说，我们遇到了主从延时的问题，如果不是高并发原因的话，可以手动临时解决一下：

方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况

解决：

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.把mysql备份文件传到从库机器，进行数据恢复

#使用scp命令

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

4.停止从库的状态

mysql> stop slave;

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

mysql> source /tmp/mysql.bak.sql

6.设置从库同步，注意该处的同步点，就是主库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;

7.重新开启从同步

mysql> stop slave;

8.查看同步状态

mysql> show slave status\G 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

好了，同步完成啦。