关于IT运维技术的
最佳实践博客网站

使用MySQL主备复制(MySQL replication)作为MySQL高可用的解决方案

主备复制解决方案介绍:

这是Mysql自身提供的一种高可用解决方案,数据同步方法采用的是MySQL replication 技术。这种技术就是一种日志的复制过程。在复制过程中一台服务器充当主库服务器。而一台或者多台其它服务器充当备库服务器。

MySQL Replication是单向、异步复制,基本复制过程为:Master服务器首先将更新写人二进制日志文件,并维护文件的一个索引以跟踪日志的循环。这些日志文件发送到Slave服务器进行更新。当一台Slave服务器连接Master服务器时,它从Master服务器日志中读取上一次成功更新的位置。然后Slave服务器开始接收从上一次完成更新后发生的所有更新,所有更新完成,将等待主库服务器通知新的更新。

简而言之主备复制解决方案就是:备库服务器(Slave) 拉取 主库服务器(Master)的二进制日志文件。然后在备库服务器上将日志文件解析成相应的SQL,从新执行一遍主库服务器的操作,通过这种方式确保了数据的一致性。

MySQL Replication支持链式复制,也就是说Slave服务器下还可以再链接Slave 服务器,同时Slave服务器也可以充当Master服务器角色。这里需要注意的是,在mysql主备复制中,所有表的更新必须在Master服务器上进行,Slave服务器仅能提供查询操作。

基于单向复制的MySQL Replication技术有如下优点:

  • 增加了 MySQL应用的健壮性,如果Master服务器出现问题,可以随时切换到Slave 服务器,继续提供服务。
  • 可以将MySQL读、写操作分离,写操作只在Master服务器完成,读操作可在多个 Slave服务器上完成,由于Master服务器和Slave服务器是保持数据同步的,因此不会对前端业务系统产生影响。同时,通过读、写的分离,可以大大降低MySQL的运行负荷。
  • 在网络环境较好,业务量不是很大的环境中,Slave服务器同步数据非常快,基本可以达到实时同步,并且,Slave服务器在同步过程中不会干扰Master服务器。

MySQL replication 技术仅仅提供了日志的同步执行功能,而且备库服务器只提供了读操作,并且当主库服务器发生了故障的时,必须手动处理 故障转移(failover),通常的做法是将一台备库服务器更改为主库服务器。这种解决方案在一定程度上实现了mysql的高可用性,可以实现90.000%的SLA。(一种服务标准等级划分)。

MySQL Replication支持多种类型的复制方式,常见的有基于语句的复制、基于行的复制和混合类型的复制。

  • 基于语句的复制

MySQL默认采用基于语句的复制,效率很高。基本方式是:在Master服务器上执行的 SQL语句,在Slave服务器上再次执行同样的语句。而一旦发现没法精确复制时,会自动选择基于行的复制。

  • 基于行的复制

把Master服务器上改变的内容复制过去,而不是把SQL语句在备库服务器上执行一遍,从MySQL 5.0开始支持基于行的复制。

  • 混合类型的复制

其实就是上面两种类型的组合,默认采用基于语句的复制,如果发现基于语句的复制无法精确完成,就会采用基于行的复制。

 MySQL Replication 实现原理

MySQL Replication是一个从Master复制到一台或多台Slave的异步过程,在Master 与Slave之间实现整个复制过程主要由三个线程来完成,其中一个线程I/O在Master端,另两个线程(SQL线程和I/O线程)在Slave端。

要实现MySQL Replication,首先在Master服务器上打开MySQL的Binary Log (产生二进制日志文件)功能,因为整个复制过程实际上就是Slave从Master端拉取该日志,然后在自身上将二进制文件解析为SQL语句并完全顺序地执行SQL语句所记录的各种操作。 更详细的过程如下。

  • 首先Slave上的I/O线程连接上Master,然后请求从指定日志文件的指定位置或者从最开始的日志位置之后的日志内容。
  • Master在接收到来自Slave的I/O线程请求后,通过自身的I/O线程,根据请求信息读取指定日志位置之后的日志信息,并返回给Slave端的I/O线程。返回信息中除了日志所包含的信息之外,还包括此次返回的信息在Master端对应的Binary Log文件的名称以及在 Binary Log 中的位置
  • Slave的I/O线程接收到信息后,将获取到的日志内容依次写入Slave端的Relay Log 文件(类似于mysql-relay-bin.xxxxxx),并且将读取到的Master端的Binary Log的 文件名和位置记录到一个名为master-info的文件中,以便在下一次读取的时候能够迅速定位开始往后读取日志信息的位置。
  • Slave的SQL线程在检测到Relay Log文件中新增加了内容后,会马上解析该Relay Log文件中的内容,将日志内容解析为SQL语句,然后在自身执行这些SQL,由于是在 Master端和Slave端执行了同样的SQL操作,所以两端的数据是完全样的。至此整个复制过程结束。
MySQL主备复制原理图

MySQL主备复制原理图

MySQL Replication 常用架构

MySQL Replication技术在实际应用中有多种实现架构,常见的有:

  • 一主一备,即一台Master服务器和一台Slave服务器。这是最常见的架构。
  • 一主多备,即一台Master服务器和两台或两台以上slave服务器。经常用在写操作不频繁、查询量比较大的业务环境中。
  • 主主互备,又称双主互备,即两台MySQL Server互相将对方作为自己的Master,自己又同时作为对方的Slave来进行复制。主要用于对MySQL写操作要求比较高的环境中,避免了 MySQL单点故障。
  • 双主多备,其实就是双主互备,然后再加上多台Slave服务器。主要用于对写操作要求比较高,同时查询量比较大的环境中。

其实可以根据具体的情况灵活地将Master/Slave结构进行变化组合,但万变不离其宗。

在进行MySQL Replication的各种部署之前,必须遵守的规则如下:

  • 同一时刻只能有一台Master服务器进行写操作。
  • 一台Master服务器可以有多台Slave服务器。
  • 无论是Master服务器还是Slave服务器,都要确保各自的Server ID唯一,否则双主互备就会出问题。
  • 一台Slave服务器可以将其从Master服务器获得的更新信息传递给其他的Slave服务器。依此类推。

安装mysql数据库

在最小化安装的centos6.5中编译安装最新mysql-5.6.21并做基本优化

基本的主备复制的配置分三步

1、在每台mysql服务器上创建复制账号。

MySQL会赋予一些特殊权限给复制线程。

在备库运行的I/O线程会建立一个到主库的TCP/IP连接。这意味着必须在主库创建一个用户,并且赋予其合适的权限。备库I/O线程以该用户名连接到主库并读取其二进制日志

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.163.%' IDENTIFIED BY 'replication_password';

在主库和备库中都要创建该账号。注意要把这个账号限制在本地网络,因为这是一个特权账号(尽管该账号无法执行select或修改数据,但是仍然能从二进制日志中获取一些数据)

2、拷贝数据

(假如是你完全新安装mysql主备服务器,这个一步就不需要。因为新安装的master和slave有相同的数据)

关停Master服务器,将Master中的数据拷贝到B服务器中,使得Master和slave中的数据同步,并且确保在全部设置操作结束前,禁止在Master和slave服务器中进行写操作,使得两数据库中的数据一定要相同!

3、配置主库和备库

修改主库

log_bin = mysql-bin
server_id = 130
sync_binlog=1

修改备库

log_bin           = mysql-bin
server_id         = 131
relay_log         = mysql-relay-bin
skip_slave_start
log_slave_updates = 1
read_only         = 1
sync_master_info  = 1
sync_relay_log    = 1
sync_relay_log_info = 1

主库和备库的配置修改完成后,必须需要重启mysql

service mysqld restart
或者
/etc/init.d/mysqld restart

4、通知备库连接到主库并且从主库复制数据。

在主库中通过 SHOW MASTER STATUS; 查询获取需要同步的主库位置信息

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      211 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在备库中输入下面的命令进行链接主库

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name', //主库服务器的IP地址
-> MASTER_USER='replication_user_name', //同步数据库的用户
-> MASTER_PASSWORD='replication_password', //同步数据库的密码
-> MASTER_LOG_FILE='recorded_log_file_name', //主库服务器二进制日志的文件名(前面要求记住的参数)
-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记住的参数)

其中,MASTER_LOG_FILE与MASTER_LOG_POS应该在备份主数据库时记录,通过 SHOW MASTER STATUS; 查询获取

查看备库服务器状态

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.163.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 120
              Relay_Log_Space: 120
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

从上面查询出来的结果很明显可以看出

Slave_IO_State:
Slave_IO_Running: No
Slave_SQL_Running: No

表面备库复制尚未运行。

运行下面的命令开始复制

mysql> START SLAVE;
Query OK, 0 rows affected (0.07 sec)

查看备库复制情况:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.163.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 552
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1007
                   Last_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'CREATE DATABASE `test`'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 1846
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1007
               Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'CREATE DATABASE `test`'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 130
                  Master_UUID: 533d90d2-4724-11e4-9818-000c2974d2ae
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 141013 17:52:43
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

有一个错误提示:

 Last_SQL_Errno: 1007
               Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'CREATE DATABASE `test`'

如果Replication在Slave上出现上面错误而停止,解决方法有两种:

第一种:修改mysql的配置文件,/etc/my.cnf,在[mysqld]下面添加一行

slave_skip_errors = 1007

第二种:一般都期望Slave能忽略这个错误,继续进行同步,而不是重新启动Slave。

这时可以使用 SQL_SLAVE_SKIP_COUNTER

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

保存、重启mysql服务。再次查看备库复制,问题解决。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.163.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 651
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 382
        Relay_Master_Log_File: mysql-bin.000001
             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: 651
              Relay_Log_Space: 555
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 130
                  Master_UUID: 533d90d2-4724-11e4-9818-000c2974d2ae
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

在这里主要是看:

                   Slave_IO_Running=Yes
                   Slave_SQL_Running=Yes

在master上输入show processlist\G

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: repl
   Host: 192.168.163.131:24003
     db: NULL
Command: Binlog Dump
   Time: 2081
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
2 rows in set (0.00 sec)

行1为处理slave的I/O线程的连接。

在Slave上输入show processlist\G

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1978
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2168
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 3
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
3 rows in set (0.00 sec)

行2为I/O线程状态,行1为SQL线程状态。

赞(0)
未经允许不得转载:菜鸟HOW站长 » 使用MySQL主备复制(MySQL replication)作为MySQL高可用的解决方案
分享到: 更多 (0)

1
留下你的脚印

1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Recent comment authors
  订阅  
最新 最旧 得票最多
关注动态
过客
LuckyYear

非常给力~!!!