MySQL 8.0 双主架构业务部署（欧拉系统）

Kevin song

发布于 2023-08-31 14:08:18

1.6K0

MySQL 高可用介绍

MySQL集群具备高可用、可扩展、以管理、低成本的特点。生产环境中MySQL部署会使用Mysql 主从架构或Mysql双主互备架构。同时采用keepalived来实现mysql的自动故障切换。两台Mysql服务器互为主从，但同一时刻只有一个Mysql服务器可读写，另一个Mysql服务器只能进行读操作，保证数据的一致性。MySQl主主同步就是两台机器互为主的关系，在任何一台机器上写入都会同步至备端。

MySQL 主从复制流程

（1）master记录二进制日志：在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志。在事务写入二进制日志完成后，master通知存储引擎提交事务。

（2）slave将master的binarylog拷贝到自己的中继日志：首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事务，如果已经同步了master，它会睡眠并等待master产生新的事件。I/O线程将这些事务写入中继日志。

（3）SQL slave thread处理该过程的最后一步：SQL线程从中继日志读取事务，并重放其中的事务而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

主机清单

主机名	IP	VIP	同步用户
MySQL01	192.168.100.92	192.168.100.94	repl
MySQL02	192.168.100.93	192.168.100.94	repl

MySQL高可用业务架构

MySQL01和MySQL02互为主从，MySQL01和MySQL02部署高可用软件keepalived。在正常情况下，Zabbix Server主机仅从MySQL01进行数据的读写操作，MySQL02主负责从MySQL01同步数据。使用 Keepalived VIP 对外部提供访问。同时 Keepalived 负责监控MySQL01和MySQL02上Mysql数据库的运行状态。当MySQL01主机出现故障或者Mysql运行异常时，自动将VIP地址切换到MySQL02上实现数据读取写入。

系统版本

cat /etc/os-release

NAME="openEuler"
VERSION="22.03 (LTS-SP1)"
ID="openEuler"
VERSION_ID="22.03"
PRETTY_NAME="openEuler 22.03 (LTS-SP1)"
ANSI_COLOR="0;31"

MySQL 配置文件

MySQL01配置

[mysqld]#开启二进制日志log-bin=mysql-bin#id标识唯一server-id=92#不同步的数据库，可设置多个binlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=mysql#指定需要同步的数据库（和slave是相互匹配的），可以设置多个binlog-do-db=zabbixreplicate-do-db=zabbixlog-slave-updatesslave-skip-errors=allslave-net-timeout=60###设置存储模式不设置默认binlog_format=MIXED#####日志清理时间expire_logs_days=7#####日志大小max_binlog_size=100m#####缓存大小binlog_cache_size=4m#####最大缓存大小max_binlog_cache_size=512m

MySQL02配置

[mysqld]#开启二进制日志log-bin=mysql-bin#id标识唯一server-id=93#不同步的数据库，可设置多个binlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=mysql#指定需要同步的数据库（和slave是相互匹配的），可以设置多个binlog-do-db=zabbixreplicate-do-db=zabbixlog-slave-updatesslave-skip-errors=allslave-net-timeout=60###设置存储模式不设置默认binlog_format=MIXED#####日志清理时间expire_logs_days=7#####日志大小max_binlog_size=100m#####缓存大小binlog_cache_size=4m#####最大缓存大小max_binlog_cache_size=512m

主从数据库除server-id 外其他配置相同，修改完配置，重启数据库

systemctl restart  mysql

创建 MySQL 同步账户

创建账户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';

赋予同步slave权限
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%';
•Replication client
允许执行show master status,show slave status,show binary logs命令
•Replication slave
允许slave主机通过此用户连接master以便建立主从复制关系

赋予全部权限
grant all privileges on *.* to 'repl'@'%' with grant option;

刷新策略
flush privileges;

查看 MySQL Master 状态

show global variables like "%datadir%";

show master status;

更新密码存储方式

修改repl用户密码存储方式
alter user repl identified with mysql_native_password by "repl@passwd";
查看密码存储方式
select host, user, plugin from mysql.user;

MySQL 数据表结构同步

由于MySQL01数据库已经在使用，MySQL02导入MySQL01的zabbix数据库表项和数据(保证主从数据库表结构和数据相同)，使用mysqldump对主库数据进行备份，从库进行导入。

主数据库操作：

备份zabbix数据库
mysqldump -uzabbix -pzabbix  zabbix > /opt/zabbix.sql
拷贝备份sql到备库
scp root@192.168.100.93:/opt/zabbix.sql  /opt/

备数据库操作:

登录数据库
mysql -uroot -ppassword
创建 zabbix 数据库
mysql> create database zabbix character set utf8mb4 collate utf8mb4_bin;
创建 zabbix 用户
mysql> create user 'zabbix'@'%' identified by 'zabbix';
授权访问权限
mysql> grant all privileges on zabbix.* to 'zabbix'@'%';
进入zabbix数据库
mysql> use zabbix；
导入备份数据库
mysql> source  /opt/zabbix.sql

查看zabbix数据表,验证从数据库数据

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES where table_schema ='zabbix';

MySQL主主同步

Zabbix-MySQL02配置

使用show master status;命令查看bin-log信息，master_log_file对应File字段， master_log_pos对应Position 字段。查看由于Position值会不断发生变化，Slave在进行数据同步主库需要锁表操作；

Zabbix-MySQL01 锁表
flush tables with read lock; （会话关闭后自动解锁）
unlock tables；当前会话解锁；
查看 master 状态
show master status;

Zabbix-MySQL02 配置同步操作
停止slave
stop slave;
同步配置
CHANGE MASTER TO
MASTER_HOST='192.168.100.92',
MASTER_USER='repl',
MASTER_PASSWORD='repl@passwd',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=588924;

开启slave
start slave;
查看同步状态
show slave status \G

Zabbix-MySQL01配置

Zabbix-MySQL02 锁表
flush tables with read lock; 
unlock tables； 
查看 master 状态
show master status;

Zabbix-MySQL01 配置同步操作
停止slave
stop slave;
同步配置
CHANGE MASTER TO
MASTER_HOST='192.168.100.93',
MASTER_USER='repl',
MASTER_PASSWORD='repl@passwd',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=94453;

开启slave
start slave;

查看slave同步状态

show slave status \G

Slave_IO_Running:从服务器正从主服务器上读取BINLOG日志,并写入从服务器的中继日志 Slave_SQL_Running:进程正在读取从服务器的BINLOG中继日志，并转化为SQL执行

MySQL同步命令

show processlist;  //显示与当前用户帐户相关的所有正在运行的线程信息

show slave status \G //查看slave状态

stop slave;　　//停止

reset slave all;　 //清空

start slave;　　//开启

查看用户信息

select host,user from mysql.user;

查看用户授权

SHOW GRANTS FOR 'repl'@'%';

查看当前用户权限

show grants;

删除用户授权

DROP USER 'repl'@'%' ;

MySQL密码策略

-- 显示相关策略

show variables like 'validate%';

-- 密码验证策略低要求(0或LOW代表低级)

set global validate_password.policy=0;

-- 密码至少要包含的小写字母个数和大写字母个数

set global validate_password.mixed_case_count=0;

-- 密码至少要包含的数字个数。

set global validate_password.number_count=0;

-- 密码至少要包含的特殊字符数

set global validate_password.special_char_count=0;

-- 密码长度

set global validate_password.length=6;
-- 更改密码

ALTER user 'root'@'localhost' IDENTIFIED BY 'password'

MySQL 进程监控

cat /etc/keepalived/chkmysql.sh

#! /bin/bash

mysqld=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

if [ "${mysqld}" -eq 0 ]; then

  exit 1

else

  exit 0

fi

keepalived 配置

使用keepalived组建mysql数据库高可用架构，由于数据库节点特殊，Keepalived部署在非抢占模式。

MySQL01 keepalived配置

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived global_defs { router_id lvs-mysql01}vrrp_script chkmysql{ script "/etc/keepalived/chkmysql.sh" interval 2 fall 2 rise 2 }vrrp_instance mysql-ha { state BACKUP interface ens3 virtual_router_id 100 priority 100 nopreempt advert_int 1 authentication { auth_type PASS auth_pass 1111}virtual_ipaddress { 192.168.100.94}track_script { chkmysql}}

MySQL02 keepalived配置

vim /etc/keepalived/keepalived.conf

vim /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs { router_id lvs-mysql02}vrrp_script chkmysql{ script "/etc/keepalived/chkmysql.sh" interval 2 fall 2 rise 2 }vrrp_instance mysql-ha { state BACKUP interface ens3 virtual_router_id 100 priority 90 nopreempt advert_int 1 authentication { auth_type PASS auth_pass 1111}virtual_ipaddress { 192.168.100.94}track_script { chkmysql}}

启动、重启、开机启动keepalived

systemctl start  keepalived

systemctl restart  keepalived  

systemctl enable keepalived

Keepalived VIP

keepalived 切换测试

MySQL01停止mysql服务

VIP切换到MySQL02

非抢占模式测试

MySQL01 启动mysql服务，Keepalived未抢占VIP

MySQL 数据库备份脚本

#!/bin/bash

/usr/bin/mysqldump -uzabbix -pzabbix zabbix | gzip > /mysql-backup/zabbix_$(date +%Y%m%d_%H%M%S).sql.gz

过滤历史数据和趋势数据表

#!/bin/bash

/usr/bin/mysqldump -uzabbix -pzabbix zabbix --ignore-table=zabbix.history --ignore-table=zabbix.history_str --ignore-table=zabbix.history_uint --ignore-table=zabbix.trends --ignore-table=zabbix.trends_uint > /opt/zabbix`date +%y%m%d`-zabbix.sql.gz

删除20天之前的备份数据

#!/bin/bash

find /mysql-backup -name zabbix"*.sql.gz" -type f -mtime +20 -exec rm -rf {} \; > /dev/null 2>&1

crontab计划任务

crontab -e
30 11 * * * /mysql-backup/bkzabbix.sh
30 11 * * * /mysql-backup/rmysql.sh

systemctl restart  crond

mysql明文密码会打印如下信息,提示明文密码不安全

mysql -root -p’password’

[Warning] Using a password on the command line interface can be insecure

MySQL登录调用密码文件方式解决消除登录提示不安全的问题

创建MySQL用户名、密码文件

vim /root/mysql.pass

[client]

host=localhost

user=repl

password='repl@passwd'

登录测试

mysql --defaults-extra-file=/root/mysql.pass -e  "show slave status\G" |grep Running |grep -c Yes

监控 MySQL 主备状态

#!/bin/bash

count=$(mysql--defaults-extra-file=/root/mysql.pass -e "show slave status\G" | grep "Running:" | grep -c 'Yes')

if [ $count -ne 2 ];then

  echo '1'

else

  echo '0'

fi

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-06-22，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库