MySQL5.7.x 双主高可用

下载安装

wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
rpm -ivh mysql57-community-release-el7-10.noarch.rpm
yum install mysql-community-server-5.7.22 -y

配置

/etc/my.cnf

[mysqld]
skip_name_resolve
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
datadir=/mysqldata/data
socket=/mysqldata/mysql.sock
character-set-server=utf8
max_connections=1000
long_query_time=3
slow_query_log=ON
gdb
innodb_buffer_pool_chunk_size=1G
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4
server-id=98    
log-bin=/mysqldata/mysql-binlog/mysql-binlog
binlog_format=row
slave-skip-errors=all
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
relay_log=/mysqldata/mysql-relaylog/mysql-relaylog
relay_log_info_repository = TABLE
master_info_repository    = TABLE
relay_log_recovery =1
expire_logs_days=7
[client]
default-character-set=utf8
socket=/mysqldata/mysql.sock

模板配置

[mysqld]
skip_name_resolve
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
datadir=/mysqldata/data
socket=/mysqldata/mysql.sock
character-set-server=utf8
max_connections=1000
long_query_time=3
slow_query_log=ON
gdb
innodb_buffer_pool_chunk_size=1G
innodb_buffer_pool_size={{ ansible_memtotal_mb//2 }}M
innodb_buffer_pool_instances=4
server-id={{ server_id }}
log-bin=/mysqldata/mysql-binlog/mysql-binlog
binlog_format=row
slave-skip-errors=all
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
relay_log=/mysqldata/mysql-relaylog/mysql-relaylog
relay_log_info_repository = TABLE
master_info_repository    = TABLE
relay_log_recovery =1
expire_logs_days=7
[client]
default-character-set=utf8
socket=/mysqldata/mysql.sock

初始化数据库

mkdir -p /mysqldata/{data,mysql-binlog,mysql-relaylog,data}
chown -R mysql:mysql /mysqldata
mysqld --initialize-insecure --user=mysql  --datadir=/mysqldata/data

启动

systemctl start mysqld.service
systemctl enable mysqld.service

配置远程登陆用户

grant all on *.* to 'app'@'%' identified by '123456';

配置复制

192.168.41.25执行

grant replication slave,replication client on *.* to 'rep'@'192.168.41.%' identified by 'vcloud@123';

192.168.41.26执行

change master to
master_host='192.168.41.25',
master_port=3306,
master_user='rep',
master_password='vcloud@123',
master_auto_position=1;


start slave;
show slave status\G

192.168.41.25执行

change master to
master_host='192.168.41.26',
master_port=3306,
master_user='rep',
master_password='vcloud@123',
master_auto_position=1;


start slave;
show slave status\G

mysqladmin -uroot password root@123

配置VIP

yum install -y keepalived

/etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id vcloudmysql1
}

vrrp_script chk_all {
    script "/etc/keepalived/check_all.sh"
    interval 2
}
vrrp_instance vcloudmysql {
    state BACKUP
    interface eth0
    virtual_router_id 33
    priority 100
    nopreempt
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.41.24/24 dev eth0
    }
    track_script {
        chk_all
    }
}

! Configuration File for keepalived

global_defs {
   router_id vcloudmysql2
}

vrrp_script chk_all {
    script "/etc/keepalived/check_all.sh"
    interval 2
}
vrrp_instance vcloudmysql {
    state BACKUP
    interface eth0
    virtual_router_id 33
    priority 80
    nopreempt
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.41.24/24 dev eth0
    }
    track_script {
        chk_all
    }
}

keepalived.conf.j2

! Configuration File for keepalived

global_defs {
   router_id {{ ansible_hostname }}
}

vrrp_script chk_all {
    script "/etc/keepalived/check_all.sh"
    interval 2
}
vrrp_instance {{ instance_name }} {
    state BACKUP
    interface eth0
    virtual_router_id {{ virtual_router_id }}
    priority {{ priority_value }}
    nopreempt
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        {{ vip }}/24 dev eth0
    }
    track_script {
        chk_all
    }
}

/etc/keepalived/check_all.sh

#!/bin/bash
#check mysql status
killall -0 mysqld
if [ $? != 0 ]
then
  killall  keepalived
fi

启动

systemctl start keepalived