Llinux中mysql数据单向&双向同步笔记

yushan

浏览: 145822 次
性别:
来自: 厦门

最近访客更多访客>>

java_my_life

l1a1j1

jeu1

zhang3228980

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

linux

MySQL 数据结构 SQL SQL Server Linux

最近因项目要求，在linux服务器上设置mysql的数据同步，幸好网络资源多啊，google一下全有了，不过实际操作起来，就麻烦了，遇到很多问题，不过多google一下也解决了，

同时学习了很多相关方面的知识，现将总结于此，以备后用，希望对有此需要的JE们有所帮助；

一、准备环境
    自己做练习硬件资源少，只能在虚拟器上操作了哈哈！

     linux 版本：Red Hat Enterprise Linux Server release 5.1
     mysql 版本：MySQL-server-community-5.0.67-0.rhel5.i386.rpm
                 MySQL-client-community-5.0.67-0.rhel5.i386.rpm
     （PS:mysql的同步机制是基于二进制日志binlog，不同的mysql版本会导致其格式的不同，从而导致不能实现数据同步，因此最好的搭配组合是Master的MySQL版本和Slave的

版本相同或者Slave的版本高于Master的版本，因为mysql是向下兼容的，为此Master的版本不能高于Slave版本，否则有可能不能实现功能；如果要实现双向同步，最好mysql是同

一版本）

     虚拟机版本：VMware6.0

     在虚拟机上安装两个linux系统，并分别安装上mysql（mysql安装在俺博客的mysql安装有详细说明）

   mysql默认安装的文件分别为：

　　/var/lib/mysql/ （数据库目录，其所创建的数据库文件都在该目录下）

　　/usr/share/mysql （mysql相关配置文件）

　　/usr/bin （mysql相关命令，如mysql mysqladmin等）

   为了区分系统，给系统命名一下，便于后面的说名：

    LinuxA 系统： Ip：192.168.59.123 （Master）

    LinuxB 系统： Ip：192.168.59.124 （Slave）

    关闭LinuxA 和 LinuxB 的防火墙 #service iptables stop （保证系统互相可以ping的通）

二、设置单向mysql数据同步（LinuxA(Master)->LinuxB(Slave)）

    1、 LinuxA 为 master

        LinuxB 为 slave （同步LinuxA 上mysql上指定的test数据库中的数据）；

    由于刚安装的mysql，因此分别在LinuxA 和 LinuxB 的 mysql 上创建相同的数据库如 test（表有 stu ， class，teacher），保证两个mysql上的数据库名一致其表名及表的

结构一致，若有一个不同，将导致无法实现同步；
    （PS: 要使数据同步，必须保证数据库名一致其表名及表的结构一致）

    2、在作为master（即LinuxA）的mysql上建立一个账户专门用于slave（即LinuxB）来进行数据同步

       # mysql （默认用户，如设置了root密码，使用mysql -u root -p 登入mysql）
       mysql>grant file on *.* to backa@'192.168.59.124' identified by 'backa'
       mysql>flush privileges;

      （PS：此backa账户只能通过ip为192.168.59.124的访问）

      在LinuxB上测试账户backa是否可以访问LinxuA上的mysql：
      #mysql -u backa -p -h 192.168.59.123（输入密码backa，可以访问说明设置正确）

    3、修改 my.cnf 文件

     由于刚安装的mysql ，在/etc目录下可能无my.cnf文件，从/user/share/mysql目录中拷贝my-medium.cnf 到/etc并修改成my.cnf （LinuxA 和LinuxB 上一样）
     如 # cp /user/share/mysql/my-medium.cnf /etc/my.cnf

     修改在LinuxA(Master)上的/etc/my.cnf

      log-bin=mysql-bin （日志文件，默认存在/var/lib/mysql下日志文件以mysql-bin为开头）
      #log_bin= /var/log/mysql/mysql-bin.log（也可以自己指定，在此用# 表示注释掉了）
      binlog-do-db =test（需要同步的数据库名，如需同步多个数据库，可以另起行如binlog-do-db =test1 ）
      server-id= 1 （mysql标示）

     修改完后保存退出，并重启mysql 如#service mysql restart （重启正常，表示配置没错误）

     修改在LinxuB(Slave)上的/etc/my.cnf

      server-id= 2 （mysql标示，唯一否则启动会出错误）
      master-host=192.168.59.123 （同步Master的ip地址）
      master-user=backa （同步所需的账号）
      master-password=backa （同步账号的密码）
      master-port=3306 （LinuxA 中mysql的访问端口）
      replicate-do-db=test （所需同步的数据库名，如果是同步多个数据库，可以另起一行如 replicate-do-db=test1）
      #replicate-do-table=test.stu （如果只需同步test数据库中的stu表）
      #replicate-do-table=test.teacher（如果只需同步test数据库中的stu表）
      master-connect-retry=60 （主服务器（Master）宕机或连接丢失的情况下，从服务器（slave）线程重新尝试连接主服务器之前睡眠的秒数）

     修改完后保存退出，并重启mysql 如#service mysql restart （重启正常，表示配置没错误）

    4.启动同步

     进到LinuxA（Master）的mysql上：

     mysql>show master status\G (查看master当前状态)
     运行完以上命令显示结果如下：


            File: mysql-bin.000001 （当前binlog日志文件）
        Position: 98 （但前binlog日志文件位置）
    Binlog_Do_DB: test （同步数据库）
Binlog_Ignore_DB:     （不需同步的数据，当前为设置）

-------------------------------------------------------------
     进到LinuxB（Slave）的mysql上：

     mysql>show slave status\G (查看slave同步状态)

     运行完以上命令显示结果如下：

             Slave_IO_State: Waiting for master to send event （slave 的io线程，获取master的binlog的日志内容，该为其目前的状态）
                Master_Host: 192.168.59.123 （所需同步Master的ip地址）
                Master_User: backa（同步所需的账号）
                Master_Port: 3306（同步所需的mysql端口号）
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001 （master中的binlong的日志文件，如与master的日志文件名不同则同步失效）
        Read_Master_Log_Pos: 98 （master中的binlong的日志文件的位置，即mysql-bin.000001文件中的第98位置开始同步，若与master上的位置不同则同步失效）
             Relay_Log_File: localhost-relay-bin.000001
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes （Slave的io线程是否运行）
          Slave_SQL_Running: Yes （slave的sql线程是否运行，需要io线程和sql线程同为yes才有效）
            Replicate_Do_DB: test
        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: 98
            Relay_Log_Space: 235
            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


     如果Slave_IO_State：不为 Waiting for master to send event 表示未现实同步


     原因可能很多先列举自己遇到：
        1、 Master_Log_File: mysql-bin.000001
            Read_Master_Log_Pos:98
            日志文件和位置与LinuxA（Master）的当前状态不一致
        解决方式：（在LinuxB的mysql操作）
        mysql>slave stop;(修改前先关闭slave)
        mysql>change master to
             >master_log_file='mysql-bin.000001' (ps:与LinuxA中的master的日志文件名相同)
             >master_log_pso=98 (ps:与LinuxA中的master的日志文件当前位置相同)
             >master_user='backa' (如果同步的账户不一致，也可在此修改)
             >master_password='backa' (如果同步的账户密码不一致，也可在此修改)
             >;

        (PS:如果重新修改/etc/my.cnf 中相关slave的配置信息，重新启动查看 slave的状态还是显示未修改的数据，应为第一次是读取my.cnf，之后就会在/var/lib/mysql/下

生成一个master.info 的文件，因此第二次就不会读取my.cnf的内容，而是读取master.info中的内容，为此要想使重新修改的my.cnf生效的话，删除master.info文件，重起mysql

即可)
        mysql>slave start;(重新启动slave)

        mysql>show slave status\G (查看slave状态)

        如果显示的 Slave_IO_State: Waiting for master to send event
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
        祝贺成功实现单向同步即 linuxA --> linuxB 的同步；
        在LinuxA的mysql上对test的 insert ， delete ，update及对表结构的修改等操作都会同步到 LinuxB的mysql上的test数据库上
        （ps：如果在master上执行如 insert into to stu (uuid(),'yushan') 则同步到 slave上，id不同，因为其id是用uuid生成的，所以会不一致，之后对这条数据的

delete 和 update则不会实现同步）

三、双向同步（LinuxA<--->LinuxB）

    双向同步最好使用的mysql版本要一致，保证二进制日志binlog的格式相同；双向同步即对任何一方mysql的insert，update，delete，alter等操作都会影响到另一方mysql中

的数据；由于前面已成功设置单向同步，在此基础上，把LinuxB（Slave）的配置添加到 LinuxA(Master) 上，把LinuxA（Master）的配置添加到LinuxB（Slave）上，然后启动设

置同步的binlong文件及当前位置即可,具体操作如下：

    1、 LinuxA 在原有Master的基础上增加Slave （同步LinuxB 上mysql上指定的test数据库中的数据）

        LinuxB 在原有Slave的基础上增加Mmaster（同步LinuxA 上mysql上指定的test数据库中的数据）；

    2、在作为master（即LinuxB）的mysql上建立一个账户专门用于slave（即LinuxA）来进行数据同步

       # mysql （默认用户，如设置了root密码，使用mysql -u root -p 登入mysql）
       mysql>grant file on *.* to backb@'192.168.59.123' identified by 'backb'
       mysql>flush privileges;

      （PS：此backb账户只能通过ip为192.168.59.123的访问）

      在LinuxA上测试账户backb是否可以访问LinxuB上的mysql：
      #mysql -u backb -p -h 192.168.59.124（输入密码backb，可以访问说明设置正确）


   3、修改 my.cnf 文件

    在LinuxA 上的/etc/my.cnf 文件上添加Slave的配置

      #server-id= 2 （mysql标示，唯一否则启动会出错误，因为原先已有标示为1，在此就可以注释掉，一个mysql只需一个标示即可）
      master-host=192.168.59.124 （同步LinuxB (Master)的ip地址）
      master-user=backb （同步所需的账号）
      master-password=backb （同步账号的密码）
      master-port=3306 （LinuxB 中mysql的访问端口）
      replicate-do-db=test （所需同步的数据库名，如果是同步多个数据库，可以另起一行如 replicate-do-db=test1）
      #replicate-do-table=test.stu （如果只需同步test数据库中的stu表）
      #replicate-do-table=test.teacher（如果只需同步test数据库中的stu表）
      master-connect-retry=60 （主服务器（Master）宕机或连接丢失的情况下，从服务器（slave）线程重新尝试连接主服务器之前睡眠的秒数）

    修改完后保存退出，并重启mysql 如#service mysql restart （重启正常，表示配置没错误）


    在LinxuB上的/etc/my.cnf文件上添加Master的配置

      #server-id= 1 （mysql标示,唯一否则启动会出错误，因为原先已有标示为2，在此就可以注释掉，一个mysql只需一个标示即可）
      log-bin=mysql-bin （日志文件，默认存在/var/lib/mysql下日志文件以mysql-bin为开头）
      #log_bin= /var/log/mysql/mysql-bin.log（也可以自己指定，在此用# 表示注释掉了）
      binlog-do-db =test（需要同步的数据库名，如需同步多个数据库，可以另起行如binlog-do-db =test1 ）

     修改完后保存退出，并重启mysql 如#service mysql restart （重启正常，表示配置没错误）

   4.启动同步

     进到LinuxB（Master）的mysql上：

     mysql>show master status\G (查看master当前状态)
     运行完以上命令显示结果如下：

            File: mysql-bin.000003 （当前binlog日志文件）
        Position: 231 （但前binlog日志文件位置）
    Binlog_Do_DB: test （同步数据库）
Binlog_Ignore_DB:     （不需同步的数据，当前为设置）

-------------------------------------------------------------
     进到LinuxA（Slave）的mysql上：

     mysql>show slave status\G (查看slave同步状态)

     运行完以上命令显示结果如下：

             Slave_IO_State: Waiting for master to send event （slave 的io线程，获取master的binlog的日志内容，该为其目前的状态）
                Master_Host: 192.168.59.124 （所需同步Master的ip地址）
                Master_User: backb（同步所需的账号）
                Master_Port: 3306（同步所需的mysql端口号）
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003 （master中的binlong的日志文件，如与master的日志文件名不同则同步失效）
        Read_Master_Log_Pos: 98 （master中的binlong的日志文件的位置，即mysql-bin.000001文件中的第98位置开始同步，若与master上的位置不同则同步失效）
             Relay_Log_File: localhost-relay-bin.000001
              Relay_Log_Pos: 231
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes （Slave的io线程是否运行）
          Slave_SQL_Running: Yes （slave的sql线程是否运行，需要io线程和sql线程同为yes才有效）
            Replicate_Do_DB: test
        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: 98
            Relay_Log_Space: 235
            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

     以上显示为正确同步如果显示的一下数据不是
       Slave_IO_State: Waiting for master to send event
       Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

     若与以上的三个状态表示不同，则未实现同步；可以采用以上单向同步中的 change master 进行重新设置（ps：防火墙需关闭）

     以上都能正确配置完且显示的状态一致，那么祝贺您成功实现双向同步即 linuxA <--> linuxB 的同步；

四、 mysql 数据同步相关说明（转载于网络，google一下很多）

　　1. 同步机制实现概述

　　MySQL同步机制基于master把所有对数据库的更新操作(更新、删除等)都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进制日志。

　　每个slave接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开始的

时刻才记录更新操作的。所有的slave必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的

话，那么slave同步就会失败。

　　2. 同步实现细节

　　MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行 START SLAVE 语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志

中的语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取

master的 Binlog Dump 线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。

　　如上所述，每个mster/slave上都有3个线程。每个master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。

　　在MySQL 4.0.2以前，同步只需2个线程(master和slave各一个)。slave上的I/O和SQL线程合并成一个了，它不使用中继日志。

　　slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果 slave停止了一段时间，那么I/O线程

可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部执行完就停止了，不过I/O线程却已经把所有的更新日

志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就允许在master上清除二进制日志，因为slave已经无需去master读取更新日志了。

　　在mysql中执行 SHOW PROCESSLIST 语句就会告诉我们所关心的master和slave上发生的情况

　　在master上，SHOW PROCESSLIST 的结果如下：

　　mysql> SHOW PROCESSLIST\G

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

　　Id: 21

　　User: root

　　Host: localhost

　　db: mysql

　　Command: Query

　　Time: 0

　　State: NULL

　　Info: SHOW PROCESSLIST

　　*************************** 2. row (MASTER线程)***************************

　　Id: 25

　　User: back

　　Host: 192.168.1.125:34085

　　db: NULL

　　Command: Binlog Dump

　　Time: 746

　　State: Has sent all binlog to slave; waiting for binlog to be updated

　　Info: NULL

　　2 rows in set (0.01 sec)

　　在这里，线程2是为一个slave连接创建的。结果表明所有未完成的更新日志已经都发送到slave了，master正等待新的更新日志发生。

　　在slave上，SHOW PROCESSLIST 的结果如下：

　　mysql> SHOW PROCESSLIST\G

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

　　Id: 2

　　User: system user

　　Host:

　　db: NULL

　　Command: Connect

　　Time: 70479

　　State: Waiting for master to send event

　　Info: NULL

　　*************************** 2. row (I/O线程) ***************************

　　Id: 3

　　User: system user

　　Host:

　　db: NULL

　　Command: Connect

　　Time: 16971

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

　　Info: NULL

　　*************************** 3. row (SQL线程)***************************

　　Id: 8

　　User: root

　　Host: localhost

　　db: NULL

　　Command: Query

　　Time: 0

　　State: NULL

　　Info: SHOW PROCESSLIST

　　3 rows in set (0.00 sec)

　　这表明线程2是I/O线程，它正连接到master上；线程3是SQL线程，它执行中继日志中的更新操作。现在，这2个线程都处于空闲状态，正等待新的更新日志。

　　注意，Time 字段的值告诉我们slave上的日志比master晚了多久。

　　3. Master 同步线程状态

　　以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。也就是说，没有slave连接上来

。

　　Sending binlog event to slave

　　事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。

　　Finished reading one binlog; switching to next binlog

　　读取完了一个二进制日志，正切换到下一个。

　　Has sent all binlog to slave; waiting for binlog to be updated

　　已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新的事件，然后读取它们。

　　Waiting to finalize termination

　　当前线程停止了，这个时间很短

　　4. Slave的I/O线程状态

　　以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的 Slave_IO_State 字段也会出现。这意味

着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。

　　Connecting to master

　　该线程证尝试连接到master上。

　　Checking master version

　　确定连接到master后出现的一个短暂的状态。

　　Registering slave on master

　　确定连接到master后出现的一个短暂的状态。

　　Requesting binlog dump

　　确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。

　　Waiting to reconnect after a failed binlog dump request

　　如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由

　　--master-connect-retry 选项来指定。

　　Reconnecting after a failed binlog dump request

　　该线程正尝试重连到master。

　　Waiting for master to send event

　　已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒，就会发生超时。这时，它就会

考虑断开连接，然后尝试重连。

　　Queueing master event to the relay log

　　已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。

　　Waiting to reconnect after a failed master event read

　　读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。

　　Reconnecting after a failed master event read

　　正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。

　　Waiting for the slave SQL thread to free enough relay log space

　　relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间。

　　Waiting for slave mutex on exit

　　当前线程停止了，这个时间很短。

　　5. Slave的SQL线程状态

　　以下列出了slave的SQL线程 State 字段中最常见的几种状态：

　　Reading event from the relay log

　　从中继日志里读到一个事件以备执行。

　　Has read all relay log; waiting for the slave I/O thread to update it

　　已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。

　　Waiting for slave mutex on exit

　　当前线程停止了，这个时间很短。

　　SQL线程的 State 字段有时候也可能是一个SQL语句。这意味着它从中继日志中读取到一个事件了，从中提取出SQL语句，并执行它。


      （自己画的mysql同步原理图）