1mysql主从同步原理介绍

Replication 线程

Mysql Replication 是一个异步的复制过程,从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master Slave 之间的实现整个复制过程主要由三个线程来完成,其中两个线程(Sql线程和IO线程) Slave 端,另外一个线程(IO线程) Master 端。

要实现 MySQL Replication ,首先必须打开 Master 端的Binary Log(mysql-bin.xxxxxx)功能,否则无法实现。因为整个复制过程实际上就是SlaveMaster端获取该日志然后再在自己身上完全 顺序的执行日志中所记录的各种操作。打开 MySQL Binary Log 可以通过在启动 MySQL Server 的过程中使用 “—log-bin” 参数选项,或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。

MySQL 复制的基本过程如下:

1.1 Slave 上面的IO线程连接上 Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

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

1.3 Slave IO 线程接收到信息后,将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端,并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中,以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我

1.4 Slave SQL 线程检测到 Relay Log 中新增加了内容后,会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句,并在自身执行这些 Query。这样,实际上就是在 Master 端和 Slave 端执行了同样的 Query,所以两端的数据是完全一样的。

实际上,在老版本中,MySQL 的复制实现在 Slave 端并不是由 SQL 线程和 IO 线程这两个线程共同协作而完成的,而是由单独的一个线程来完成所有的工作。但是 MySQL 的工程师们很快发现,这样做存在很大的风险和性能问题,主要如下:

首先,如果通过一个单一的线程来独立实现这个工作的话,就使复制 Master 端的,Binary Log日志,以及解析这些日志,然后再在自身执行的这个过程成为一个串行的过程,性能自然会受到较大的限制,这种架构下的 Replication 的延迟自然就比较长了。

其次,Slave 端的这个复制线程从 Master 端获取 Binary Log 过来之后,需要接着解析这些内容,还原成 Master 端所执行的原始 Query,然后在自身执行。在这个过程中,Master端很可能又已经产生了大量的变化并生成了大量的 Binary Log 信息。如果在这个阶段 Master 端的存储系统出现了无法修复的故障,那么在这个阶段所产生的所有变更都将永远的丢失,无法再找回来。这种潜在风险在Slave 端压力比较大的时候尤其突出,因为如果 Slave 压力比较大,解析日志以及应用这些日志所花费的时间自然就会更长一些,可能丢失的数据也就会更多。

所以,在后期的改造中,新版本的 MySQL 为了尽量减小这个风险,并提高复制的性能,将 Slave 端的复制改为两个线程来完成,也就是前面所提到的 SQL 线程和 IO 线程。最早提出这个改进方案的是Yahoo!的一位工程师“Jeremy Zawodny”。通过这样的改造,这样既在很大程度上解决了性能问题,缩短了异步的延时时间,同时也减少了潜在的数据丢失量。

当然,即使是换成了现在这样两个线程来协作处理之后,同样也还是存在 Slave 数据延时以及数据丢失的可能性的,毕竟这个复制是异步的。只要数据的更改不是在一个事务中,这些问题都是存在的。

如果要完全避免这些问题,就只能用 MySQL Cluster 来解决了。不过 MySQL Cluster 知道笔者写这部分内容的时候,仍然还是一个内存数 据库的解决方案,也就是需要将所有数据包括索引全部都 Load 到内存中,这样就对内存的要求就非常大的大,对于一般的大众化应用来说可实施性并不是太大。当然,在之前与 MySQL CTO David 交流的时候得知,MySQL 现在正在不断改进其 Cluster 的实现,其中非常大的一个改动就是允许数据不用全部 Load 到内存中,而仅仅只是索引全部 Load 到内存中,我相信在完成该项改造之后的 MySQL Cluster 将会更加受人欢迎,可实施性也会更大。

 

2、环境说明

mysql数据库master节点:192.168.1.201  web01.xie CentOS release 5.6 x86_64

mysql数据库slave节点:192.168.1.202   web02.xie CentOS release 5.6 x86_64

 

3、本地yum源安装mysql(因为我的测试机不能联网,所以采用此种方式安装)

在两个mysql节点做如下操作:

 

  1. mkdir -p /mnt/dvd/ 
  2. mount /dev/cdrom /mnt/dvd/ 
  3. cd /mnt/dvd/CentOS/ 
  4. rpm -ivh createrepo-0.4.11-3.el5.noarch.rpm 
  5. cd /mnt/ 
  6. createrepo ./ 
  7. cd /etc/yum.repos.d/ 

将此目录下所有文件修改为非repo结尾的文件。

  1. vi /etc/yum.repos.d/dvd.repo 

添加如下内容:

  1. [DVDISO] 
  2. name=DVD ISO 
  3. baseurl=file:///mnt/ 
  4. enabled=1 
  5. gpgcheck=0  
  1. kill -HUP `cat /var/run/yum.pid` 
  2. yum clean all 
  3. yum list 
  4. yum install mysql* -y 
  5. /etc/init.d/mysqld start 

 

4、配置mysql主从模式

4.1 mysql数据库master节点配置:

  1. cp /usr/share/mysql/my-large.cnf /etc/my.cnf 
  2. vi /etc/my.cnf 

去掉#log-bin=mysql-bin行前的注释。

建立同步用的数据库账号:

  1. mysql 
  2. mysql> grant replication slave on *.* to 'copyuser'@'192.168.1.202' identified by 'copyuser.123'; 

锁住主库表,停止数据更新:

  1. mysql> flush tables with read lock; 

此时所有数据库内的表只提供读的操作,不能再写入。

显示主库信息并记录:

  1. mysql> show master status; 

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      246 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

将上面的日志名mysql-bin.000002和偏移量246记录下来。

 

备份所有库文件并复制到从库服务器上:

备份有很多种方法,如果在同一局域网内可直接将主库上的数据库复制到从库服务器上,如果不同网段可先将主库上的数据库打包压缩,然后传到从库上再解压,测试环境是局域网,那么就使用下面方法进行:

  1. mount 192.168.1.202:/var/lib/mysql /mnt //将从库的/var/lib/mysql挂载到主库的/mnt目录上 
  2. cp -r /var/lib/mysql  /mnt //复制主库的数据库到从库服务器上 

遇到的问题:

mount: 192.168.1.202:/var/lib/mysql failed, reason given by server: Permission denied

到从库上找到/etc/export进行编辑,加入共享路径:

  1. vi /etc/exports 

添加如下内容:

  1. /var/lib/mysql  192.168.1.202(ro,rw,no_root_squash) 

重启从库服务器NFS服务

  1. /etc/init.d/nfs restart 

数据库复制完成后对主库所有表解锁:

  1. mysql> unlock tables; 

 

4.2 mysql数据库slave节点配置:

  1. cp /usr/share/mysql/my-large.cnf /etc/my.cnf 
  2. vi /etc/my.cnf 

修改内容如下:

  1. server-id = 2 将这个ID号改为2 
  2. log-bin=mysql-bin 此项如果开启建议关闭 
  3. master-host = 192.168.1.202 配置主库的IP 
  4. master-user =copyuser 同步用的账户 
  5. master-password = <password> 同步用的账户密码 
  6. master-port = 3306 同步数据库的端口号  

调整权限:

主库复制过来的数据库全部都是root权限,需要修改为mysql权限

  1. chown -R mysql:mysql /var/lib/mysql 

重启mysql服务:

  1. /etc/init.d/mysqld restart 

 

手动同步:

 

  1. mysql> CHANGE MASTER TO 
  2. MASTER_HOST='192.168.1.201'
  3. MASTER_USER='copyuser'
  4. MASTER_PASSWORD='<password>'
  5. MASTER_PORT=3306
  6. MASTER_LOG_FILE='mysql-bin.000002'
  7. MASTER_LOG_POS=246
  8. MASTER_CONNECT_RETRY=60

此项操作遇到的问题:

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

根据提示先停止slave

  1. mysql> stop slave; 

执行完手动同步之后,再开启slave

  1. mysql> start slave; 

配置到这里几本完成。

 

检查从库是否正常同步:

  1. mysql>show slave status \G; 

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

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.1.201

                Master_User: copyuser

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000002

        Read_Master_Log_Pos: 892

             Relay_Log_File: mysqld-relay-bin.000004

              Relay_Log_Pos: 794

      Relay_Master_Log_File: mysql-bin.000002

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB:

        Replicate_Ignore_DB:

         Replicate_Do_Table:

     Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

                 Last_Errno: 0

                 Last_Error:

               Skip_Counter: 0

        Exec_Master_Log_Pos: 892

            Relay_Log_Space: 794

            Until_Condition: None

             Until_Log_File:

              Until_Log_Pos: 0

         Master_SSL_Allowed: No

         Master_SSL_CA_File:

         Master_SSL_CA_Path:

            Master_SSL_Cert:

          Master_SSL_Cipher:

             Master_SSL_Key:

      Seconds_Behind_Master: 0

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

标红的两个线程显示状态为yes,说明正常。

5其他命令

5.1 查看复制进度:

需要在主库上运行

  1. mysql>show processlist \G; 

*************************** 84. row ***************************

     Id: 9134

   User: db2009421jlg

   Host: 192.168.1.2:1384

     db: www_armorus_com_cn

Command: Sleep

   Time: 2

  State:

   Info: NULL

*************************** 85. row ***************************

     Id: 9135

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: NULL

   Info: show processlist

85 rows in set (0.00 sec)

 

ERROR:

No query specified

 

Time可以看出此数据库刚同步完

 

5.2 主服务器上的相关命令:

show processlist;

show master status

show slave hosts

show {master|binary} logs

show binlog events

purge {master|binary} logs to 'log_name'

purge {master|binary} logs before 'date'

reset master(老版本flush master)

set sql_log_bin={0|1}

 

5.3 从服务器上的相关命令:

slave start

slave stop

slave stop IO_THREAD //此线程把master段的日志写到本地

slave start IO_THREAD

slave stop SQL_THREAD //此线程把写到本地的日志应用于数据库

slave start SQL_THREAD

reset slave

set global sql_slave_skip_counter

load data from master

show slave status(SUPER,REPLICATION CLIENT)

CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息

PURGE MASTER [before 'date'] 删除master端已同步过的日志

 

参考文献:

http://machael.blog.51cto.com/829462/239112

http://blog.sina.com.cn/s/blog_4bfc7f1f0100hprx.html