MySQL 5.7.42双主半同步复制部署【模板】
MySQL 双主复制架构
|
主机名 |
IP地址 |
端口 |
网卡名称 |
功能用途 |
|
Server102 |
10.8.98.102 |
33060 |
ens160 |
Mysql主库实例，读写 |
|
Server103 |
10.8.98.103 |
33060 |
ens160 |
Mysql主库实例，读写 |
|
|
10.8.98.104 |
33060 |
ens160 |
应用侧访问的虚拟ip |
MySQL 安装介质
mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz安装MySQL Server
先检查是否已经安装低版本mysql 或者mariadb。若有，则先卸载，否则后续安装mysql 可
能引起冲突报错！如：
# service mysqld start
Starting MySQL.yyyy-mm-ddT07:47:07.712147Z mysqld_safe error: log-error set to
'/var/log/MySQL/MySQL.log', however file don't exists. Create writable for user 'mysql'.
ERROR! The server quit without updating PID file (/var/lib/mysql/imistest.pid).卸载旧版本mysql 相关软件
mariadb-libs-5.5.68-1.el7.x86_64
# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
# rpm -qa|grep mariadb
#
删除旧用户【若存在mysql 用户】-可选
# cat /etc/passwd
......
mysql:x:998:1001::/home/mysql:/bin/bash
# userdel mysql操作系统层依赖配置
# yum search libaio ### search for info
# yum install libaio ### install library
或者
# rpm -qa|grep libaio ### query for info
创建mysql 组与用户
# groupadd -g 27 -o -r mysql
-g 27 and -o 选项分配非唯一的group ID (GID).
-r 选项，将该组作为系统组
# useradd -M -N -g mysql -o -r -s /bin/false -c "MySQL Server" -u 27 mysql
-M 选项，防止创建用户home 目录
-N 选项，表明需要搭配-g 选项，将该用户添加到指定组
-o 和-u 27 选项，分配非唯一的user ID (UID)
-r 和-s /bin/false 选项用于创建一个无登录权限的服务器用户。mysql 用户仅仅用于所有者
身份鉴别目的，并非登录目的。
-c 选项，用户账号描述信息
# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)创建数据存放目录
# mkdir /data/mysqldata/{mydata,sock,mysql-files,tmpdir,innodb_ts,binlog,relaylog,slowlog,innodb_log,undo} -p
# chown mysql:mysql -R /data/mysqldata
# chmod 750 -R /data/mysqldata
# ll /data/mysqldata/
# tree /data/mysqldata/ --tree 工具软件检查目录信息，默认未安装，可单独安装
-bash: tree: command not found
# yum install tree -y解压二进制安装文件并设置目录权限
上传安装文件mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz到指定目录 /root
在/usr/local/目录下，解压安装文件
# cd /usr/local/
# tar -xvf /root/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# mv /usr/local/mysql-5.7.42-linux-glibc2.12-x86_64 /usr/local/mysql
# ll /usr/local/mysql
# chown mysql:mysql -R /usr/local/mysql ###目录授权
# ll /usr/local/mysql配置mysql 环境变量
把/usr/local/mysql/bin/添加到系统环境变量中，以便使用mysql 相关命令时，不需要输入绝
对路径。
# ll /usr/local/mysql
# export PATH=$PATH:/usr/local/mysql/bin/
# echo 'export PATH=$PATH:/usr/local/mysql/bin/' >>/etc/profile
# tail -l /etc/profile
......
#export PATH=$PATH:/usr/local/mysql/bin/查看mysql 读取my.cnf 的顺序
# mysql --help|grep '/etc/my.cnf'
/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnfmy.cnf 参数配置
10.8.98.102 MySQL 节点
# vi /etc/my.cnf
[mysqld]
#<----主从复制必须设置此参数，保证复制拓扑内，各成员唯一
server_id = 102
#<----根据业务要求设置指定端口
port = 33060
max_connections = 9000
default-time-zone = '+08:00'
lower_case_table_names = 1
#<----根据服务器物理内存的75%-80%设置
innodb_buffer_pool_size = 12G
basedir = /usr/local/mysql
socket = /data/mysqldata/sock/mysql.sock
pid-file = /data/mysqldata/sock/mysql.pid
datadir = /data/mysqldata/mydata
tmpdir = /data/mysqldata/tmpdir
log-error = /data/mysqldata/error.log
secure_file_priv=/data/mysqldata/mysql-files
local_infile=OFF
#<----根据业务要求设置指定字符集
character_set_server = utf8mb4
#<----根据业务要求设置指定字符集排序规则
collation_server = utf8mb4_general_ci
default_storage_engine = INNODB
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
innodb_file_per_table = ON
innodb_data_home_dir = /data/mysqldata/innodb_ts/
innodb_data_file_path = ibdatal:2048M:autoextend
innodb_flush_method = O_DIRECT
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
log_bin = /data/mysqldata/binlog/master_binlog
log_bin_index = /data/mysqldata/binlog/master_binlog.index
relay_log = /data/mysqldata/relaylog/relay_binlog
relay_log_index = /data/mysqldata/relaylog/relay_binlog.index
max_binlog_size = 1G
expire_logs_days = 8
master_verify_checksum = ON
slave_sql_verify_checksum = ON
sync_binlog = 1
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = CRC32
log_slave_updates = ON
relay_log_recovery = ON
symbolic-links = OFF
slow_query_log = ON
slow_query_log_file = /data/mysqldata/slowlog/slow_query.log
##log_output = TABLE
long_query_time = 10
key_buffer_size = 512M
max_allowed_packet = 512M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 8M
## Redo options
innodb_log_group_home_dir = /data/mysqldata/innodb_log/
innodb_log_buffer_size = 128M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_fast_shutdown = 1
## Transaction options
innodb_thread_concurrency = 128
innodb_lock_wait_timeout = 120
innodb_rollback_on_timeout = 1
transaction_isolation = READ-COMMITTED
## I0 options
performance_schema = on
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_use_native_aio = ON
##Undo options
innodb_undo_directory = /data/mysqldata/undo/
innodb_undo_tablespaces = 4
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 4G
innodb_purge_threads = 4
innodb_purge_batch_size = 512
innodb_max_purge_lag = 65536
binlog_rows_query_log_events = ON
#<----双主复制，设置其中一台奇数自增，另一台偶数自增,本台offset为1
auto_increment_offset = 1
auto_increment_increment = 2
###开启多线程复制，同时确保并行事务的数据一致性###
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = 1
###开启多线程复制，同时确保并行事务的数据一致性###end
###开启半同步复制，在MySQL 环境初始化阶段，先注释此段配置###
##semi sync replication config options
#plugin_dir = /usr/local/mysql/lib/plugin
#plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 5000
#rpl_semi_sync_slave_enabled = 1
###开启半同步复制，在MySQL 环境初始化阶段，先注释此段配置###end
[client]
default_character_set = utf8mb4
port = 33060
socket = /data/mysqldata/sock/mysql.sock
[mysql]
default_character_set = utf8mb4
[mysqld_safe]
user = mysql
open-files-limit = 102400
[mysqldump]
quick
max_allowed_packet = 1G
default_character_set = utf8mb410.8.98.103 MySQL 节点
# vi /etc/my.cnf
[mysqld]
#<----主从复制必须设置此参数，保证复制拓扑内，各成员唯一
server_id = 103
#<----根据业务要求设置指定端口
port = 33060
max_connections = 9000
default-time-zone = '+08:00'
lower_case_table_names = 1
#<----根据服务器物理内存的75%-80%设置
innodb_buffer_pool_size = 12G
basedir = /usr/local/mysql
socket = /data/mysqldata/sock/mysql.sock
pid-file = /data/mysqldata/sock/mysql.pid
datadir = /data/mysqldata/mydata
tmpdir = /data/mysqldata/tmpdir
log-error = /data/mysqldata/error.log
secure_file_priv=/data/mysqldata/mysql-files
local_infile=OFF
#<----根据业务要求设置指定字符集
character_set_server = utf8mb4
#<----根据业务要求设置指定字符集排序规则
collation_server = utf8mb4_general_ci
default_storage_engine = INNODB
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
innodb_file_per_table = ON
innodb_data_home_dir = /data/mysqldata/innodb_ts/
innodb_data_file_path = ibdatal:2048M:autoextend
innodb_flush_method = O_DIRECT
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
log_bin = /data/mysqldata/binlog/master_binlog
log_bin_index = /data/mysqldata/binlog/master_binlog.index
relay_log = /data/mysqldata/relaylog/relay_binlog
relay_log_index = /data/mysqldata/relaylog/relay_binlog.index
max_binlog_size = 1G
expire_logs_days = 8
master_verify_checksum = ON
slave_sql_verify_checksum = ON
sync_binlog = 1
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = CRC32
log_slave_updates = ON
relay_log_recovery = ON
symbolic-links = OFF
slow_query_log = ON
slow_query_log_file = /data/mysqldata/slowlog/slow_query.log
##log_output = TABLE
long_query_time = 10
key_buffer_size = 512M
max_allowed_packet = 512M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 8M
## Redo options
innodb_log_group_home_dir = /data/mysqldata/innodb_log/
innodb_log_buffer_size = 128M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_fast_shutdown = 1
## Transaction options
innodb_thread_concurrency = 128
innodb_lock_wait_timeout = 120
innodb_rollback_on_timeout = 1
transaction_isolation = READ-COMMITTED
## I0 options
performance_schema = on
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_use_native_aio = ON
##Undo options
innodb_undo_directory = /data/mysqldata/undo/
innodb_undo_tablespaces = 4
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 4G
innodb_purge_threads = 4
innodb_purge_batch_size = 512
innodb_max_purge_lag = 65536
binlog_rows_query_log_events = ON
#<----双主复制，设置其中一台偶数自增，另一台奇数自增,本台offset为2
auto_increment_offset = 2
auto_increment_increment = 2
###开启多线程复制，同时确保并行事务的数据一致性###
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = 1
###开启多线程复制，同时确保并行事务的数据一致性###end
###开启半同步复制，在MySQL 环境初始化阶段，先注释此段配置###
##semi sync replication config options
#plugin_dir = /usr/local/mysql/lib/plugin
#plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 5000
#rpl_semi_sync_slave_enabled = 1
###开启半同步复制，在MySQL 环境初始化阶段，先注释此段配置###end
[client]
default_character_set = utf8mb4
port = 33060
socket = /data/mysqldata/sock/mysql.sock
[mysql]
default_character_set = utf8mb4
[mysqld_safe]
user = mysql
open-files-limit = 102400
[mysqldump]
quick
max_allowed_packet = 1G
default_character_set = utf8mb4my.cnf 文件授权
所有数据库服务器节点都要执行
# chown root:root /etc/my.cnf
# chmod 644 /etc/my.cnf初始化MySQL 实例
使用配置文件初始化MySQL，如下：
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize--检查关键目录是否产生正确的文件
# ls -l /data/mysqldata查看关键目录在初始化之后是否有正确的数据文件和目录、权限
# ll /data/mysqldata/或者
# tree /data/mysqldata
10.8.98.102:
[root@server102 mysqldata]# cat /data/mysqldata/error.log
2023-08-04T01:52:39.757294Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2023-08-04T01:53:17.167930Z 0 [Warning] InnoDB: New log files created, LSN=49320
2023-08-04T01:53:17.970713Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-08-04T01:53:18.048790Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: aec9ebd7-3269-11ee-8345-0050569d1100.
2023-08-04T01:53:18.053090Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-08-04T01:53:18.265768Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-08-04T01:53:18.265817Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-08-04T01:53:18.266924Z 0 [Warning] CA certificate ca.pem is self signed.
2023-08-04T01:53:18.313514Z 1 [Note] A temporary password is generated for root@localhost: Ldh1.iV)Prla
[root@server102 mysqldata]#
10.8.98.103
[root@server103 local]# cat /data/mysqldata/error.log
2023-08-04T01:46:33.765505Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2023-08-04T01:47:13.520284Z 0 [Warning] InnoDB: New log files created, LSN=49320
2023-08-04T01:47:13.924403Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-08-04T01:47:15.573309Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d6bc8db0-3268-11ee-8b62-0050569d6ed0.
2023-08-04T01:47:15.574966Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-08-04T01:47:16.048740Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-08-04T01:47:16.048769Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-08-04T01:47:16.049861Z 0 [Warning] CA certificate ca.pem is self signed.
2023-08-04T01:47:16.070448Z 1 [Note] A temporary password is generated for root@localhost: #,gUNda!*7#O
[root@server103 local]#
再次检查数据目录和文件的属主是否正确，确保全部是mysql 组和mysql 用户，否则，后续
使用mysql 用户启动服务会报错。
# chown mysql:mysql -R /data/mysqldata/重新初始化MySQL 实例(可选)
清理安装目录【可选】慎重执行！！
# cd /data/mysqldata
# rm -rf /data/mysqldata/*
#mkdir -p
/data/mysqldata/{mydata,sock,mysql-files,tmpdir,innodb_ts,binlog,relaylog,slowlog,innodb_log,u
ndo}
# chown mysql:mysql -R /data/mysqldata
# chmod 750 -R /data/mysqldata
# ll /data/mysqldata/init.d 方式实现mysqld 服务配置【可选】
将mysql.server 复制到/etc/init.d/目录下，命名为mysqld 程序，授予执行权限，并使用这个
脚本启动、停止MySQL
# cp -ar /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# chmod +x /etc/init.d/mysqld
# ls -l /etc/init.d/mysqld
-rwxr-xr-x 1 mysql mysql 10576 Oct 24 15:33 /etc/init.d/mysqld
#
# service mysqld startsystemd 方式实现mysqld 服务配置【推荐】
# vi /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(7)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/data/mysqldata/sock/mysql.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize --pid-file=/data/mysqldata/sock/mysql.pid $MYSQLD_OPTS
# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 102400
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
# chmod 644 /usr/lib/systemd/system/mysqld.service服务进程配置重新载入-【可选】
# systemctl daemon-reload
# systemctl start mysqld
[root@server102 mysqldata]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
Active: active (running) since Fri 2023-08-04 10:02:17 CST; 8s ago
Docs: man:mysqld(7)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 3909 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize (code=exited, status=0/SUCCESS)
Main PID: 3913 (mysqld)
Tasks: 54
CGroup: /system.slice/mysqld.service
└─3913 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize
Aug 04 10:02:15 server102 systemd[1]: Starting MySQL Server...
Aug 04 10:02:17 server102 systemd[1]: Started MySQL Server.
[root@server102 mysqldata]#配置MySQL 服务开机自启
#systemctl enable mysqld
#systemctl stop mysqld
#systemctl start mysqld
#systemctl status mysqld
暂时先把防火墙关掉
#systemctl stop firewalld
或者
# service mysqld stop
# service mysqld start
# service mysqld status
# tail -f /data/mysqldata/error.log确认MySQL 服务开机自启配置是否生效
# systemctl list-unit-files |grep enable| grep mysql
mysqld.service enabled修改MySQL 数据库的root 用户口令(2台)
# mysql -u root -p -h 127.0.0.1 -P 33060
Enter password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootP@ssw0rd';
mysql> grant all privileges on *.* to 'root'@'%' IDENTIFIED BY 'rootP@ssw0rd' with grant option;
10.8.98.102 MySQL 节点
mysql -u root -p -h 127.0.0.1 -P 33060
SET SQL_LOG_BIN=OFF;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootP@ssw0rd';
grant all privileges on *.* to 'root'@'%' IDENTIFIED BY 'rootP@ssw0rd' with grant option;
flush privileges;
SET SQL_LOG_BIN=ON;
10.8.98.103 MySQL 节点
mysql -u root -p -h 127.0.0.1 -P 33060
SET SQL_LOG_BIN=OFF;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootP@ssw0rd';
grant all privileges on *.* to 'root'@'%' IDENTIFIED BY 'rootP@ssw0rd' with grant option;
flush privileges;
SET SQL_LOG_BIN=ON;
选择其中一台10.8.98.102创建复制用户
mysql> show master status\G
mysql> grant replication slave on *.* to 'rep'@'%' identified BY 'repP@ssw0rd';
mysql> flush privileges;
17
mysql> show global variables like 'server_uuid';
mysql> show master status;
mysql> select user,host from mysql.user;
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
安装密码插件并设置强密码策略
各节点的MySQL 实例，分别执行密码插件安装及配置
mysql -u root -p -h 127.0.0.1 -P 33060
show plugins;
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
UNINSTALL PLUGIN validate_password; --卸载【可选】
show variables like 'validate_password%';
set global validate_password_check_user_name=1;
set global validate_password_length=10;密码策略配置持久化
# vi /etc/my.cnf
在[mysqld]节点下添加
##password options
validate-password=FORCE_PLUS_PERMANENT
validate_password_policy=MEDIUM
validate_password_check_user_name=1
validate_password_length=10
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1取消注释半同步配置的段落
##semi sync replication config options
plugin_dir = /usr/local/mysql/lib/plugin
plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_slave_enabled = 1重启MySQL 实例服务
systemctl stop mysqld
systemctl start mysqld
或
systemctl restart mysqld登录MySQL 检查参数信息
show plugins;
show variables like 'validate_password%';
show variables like '%semi%';
2 台MySQL 分别配置slave 复制
防火墙放行33060 端口配置(我这里直接关闭了防火墙)
# firewall-cmd --zone=public --add-port=33060/tcp --permanent
# firewall-cmd --reload
# systemctl restart firewalld
# firewall-cmd --list-all
# firewall-cmd --list-ports10.8.98.103 端复制10.8.98.102 的slave 配置
这个就是在10.8.98.103上配置
配置前103上还没有rep用户
mysql> change master to
master_host='10.8.98.102',master_port=33060,master_user='rep',master_password='repP@ssw0rd',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G
此处，需等待几分钟，等数据同步过来之后，然后在另一台配置slave 复制。
查看10.8.98.103上是否有rep用户10.8.98.102 端复制10.8.98.103 的slave 配置
mysql> change master to
master_host='10.8.98.103',master_port=33060,master_user='rep',master_password='repP@ssw0rd',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G使用Keepalived 实现MySQL 高可用
内核参数配置
echo "vm.swappiness=10" >> /etc/sysctl.conf
echo "vm.min_free_kbytes=134218" >> /etc/sysctl.conf
echo "net.ipv4.ip_nonlocal_bind=1" >> /etc/sysctl.conf
# sysctl -p安装keepalived
# yum -y install keepalived
注：建议使用操作系统iso 文件挂载，配置yum 源；或者配置网络，使用url 配置yum 源。关闭selinux
[root@server102 ~]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
重启操作系统
配置keepalived
##公共部分
# vi /etc/keepalived/chk_mysqld.sh
# cat /etc/keepalived/chk_mysqld.sh
#!/bin/bash
#pkill keepalived
systemctl stop keepalived
systemctl start keepalived
# cd /etc/keepalived
# ls
chk_mysqld.sh keepalived.conf
# chmod u+x /etc/keepalived/chk_mysqld.sh
# bash -n /etc/keepalived/chk_mysqld.sh <---检查shell 脚本是否存在语法错误
##备注：通过Keepalived 自带的服务监控功能和自定义脚本实现MySQL 故障自动转移
##公共部分
# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
# cat /etc/keepalived/keepalived.conf
[root@server102 keepalived]# cat /etc/keepalived/keepalived.conf
!Configuration File for keepalived
global_defs {
#标识，双主相同
router_id MYSQL_HA104
script_user root
enable_script_security
}
vrrp_instance VI_104 {
#两台都设置BACKUP
state BACKUP
#网卡名称
interface ens160
#主备相同
virtual_router_id 104
#优先级，其中一台MySQL 服务器100，另一台90
priority 100
#组播信息发送间隔，两个节点设置必须一样
advert_int 1
#不主动抢占资源
nopreempt
authentication {
auth_type PASS
auth_pass 1111AwqQzX
}
virtual_ipaddress {
#指定VIP，两个节点设置必须一样
10.8.98.104/24
}
}
virtual_server 10.8.98.104 33060 {
#每2 秒检查一次real_server 状态
delay_loop 2
#会话保持时间
persistence_timeout 30
#使用TCP 协议
protocol TCP
#此处10.8.98.xx根据物理机实际ip来定
real_server 10.8.98.102 33060 {
#权重
weight 3
#此脚本实现vip 切换
notify_down /etc/keepalived/chk_mysqld.sh
TCP_CHECK {
#连接超时
connect_timeout 3
#重试次数
retry 3
#重试间隔时间
delay_before_retry 3
}
}
}
[root@server102 keepalived]#
[root@server103 keepalived]# cat /etc/keepalived/keepalived.conf
!Configuration File for keepalived
global_defs {
#标识，双主相同
router_id MYSQL_HA104
script_user root
enable_script_security
}
vrrp_instance VI_104 {
#两台都设置BACKUP
state BACKUP
#网卡名称
interface ens160
#主备相同
virtual_router_id 104
#优先级，其中一台MySQL 服务器100，另一台90
priority 90
#组播信息发送间隔，两个节点设置必须一样
advert_int 1
#不主动抢占资源
nopreempt
authentication {
auth_type PASS
auth_pass 1111AwqQzX
}
virtual_ipaddress {
#指定VIP，两个节点设置必须一样
10.8.98.104/24
}
}
virtual_server 10.8.98.104 33060 {
#每2 秒检查一次real_server 状态
delay_loop 2
#会话保持时间
persistence_timeout 30
#使用TCP 协议
protocol TCP
#此处10.8.98.xx根据物理机实际ip来定
real_server 10.8.98.103 33060 {
#权重
weight 3
#此脚本实现vip 切换
notify_down /etc/keepalived/chk_mysqld.sh
TCP_CHECK {
#连接超时
connect_timeout 3
#重试次数
retry 3
#重试间隔时间
delay_before_retry 3
}
}
}
[root@server103 keepalived]#Keepalived 服务启停命令
关闭防火墙，否则keepalived 的广播包无法抵达对端，出现虚拟IP 重复，导致环路问题！！
systemctl stop firewalld
systemctl status firewalld
systemctl disable firewalld
systemctl list-unit-files |grep enable| grep firewall
systemctl start keepalived
systemctl stop keepalived
systemctl status keepalived将keepalived 服务配置开机自启动
systemctl enable keepalived
systemctl list-unit-files |grep enable| grep keepalived
[root@server102 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2023-08-04 15:01:24 CST; 3min 29s ago
Process: 3045 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 3046 (keepalived)
Tasks: 3
CGroup: /system.slice/keepalived.service
├─3046 /usr/sbin/keepalived -D
├─3047 /usr/sbin/keepalived -D
└─3048 /usr/sbin/keepalived -D
Aug 04 15:02:02 server102 Keepalived_vrrp[3048]: Sending gratuitous ARP on ens160 for 10.8.98.104
[root@server102 ~]# ^C通过ip a 命令检查虚拟ip 在哪一台服务器
以上可见，2 台MySQL 服务都正常开启，keepalived 服务启动之后，VIP 只在其中一边出现，
因为keepalived 配置采用不主动抢占资源模式，但是如果其中一台服务器mysql 服务关闭或
者异常终止，VIP 将自动漂移到一台服务器。
查看操作系统日志记录，也可以看到keepalived 相关记录
# tail -f /var/log/messages
创建管理员用户
【选择其中一台MySQL 执行即可，可验证该用户信息，是否实时同步到另一台MySQL】
mysql>
CREATE USER myadmin IDENTIFIED BY 'adminP@ssw0rd';
GRANT ALL ON *.* TO myadmin WITH GRANT OPTION;
flush privileges;
select user,host from mysql.user;验证用户信息是否已经同步到另一台MySQL
创建应用系统用户
【选择其中一台MySQL 执行即可，可验证该用户信息，是否实时同步到另一台MySQL】
mysql>
CREATE USER appuser IDENTIFIED BY 'appP@ssw0rd';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, REFERENCES ON *.* TO appuser ;
flush privileges;
select user,host from mysql.user;
show grants for 'appuser '@'%'\G创建备份用户
【选择其中一台MySQL 执行即可，可验证该用户信息，是否实时同步到另一台MySQL】
mysql>
create user mybackup@'%' identified BY 'backP@ssw0rd';
grant SELECT,RELOAD,LOCK TABLES,SHOW VIEW,EVENT,TRIGGER,REPLICATION CLIENT,CREATE TABLESPACE,PROCESS,SUPER,EXECUTE on *.* to mybackup@'%';
flush privileges;
select user,host from mysql.user;
show grants for mybackup@'%'\G验证MySQL 的vip 能否自动切换
方式1：停止MySQL 服务【或重启MySQL】
vip 当前在10.8.98.103 主机，重启MySQL 服务，观察vip 是否自动漂移到另一台MySQL 主机。
[root@server103 ~]# systemctl restart mysqld
[root@server102 ~]# tail -f /var/log/messages上图显示vip 当前已经漂移到10.8.98.102 主机。
方式2：停止keepalived 服务【或重启keepalived】
[root@server102 ~]# systemctl restart keepalived
[root@server103 ~]# tail -f /var/log/messages
上图显示vip 当前已经漂移到10.8.98.103 主机。__
系统防火墙的配置
由于安全的原因，安全方面要求开启系统防火墙，所以添加最简单的防火墙设置
主机A: 10.8.98.102
主机B： 10.8.98.103
对外提供给应用的ip 10.8.98.104
mysql 对外提供服务的端口33060
# firewall-cmd --add-rich-rule='rule protocol value="vrrp" accept' --permanent
# firewall-cmd --zone=public --add-port=33060/tcp --permanent
# firewall-cmd --reload
浙公网安备 33010602011771号