Mysql8.0高可用部署

Mysql8.0高可用部署

前言

目前是三台服务器，每台服务器部署mysql、mgr、proxysql、keepalived，mysql8.0是通过mgr的方式来实现mysql服务的高可用，实现故障自动检测及自动切换，发生故障时能自动切换到新的主节点，但是当程序调用的mysql对应的服务宕机后，无法自动切换到正常服务上面，需要进行人工干预，为了解决这一问题，加入了proxysql,主要有两个作用，第一个作用实现简单的读写分离，缓解主数据库的压力，第二个作用当主mysql服务宕掉后，会自动识别新的主mysql，提供服务，但是这样的话，当Proxysql宕掉的话，也无法提供服务，所以加入keepalived对proxysql做高可用，从而实现MySQL的高可用部署。

部署环境及版本

系统版本：CentOS Linux release 7.9.2009
Mysql版本：8.0.26
ProxySQL版本：2.2.0-1
Keepalived版本：v1.3.5

准备

1、关闭自带防火墙，用iptables,添加hosts文件解析

# 关闭SELinux
setenforce 0 
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

# 关闭Firewalld并禁止自启动
systemctl stop firewalld
systemctl disable firewalld

# 安装iptables
yum install -y iptables-services

# 启动iptables服务，并设置开机自启动（修改规则在/etc/sysconfig/iptables）
systemctl start iptables
systemctl enable iptables.service

# 通过/etc/hosts做域名解析
vim /etc/hosts

192.168.102.212 mgr01
192.168.102.213 mgr02
192.168.102.68 mgr03

2、时间同步

yum -y install ntp
systemctl enable ntpd
systemctl start ntpd
timedatectl set-timezone Asia/Shanghai
ntpdate -u time.nist.gov
ntpdate -u time.nist.gov
date

一、Mysql的部署

1、据库安装目录

mkdir -pv /home/work/mysql/{
   
   data,logs,binlog}

2、据库错误日志文件

touch /home/work/mysql/logs/mysqlerr.log

3、mysql压缩包到/home/work目录下

tar xvf mysql-8.0.26-el7-x86_64.tar.gz -C /home/work

4、连接进入/home/work目录下，做软连接

ln -sv mysql-8.0.26-el7-x86_64 mysql

5、创建work用户，并给work目录赋予work用户权限

useradd work
cd /home
chown -R work.work work

6、配置环境变量

echo "export PATH=/home/work/mysql/bin:$PATH" >> /etc/profile
source /etc/profile

7、查看Mysql版本

mysql -V

8、初始化数据库

mysqld --initialize-insecure --user=work --basedir=/home/work/mysql --datadir=/home/work/mysql/data

9、修改配置文件

slave节点配置文件只需要把loose-group_replication_local_address和server_id改成对应的即可
可以使用uuidgen获取一个随机uuid作为复制组的名称。

cat > /etc/my.cnf << EOF
[mysqld]
user=work
datadir=/home/work/mysql/data
basedir=/home/work/mysql
port=3306
socket=/home/work/mysql/mysql.sock
pid-file=/home/work/mysql/mysqld.pid
log-error=/home/work/mysql/logs/mysqlerr.log
innodb_file_per_table=1
skip_name_resolve=1
slow_query_log=1
slow_query_log_file=/home/work/mysql/logs/mysql-slow.log
long_query_time=2
symbolic-links=0
explicit_defaults_for_timestamp=1
default_authentication_plugin=mysql_native_password	#sqlproxy不支持caching_sha2_password，复制用户可以考虑使用，但是也仅限于此，客户端等配置比较麻烦

collation-server=utf8mb4_general_ci
character-set-server=utf8mb4
net_buffer_length=8k
myisam_sort_buffer_size=5M
max_connections=8192
wait_timeout=3600
interactive_timeout=43200
key_buffer_size=32M
max_connect_errors=500
sort_buffer_size=2M
join_buffer_size=2M
max_allowed_packet=48M
thread_cache_size=64
innodb_buffer_pool_size=11000M
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=128
innodb_log_buffer_size=128M
innodb_log_file_size=100M
innodb_log_files_in_group=3
read_buffer_size=1M
read_rnd_buffer_size=2M
innodb_flush_method=O_DIRECT



#mysql group replication
log_bin=/home/work/mysql/binlog/mysqlbin
log_bin_index=/home/work/mysql/binlog/mysql-bin.index
binlog_format=row
sync_binlog=1
server_id=212
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="03f43914-7f38-4a00-919f-f748794c04ac"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.102.212:33061"
loose-group_replication_group_seeds="192.168.102.212:33061,192.168.102.213:33061,192.168.102.68:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks= off
[mysql]
socket=/home/work/mysql/mysql.sock
#
#include all files from the config directory
#
!includedir /etc/my.cnf.d

EOF

10、将Mysql加入系统服务

cp /home/work/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld off

11、启动mysql服务

systemctl start mysqld
systemctl status mysqld.service

二、Mysql的MGR部署

1、所有节点更改主机名和hosts解析

hostnamectl set-hostname mgr01
cat >> /etc/hosts << EOF
192.168.102.212 mgr01
192.168.102.213 mgr02
192.168.102.68 mgr03
EOF

2、配置文件见上文

3、在所有主机创建复制用户并安装插件

[root@mgr01 home]# mysql
mysql> set sql_log_bin=0;
mysql> create user repluser@'%' identified by '123456'
mysql> grant replication slave on *.* to repluser@'%';
mysql> flush privileges;
mysql> set sql_log_bin=1;
mysql> install plugin group_replication soname 'group_replication.so';
mysql> select * from information_schema.plugins where plugin_name='group_replication'\G
*************************** 1. row ***************************
           PLUGIN_NAME: group_replication
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: GROUP REPLICATION
   PLUGIN_TYPE_VERSION: 1.4
        PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.10
         PLUGIN_AUTHOR: Oracle Corporation
    PLUGIN_DESCRIPTION: Group Replication (1.1.0)
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
# node2、node3步骤同node1 

4、启用第一个节点Primary（引导启动）

[root@mgr01 home]# mysql
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01       |        3306 | ONLINE       | PRIMARY     | 8.0.26         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

5、启用剩余的所有节点Secondary

[root@mgr02 home]# mysql
mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;

[root@mgr03 home]# mysql
mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;

6、在所有节点都可以查看信息：

mysql>