[关闭]
@Spongcer 2016-11-23T07:45:53.000000Z 字数 44557 阅读 6574

MySQL 双机热备配置步骤

MySQL HA 双机热备


前言

本文旨在利用 MySQL 的 Binary Log 机制详细讲解如何在 Cent OS 平台上搭建 MySQL 的主从复制和双机热备环境,并对它们进行简单的测试。

本文适合有一定 Linux 基础,大致了解过 MySQL 的 Binary Log 机制,并想了解如何从零基础开始搭建 MySQL 的主从复制和双机互备环境,或者有相关需求的研发、运维、数据库管理员参阅。

在本文行文之初,作者就打算完全依靠自身的 Linux 基础知识和 MySQL 官方提供的 MySQL Reference 文档来搭建所需环境,一方面锻炼一下自己查找知识、发现问题、解决问题的能力,另一方面也测试一下在没有度娘和谷哥的帮助下,对于一个新的问题,自己接受程度,经过本次测试,总体来说还算满意。

由于时间和篇幅的原因,本文并没有深入探讨 MySQL 的 Binary Log 的原理:

针对这些问题,后续有机会再给大家分享。

同时,本文也有诸多不足,有很多地方为了简单方便,没有按照生产环境的要求来进行配置:

针对上述问题,作者只能说声抱歉,建议大家在实际生产环境中,一定要按照正规流程来执行,提高安全性和可维护性。

最后,对于如何监控主从复制和双机热备环境,搭建一个自动化的预警平台,还没有来得及研究,等研究明白了,后续会补上这些知识,一定做到有始有终!

个人能力有限,若发现行文有误之处,或者有任何意见或建议,欢迎致信:zhaohm3@asiainfo.com,同时欢迎对大规模分布式存储系统、JVM、RDBMS、分布式事务感兴趣的童鞋留下你们的联系方式,我们抽机会线下交流,相互学习,共同进步!

主机环境

主机名 角色 IP地址 MySQL版本 OS
MySQL1 Master 192.168.26.133 5.1.71 CentOS 6.5 x86_64
MySQL2 Slave 192.168.26.134 5.1.71 CentOS 6.5 x86_64

MySQL 在 3.23.15 以后的版本中提供数据库复制功能,利用该功能可以实现 MySQL 数据库备份主从复制双机互备功能

在配置主从复制时,需要注意以下两点:

本文选用 MySQL 5.1.71 进行主从复制双机互备的配置。

安装 CentOS

在本地安装 VMware,并安装 CentOS 6.5 x86_64 系统,安装时,网络类型选择 NAT 模式:

安装完成后,如下:

按照如下步骤,克隆一份 CentOS 6.5 x86_64,克隆后的名称为 CentOS_65_x64 Clone:

克隆完成后,按照如下步骤对 CentOS_65_x64 Clone 进行备份:

备份后,按照如下步骤对 Master 和 Slave 主机的 OS 系统进行配置:

这样,我们就得到了两份完全一样的 CentOS 6.5 x86_64 系统,其中一份作为 Master 主机的 OS 系统,另外一份作为 Slave 主机的 OS 系统,接下来,我们需要对它们的防火墙、网络设备、主机信息等进行配置。

配置 CentOS

在 VMware 中开启 Master 和 Slave,以 root 用户登录,进行如下配置:

注意如无特殊说明,Master 和 Slave 主机上都需要执行本节所进行的所有配置

防火墙配置

由于 MySQL 主从机之间需要进行网络通信,通信端口号为 3306,因此,需要对其进行防火墙配置,有两种配置方式:

网卡配置

编辑 /etc/udev/rules.d/70-persistent-net.rules,删除其中的所有网卡配置信息:

编辑 vi /etc/sysconfig/network-scripts/ifcfg-eth0,将 HWADDR 和 UUID 两项注掉,将 ONBOOT 项设置为 yes 值:

按照如下方式查看 VMware 所提供的网关地址:

本位所查得地网关地址为:192.168.26.2

获得网关地址后,编辑 /etc/sysconfig/network 文件,配置网关和主机名信息:

HOST 配置

编辑 /etc/hosts 文件,对 Master 和 Slave 主机进行 HOST 配置,添加 Master 到 192.168.26.133 的 IP 映射,添加 Slave 到 192.168.26.134 的 IP 映射:

重启主机

上述配置完成后,重启 Master 和 Slave 主机,使配置生效。

安装 MySQL

可以通过 yum、rpm 或者源码编译的方式安装 MySQL,建议使用源码编译方式安装到一个指定的目录内(比如 /opt/MySQL),如果需要在另一台主机上安装 MySQL,拷贝该目录即可。

注意在 Master 和 Slave 主机上都需要安装 MySQL 客户端和 MySQL 服务器

通过 yum 安装

查看有没有安装过 MySQL:

  1. yum list installed mysql*
  2. rpm -qa | grep mysql*

查看有没有安装包:

  1. yum list mysql*

安装mysql客户端:

  1. yum install mysql

安装mysql 服务器端:

  1. yum install mysql-server
  2. yum install mysql-devel

通过 rpm 安装

在 CentOS 的 DVD 镜像包中已经提供了 MySQL 的 rpm 包,因此,本文采用 rpm 方式安装 MySQL。

需要安装的 rpm 包有如下三个:

  1. mysql-5.1.71-1.el6.x86_64.rpm
  2. mysql-devel-5.1.71-1.el6.x86_64.rpm
  3. mysql-server-5.1.71-1.el6.x86_64.rpm

安装时,可以通过如下命令来检测上述三个 rpm 包的依赖包:

  1. rpm -ivh --test mysql-5.1.71-1.el6.x86_64.rpm mysql-devel-5.1.71-1.el6.x86_64.rpm mysql-server-5.1.71-1.el6.x86_64.rpm

本文检测出的依赖包如下:

  1. [root@localhost CentOS-6.5-x86_6]# rpm -ivh --test mysql-5.1.71-1.el6.x86_64.rpm mysql-devel-5.1.71-1.el6.x86_64.rpm mysql-server-5.1.71-1.el6.x86_64.rpm
  2. warning: mysql-5.1.71-1.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
  3. error: Failed dependencies:
  4. openssl-devel is needed by mysql-devel-5.1.71-1.el6.x86_64
  5. perl(DBI) is needed by mysql-server-5.1.71-1.el6.x86_64
  6. perl-DBD-MySQL is needed by mysql-server-5.1.71-1.el6.x86_64
  7. perl-DBI is needed by mysql-server-5.1.71-1.el6.x86_64
  8. [root@localhost CentOS-6.5-x86_6]#

因此,通过如下命令即可安装 MySQL 客户端和 MySQL 服务器:

  1. rpm -ivh mysql-5.1.71-1.el6.x86_64.rpm mysql-devel-5.1.71-1.el6.x86_64.rpm mysql-server-5.1.71-1.el6.x86_64.rpm openssl-devel-1.0.1e-15.el6.x86_64.rpm perl-DBI-1.609-4.el6.x86_64.rpm perl-DBD-MySQL-4.013-3.el6.x86_64.rpm

启动 MySQL 服务

安装完成,启动 MySQL 服务:

  1. service mysqld start

通过以下命令可以查看 MySQL Server 的运行状态:

  1. service mysqld status

由于 mysqld 服务位于 /etc/init.d/ 目录下,因此上述命令也可通过如下方式完成:

  1. /etc/init.d/mysqld start
  2. /etc/init.d/mysqld status

创建同步资源

MySQL 的 Binary Log 机制可以同步整个 MySQL 库,也可以只同步一个或者多个数据库,无论哪种同步方式,都需在 Slave 上创建一个同步用户,允许该用户可以远程访问 Master 数据库,并可读取 Master 数据库的 Binary Log 日志。

理论上,可以在 Master 和 Slave 的 MySQL Server 中创建不同的用户用于同步,只要其中的初始数据一致即可,但在大多数实际生产环境中,为了降低运维成本,在 Master 和 Slave 上所创建的用于同步的用户名和数据库名通常都保持一致,本文在 Master 和 Slave 创建的同步用户名为 repl,密码是 123456。

本文不配置同步整个 MySQL 库,仅仅同步的名为 repldb 的数据库,配置步骤如下:

  1. 修改 MySQL 服务器 root 用户的密码:

    1. mysqladmin -u root password 'root'
  2. 以 root 用户身份登录 MySQL Server:

    1. mysql -uroot -proot
  3. 创建同步用户 repl,密码是 123456:

    1. create user 'repl' identified by '123456';
  4. repl 用户建好以后,需要为之赋予一些权限,以便该用户可以执行登录、建表、insert等操作,在实际生产环节中,可以根据实际需要赋予特定的角色,本文使用如下语句为之赋予最大权限:

    • Master 端

      1. grant all privileges on *.* to 'repl'@'*' with grant option;
      2. grant all privileges on *.* to 'repl'@'%' with grant option;
      3. grant all privileges on *.* to 'repl'@'localhost' with grant option;
      4. grant all privileges on *.* to 'repl'@'Master' with grant option;
      5. flush privileges;
    • Slave 端

      1. grant all privileges on *.* to 'repl'@'*' with grant option;
      2. grant all privileges on *.* to 'repl'@'%' with grant option;
      3. grant all privileges on *.* to 'repl'@'localhost' with grant option;
      4. grant all privileges on *.* to 'repl'@'Slave' with grant option;
      5. flush privileges;
  5. 创建用于同步的数据库 repldb:

    1. create database repldb;
    2. show databases;
  6. 测试以 repl 用户登录 MySQL Server:

    1. mysql -urepl -p123456

    如果登录的过程中出现如下错误:

    1. ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)

    使用 mysqladmin 更正 repl 密码即可:

    1. mysqladmin -u repl password '123456'

主从配置

  1. 修改 Master 主机的 MySQL 配置文件 my.cnf(/etc/my.cnf),为 mysqld 添加如下选项:

    1. log-bin=mysql-bin #表示启用mysql二进制日志,
    2. #该项必须要启用,否则mysql主从不会生效。
    3. max_binlog_size=500M #表示每个binlog文件最大大小,
    4. #当此文件大小等于500M时,会自动生成一个新的日志文件。
    5. #注意:一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
    6. server-id=1 #表示mysql服务器ID,该ID必须在该主从中是唯一的,
    7. #默认是1,该ID可以自行自定义,但必须为数字。
    8. binlog-do-db=repldb #表示需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    9. binlog-ignore-db=mysql #表示不需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    10. #如不加binlog-do-db和binlog-ignore-db,默认同步复制整个mysql数据库。
    11. innodb_flush_log_at_trx_commit=1 #当MySQL的存储引擎是InnoDB时,表示每次事务提交时,刷出日志。
    12. #虽然性能较慢,但是能够保证强一致性。
    13. sync_binlog=1 #设置MySQL在每次事务提交后,执行一次磁盘同步指令,
    14. #将binlog_cache中的数据强制写入磁盘。
    15. #最后面两个参数可以根据实际生产环境对数据一致性的刚醒需求程度来决定是否添加。

    注意一定要添加到 mysqld 的参数区域中,而不是 mysqld_safe 的参数区域

    配置完成后,Master 主机的 /etc/my.cnf 的全部内容如下:

    1. [root@Master ~]# cat /etc/my.cnf
    2. [mysqld]
    3. datadir=/var/lib/mysql
    4. socket=/var/lib/mysql/mysql.sock
    5. user=mysql
    6. # Disabling symbolic-links is recommended to prevent assorted security risks
    7. symbolic-links=0
    8. log-bin=mysql-bin #表示启用mysql二进制日志,
    9. #该项必须要启用,否则mysql主从不会生效。
    10. max_binlog_size=500M #表示每个binlog文件最大大小,
    11. #当此文件大小等于500M时,会自动生成一个新的日志文件。
    12. #注意:一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
    13. server-id=1 #表示mysql服务器ID,该ID必须在该主从中是唯一的,
    14. #默认是1,该ID可以自行自定义,但必须为数字。
    15. binlog-do-db=repldb #表示需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    16. binlog-ignore-db=mysql #表示不需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    17. #如不加binlog-do-db和binlog-ignore-db,默认同步复制整个mysql数据库。
    18. innodb_flush_log_at_trx_commit=1 #当MySQL的存储引擎是InnoDB时,表示每次事务提交时,刷出日志。
    19. #虽然性能较慢,但是能够保证强一致性。
    20. sync_binlog=1 #设置MySQL在每次事务提交后,执行一次磁盘同步指令,
    21. #将binlog_cache中的数据强制写入磁盘。
    22. #最后面两个参数可以根据实际生产环境对数据一致性的刚醒需求程度来决定是否添加。
    23. [mysqld_safe]
    24. log-error=/var/log/mysqld.log
    25. pid-file=/var/run/mysqld/mysqld.pid
    26. [root@Master ~]#
  2. 为了使 Slave 在同步时,能够以 repl 身份登录 Master 并访问 Master 的 repldb,需要在 Master 端以 root 用户登录 MySQL Server,然后执行以下 SQL 语句为 Slave 的 repl 用户赋予 REPLICATION SLAVE 权限:

    1. mysql -uroot -proot
    2. grant replication slave,file on *.* to 'repl'@'Slave' identified by '123456';
    3. flush privileges;

    执行完成后,可以通过在 mysql 数据库中查看权限的赋值情况:

    1. use mysql;
    2. select user,repl_slave_priv from user where user='repl';

    本文结果如下:

    1. [root@Master ~]# mysql -uroot -proot
    2. Welcome to the MySQL monitor. Commands end with ; or \g.
    3. Your MySQL connection id is 6
    4. Server version: 5.1.71 Source distribution
    5. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    6. Oracle is a registered trademark of Oracle Corporation and/or its
    7. affiliates. Other names may be trademarks of their respective
    8. owners.
    9. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    10. mysql> grant replication slave,file on *.* to 'repl'@'Slave' identified by '123456';
    11. Query OK, 0 rows affected (0.00 sec)
    12. mysql> flush privileges;
    13. Query OK, 0 rows affected (0.00 sec)
    14. mysql> use mysql;
    15. Reading table information for completion of table and column names
    16. You can turn off this feature to get a quicker startup with -A
    17. Database changed
    18. mysql> select user,repl_slave_priv from user where user='repl';
    19. +------+-----------------+
    20. | user | repl_slave_priv |
    21. +------+-----------------+
    22. | repl | Y |
    23. | repl | Y |
    24. | repl | Y |
    25. | repl | Y |
    26. | repl | Y |
    27. +------+-----------------+
    28. 5 rows in set (0.00 sec)
    29. mysql>
  3. 配置完成后,重启 Master 端的 MySQL Server:

    1. service mysqld restart
    2. service mysqld status
  4. 重启后,以 root 身份登录 Master 端的 MySQL server:

    1. mysql -uroot -proot
  5. 查看 Master 端的 Binary Log 参数状态:

    1. show variables like '%log_bin%';

    本文结果如下:

    1. mysql> show variables like '%log_bin%';
    2. +---------------------------------+-------+
    3. | Variable_name | Value |
    4. +---------------------------------+-------+
    5. | log_bin | ON |
    6. | log_bin_trust_function_creators | OFF |
    7. | log_bin_trust_routine_creators | OFF |
    8. | sql_log_bin | ON |
    9. +---------------------------------+-------+
    10. 4 rows in set (0.00 sec)
    11. mysql>
  6. 锁住 Master 端的表,禁止其写权限,并记录 Binary Log 日志文件名和起始位置:

    1. flush tables with read lock;
    2. show master status;
    3. show master status\G;

    本文执行结果如下:

    1. mysql> flush tables with read lock;
    2. Query OK, 0 rows affected (0.00 sec)
    3. mysql> show master status;
    4. +------------------+----------+--------------+------------------+
    5. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    6. +------------------+----------+--------------+------------------+
    7. | mysql-bin.000001 | 106 | repldb | mysql |
    8. +------------------+----------+--------------+------------------+
    9. 1 row in set (0.00 sec)
    10. mysql> show master status\G;
    11. *************************** 1. row ***************************
    12. File: mysql-bin.000001
    13. Position: 106
    14. Binlog_Do_DB: repldb
    15. Binlog_Ignore_DB: mysql
    16. 1 row in set (0.00 sec)
    17. ERROR:
    18. No query specified
    19. mysql>

    从上面的结果可以看出,Binary Log 的文件名为:mysql-bin.000001,日志起始位置为:106。

  7. 切换到 Slave 端,修改 Slave 主机的 MySQL 配置文件 my.cnf(/etc/my.cnf),为 mysqld 添加如下选项:

    1. server-id=2
    2. replicate-do-db=repldb
    3. replicate-ignore-db=mysql

    注意一定要添加到 mysqld 的参数区域中,而不是 mysqld_safe 的参数区域

    配置完成后,Slave 主机的 /etc/my.cnf 的全部内容如下:

    1. [root@Slave ~]# cat /etc/my.cnf
    2. [mysqld]
    3. datadir=/var/lib/mysql
    4. socket=/var/lib/mysql/mysql.sock
    5. user=mysql
    6. # Disabling symbolic-links is recommended to prevent assorted security risks
    7. symbolic-links=0
    8. server-id=2
    9. replicate-do-db=repldb
    10. replicate-ignore-db=mysql
    11. [mysqld_safe]
    12. log-error=/var/log/mysqld.log
    13. pid-file=/var/run/mysqld/mysqld.pid
    14. [root@Slave ~]#
  8. 配置完成后,重启 Slave 端的 MySQL Server,以 root 用户登录,并检测 Slave 端的 Slave 状态:

    1. service mysqld restart
    2. mysql -uroot -proot
    3. show slave status;
    4. show slave status\G;

    本文执行结果如下:

    1. [root@Slave ~]# service mysqld restart
    2. 停止 mysqld [确定]
    3. 正在启动 mysqld [确定]
    4. [root@Slave ~]# mysql -uroot -proot
    5. Welcome to the MySQL monitor. Commands end with ; or \g.
    6. Your MySQL connection id is 2
    7. Server version: 5.1.71 Source distribution
    8. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    9. Oracle is a registered trademark of Oracle Corporation and/or its
    10. affiliates. Other names may be trademarks of their respective
    11. owners.
    12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    13. mysql> show slave status;
    14. Empty set (0.00 sec)
    15. mysql> show slave status\G;
    16. Empty set (0.00 sec)
    17. ERROR:
    18. No query specified
    19. mysql>
  9. 在 Slave 端执行如下命令,创建 Slave 到 Master 的 Replication:

    1. slave stop;
    2. CHANGE MASTER TO MASTER_HOST='Master',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_CONNECT_RETRY=60,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;
    3. slave start;
    4. show slave status\G;
    5. show slave status;
    1. 在执行 CHANGE MASTER TO 语句之前,一定要先执行 SLAVE STOP 指令。
    2. MASTER_HOST 表示需要进行同步的 MySQL 库所在的主机名,可以写 IP 地址,也可以填写 HOSTNAME
    3. MASTER_LOG_FILE MASTER_LOG_POS 一定要和在 Master 端执行 show master status 命令的结果保持一致,否则无法同步。
    4. 最后需要执行 SLAVE START 指令,开始主从同步。

    本文执行结果如下:

    1. mysql> slave stop;
    2. Query OK, 0 rows affected, 1 warning (0.00 sec)
    3. mysql>
    4. mysql> CHANGE MASTER TO MASTER_HOST='Master',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_CONNECT_RETRY=60,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;
    5. Query OK, 0 rows affected (0.04 sec)
    6. mysql>
    7. mysql> slave start;
    8. Query OK, 0 rows affected (0.00 sec)
    9. mysql> show slave status\G;
    10. *************************** 1. row ***************************
    11. Slave_IO_State: Connecting to master
    12. Master_Host: Master
    13. Master_User: repl
    14. Master_Port: 3306
    15. Connect_Retry: 60
    16. Master_Log_File: mysql-bin.000001
    17. Read_Master_Log_Pos: 106
    18. Relay_Log_File: mysqld-relay-bin.000001
    19. Relay_Log_Pos: 4
    20. Relay_Master_Log_File: mysql-bin.000001
    21. Slave_IO_Running: No
    22. Slave_SQL_Running: Yes
    23. Replicate_Do_DB: repldb
    24. Replicate_Ignore_DB: mysql
    25. Replicate_Do_Table:
    26. Replicate_Ignore_Table:
    27. Replicate_Wild_Do_Table:
    28. Replicate_Wild_Ignore_Table:
    29. Last_Errno: 0
    30. Last_Error:
    31. Skip_Counter: 0
    32. Exec_Master_Log_Pos: 106
    33. Relay_Log_Space: 106
    34. Until_Condition: None
    35. Until_Log_File:
    36. Until_Log_Pos: 0
    37. Master_SSL_Allowed: No
    38. Master_SSL_CA_File:
    39. Master_SSL_CA_Path:
    40. Master_SSL_Cert:
    41. Master_SSL_Cipher:
    42. Master_SSL_Key:
    43. Seconds_Behind_Master: NULL
    44. Master_SSL_Verify_Server_Cert: No
    45. Last_IO_Errno: 0
    46. Last_IO_Error:
    47. Last_SQL_Errno: 0
    48. Last_SQL_Error:
    49. 1 row in set (0.00 sec)
    50. ERROR:
    51. No query specified
    52. mysql> show slave status;
    53. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
    54. | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
    55. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
    56. | Waiting for master to send event | Master | repl | 3306 | 60 | mysql-bin.000001 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000001 | Yes | Yes | repldb | mysql | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
    57. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
    58. 1 row in set (0.00 sec)
    59. mysql>
  10. 切回 Master 端,执行如下命令对 Master 库解锁:

    1. unlock tables;

    在对 Slave 进行配置之前,需要执行 flush tables with read lock 命令将 Master 端锁住,防止发生写操作,当 Slave 配置成功并开始 Replication 后,需要切回 Master 端,执行 unlock tables 命令进行解锁,解锁后,就可以在 Master 端执行读写操作了,对于 Master 端的 repldb 库所执行的写操作,Slave 会将其同步到 Salve 端的 repldb 数据库中。

    本文执行结果如下:

    1. mysql> unlock tables;
    2. Query OK, 0 rows affected (0.01 sec)
    3. mysql>
  11. 切回 Slave 端,再次查看 Slave 状态,验证 Slave 是否已经开始准备同步:

    1. show slave status\G;

    本文执行结果如下:

    1. mysql> show slave status\G;
    2. *************************** 1. row ***************************
    3. Slave_IO_State: Waiting for master to send event
    4. Master_Host: Master
    5. Master_User: repl
    6. Master_Port: 3306
    7. Connect_Retry: 60
    8. Master_Log_File: mysql-bin.000001
    9. Read_Master_Log_Pos: 106
    10. Relay_Log_File: mysqld-relay-bin.000002
    11. Relay_Log_Pos: 251
    12. Relay_Master_Log_File: mysql-bin.000001
    13. Slave_IO_Running: Yes
    14. Slave_SQL_Running: Yes
    15. Replicate_Do_DB: repldb
    16. Replicate_Ignore_DB: mysql
    17. Replicate_Do_Table:
    18. Replicate_Ignore_Table:
    19. Replicate_Wild_Do_Table:
    20. Replicate_Wild_Ignore_Table:
    21. Last_Errno: 0
    22. Last_Error:
    23. Skip_Counter: 0
    24. Exec_Master_Log_Pos: 106
    25. Relay_Log_Space: 407
    26. Until_Condition: None
    27. Until_Log_File:
    28. Until_Log_Pos: 0
    29. Master_SSL_Allowed: No
    30. Master_SSL_CA_File:
    31. Master_SSL_CA_Path:
    32. Master_SSL_Cert:
    33. Master_SSL_Cipher:
    34. Master_SSL_Key:
    35. Seconds_Behind_Master: 0
    36. Master_SSL_Verify_Server_Cert: No
    37. Last_IO_Errno: 0
    38. Last_IO_Error:
    39. Last_SQL_Errno: 0
    40. Last_SQL_Error:
    41. 1 row in set (0.00 sec)
    42. ERROR:
    43. No query specified
    44. mysql>

    从上面的结果可以看出,在 Master 端执行 unlock tables 命令后,Slave 端 Slave 状态的 Slave_IO_State 已经由 Connecting to master 变成了 Waiting for master to send event,说明 Slave 已经做好了同步的准备,接下来就可以对主从复制进行测试了。

测试主从复制

  1. 在 Master 端以任意用户登录,并切换到 repldb 库,在其中创建表 test,然后切换到 Slave 端,以任意用户登录,查看 repldb 库中是否存在该表:

    • Master 端
    1. mysql -uroot -proot
    2. use repldb;
    3. create table test(tc1 int, tc2 int);

    Master 端执行结果如下:

    1. [root@Master ~]# mysql -uroot -proot
    2. Welcome to the MySQL monitor. Commands end with ; or \g.
    3. Your MySQL connection id is 4
    4. Server version: 5.1.71-log Source distribution
    5. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    6. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
    7. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    8. mysql> use repldb;
    9. Database changed
    10. mysql> create table test(tc1 int, tc2 int);
    11. Query OK, 0 rows affected (0.11 sec)
    12. mysql>
    • Slave 端
    1. mysql -uroot -proot
    2. use repldb;
    3. desc test;

    Slave 端执行结果如下:

    1. [root@Slave ~]# mysql -uroot -proot
    2. Welcome to the MySQL monitor. Commands end with ; or \g.
    3. Your MySQL connection id is 6
    4. Server version: 5.1.71 Source distribution
    5. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    6. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
    7. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    8. mysql> use repldb;
    9. Reading table information for completion of table and column names
    10. You can turn off this feature to get a quicker startup with -A
    11. Database changed
    12. mysql> desc test;
    13. +-------+---------+------+-----+---------+-------+
    14. | Field | Type | Null | Key | Default | Extra |
    15. +-------+---------+------+-----+---------+-------+
    16. | tc1 | int(11) | YES | | NULL | |
    17. | tc2 | int(11) | YES | | NULL | |
    18. +-------+---------+------+-----+---------+-------+
    19. 2 rows in set (0.00 sec)
    20. mysql>

    同步成功。

  2. 在 Master 端 repldb 库中,向 test 中插入一些数据,然后查看这些数据是否同步到了 Slave 端 repldb 的 test 表中:

    • Master 端

      1. insert into test values(1,2);
      2. insert into test values(3,4);

      Master 端执行结果如下:

      1. mysql> insert into test values(1,2);
      2. Query OK, 1 row affected (0.42 sec)
      3. mysql> insert into test values(3,4);
      4. Query OK, 1 row affected (0.50 sec)
      5. mysql>
    • Slave 端

      1. select * from test;

      Slave 端执行结果如下:

      1. mysql> select * from test;
      2. +------+------+
      3. | tc1 | tc2 |
      4. +------+------+
      5. | 1 | 2 |
      6. | 3 | 4 |
      7. +------+------+
      8. 2 rows in set (0.00 sec)
      9. mysql>

      同步成功。

  3. 在 Master 端 repldb 库中,删除 test 中的一些数据,然后查看 Slave 端 repldb 的 test 表中是否也删除了这些数据:

    • Master 端

      1. delete from test where tc1 = 1;

      Master 端执行结果如下:

      1. mysql> delete from test where tc1 = 1;
      2. Query OK, 1 row affected (0.00 sec)
      3. mysql>
    • Slave 端

      1. select * from test;

      Slave 端执行结果如下:

      1. mysql> select * from test;
      2. +------+------+
      3. | tc1 | tc2 |
      4. +------+------+
      5. | 3 | 4 |
      6. +------+------+
      7. 1 row in set (0.00 sec)
      8. mysql>

      同步成功。

  4. 更改 Master 端 repldb 库的 test 表的结构,然后查看 Slave 端 repldb 的 test 表结构是否也随之改变了:

    • Master 端

      1. alter table test change tc1 modify_tc1 int;

      Master 端执行结果如下:

      1. mysql> alter table test change tc1 modify_tc1 int;
      2. Query OK, 0 rows affected (0.03 sec)
      3. Records: 0 Duplicates: 0 Warnings: 0
      4. mysql>
    • Slave 端

      1. desc test;

      Slave 端执行结果如下:

      1. mysql> desc test;
      2. +------------+---------+------+-----+---------+-------+
      3. | Field | Type | Null | Key | Default | Extra |
      4. +------------+---------+------+-----+---------+-------+
      5. | modify_tc1 | int(11) | YES | | NULL | |
      6. | tc2 | int(11) | YES | | NULL | |
      7. +------------+---------+------+-----+---------+-------+
      8. 2 rows in set (0.00 sec)
      9. mysql>

      同步成功。

双机热备

在实际生产环境中,无论配置主从复制模式,还是配置双机热备模式,必须让需要进行同步的数据库达到一个一致的初始状态,至于如何达到一致状态的方法,可以参考 MySQL 官方提供的 MySQL ReferenceChapter 16 Replication 中所讲解的步骤进行,由于篇幅有限,本不做过程阐述。

主从测试结束后,由于 Master 和 Slave 主机的 repldb 数据库是一致的,因此,我们可以在此基础上稍加修改,即可将 Master 和 Slave 配置成双机热备模式,具体步骤如下:

  1. 修改 Master 主机的 MySQL 配置文件 my.cnf(/etc/my.cnf),在主从复制的基础之上,为其 mysqld 添加如下选项:

    1. replicate-do-db=repldb
    2. replicate-ignore-db=mysql
    3. log_slave_updates=1

    配置完成后,Master 主机的 /etc/my.cnf 的全部内容如下:

    1. [root@Master ~]# cat /etc/my.cnf
    2. [mysqld]
    3. datadir=/var/lib/mysql
    4. socket=/var/lib/mysql/mysql.sock
    5. user=mysql
    6. # Disabling symbolic-links is recommended to prevent assorted security risks
    7. symbolic-links=0
    8. log-bin=mysql-bin #表示启用mysql二进制日志,
    9. #该项必须要启用,否则mysql主从不会生效。
    10. max_binlog_size=500M #表示每个binlog文件最大大小,
    11. #当此文件大小等于500M时,会自动生成一个新的日志文件。
    12. #注意:一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
    13. server-id=1 #表示mysql服务器ID,该ID必须在该主从中是唯一的,
    14. #默认是1,该ID可以自行自定义,但必须为数字。
    15. binlog-do-db=repldb #表示需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    16. binlog-ignore-db=mysql #表示不需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    17. #如不加binlog-do-db和binlog-ignore-db,默认同步复制整个mysql数据库。
    18. innodb_flush_log_at_trx_commit=1 #当MySQL的存储引擎是InnoDB时,表示每次事务提交时,刷出日志。
    19. #虽然性能较慢,但是能够保证强一致性。
    20. sync_binlog=1 #设置MySQL在每次事务提交后,执行一次磁盘同步指令,
    21. #将binlog_cache中的数据强制写入磁盘。
    22. #最后面两个参数可以根据实际生产环境对数据一致性的刚醒需求程度来决定是否添加。
    23. replicate-do-db=repldb
    24. replicate-ignore-db=mysql
    25. log_slave_updates=1 #通常情况,从服务器从主服务器接收到的更新不记入它的二进制日志。
    26. #该选项告诉从服务器将其SQL线程执行的更新记入到从服务器自己的二进制日志。
    27. #为了使该选项生效,还必须用--logs-bin选项启动从服务器以启用二进制日志。
    28. #如果想要应用链式复制服务器,应使用--logs-slave-updates。
    29. [mysqld_safe]
    30. log-error=/var/log/mysqld.log
    31. pid-file=/var/run/mysqld/mysqld.pid
    32. [root@Master ~]#
  2. 修改 Slave 主机的 MySQL 配置文件 my.cnf(/etc/my.cnf),在主从复制的基础之上,为其 mysqld 添加如下选项:

    1. log-bin=mysql-bin #表示启用mysql二进制日志,
    2. #该项必须要启用,否则mysql主从不会生效。
    3. log_slave_updates=1
    4. max_binlog_size=500M #表示每个binlog文件最大大小,
    5. #当此文件大小等于500M时,会自动生成一个新的日志文件。
    6. #注意:一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
    7. binlog-do-db=repldb #表示需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    8. binlog-ignore-db=mysql #表示不需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    9. #如不加binlog-do-db和binlog-ignore-db,默认同步复制整个mysql数据库。
    10. innodb_flush_log_at_trx_commit=1 #当MySQL的存储引擎是InnoDB时,表示每次事务提交时,刷出日志。
    11. #虽然性能较慢,但是能够保证强一致性。
    12. sync_binlog=1 #设置MySQL在每次事务提交后,执行一次磁盘同步指令,
    13. #将binlog_cache中的数据强制写入磁盘。
    14. #最后面两个参数可以根据实际生产环境对数据一致性的刚醒需求程度来决定是否添加。

    配置完成后,Slave 主机的 /etc/my.cnf 的全部内容如下:

    1. [root@Slave ~]# cat /etc/my.cnf
    2. [mysqld]
    3. datadir=/var/lib/mysql
    4. socket=/var/lib/mysql/mysql.sock
    5. user=mysql
    6. # Disabling symbolic-links is recommended to prevent assorted security risks
    7. symbolic-links=0
    8. log-bin=mysql-bin #表示启用mysql二进制日志,
    9. #该项必须要启用,否则mysql主从不会生效。
    10. max_binlog_size=500M #表示每个binlog文件最大大小,
    11. #当此文件大小等于500M时,会自动生成一个新的日志文件。
    12. #注意:一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
    13. server-id=2 #表示mysql服务器ID,该ID必须在该主从中是唯一的,
    14. #默认是1,该ID可以自行自定义,但必须为数字。
    15. binlog-do-db=repldb #表示需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    16. binlog-ignore-db=mysql #表示不需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    17. #如不加binlog-do-db和binlog-ignore-db,默认同步复制整个mysql数据库。
    18. innodb_flush_log_at_trx_commit=1 #当MySQL的存储引擎是InnoDB时,表示每次事务提交时,刷出日志。
    19. #虽然性能较慢,但是能够保证强一致性。
    20. sync_binlog=1 #设置MySQL在每次事务提交后,执行一次磁盘同步指令,
    21. #将binlog_cache中的数据强制写入磁盘。
    22. #最后面两个参数可以根据实际生产环境对数据一致性的刚醒需求程度来决定是否添加。
    23. replicate-do-db=repldb
    24. replicate-ignore-db=mysql
    25. log_slave_updates=1
    26. [mysqld_safe]
    27. log-error=/var/log/mysqld.log
    28. pid-file=/var/run/mysqld/mysqld.pid
    29. [root@Slave ~]#
  3. 同时重启 Master 端和 Slave 端的 MySQL Server,启动后,以 root 身份分别登录 Master 和 Slave 端的 MySQL Server,登录后,查看它们的 Binary Log 相关参数的开启状态:

    1. service mysqld restart
    2. mysql -uroot -proot
    3. show variables like '%log_bin%';
    • Master 端执行结果如下:

      1. [root@Master ~]# service mysqld restart
      2. 停止 mysqld [确定]
      3. 正在启动 mysqld [确定]
      4. [root@Master ~]# mysql -u root -proot
      5. Welcome to the MySQL monitor. Commands end with ; or \g.
      6. Your MySQL connection id is 2
      7. Server version: 5.1.71-log Source distribution
      8. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
      9. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective
      10. owners.
      11. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      12. mysql> show variables like '%log_bin%';
      13. +---------------------------------+-------+
      14. | Variable_name | Value |
      15. +---------------------------------+-------+
      16. | log_bin | ON |
      17. | log_bin_trust_function_creators | OFF |
      18. | log_bin_trust_routine_creators | OFF |
      19. | sql_log_bin | ON |
      20. +---------------------------------+-------+
      21. 4 rows in set (0.00 sec)
      22. mysql>
    • Slave 端执行结果如下:

      1. [root@Slave ~]# service mysqld restart
      2. 停止 mysqld [确定]
      3. 正在启动 mysqld [确定]
      4. [root@Slave ~]# mysql -uroot -proot
      5. Welcome to the MySQL monitor. Commands end with ; or \g.
      6. Your MySQL connection id is 4
      7. Server version: 5.1.71-log Source distribution
      8. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
      9. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
      10. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      11. mysql> show variables like '%log_bin%';
      12. +---------------------------------+-------+
      13. | Variable_name | Value |
      14. +---------------------------------+-------+
      15. | log_bin | ON |
      16. | log_bin_trust_function_creators | OFF |
      17. | log_bin_trust_routine_creators | OFF |
      18. | sql_log_bin | ON |
      19. +---------------------------------+-------+
      20. 4 rows in set (0.00 sec)
      21. mysql>
  4. 为了使 Master 在双机热备模式下进行同步时,能够以 repl 身份登录 Slave 并访问 Slave 的 repldb,需要在 Slave 端以 root 用户登录 MySQL Server,然后执行以下 SQL 语句为 Master 的 repl 用户赋予 REPLICATION SLAVE 权限,由于 Slave 端已经处于 root 用户的登录状态,因此,仅仅执执行 grant 命令即可:

    1. grant replication slave on *.* to 'repl'@'Master' identified by '123456';
    2. flush privileges;

    执行完成后,可以通过在 mysql 数据库中查看权限的赋值情况:

    1. use mysql;
    2. select user,repl_slave_priv from user where user='repl';

    本文结果如下:

    1. mysql> grant replication slave on *.* to 'repl'@'Master' identified by '123456';
    2. Query OK, 0 rows affected (0.02 sec)
    3. mysql> flush privileges;
    4. Query OK, 0 rows affected (0.63 sec)
    5. mysql> use mysql;
    6. Reading table information for completion of table and column names
    7. You can turn off this feature to get a quicker startup with -A
    8. Database changed
    9. mysql> select user,repl_slave_priv from user where user='repl';
    10. +------+-----------------+
    11. | user | repl_slave_priv |
    12. +------+-----------------+
    13. | repl | Y |
    14. | repl | Y |
    15. | repl | Y |
    16. | repl | Y |
    17. | repl | Y |
    18. +------+-----------------+
    19. 5 rows in set (0.00 sec)
    20. mysql>
  5. 在 Master 端和 Slave 端分别执行如下命令,锁住表,禁止写操作,并记录 Binary Log 日志文件名和起始位置:

    1. flush tables with read lock;
    2. show master status;
    3. show master status\G;
    • Master 端执行结果如下:

      1. mysql> flush tables with read lock;
      2. Query OK, 0 rows affected (0.00 sec)
      3. mysql> show master status;
      4. +------------------+----------+--------------+------------------+
      5. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      6. +------------------+----------+--------------+------------------+
      7. | mysql-bin.000002 | 106 | repldb | mysql |
      8. +------------------+----------+--------------+------------------+
      9. 1 row in set (0.00 sec)
      10. mysql> show master status\G;
      11. *************************** 1. row ***************************
      12. File: mysql-bin.000002
      13. Position: 106
      14. Binlog_Do_DB: repldb
      15. Binlog_Ignore_DB: mysql
      16. 1 row in set (0.00 sec)
      17. ERROR:
      18. No query specified
      19. mysql>
    • Slave 端执行结果如下:

      1. mysql> flush tables with read lock;
      2. Query OK, 0 rows affected (0.35 sec)
      3. mysql> show master status;
      4. +------------------+----------+--------------+------------------+
      5. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      6. +------------------+----------+--------------+------------------+
      7. | mysql-bin.000001 | 328 | repldb | mysql |
      8. +------------------+----------+--------------+------------------+
      9. 1 row in set (0.00 sec)
      10. mysql> show master status\G;
      11. *************************** 1. row ***************************
      12. File: mysql-bin.000001
      13. Position: 328
      14. Binlog_Do_DB: repldb
      15. Binlog_Ignore_DB: mysql
      16. 1 row in set (0.00 sec)
      17. ERROR:
      18. No query specified
      19. mysql>

    从上面的结果可以看出:

    • Master 端的 Binary Log 的文件名为:mysql-bin.000002,日志起始位置为:106

    • Master 端的 Binary Log 的文件名为:mysql-bin.000001,日志起始位置为:328

  6. Slave 到 Master 的双向 Replication:

    1. 执行 CHANGE MASTER TO 命令时,需要特别注意的是:MASTER_LOG_FILE MASTER_LOG_POS 一定要和在 show master status 命令的结果保持一致,否则无法同步。
    • Master 端

      1. unlock tables;
      2. slave stop;
      3. CHANGE MASTER TO MASTER_HOST='Slave',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_CONNECT_RETRY=60,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=328;
      4. slave start;
      5. show slave status\G;
      6. show slave status;

      Master 端执行结果如下:

      1. mysql> unlock tables;
      2. Query OK, 0 rows affected (0.00 sec)
      3. mysql> slave stop;
      4. Query OK, 0 rows affected, 1 warning (0.00 sec)
      5. mysql>
      6. mysql> CHANGE MASTER TO MASTER_HOST='Slave',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_CONNECT_RETRY=60,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=328;
      7. Query OK, 0 rows affected (0.67 sec)
      8. mysql>
      9. mysql> slave start;
      10. Query OK, 0 rows affected (0.00 sec)
      11. mysql> show slave status\G;
      12. *************************** 1. row ***************************
      13. Slave_IO_State: Connecting to master
      14. Master_Host: Slave
      15. Master_User: repl
      16. Master_Port: 3306
      17. Connect_Retry: 60
      18. Master_Log_File: mysql-bin.000001
      19. Read_Master_Log_Pos: 328
      20. Relay_Log_File: mysqld-relay-bin.000001
      21. Relay_Log_Pos: 4
      22. Relay_Master_Log_File: mysql-bin.000001
      23. Slave_IO_Running: No
      24. Slave_SQL_Running: Yes
      25. Replicate_Do_DB: repldb
      26. Replicate_Ignore_DB: mysql
      27. Replicate_Do_Table:
      28. Replicate_Ignore_Table:
      29. Replicate_Wild_Do_Table:
      30. Replicate_Wild_Ignore_Table:
      31. Last_Errno: 0
      32. Last_Error:
      33. Skip_Counter: 0
      34. Exec_Master_Log_Pos: 328
      35. Relay_Log_Space: 106
      36. Until_Condition: None
      37. Until_Log_File:
      38. Until_Log_Pos: 0
      39. Master_SSL_Allowed: No
      40. Master_SSL_CA_File:
      41. Master_SSL_CA_Path:
      42. Master_SSL_Cert:
      43. Master_SSL_Cipher:
      44. Master_SSL_Key:
      45. Seconds_Behind_Master: NULL
      46. Master_SSL_Verify_Server_Cert: No
      47. Last_IO_Errno: 0
      48. Last_IO_Error:
      49. Last_SQL_Errno: 0
      50. Last_SQL_Error:
      51. 1 row in set (0.00 sec)
      52. ERROR:
      53. No query specified
      54. mysql> show slave status;
      55. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
      56. | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
      57. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
      58. | Waiting for master to send event | Slave | repl | 3306 | 60 | mysql-bin.000001 | 328 | mysqld-relay-bin.000002 | 251 | mysql-bin.000001 | Yes | Yes | repldb | mysql | | | | | 0 | | 0 | 328 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
      59. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
      60. 1 row in set (0.00 sec)
      61. mysql>
    • Slave 端

      1. unlock tables;
      2. slave stop;
      3. CHANGE MASTER TO MASTER_HOST='Master',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_CONNECT_RETRY=60,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=106;
      4. slave start;
      5. show slave status\G;
      6. show slave status;

      Slave 端执行结果如下:

      1. mysql> unlock tables;
      2. Query OK, 0 rows affected (0.00 sec)
      3. mysql> slave stop;
      4. Query OK, 0 rows affected (0.00 sec)
      5. mysql>
      6. mysql> CHANGE MASTER TO MASTER_HOST='Master',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_CONNECT_RETRY=60,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=106;
      7. Query OK, 0 rows affected (0.01 sec)
      8. mysql>
      9. mysql> slave start;
      10. Query OK, 0 rows affected (0.00 sec)
      11. mysql> show slave status\G;
      12. *************************** 1. row ***************************
      13. Slave_IO_State: Connecting to master
      14. Master_Host: Master
      15. Master_User: repl
      16. Master_Port: 3306
      17. Connect_Retry: 60
      18. Master_Log_File: mysql-bin.000002
      19. Read_Master_Log_Pos: 106
      20. Relay_Log_File: mysqld-relay-bin.000001
      21. Relay_Log_Pos: 4
      22. Relay_Master_Log_File: mysql-bin.000002
      23. Slave_IO_Running: No
      24. Slave_SQL_Running: Yes
      25. Replicate_Do_DB: repldb
      26. Replicate_Ignore_DB: mysql
      27. Replicate_Do_Table:
      28. Replicate_Ignore_Table:
      29. Replicate_Wild_Do_Table:
      30. Replicate_Wild_Ignore_Table:
      31. Last_Errno: 0
      32. Last_Error:
      33. Skip_Counter: 0
      34. Exec_Master_Log_Pos: 106
      35. Relay_Log_Space: 106
      36. Until_Condition: None
      37. Until_Log_File:
      38. Until_Log_Pos: 0
      39. Master_SSL_Allowed: No
      40. Master_SSL_CA_File:
      41. Master_SSL_CA_Path:
      42. Master_SSL_Cert:
      43. Master_SSL_Cipher:
      44. Master_SSL_Key:
      45. Seconds_Behind_Master: NULL
      46. Master_SSL_Verify_Server_Cert: No
      47. Last_IO_Errno: 0
      48. Last_IO_Error:
      49. Last_SQL_Errno: 0
      50. Last_SQL_Error:
      51. 1 row in set (0.00 sec)
      52. ERROR:
      53. No query specified
      54. mysql> show slave status;
      55. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
      56. | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
      57. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
      58. | Waiting for master to send event | Master | repl | 3306 | 60 | mysql-bin.000002 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000002 | Yes | Yes | repldb | mysql | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
      59. +----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
      60. 1 row in set (0.00 sec)
      61. mysql>
  7. 最后需要再次查看 Master 和 Slave 的 Slave 状态,验证它们是否已经处于双机热备状态:

    1. show slave status\G;
    • Master 端执行结果如下:

      1. mysql> show slave status\G;
      2. *************************** 1. row ***************************
      3. Slave_IO_State: Waiting for master to send event
      4. Master_Host: Slave
      5. Master_User: repl
      6. Master_Port: 3306
      7. Connect_Retry: 60
      8. Master_Log_File: mysql-bin.000001
      9. Read_Master_Log_Pos: 328
      10. Relay_Log_File: mysqld-relay-bin.000002
      11. Relay_Log_Pos: 251
      12. Relay_Master_Log_File: mysql-bin.000001
      13. Slave_IO_Running: Yes
      14. Slave_SQL_Running: Yes
      15. Replicate_Do_DB: repldb
      16. Replicate_Ignore_DB: mysql
      17. Replicate_Do_Table:
      18. Replicate_Ignore_Table:
      19. Replicate_Wild_Do_Table:
      20. Replicate_Wild_Ignore_Table:
      21. Last_Errno: 0
      22. Last_Error:
      23. Skip_Counter: 0
      24. Exec_Master_Log_Pos: 328
      25. Relay_Log_Space: 407
      26. Until_Condition: None
      27. Until_Log_File:
      28. Until_Log_Pos: 0
      29. Master_SSL_Allowed: No
      30. Master_SSL_CA_File:
      31. Master_SSL_CA_Path:
      32. Master_SSL_Cert:
      33. Master_SSL_Cipher:
      34. Master_SSL_Key:
      35. Seconds_Behind_Master: 0
      36. Master_SSL_Verify_Server_Cert: No
      37. Last_IO_Errno: 0
      38. Last_IO_Error:
      39. Last_SQL_Errno: 0
      40. Last_SQL_Error:
      41. 1 row in set (0.00 sec)
      42. ERROR:
      43. No query specified
      44. mysql>
    • Slave 端执行结果如下:

      1. mysql> show slave status\G;
      2. *************************** 1. row ***************************
      3. Slave_IO_State: Waiting for master to send event
      4. Master_Host: Master
      5. Master_User: repl
      6. Master_Port: 3306
      7. Connect_Retry: 60
      8. Master_Log_File: mysql-bin.000002
      9. Read_Master_Log_Pos: 106
      10. Relay_Log_File: mysqld-relay-bin.000002
      11. Relay_Log_Pos: 251
      12. Relay_Master_Log_File: mysql-bin.000002
      13. Slave_IO_Running: Yes
      14. Slave_SQL_Running: Yes
      15. Replicate_Do_DB: repldb
      16. Replicate_Ignore_DB: mysql
      17. Replicate_Do_Table:
      18. Replicate_Ignore_Table:
      19. Replicate_Wild_Do_Table:
      20. Replicate_Wild_Ignore_Table:
      21. Last_Errno: 0
      22. Last_Error:
      23. Skip_Counter: 0
      24. Exec_Master_Log_Pos: 106
      25. Relay_Log_Space: 407
      26. Until_Condition: None
      27. Until_Log_File:
      28. Until_Log_Pos: 0
      29. Master_SSL_Allowed: No
      30. Master_SSL_CA_File:
      31. Master_SSL_CA_Path:
      32. Master_SSL_Cert:
      33. Master_SSL_Cipher:
      34. Master_SSL_Key:
      35. Seconds_Behind_Master: 0
      36. Master_SSL_Verify_Server_Cert: No
      37. Last_IO_Errno: 0
      38. Last_IO_Error:
      39. Last_SQL_Errno: 0
      40. Last_SQL_Error:
      41. 1 row in set (0.00 sec)
      42. ERROR:
      43. No query specified
      44. mysql>

    从上面的结果可以看出,双机 Slave 状态的 Slave_IO_State 均已经由 Connecting to master 变成了 Waiting for master to send event,说明双机热备搭建成功,接下来就可以对其进行测试了。

测试双机热备

在 Master 端向 test 表中插入数据,然后在 Slave 端也插入数据,最后在 Master 端 和 Slave 端查看 test 表中的数据是否一致:

异常监控

方案一

检测 MySQL 主从数据是否同步,可以通过定时查询 Master 和 Slave 库中一张或者多张常用的大表的 count 结果是否相同,具体脚本如下:

  1. #!/bin/bash
  2. logfile=/myspace/mysqlcheck.log
  3. echo "check time : `date`" >> $logfile
  4. r1=`mysql -hMaster -urepl -p123456 -Drepldb -e"select count(*) from test" | tail -1`
  5. r2=`mysql -hSlave -urepl -p123456 -Drepldb -e"select count(*) from test" | tail -1`
  6. if [ $r1 -eq $r2 ]; then
  7. echo "result from Master is same as that from Slave : [$r1]" >> $logfile
  8. else
  9. echo "result from Master is different from that from Slave : [$r1 - $r2]" >> $logfile
  10. fi
  11. echo "-----------------------------------------" >> $logfile

通过 crontab 命令设置每隔 3 分钟执行一次上面的脚本:

  1. */3 * * * * /myspace/mysqlcheck.sh

方案二

可以利用 Zabbix 或者 Nagios 对 MySQL 主从的 Slave 主机的 Slave I/O 线程和 Slave SQL 线程的运行状态进行监控,监控脚本如下:

  1. #!/bin/sh
  2. user=$1
  3. password=$2
  4. declare -a status
  5. status=($(mysql -u$user -p$password -e "show slave status\G" | grep Running | awk '{print $2}'))
  6. if [ "${status[0]}" = "Yes" -a "${status[1]}" = "Yes" ]
  7. then
  8. echo "OK ==> Slave is running"
  9. exit 0
  10. else
  11. echo "Critical ==> Slave is error"
  12. exit 2
  13. fi

至于如何配置 Zabbix 或者 Nagios 监控系统,本文不做过多说明,网上一搜一大推!

配置 Keepalived HA

安装 Keepalived

和 MySQL 类似,采用 rpm 方式安装,安装脚本如下:

  1. rpm -ivh keepalived-1.2.7-3.el6.x86_64.rpm net-snmp-5.5-49.el6.x86_64.rpm net-snmp-libs-5.5-49.el6.x86_64.rpm net-snmp-devel-5.5-49.el6.x86_64.rpm lm_sensors-3.1.1-17.el6.x86_64.rpm lm_sensors-libs-3.1.1-17.el6.x86_64.rpm lm_sensors-devel-3.1.1-17.el6.x86_64.rpm elfutils-devel-0.152-1.el6.x86_64.rpm elfutils-libelf-devel-0.152-1.el6.x86_64.rpm rpm-devel-4.8.0-37.el6.x86_64.rpm file-devel-5.04-15.el6.x86_64.rpm popt-devel-1.13-7.el6.x86_64.rpm tcp_wrappers-devel-7.6-57.el6.x86_64.rpm --test

配置 Keepalived

Keepalived 的配置文件位于 /etc/keepalived/keepalived.conf 文件中,具体配置如下:

  1. global_defs {
  2. notification_email {
  3. zhaohm3@asiainfo.com
  4. }
  5. notification_email_from zhaohm3@asiainfo.com
  6. smtp_server mail.asiainfo.com
  7. smtp_connect_timeout 30
  8. router_id LVS_DEVEL
  9. }
  10. vrrp_instance VI_1 {
  11. state MASTER #指定Master节点为主节点 备用节点上设置为BACKUP即可
  12. interface eth0 #绑定虚拟IP的网络接口
  13. virtual_router_id 51 #VRRP组名,两个节点的设置必须一样,以指明各个节点属于同一VRRP组
  14. priority 100 #主节点的优先级(1-254之间),备用节点必须比主节点优先级低
  15. advert_int 1 #组播信息发送间隔,两个节点设置必须一样
  16. authentication { #设置验证信息,两个节点必须一致
  17. auth_type PASS
  18. auth_pass 1111
  19. }
  20. virtual_ipaddress { #指定虚拟IP, 两个节点设置必须一样
  21. 192.168.26.130
  22. }
  23. }
  24. virtual_server 192.168.26.130 3306 {
  25. delay_loop 2
  26. lb_algo rr
  27. lb_kind NAT
  28. nat_mask 255.255.255.0
  29. persistence_timeout 50
  30. protocol TCP
  31. real_server 192.168.26.133 3306 {
  32. weight 3
  33. notify_down /myspace/MySQL.sh
  34. TCP_CHECK {
  35. connect_timeout 10
  36. nb_get_retry 3
  37. delay_before_retry 3
  38. connect_port 3306
  39. }
  40. }
  41. }
  1. global_defs {
  2. notification_email {
  3. zhaohm3@asiainfo.com
  4. }
  5. notification_email_from zhaohm3@asiainfo.com
  6. smtp_server mail.asiainfo.com
  7. smtp_connect_timeout 30
  8. router_id LVS_DEVEL
  9. }
  10. vrrp_instance VI_1 {
  11. state MASTER
  12. interface eth0
  13. virtual_router_id 51
  14. priority 90
  15. advert_int 1
  16. authentication {
  17. auth_type PASS
  18. auth_pass 1111
  19. }
  20. virtual_ipaddress {
  21. 192.168.26.130
  22. }
  23. }
  24. virtual_server 192.168.26.130 3306 {
  25. delay_loop 2
  26. lb_algo rr
  27. lb_kind NAT
  28. nat_mask 255.255.255.0
  29. persistence_timeout 50
  30. protocol TCP
  31. real_server 192.168.26.134 3306 {
  32. weight 3
  33. notify_down /myspace/MySQL.sh
  34. TCP_CHECK {
  35. connect_timeout 10
  36. nb_get_retry 3
  37. delay_before_retry 3
  38. connect_port 3306
  39. }
  40. }
  41. }

notify_down 选项服务时效后所执行的脚本,本文为 /myspace/MySQL.sh,其内容如下:

  1. #!/bin/sh
  2. service keepalived stop

启动 Keepalived

配置完成后,执行 service keepalived start 即可启动 keepalived 服务。启动后,客户端通过 keepalived 所提供的虚拟 ip 地址 192.168.26.130 即可访问 Master,当 Master 出现故障后,keepalived 会将 192.168.26.130 切换到 Slave 继续提供服务。

说明

由于 MySQL Replication 是异步非阻塞模式,为了防止人为原因引起主从同步不一致的问题,客户端必须只能够通过 Keepalived 或者其它的 LVS 所提供的 VIP 连接 MySQL Server,不能够直接连接到 Master 或者 Slave 上对 MySQL 数据库进行写操作。

宕机恢复

在双机热备模式下,如果当前对外提供服务的 A 主机发生宕机,Keepalived 会将服务切换到另外一台 B 主机上,由 B 主机对外提供读写服务,当 A 机故障恢复重启后,需要手动对 A 主机进行同步恢复操作,具体步骤如下:

从上面可以看出,在宕机恢复期间,MySQL 双机热备模式对外只能够提供读服务,不能够提供写服务。

如果应用在此期间的写服务没有 Update,只有 Insert,那么可以在 MySQL 双机热备之上添加一个消息队列服务组件(比如 Active MQ),在手动恢复期间,将所有的 Insert 数据插入消息队列当中,等到手动同步完成后,再将消息队列当中的 Insert 数据应用到 MySQL 数据库中,这样,就可以在手动恢复期间对外提供部分写服务。

References

[1] MySQL Replicationhttp://dev.mysql.com/doc/refman/5.1/en/replication.htm
[2] Keepalived原理与实战精讲: http://bbs.nanjimao.com/thread-845-1-1.html
[3] VRRP协议介绍: http://bbs.nanjimao.com/thread-790-1-1.html
[4] Keepalived案例一:Keepalived双机热备(HA)精讲: http://bbs.nanjimao.com/thread-855-1-1.html
[5] 利用keepalived构建高可用MySQL-HA: http://kb.cnblogs.com/page/83944/
[6] keepalived配置相关: https://github.com/chenzhiwei/linux/tree/master/keepalived
[7] Keepalived + nginx实现高可用性和负载均衡: http://my.oschina.net/zyc1016/blog/138574
[8] haproxy+keepalived实现高可用负载均衡: http://www.cnblogs.com/dkblog/archive/2011/07/06/2098949.html
[9] MySQL 双机热备配置步骤: https://www.zybuluo.com/Spongcer/note/69119

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注