MySQL Replication+Keepalived

发表于 更新于 分类于

概述

  MySql官网提供了2种复制方案，一种是基于日志文件来实现复制，一种是基于GTIDs来实现复制，这篇文章采用第一种来试验，结合keepalived完成高可用。试验环境如下：

系统环境 IP地址 节点类型
CentOS6.5 192.168.1.92 Master
CentOS6.5 192.168.1.93 Slave(用于备份服务器)
CentOS6.5 192.168.1.94 VIP
CentOS6.5 192.168.1.17 Client

MySql安装

  本文采用离线安装包的方式安装mysql服务。使用的版本是5.7.13，下载地址,下载的文件清单：

  1. mysql-community-client-5.7.13-1.el6.x86_64.rpm
  2. mysql-community-common-5.7.13-1.el6.x86_64.rpm
  3. mysql-community-libs-5.7.13-1.el6.x86_64.rpm
  4. mysql-community-server-5.7.13-1.el6.x86_64.rpm

Master和Slave节点都安装mysql，执行以下操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@localhost scripts]# ntpdate -u ntp.api.bz
[root@localhost ~]# rpm -qa | grep mysql
mysql-server-5.1.71-1.el6.x86_64
mysql-libs-5.1.71-1.el6.x86_64
mysql-5.1.71-1.el6.x86_64
qt-mysql-4.6.2-26.el6_4.x86_64
[root@localhost ~]# rpm -e --nodeps mysql-server-5.1.71-1.el6.x86_64
[root@localhost ~]# rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
[root@localhost ~]# rpm -e --nodeps mysql-5.1.71-1.el6.x86_64
[root@localhost ~]# rpm -e --nodeps qt-mysql-4.6.2-26.el6_4.x86_64
[root@localhost mysql]# ls
mysql-community-client-5.7.13-1.el6.x86_64.rpm  mysql-community-common-5.7.13-1.el6.x86_64.rpm  mysql-community-libs-5.7.13-1.el6.x86_64.rpm  mysql-community-server-5.7.13-1.el6.x86_64.rpm
[root@localhost mysql]# rpm -ivh mysql-community-common-5.7.13-1.el6.x86_64.rpm
warning: mysql-community-common-5.7.13-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-common ########################################### [100%]
[root@localhost mysql]# rpm -ivh mysql-community-libs-5.7.13-1.el6.x86_64.rpm
warning: mysql-community-libs-5.7.13-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-libs   ########################################### [100%]
[root@localhost mysql]# rpm -ivh mysql-community-client-5.7.13-1.el6.x86_64.rpm
warning: mysql-community-client-5.7.13-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-client ########################################### [100%]
[root@localhost mysql]# rpm -ivh mysql-community-server-5.7.13-1.el6.x86_64.rpm
warning: mysql-community-server-5.7.13-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-server ########################################### [100%]

启动mysql服务,获取root初始化密码,测试成功后停止mysql：
[root@localhost lib]# service mysqld start
[root@localhost lib]# service mysqld status
[root@localhost lib]# grep ‘temporary password’ /var/log/mysqld.log
[root@localhost lib]# service mysqld stop

mysql服务就这样安装好了，下面接着进行服务的配置：

  1. 数据库目录的配置，默认是指向的/var/lib/mysql,这个时候你要注意当前路径所在的磁盘空间是否足够：
    1
    2
    3
    4
    5
    [root@localhost mysql]# df -hl
    Filesystem                      Size  Used Avail Use% Mounted on
    /dev/mapper/vg_livedvd-lv_root   36G  5.7G   30G  17% /
    tmpfs                           939M     0  939M   0% /dev/shm
    /dev/sda1                       485M   40M  421M   9% /boot
    Master的配置文件:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    [root@localhost mysql]# vim /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    #replication
    server-id=1
    log-bin=mysql-bin
    sync_binlog=1
    replicate-wild-ignore-table=mysql.%
    replicate-wild-ignore-table=test.%
    replicate-wild-ignore-table=information_schema.%
    replicate-wild-ignore-table=performance_schema.%
    replicate-wild-ignore-table=sys.%
    relay-log=mysql-relay-bin
    skip-name-resolve
    Slave的配置内容：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    [root@localhost mysql]# vim /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

    #replication
    server-id=2
    log-bin=mysql-bin
    sync_binlog=1
    replicate-wild-ignore-table=mysql.%
    replicate-wild-ignore-table=test.%
    replicate-wild-ignore-table=information_schema.%
    replicate-wild-ignore-table=performance_schema.%
    replicate-wild-ignore-table=sys.%
    relay-log=mysql-relay-bin
    skip-name-resolve
    编辑文件/etc/sysconfig/iptables, 打开端口3306:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    [root@localhost data]# vim /etc/sysconfig/iptables

    -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

    [root@localhost data]# /etc/init.d/iptables restart
    iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
    iptables: Flushing firewall rules:                         [  OK  ]
    iptables: Unloading modules:                               [  OK  ]
    iptables: Applying firewall rules:                         [  OK  ]
    启动mysql服务，修改mysql的默认密码：
    1
    2
    3
    4
    5
    6
    7
    [root@localhost opt]# grep 'password' /var/log/mysqld.log 
    [root@localhost opt]# service mysqld start
    Initializing MySQL database:                               [  OK  ]
    Installing validate password plugin:                       [  OK  ]
    Starting mysqld:                                           [  OK  ]
    [root@localhost opt]# grep 'temporary password' /var/log/mysqld.log
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Admin@2016';

Master配置

1.提供一个slave可以复制的账号,ip地址根据实际情况修改。

1
2
3
4
mysql> CREATE USER 'repl'@'192.168.1.93' IDENTIFIED BY 'Admin@2016';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.93';
mysql> grant all on *.* to root@'192.168.1.17' identified by 'Admin@2016';
mysql> flush privileges;

2.去Slave数据库查询坐标信息：

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      1304 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.命令行里面设置slave配置信息，注意日志文件的起点(Master和Slave都执行了1和2步骤才执行该步骤):

1
2
3
4
5
6
7
mysql> CHANGE MASTER TO \
MASTER_HOST='192.168.1.93', \
MASTER_PORT=3306, \
MASTER_USER='repl', \
MASTER_PASSWORD='Admin@2016', \
MASTER_LOG_FILE='mysql-bin.000003', \
MASTER_LOG_POS=1304;

Slave配置

1.提供一个Master可以复制的账号,ip地址根据实际情况修改。

1
2
3
4
mysql> CREATE USER 'repl'@'192.168.1.92' IDENTIFIED BY 'Admin@2016';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.92';
mysql> grant all on *.* to root@'192.168.1.17' identified by 'Admin@2016';
mysql> flush privileges;

2.去Master数据库查询坐标信息：

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1304 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.命令行里面设置Master配置信息，注意日志文件的起点(Master和Slave都执行了1和2步骤才执行该步骤):

1
2
3
4
5
6
7
mysql> CHANGE MASTER TO \
MASTER_HOST='192.168.1.92', \
MASTER_PORT=3306, \
MASTER_USER='repl', \
MASTER_PASSWORD='Admin@2016', \
MASTER_LOG_FILE='mysql-bin.000003', \
MASTER_LOG_POS=1304;

测试同步效果

两台服务启动slave，查看状态Slave_IO_Running: Yes和Slave_SQL_Running: Yes,并且没有error错误：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.93
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1304
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             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: 1304
              Relay_Log_Space: 527
              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: 2
                  Master_UUID: a811e6e5-918d-11e7-9670-000c29358af8
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql>show processList\G;

主库创建数据库,创建表，插入数据，去从库看一下是否同步:

1
2
3
4
5
6
7
8
9
10
create database test1;
use test1;
CREATE TABLE `catalog` (
  `id` varchar(32) NOT NULL,
  `parent_id` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `catalog` VALUES ('01', 'A', '农业');
INSERT INTO `catalog` VALUES ('011', '01', '谷物种植');

从库创建数据库,创建表，插入数据，去主库看一下是否同步:

1
2
3
4
5
6
7
8
9
10
create database test2;
use test2;
CREATE TABLE `catalog` (
  `id` varchar(32) NOT NULL,
  `parent_id` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `catalog` VALUES ('01', 'A', '农业2');
INSERT INTO `catalog` VALUES ('011', '01', '谷物种植2');

如果两边看到的内容一致，表示主主复制关系没问题。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.01 sec)

mysql> select * from test2.catalog;
+-----+-----------+---------------+
| id  | parent_id | name          |
+-----+-----------+---------------+
| 01  | A         | 农业2         |
| 011 | 01        | 谷物种植2     |
+-----+-----------+---------------+
2 rows in set (0.00 sec)

系统调优

1
2
3
4
5
6
7
8
9
10
11
12
[root@localhost ~]# modprobe bridge
[root@localhost ~]# echo "modprobe bridge" >>/etc/rc.local

[root@localhost ~]# vim /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc unlimited
* hard nproc unlimited

[root@localhost ~]# vim /etc/security/limits.d/90-nproc.conf 
*          soft    nproc     unlimited
root       soft    nproc     unlimited

LVS安装

检查linux内核是否集成lvs模块：

1
[root@localhost ~]# modprobe -l | grep ipvs

第一种通过lvs源码安装：

1
2
3
[root@localhost ~]# yum install ipvsadm -y
[root@localhost ~]# ipvsadm -v
ipvsadm v1.26 2008/5/15 (compiled with popt and IPVS v1.2.1)

第二种通过源码编译安装：

1
2
3
4
[root@localhost]# wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.26.tar.gz
[root@localhost ipvsadm-1.26]# tar zxf ipvsadm-1.26.tar.gz
[root@localhost ipvsadm-1.26]# make
[root@localhost ipvsadm-1.26]# make install

开启路由转发功能：

1
2
3
4
5
6
7
8
9
10
[root@localhost ~]# echo "modprobe bridge" >>/etc/rc.local
[root@localhost ~]# echo "echo 1 > /proc/sys/net/ipv4/ip_forward" >> /etc/rc.local
[root@localhost ~]# echo 1 > /proc/sys/net/ipv4/ip_forward
[root@localhost ~]# modprobe ip_vs
[root@localhost ~]# modprobe ip_vs_wrr
[root@localhost ~]# lsmod | grep ip_vs
ip_vs_wrr               2179  1 
ip_vs                 126705  3 ip_vs_wrr
libcrc32c               1246  1 ip_vs
ipv6                  336368  47 ip_vs,bridge,ip6t_REJECT,nf_conntrack_ipv6,nf_defrag_ipv6

Keepalived安装

Master和Slave都执行以下命令：

1
2
3
4
[root@localhost ~]# yum install keepalived -y
[root@localhost ~]# chkconfig keepalived on
[root@localhost ~]# keepalived -v
Keepalived v1.2.13 (03/19,2015)

第二种安装方式：

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@localhost]# yum install -y gcc gcc-c++ makepcre pcre-devel kernel-devel openssl-devel libnl-devel popt-devel libnfnetlink-devel openssl-devel popt-static
[root@localhost opt]# wget http://www.keepalived.org/software/keepalived-1.2.24.tar.gz
[root@localhost opt]# tar zxf keepalived-1.2.24.tar.gz
[root@localhost opt]# cd keepalived-1.2.24
[root@localhost]# ./configure --prefix=/usr/local/keepalived
[root@localhost]# make && make install
[root@localhost ~]# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/keepalived
[root@localhost ~]# ln -s /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
[root@localhost ~]# ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/keepalived
[root@localhost ~]# ln -s /usr/local/keepalived/etc/keepalived /etc/keepalived
[root@localhost ~]# chmod +x /usr/local/keepalived/etc/rc.d/init.d/keepalived
[root@localhost ~]# chkconfig --add keepalived
[root@localhost ~]# chkconfig keepalived on

创建mysql_down监控脚本：

1
2
3
4
5
[root@localhost ~]# mkdir -p /etc/keepalived/scripts
[root@localhost ~]# vim /etc/keepalived/scripts/mysql_down.sh
#!/bin/bash
service keepalived stop
[root@master ~]# chmod +x /etc/keepalived/scripts/mysql_down.sh

Master节点配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[root@localhost ~]# vim /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
     sysadmin@firewall.loc
   }
   notification_email_from mysql_master@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id MYSQL-HA
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 59
    priority 150
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.94/24 dev eth0 scope global label eth0:0
    }
}

virtual_server 192.168.1.94 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 192.168.1.92 3306 {
        notify_down /etc/keepalived/scripts/mysql_down.sh
        TCP_CHECK {
        }
    }
}

Slave 节点配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
[root@localhost ~]# vim /etc/keepalived/keepalived.conf

global_defs {
   notification_email {
     sysadmin@firewall.loc
   }
   notification_email_from mysql_slave@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id MYSQL-HA
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 59
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.94/24 dev eth0 scope global label eth0:0
    }
}

virtual_server 192.168.1.94 3306 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 192.168.1.93 3306 {
        notify_down /etc/keepalived/scripts/mysql_down.sh
        TCP_CHECK {
        }
    }
}

防火墙设置

1
2
[root@localhost ~]# vim /etc/sysconfig/iptables
-A INPUT -p vrrp -j ACCEPT

测试效果

手动启动服务器试试正常不：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@master~]# service keepalived start
Starting keepalived:                                       [  OK  ]
[root@master ~]# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
    link/ether 00:0c:29:62:98:c0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.92/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.94/24 scope global secondary eth0:0
    inet6 fe80::20c:29ff:fe62:98c0/64 scope link 
       valid_lft forever preferred_lft forever
---------------------------------------------------------------------------------------------
[root@slave ~]# service keepalived start
Starting keepalived:                                       [  OK  ]
[root@localhost ~]# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
    link/ether 00:0c:29:35:8a:f8 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.93/24 brd 192.168.1.255 scope global eth0
    inet6 fe80::20c:29ff:fe35:8af8/64 scope link 
       valid_lft forever preferred_lft forever

先修改一下主机名信息,重启服务器：

1
2
[root@localhost ~]# vim /etc/sysconfig/network
[root@localhost ~]# vim /etc/hosts

测试故障转移，正常情况下，客户端连接的是Master节点,在Client机子上进行下面测试：

1
2
3
4
5
6
7
mysql> show variables like 'hostname%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| hostname      | localhost.master |
+---------------+------------------+
1 row in set

此时停掉Master后，在执行下面的命令：

1
2
3
4
5
6
7
mysql> show variables like 'hostname%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| hostname      | localhost.slave |
+---------------+-----------------+
1 row in set

可以测试在Slave添加一条数据，然后在恢复Master，查询该数据是否存在.