博学笃行·盛德日新

搭建keepalived+MySQL双主的数据库高可用方案


技术

搭建keepalived+MySQL双主的数据库高可用方案

一、环境

1.1 机器环境

主机1:192.168.25.56
主机2:192.168.25.57
VIP:192.168.25.53

1.2 软件环境

CentOS Linux release 7.6.1810
mysql-community-server-5.6.43
keepalived-1.3.5

二、搭建mysql双主

2.1 mysql安装

mysql的安装本身没什么好讲的,默认情况下,CentOS没有自带mysql了,而是安装的mysql的分支版本mariadb,我们先把mariadb进行卸载,然后安装mysql。

# 卸载mariadb
rpm -e --nodeps MariaDB-shared MariaDB-common

# 安装mysql的官方repo
yum install http://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-release-el7-5.noarch.rpm

# 安装mysql
yum install mysql-community-server.x86_64 mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-community-embedded.x86_64 mysql-community-embedded-devel.x86_64 mysql-community-libs.x86_64

# 进行配置(后面专门讲配置)

# 创建目录
mkdir /data/mydata
chown mysql.mysql /data/mydata

# 初始化
mysql_install_db --defaults-file=/etc/my.cnf --user=mysql

# 启动及开机启动
systemctl start mysqld
systemctl enable mysqld

2.2 mysql的主主配置

配置文件/etc/my.cnf

[client]
port        = 3306
socket        = /data/mydata/mysqld.sock

[mysqld]
port        = 3306
socket        = /data/mydata/mysqld.sock
datadir                        = /data/mydata
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 64M
table_open_cache = 2048
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 16M
thread_concurrency = 8
max_connections = 2000
tmp_table_size = 200M
explicit_defaults_for_timestamp = 1
relay_log                      = /data/mydata/relay-log

server-id    = 56  # 标识mysql双主中的server-id,两个机器需要为不一样的server-id,这里我把192.168.25.56这台标识为56
replicate-wild-do-table    = mydbname1.%    # 需要同步的库mydbname1
replicate-wild-do-table    = mydbname2.%    # 需要同步的库mydbname2
auto_increment_offset          = 1          # 自增变量的offset,这里设置为1,在另外一台机器设置为2
auto_increment_increment       = 2          # 在offset的基础上,每次自增2.
log_slave_updates              = 1          # 主主同步关键设置

binlog_format                  = mixed
binlog_checksum                = none
low_priority_updates           = 1
long_query_time                = 3
expire_logs_days               = 15
innodb_data_file_path          = ibdata1:1G:autoextend
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 1024M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G
log-bin = mysql-bin
max_binlog_size                = 999M

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

2.3 其他相关设置

2.3.1 selinux设置

> getenforce
Disabled

2.3.2 防火墙设置

firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.25.0/24" port protocol="tcp" port="3306" accept'

firewall-cmd --reload

2.4 进行主主配置

设置主从同步用户(mysql主主,其实就是两个机器相互为主从服务器)

mysql> grant super,replication slave on *.* to 'myrepl'@'192.168.25.%' identified by 'mypassword';
mysql> flush privileges;

查看mysql主相关信息

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在从上进行配置

mysql> stop slave;	--如果没有启过slave,这一步也是非必要的
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_user='myrepl', master_password='mypassword', master_host='192.168.25.56', master_port=3306, master_log_file='mysql-bin.000015', master_log_pos=120;

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.25.57
                  Master_User: myrepl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-hqps-log.000015
                Relay_Log_Pos: 120
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
............

记住192.168.25.56和192.168.25.57都需要进行配置,因为他们互为主从。

三、搭建keepalived进行VIP漂移高可用

3.1 keepalived 安装

yum install -y pcre-devel openssl-devel popt-devel
yum install keepalived

3.2 keepalived 配置

keepalived的配置文件:/etc/keepalived/keepalived.conf

! Configuration File forkeepalived

global_defs {
    notification_email {
        yongfu@test.com
    }

    notification_email_from master@test.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 3
    router_id MYSQL_53  # 两个keepalived配置需要相同
    script_user root    # 以root用户执行脚本,否则启动的时候回报错
}

vrrp_instance VI_53 {
    state BACKUP   # 都设置为BACKUP
    interface eth0   # 设置需要绑定的网卡名
    virtual_router_id 153   # 设置虚拟机路由id,需要两边一样
    priority 100     # 优先级,作为主配置文件的需要大于从keepalived,从keepalived可以设置为90
    advert_int 1
    nopreempt       # 只在主进行配置,从上不需要配置

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
       192.168.25.53
    }
}

virtual_server 192.168.25.53 3306 {
    delay_loop 2
    persistence_timeout 50
    protocol TCP

    real_server 192.168.25.56 3306 {
        weight 3
        notify_down /usr/local/keepalived/mysql.sh

        TCP_CHECK {
            connect_timeout 3
            #nb_get_retry 3
            delay_before_retry 3
        }
    }
}

3.3 监控脚本

> cat /usr/local/keepalived/mysql.sh
#!/bin/bash

pkill keepalived

3.4 其他相关说明

3.4.1 日志

日志文件在/var/log/message进行查看

3.4.2 系统配置

echo 'net.ipv4.ip_forward = 1' >> /etc/sysctl.conf 
sysctl -p 

3.4.3 防火墙配置

firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0  --in-interface eth0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --out-interface eth0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload

注意需要把网卡名(eth0)换成自己的网卡名字

四、测试

4.1 mysql主从的测试

在192.168.25.56上新建表 mytest

mysql> CREATE TABLE mytest (  id int, title varchar(255) );

在192.168.25.57上查看,是否有新建的表mytest

mysql> show tables;

在192.168.25.57上查看,插入数据

mysql> insert into mytest(`title`) values('a', 'b');

在192.168.25.56上查看

mysql> select * from mytest;
+----+-------+
| id | title |
+----+-------+
| 1  | a     |
| 3  | b     |
+----+-------+

4.2 keepalived测试

  • 可以关闭mysql,查看vip是否迁移
  • 可以关闭keepalived,查看vip是否迁移

评论