高性能MySQL集群详解（二）

neocai

　　一.通过Keepalived搭建MySQL双主模式的高可用集群系统
　　1.MySQL Replication介绍:
　　MySQL Replication是MySQL自身提供的一个主从复制功能，其实也就是一台MySQL服务器（称为Slave）从另一台MySQL服务器（称为Master）上复制日志，然后解析日志并应用到自身的过程。MySQL Replication是单向、异步复制，基本复制过程为：Master服务器首先将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志的循环，这些日志文件可以发送到Slave服务器进行更新。当一台Slave服务器连接Master服务器时，它从Master服务器日志中读取上一次成功更新的位置。然后Slave服务器开始接收从上一次完成更新后发生的所有更新，所有更新完成，将等待主服务器通知新的更新。
　　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支持多种类型的复制方式，常见的有基于语句的复制、基于行的复制和混合类型的复制。下面分别进行介绍。
　　（1）基于语句的复制
　　MySQL默认采用基于语句的复制，效率很高。基本方式是：在Master服务器上执行的SQL语句，在Slave服务器上再次执行同样的语句。而一旦发现没法精确复制时，会自动选择基于行的复制。
　　（2）基于行的复制
　　基本方式为：把Master服务器上改变的内容复制过去，而不是把SQL语句在从服务器上执行一遍，从MySQL5.0开始支持基于行的复制。
　　（3）混合类型的复制
　　其实就是上面两种类型的组合，默认采用基于语句的复制，如果发现基于语句的复制无法精确完成，就会采用基于行的复制。
　　2.MySQL Replication实现原理：
　　MySQL Replication是一个从Master复制到一台或多台Slave的异步过程，在Master与Slave之间实现整个复制过程主要由三个线程来完成，其中一个IO线程在Master端，另两个线程（SQL线程和IO线程）在Slave端。
　　要实现MySQL Replication,首先在Master服务器上打开MySQL的Binary Log（产生二进制日志文件）功能，因为整个复制过程实际上就是Slave从Master端获取该日志，然后在自身上将二进制文件解析为SQL语句并完全顺序地执行SQL语句所记录的各种操作。更详细的过程如下。
　　1）首先Slave上的IO线程连接上Master,然后请求从指定日志文件的指定位置或者从最开始的日志位置之后的日志内容。
　　2）Master在接收到来自Slave的IO线程请求后，通过自身的IO线程，根据请求信息读取指定日志位置之后的日志信息，并返回给Slave端的IO线程。返回信息中除了日志所包含的信息之外，还包括此次返回的信息在Master端对应的Binary Log文件的名称以及在Binary Log中的位置。
　　3）Slave的IO线程接收到信息后，将获取到的日志内容依次写入Slave端的Relay Log文件（类似于mysql-relay-bin.xxxxxx）的最后，并且将读取到的Master端的Binary Log的文件名和位置记录到一个名为master-info的文件中，以便在下一次读取的时候能迅速定位开始往后读取日志信息的位置。
　　4）Slave的SQL线程在检测到Relay Log文件中新增加了内容后，会马上解析该Relay Log文件中的内容，将日志内容解析为SQL语句，然后在自身执行这些SQL,由于是在Master端和Slave端执行了同样的SQL操作，所以两端的数据是完全一样的。至此整个复制过程结束。
　　3.MySQL Replication常用架构
　　MySQL Replication技术在实际应用中有多种实现架构，常见的有：
　　☆一主一次，即一台Master服务器和一台Slave服务器。这是最常见的架构。
　　★一主多从，即一台Master服务器和两台或两台以上Slave服务器，经常用在写操作不频繁、查询量比较大的业务环境中。
　　☆主主互备，又称双主互备，即两台MySQL Server互相将对方作为自己的Master，自己又同时作为对方的Slave来进行复制。主要用于对MySQL写操作要求比较高的环境中，避免了MySQL单点故障。
　　★双主多从，其实就是双主互备，然后再加上多台Slave服务器。主要用于对MySQL写操作要求比较高，同时查询量比较大的环境中。
　　其实可以根据具体的情况灵活地将Master/Slave结构进行变化组合，但万变不离其宗，在进行MySQL Replication的各种部署之前，必须遵守的规则如下：
　　◇同一时刻只能有一台Master服务器进行写操作。
　　◆一台Master服务器可以有多台Slave服务器。
　　◇无论是Master服务器还是Slave服务器，都要确保各自的ServerID唯一，否则双主互备就会出现问题。
　　◆一台Slave服务器可以将其从Master服务器获得的更新信息传递给其他的Slave服务器。
　　4.MySQL主主互备模式架构
　　企业级MySQL集群具备高可用、可扩展、易管理、低成本的特点。下面将介绍企业环境中经常应用的一个解决方案，即MySQL的双主互备架构，主要设计思路是通过MySQL Replication技术将两台MySQL Server互相将对方作为自己的Master,自己又同时作为对方的Slave来进行复制。这样就实现了高可用架构中的数据同步功能，同时，将采用Keepalived来实现MySQL的自动failover。在这种架构中，虽然两台MySQL Server互为主从，但同一时刻只能有一台MySQL Server可读写，而另一台MySQL Server只能进行读操作，这样可保证数据的一致性。整个架构如下图：

　　5.MySQL主主互备模式配置
　　MySQL主从复制的配置还是比较简单的，仅仅需要修改MySQL配置文件即可，这里要配置的是主主互备模式，但配置过程和一主一从结构是完全一样的，配置环境如下：
　　主机名      操作系统版本      MySQL版本    主机IP    MySQL VIP

　　DB1(Master)  CentOS>
　　DB2(Slave)   CentOS>　　1.修改MySQL配置文件
　　在默认情况下，MySQL的配置文件是/etc/my.cnf,首先修改DB1主机的配置文件，在/etc/my.cnf文件中的“[mysqld]”段添加如下内容：
　　server-id=1
　　log-bin=mysql-bin
　　relay-log=mysql-relay-bin
　　replicate-wild-ignore-table=mysql.%
　　replicate-wild-ignore-table=test.%
　　replicate-wild-ignore-table=information_schema.%
　　然后修改DB2主机的配置文件，在/etc/my.cnf文件中的“[mysqld]”段添加如下内容：
　　server-id=2
　　log-bin=mysql-bin
　　relay-log=mysql-relay-bin
　　replicate-wild-ignore-table=mysql.%
　　replicate-wild-ignore-table=test.%
　　replicate-wild-ignore-table=information_schema.%
　　其中，server-id是节点标识，主、从节点不能相同，必须全局唯一。log-bin表示开启MySQL的binlog日志功能。“mysql-bin”表示日志文件的命名格式，会生成文件名为mysql-bin.000001、mysql-bin.000002等的日志文件。relay-log用来定义relay-log日志文件的命名格式。replicate-wild-ignore-table是个复制过滤选项，可以过滤不需要复制的数据库或表，例如“mysql.%”表示不复制MySQL库下的所有对象，其他以此类推。与此对应的是replicate-wild-do-table选项，用来指定需要复制的数据库或表。
　　这里需要注意的是，不要在主库上使用binlog-do-db或binlog-ignore-db选项，也不要在从库上使用replicate-do-db或replicate-ignore-db选项，因为这样可能产生跨库更新失败的问题。推荐在从库使用replicate-wild-do-table和replicate-wild-ignore-table两个选项来解决复制过滤问题。
　　2.手动同步数据库
　　如果DB1上已经有MySQL数据，那么在执行主主互备之前，需要将DB1和DB2上两个MySQL的数据保持同步，首先在DB1上备份MySQL数据，执行如下SQL语句：
　　mysql> FLUSH TABLES WITH READ LOCK;
　　Query OK, 0 rows affected (0.01 sec)
　　不要退出这个终端，否则这个锁就失效了。在不退出终端的情况下，再开启一个终端直接打包压缩数据文件或使用mysqldump工具导出数据。这里通过打包mysql文件来完成数据的备份，操作过程如下：
　　[root@db1 ~]# cd /var/lib/
　　[root@db1 ~]# tar zcvf mysql.tar.gz mysql
　　[root@db1 ~]# scp mysql.tar.gz db2:/var/lib/
　　将数据传输到DB2后，依次重启DB1和DB2上面的MySQL。
　　3.创建复制用户并授权
　　首先在DB1的MySQL库中创建复制用户，操作过程如下：

　　mysql> grant replication slave on *.* to 'repl_user'@'10.0.0.36'>　　Query OK, 0 rows affected (0.01 sec)
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000001 |     1698 |              |                  |
　　+------------------+----------+--------------+------------------+
　　然后在DB2的MySQL库中将DB1设为自己的主服务器，操作如下：
　　mysql> change master to master_host='10.0.0.35',master_user='repl_user',master_password='repl_passwd',master_log_file='mysql-bin.000001',master_log_pos=1698;
　　这里需要注意master_log_file和master_log_pos两个选项，这两个选项的值刚好是在DB1上通过SQL语句“show master status”查询到的结果。
　　接着就可以在DB2上启动slave服务了，可执行如下SQL命令：
　　mysql> start slave;
　　查看DB2上slave的运行状态：
　　mysql> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 10.0.0.35
　　Master_User: repl_user
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000001
　　Read_Master_Log_Pos: 1698
　　Relay_Log_File: mysql-relay-bin.000016

　　>
　　>　　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: mysql.%,test.%,information_schema.%
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 1698

　　>　　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:
　　1 row in set (0.00 sec)
　　通过查看slave的运行状态可以发现，一切运行正常，这里需要重点关注的是Slave_IO_Running和Slave_SQL_Running，这两个就是在Slave节点上运行的主从复制线程，正常情况下这两个值都应该为Yes。另外，还需要注意的是Slave_IO_State、Master_Host、Master_Log_File、Read_Master_Log_Pos、Relay_Log_File、Relay_Log_Pos和Relay_Master_Log_File几个选项，可以查看出MySQL复制的运行原理及执行规律。最后还有一个Replicate_Wild_Ignore_Table选项，这个是之前在my.cnf中添加过的，通过此选项的输出值可以知道过滤了哪些数据库。
　　到这里，从DB1到DB2的MySQL主从复制已经完成。接下来开始配置从DB2到DB1的MySQL主从复制，这个配置过程与上面的过程完全一样，首先在DB2的MySQL库中创建复制用户，操作如下：

　　mysql>grant replication slave on *.* to 'repl_user'@'10.0.0.35'>　　mysql>show master status;
　　然后在DB1的MySQL库中将DB2设为自己的主服务器，操作如下：
　　mysql>change master to master_host='10.0.0.36',master_user='repl_user',master_password='repl_passwd',master_log_file='mysql-bin.000001',master_log_pos=266;
　　最后，就可以在DB1上启动slave服务了，可以执行如下SQL命令:
　　mysql>start slave;
　　查看DB1上slave的运行状态，Slave_IO_Running和Slave_SQL_Running都是Yes状态，表明DB1上复制服务运行正常。至此，MySQL双主模式的主从复制配置完毕。
　　6.配置Keepakived实现MySQL双主高可用
　　在进行高可用配置之前，首先需要在DB1和DB2服务器上安装Keepalived软件。我这里是直接yum安装，关于keepalived的安装这里不再说明，直接进入Keepalived的配置过程。下面是DB1服务器上/etc/keepalived/keepalived.conf文件的内容。
　　[root@db1 ~]# cat /etc/keepalived/keepalived.conf
　　! Configuration File for keepalived
　　global_defs {
　　notification_email {
　　acassen@firewall.loc
　　failover@firewall.loc
　　sysadmin@firewall.loc
　　}
　　notification_email_from Alexandre.Cassen@firewall.loc
　　smtp_server 192.168.200.1
　　smtp_connect_timeout 30
　　router_id LVS_DEVEL
　　}
　　vrrp_script check_mysqld {
　　script "/etc/keepalived/mysqlcheck/slave.sh"  #检测MySQL复制状态的脚本
　　interval 2
　　weight 21
　　}
　　vrrp_instance HA_1 {
　　state BACKUP            #在DB1和DB2上均配置为BACKUP
　　interface eth0
　　virtual_router_id 80
　　priority 100
　　advert_int 2
　　nopreempt              #不抢占模式，只在优先级高的机器上设置即可，优先级低的机器可不设置
　　authentication {
　　auth_type PASS
　　auth_pass aaaavvv
　　}
　　track_script {
　　check_mysqld
　　}
　　virtual_ipaddress {
　　10.0.0.40/24 dev eth0    #MySQL的对外服务Ip，即VIP
　　}
　　}
　　其中，/etc/keepalived/mysqlcheck/slave.sh文件的内容为：
　　[root@db1 mysqlcheck]# cat slave.sh
　　#!/bin/bash
　　#Date: 2017-07-16
　　#Mail: 1135757611@qq.com
　　#Function: 监控mysql主从同步
　　#
　　. /etc/init.d/functions
　　Slave_IO=`/usr/bin/mysql -uroot -e "show slave status\G;" | grep -i " Slave_IO_R"|awk -F ": " '{print $2}'`
　　Slave_SQL=`/usr/bin/mysql -uroot -e "show slave status\G;" | grep -i "Slave_SQL_R"|awk -F ": " '{print $2}'`
　　if [[ $Slave_IO = 'Yes' &&  $Slave_SQL = 'Yes' ]]
　　then
　　action "Slave is Running" /bin/true
　　MYSQL_SLAVE_STATUS=0
　　else
　　action "Slave is not Running" /bin/false
　　MYSQL_SLAVE_STATUS=1
　　fi
　　exit $MYSQL_SLAVE_STATUS
　　使用前要保证此脚本有执行权限。
　　接着将Keepalived.conf文件中priority值修改为90。由于配置的是不抢占模式，因此，还需要去掉nopreempt选项。
　　在完成所有配置后，分别在DB1和DB2上启动Keepalived服务，在正常情况下VIP地址应该运行在DB1服务器上。
　　7.测试MySQL主从同步功能
　　为了验证MySQL的复制功能，可以编写一个简单的程序进行测试，也可以通过远程客户端登录进行测试。这里通过一个MySQL客户端，然后利用MySQL的VIP地址登录，看能否登录，并在登录后进行读、写操作，看看DB1和DB2之间能否实现数据同步。由于采用远程登录测试，因此DB1和DB2两台MySQL服务器都要事先做好授权，允许从远程登录。
　　1.在远程客户端通过VIP登录测试
　　首先通过远程MySQL客户端命令行登录VIP为“10.0.0.40”的数据库，操作如下：
　　[root@zabbix ~]# mysql -u test -h 10.0.0.40 -pyan123
　　Welcome to the MySQL monitor.  Commands end with ; or \g.

　　Your MySQL connection>　　Server version: 5.1.73-log Source distribution
　　Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
　　Oracle is a registered trademark of Oracle Corporation and/or its
　　affiliates. Other names may be trademarks of their respective
　　owners.
　　Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
　　mysql> show variables like "%hostname%";
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| hostname      | db1   |
　　+---------------+-------+
　　1 row in set (0.00 sec)
　　mysql> show variables like "%server_id%";
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| server_id     | 1     |
　　+---------------+-------+
　　1 row in set (0.01 sec)
　　从SQL输出结果看，可以通过VIP登录，并且登录了DB1服务器。
　　2.数据复制功能测试
　　接着上面的SQL操作过程，通过远程的MySQL客户端连接VIP,进行读、写操作测试，操作过程如下：
　　mysql> create database bbs;
　　Query OK, 1 row affected (0.00 sec)
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| bbs                |
　　| blog               |
　　| mysql              |
　　| test               |
　　| wordpress          |
　　+--------------------+
　　6 rows in set (0.00 sec)
　　mysql> use bbs;
　　Database changed
　　mysql> create table user (id int,email varchar(80),password varchar(40) not null);
　　Query OK, 0 rows affected (0.20 sec)
　　mysql> show tables;
　　+---------------+
　　| Tables_in_bbs |
　　+---------------+
　　| user          |
　　+---------------+
　　1 row in set (0.01 sec)
　　mysql> insert into user (id,email,password) values(1,"bbs@163.com","aa1234");
　　Query OK, 1 row affected (0.00 sec)
　　这个过程创建了一个数据库bbs，然后在bbs库中创建了一张表user。为了验证数据是否复制到DB2主机上，登录DB2主机的MySQL命令行，查询过程如下：
　　[root@db2 ~]# mysql
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| bbs                |
　　| blog               |
　　| mysql              |
　　| wordpress          |
　　+--------------------+
　　5 rows in set (0.00 sec)
　　mysql> use bbs;
　　Database changed
　　mysql> show tables;
　　+---------------+
　　| Tables_in_bbs |
　　+---------------+
　　| user          |
　　+---------------+
　　1 row in set (0.00 sec)
　　mysql> select * from user;
　　+------+-------------+----------+

　　|>　　+------+-------------+----------+
　　|    1 | bbs@163.com | aa1234   |
　　+------+-------------+----------+
　　1 row in set (0.00 sec)
　　从SQL输出结果看，刚才创建的库和表都已经同步到了DB2服务器上。其实也可以直接登录DB2服务器，然后执行数据库的读、写操作，看数据能否迅速同步到DB1的MySQL数据库中。
　　8.测试Keepalived实现MySQL故障转移
　　为了测试Keepalived实现的故障转移功能，需要模拟一些故障，比如，可以通过断开DB1主机的网络、关闭DB1主机、关闭DB1上MySQL服务等各种操作实现。这里在DB1服务器上关闭MySQL的日志接收功能，以此来模拟DB1上MySQL的故障。由于在DB1和DB2服务器上都添加了监控MySQL运行状态的脚本slave.sh,因此当关闭DB1的MySQL日志接收功能后，Keepalived会立刻检测到，接着执行切换操作。
　　1.停止DB1服务器的日志接收功能
　　首先在远程MySQL客户端以VIP地址登录MySQL系统，不用退出这个连接，然后在DB1服务器的MySQL命令行执行如下操作：
　　[root@db1 ~]# mysql
　　mysql> stop slave;
　　2.在远程客户端测试
　　继续在刚才打开的远程MySQL连接中执行命令，操作如下:
　　mysql> select * from user;
　　ERROR 2013 (HY000): Lost connection to MySQL server during query
　　mysql> select * from user;
　　ERROR 2006 (HY000): MySQL server has gone away
　　No connection. Trying to reconnect...

　　Connection>　　Current database: bbs
　　+------+-------------+----------+

　　|>　　+------+-------------+----------+
　　|    1 | bbs@163.com | aa1234   |
　　+------+-------------+----------+
　　1 row in set (0.24 sec)
　　mysql> show variables like "%hostname%";
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| hostname      | db2   |
　　+---------------+-------+
　　1 row in set (0.00 sec)
　　mysql> show variables like "%server_id%";
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| server_id     | 2     |
　　+---------------+-------+
　　1 row in set (0.00 sec)
　　从这个操作过程可以看出，在Keepalived切换后，之前的session连接失效，所以第一查询命令失败。然后重新执行查询命令，MySQL会执行重新连接，随后输出了查询结果，从后面两个SQL的查询结果可知，MySQL服务已经从DB1服务器切换到DB2服务器。Keepalived的切换过程非常迅速，整个过程大概持续1~3s，重新切换到新的服务器后，之前所有的MySQL连接失效，重新连接可以恢复正常。
　　接着，重新打开DB1上MySQL的日志接收功能，可以发现Keepalived将不再执行切换操作，因为上面将Keepalived配置为不抢占模式，此时，MySQL服务将一直在DB2服务器上运行，直到DB2主机或服务出现故障才再次进行切换操作。这样做的原因是在数据库环境下，每次切换的代价很大，因而关闭了Keepalived的主动抢占模式。