实践项目-数据库主从高可用（MySQL-MHA、ProxySQL、mysqldump）

(250103)

实践目标

备份策略：定期全量备份和增量备份，备份文件异地存储。
恢复测试：定期在测试库上恢复备份，确保备份文件可用。
权限管理：严格控制数据库访问权限，避免误操作。
变更管理：所有数据库变更需经过审批，并在非高峰时段执行。
监控告警：实时监控数据库状态，设置告警机制，及时发现并处理问题。
同步延迟：监控主从同步延迟，确保数据一致性。
备份验证：定期验证备份文件的完整性和可恢复性。
安全防护：加强数据库安全防护，防止数据泄露和攻击。

环境

操作系统：Debian 12.8
节点规划：
主库：192.168.100.1
从库1：192.168.100.2
从库2：192.168.100.3
MHA 管理节点：192.168.100.4
读写分离中间件：192.168.100.5
备份服务器：192.168.100.6
测试库：192.168.100.7
(默认进行过ssh-copy-id)

MySQL安装

（所有的节点）

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.40.tar.gz

tar -zxvf mysql-8.0.40.tar.gz -C /usr/local/
mv /usr/local/mysql-8.0.40 /usr/local/mysql

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

/usr/local/mysql/bin/mysqld_safe --user=mysql &

主从搭建

主库配置
my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=database_name

/usr/local/mysql/bin/mysqladmin -u root -p shutdown
/usr/local/mysql/bin/mysqld_safe --user=mysql &

从库配置
my.cnf

[mysqld]
server-id=2  # 从库1
server-id=3  # 从库2
relay-log=mysql-relay-bin

/usr/local/mysql/bin/mysqladmin -u root -p shutdown
/usr/local/mysql/bin/mysqld_safe --user=mysql &

主库授权

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

从库同步

CHANGE MASTER TO
MASTER_HOST='192.168.100.1',
MASTER_USER='replica',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
START SLAVE;

检查同步

SHOW SLAVE STATUS\G;

确保Slave_IO_Running和Slave_SQL_Running都为 Yes。

Binlog 配置排错

检查 Binlog 配置
确保主库和从库的 my.cnf 文件中正确配置了 log-bin 和 server-id。
检查 Binlog 文件
在主库上检查 Binlog 文件是否存在：

ls /usr/local/mysql/data/mysql-bin.*

检查 Binlog 写入权限
确保 MySQL 用户对 Binlog 文件有写入权限：

ls -l /usr/local/mysql/data/mysql-bin.*

检查 Binlog 同步状态
在从库上检查 Binlog 同步状态，确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes。

MHA 搭建

MHA主库-github
MHA从库-github
安装 MHA 依赖
(MHA管理节点)

sudo apt-get install perl libdbd-mysql-perl libconfig-tiny-perl liblog-dispatch-perl libparallel-forkmanager-perl

(从库)

sudo apt-get install perl libdbd-mysql-perl

下载并安装 MHA
(MHA管理节点)

wget https://github.com/yoshinorim/mha4mysql-manager/archive/refs/tags/v0.58.tar.gz
tar -zxvf v0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL
make
make install

(从库)

wget https://github.com/yoshinorim/mha4mysql-node/archive/refs/tags/v0.58.tar.gz
tar -xvf v0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL
make
make install

配置 MHA
/etc/mha/app1.cnf

[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
master_binlog_dir=/usr/local/mysql/data
user=root
password=password
ssh_user=root
repl_user=replica
repl_password=password
ping_interval=3

[server1]
hostname=192.168.100.1
candidate_master=1

[server2]
hostname=192.168.100.2
candidate_master=1

[server3]
hostname=192.168.100.3
candidate_master=1

[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
master_binlog_dir=/usr/local/mysql/data
user=root
password=password
ssh_user=root
repl_user=replica
repl_password=password
ping_interval=3
master_ip_failover_script=/usr/local/bin/mha_failover.sh

启动 MHA

masterha_manager --conf=/etc/mha/app1.cnf

检查 MHA 状态

masterha_check_status --conf=/etc/mha/app1.cnf

读写分离

proxysql-github
安装依赖

apt-get update && apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates
wget -nv -O /etc/apt/trusted.gpg.d/proxysql-2.4.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key.gpg'
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/$(lsb_release -sc)/ ./" | tee /etc/apt/sources.list.d/proxysql.list

安装ProxySQL

apt-get update
apt-get install proxysql OR apt-get install proxysql=version

配置 ProxySQL
/etc/proxysql.cnf

datadir="/var/lib/proxysql"
admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
}

mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="8.0.40"
    connect_timeout_server=10000
}

mysql_servers =
(
    { address="192.168.100.1", port=3306, hostgroup=1 },  # 主库（写操作）
    { address="192.168.100.2", port=3306, hostgroup=2 },  # 从库1（读操作）
    { address="192.168.100.3", port=3306, hostgroup=2 }   # 从库2（读操作）
)

mysql_users =
(
    { username = "root", password = "password", default_hostgroup = 1 }
)

mysql_query_rules =
(
    {
        rule_id=1
        active=1
        match_pattern="^SELECT"
        destination_hostgroup=2
        apply=1
    },
    {
        rule_id=2
        active=1
        match_pattern=".*"
        destination_hostgroup=1
        apply=1
    }
)

启动 ProxySQL

sudo systemctl start proxysql
sudo systemctl enable proxysql

测试读写分离

mysql -u root -p -h 192.168.100.5 -P 6033

执行以下 SQL 语句，验证读写分离是否生效
写操作（应路由到主库）

INSERT INTO test_table (name) VALUES ('test');

读操作（应路由到从库）

SELECT * FROM test_table;

MHA故障转移

/usr/local/bin/mha_failover.sh

#!/bin/bash
# MHA 故障转移脚本

# 定义变量
MHA_CONF="/etc/mha/app1.cnf"
LOG_FILE="/var/log/mha/failover.log"
MAIL_TO="admin@example.com"
MAIL_SUBJECT="MHA Failover Notification"

# 记录日志
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - \$1" >> $LOG_FILE
}

# 发送邮件通知
send_mail() {
    echo "\$1" | mail -s "$MAIL_SUBJECT" "$MAIL_TO"
}

# 主库故障检测
log "Starting MHA failover process..."

# 提升从库为主库
log "Promoting new master..."
masterha_master_switch --conf=$MHA_CONF --master_state=dead --dead_master_host=192.168.100.1 --new_master_host=192.168.100.2 --interactive=0

# 检查提升结果
if [ $? -eq 0 ]; then
    log "New master promoted successfully: 192.168.100.2"
    send_mail "MHA failover completed successfully. New master: 192.168.100.2"
else
    log "Failed to promote new master."
    send_mail "MHA failover failed. Please check the logs."
    exit 1
fi

# 重新配置其他从库
log "Reconfiguring other slaves..."
masterha_conf_host --command=apply --conf=$MHA_CONF --host=192.168.100.3

# 检查重新配置结果
if [ $? -eq 0 ]; then
    log "Slave 192.168.100.3 reconfigured successfully."
else
    log "Failed to reconfigure slave 192.168.100.3."
    send_mail "Failed to reconfigure slave 192.168.100.3. Please check the logs."
    exit 1
fi

# 更新 ProxySQL 配置
log "Updating ProxySQL configuration..."
mysql -h 192.168.100.5 -P 6032 -u admin -padmin -e "
DELETE FROM mysql_servers WHERE hostgroup_id=1;
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.100.2', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
"

if [ $? -eq 0 ]; then
    log "ProxySQL configuration updated successfully."
else
    log "Failed to update ProxySQL configuration."
    send_mail "Failed to update ProxySQL configuration. Please check the logs."
    exit 1
fi

log "MHA failover process completed."

chmod +x /usr/local/bin/mha_failover.sh

脚本测试

手动停止主库的 MySQL 服务

/usr/local/mysql/bin/mysqladmin -u root -p shutdown

触发故障转移

masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=dead --dead_master_host=192.168.100.1 --new_master_host=192.168.100.2 --interactive=0

检查日志
查看故障转移日志/var/log/mha/failover.log，确保脚本执行成功。

备份和恢复方案

使用mysqldump进行全量备份，并将备份文件推送到备份服务器。

创建备份脚本
/usr/local/bin/mysql_backup.sh：

#!/bin/bash
# MySQL 全量备份脚本

# 定义变量
BACKUP_DIR="/backup/mysql"
BACKUP_FILE="$BACKUP_DIR/full_backup_$(date +%F).sql"
LOG_FILE="/var/log/mysql_backup.log"
MYSQL_USER="root"
MYSQL_PASSWORD="password"
BACKUP_SERVER="192.168.100.6"
BACKUP_SERVER_DIR="/backup/mysql"

# 记录日志
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - \$1" >> $LOG_FILE
}

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行全量备份
log "Starting MySQL full backup..."
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases --single-transaction --master-data=2 > $BACKUP_FILE

# 检查备份结果
if [ $? -eq 0 ]; then
    log "MySQL full backup completed successfully: $BACKUP_FILE"
else
    log "MySQL full backup failed."
    exit 1
fi

# 推送备份文件到备份服务器
log "Pushing backup file to backup server..."
scp $BACKUP_FILE $BACKUP_SERVER:$BACKUP_SERVER_DIR

# 检查推送结果
if [ $? -eq 0 ]; then
    log "Backup file pushed to backup server successfully."
else
    log "Failed to push backup file to backup server."
    exit 1
fi

log "MySQL backup process completed."

chmod +x /usr/local/bin/mysql_backup.sh

crontab -e
0 2 * * * /usr/local/bin/mysql_backup.sh

增量备份
通过mysqlbinlog工具进行增量备份。

/usr/local/bin/mysql_incremental_backup.sh

#!/bin/bash
# MySQL 增量备份脚本


# 定义变量
BACKUP_DIR="/backup/mysql"
BACKUP_FILE="$BACKUP_DIR/incremental_backup_$(date +%F).sql"
LOG_FILE="/var/log/mysql_incremental_backup.log"
MYSQL_BINLOG_DIR="/usr/local/mysql/data"
BACKUP_SERVER="192.168.100.6"
BACKUP_SERVER_DIR="/backup/mysql"

# 记录日志
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - \$1" >> $LOG_FILE
}

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行增量备份
log "Starting MySQL incremental backup..."
mysqlbinlog --raw --read-from-remote-server --host=192.168.100.1 --user=root --password=password --result-file=$BACKUP_FILE $(ls -t $MYSQL_BINLOG_DIR/mysql-bin.* | head -n 1)

# 检查备份结果
if [ $? -eq 0 ]; then
    log "MySQL incremental backup completed successfully: $BACKUP_FILE"
else
    log "MySQL incremental backup failed."
    exit 1
fi

# 推送备份文件到备份服务器
log "Pushing backup file to backup server..."
scp $BACKUP_FILE $BACKUP_SERVER:$BACKUP_SERVER_DIR

# 检查推送结果
if [ $? -eq 0 ]; then
    log "Backup file pushed to backup server successfully."
else
    log "Failed to push backup file to backup server."
    exit 1
fi

log "MySQL incremental backup process completed."

chmod +x /usr/local/bin/mysql_incremental_backup.sh

crontab -e
0 * * * * /usr/local/bin/mysql_incremental_backup.sh

备份恢复

在需要恢复数据时，可以使用全量备份和增量备份文件进行恢复。

全量恢复
在目标 MySQL 实例上执行全量恢复：

mysql -u root -p < /backup/mysql/full_backup_{{date}}.sql

增量恢复
在目标 MySQL 实例上执行增量恢复：

mysqlbinlog /backup/mysql/incremental_backup_{{date}}.sql | mysql -u root -p

将备份数据定期恢复到测试库

恢复流程
创建测试库
在测试库节点（192.168.100.7）上安装 MySQL，并创建与生产环境相同的数据库结构。

恢复全量备份

mysql -u root -p < /backup/mysql/full_backup_2023-10-01.sql

恢复增量备份

mysqlbinlog /backup/mysql/incremental_backup_2023-10-01.sql | mysql -u root -p

验证恢复结果
在测试库上执行以下操作，验证数据恢复结果：

• 检查数据完整性。
• 运行开发测试用例，确保业务逻辑正常。

自动化恢复脚本

/usr/local/bin/mysql_restore_test.sh

#!/bin/bash
# MySQL 测试库恢复脚本

# 定义变量
BACKUP_DIR="/backup/mysql"
TEST_DB_HOST="192.168.100.7"
TEST_DB_USER="root"
TEST_DB_PASSWORD="password"
LOG_FILE="/var/log/mysql_restore_test.log"

# 记录日志
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - \$1" >> $LOG_FILE
}

# 恢复全量备份
log "Starting MySQL full backup restore..."
mysql -h $TEST_DB_HOST -u $TEST_DB_USER -p$TEST_DB_PASSWORD < $BACKUP_DIR/full_backup_$(date +%F).sql

# 检查恢复结果
if [ $? -eq 0 ]; then
    log "MySQL full backup restore completed successfully."
else
    log "MySQL full backup restore failed."
    exit 1
fi

# 恢复增量备份
log "Starting MySQL incremental backup restore..."
mysqlbinlog $BACKUP_DIR/incremental_backup_$(date +%F).sql | mysql -h $TEST_DB_HOST -u $TEST_DB_USER -p$TEST_DB_PASSWORD

# 检查恢复结果
if [ $? -eq 0 ]; then
    log "MySQL incremental backup restore completed successfully."
else
    log "MySQL incremental backup restore failed."
    exit 1
fi

log "MySQL test database restore process completed."

chmod +x /usr/local/bin/mysql_restore_test.sh

crontab -e
0 3 * * * /usr/local/bin/mysql_restore_test.sh

人工更新数据库的流程及制度

更新流程

更新申请
申请人：开发人员或运维人员。
申请内容：包括更新的 SQL 语句、更新原因、更新时间等。
申请方式：通过工单系统或邮件提交申请。

更新审批
审批人：数据库管理员（DBA）或相关负责人。
审批内容：审核 SQL 语句的正确性、更新对系统的影响等。
审批方式：通过工单系统或邮件进行审批。

更新执行
执行人：数据库管理员（DBA）或授权人员。
执行步骤：
备份数据：在执行更新前，先对相关表或数据库进行备份。
执行更新：在非高峰时段执行更新操作。
验证更新：检查更新结果，确保数据一致性和业务正常运行。
记录日志：记录更新操作的详细信息，包括执行时间、执行人、更新内容等。
更新回滚
回滚条件：如果更新导致数据不一致或业务异常，立即执行回滚操作。

回滚步骤：

• 停止更新：立即停止所有相关操作。
• 恢复数据：使用备份文件恢复数据。
• 验证恢复：检查数据恢复结果，确保业务正常运行。
• 记录日志：记录回滚操作的详细信息，包括回滚时间、回滚人、回滚内容等。

更新制度

更新权限
权限分配：只有经过授权的数据库管理员（DBA）或相关人员才能执行更新操作。
权限管理：定期审查和更新权限分配，确保权限最小化。
更新时间
非高峰时段：更新操作应在非高峰时段进行，避免影响业务正常运行。
紧急更新：对于紧急更新，需经过特别审批，并在执行前通知相关人员。
更新记录
记录内容：每次更新操作都需详细记录，包括更新内容、执行时间、执行人、审批人等。
记录保存：更新记录应长期保存，便于后续审计和查询。
更新培训
培训内容：定期对相关人员进行数据库更新操作的培训，确保操作规范和安全。
培训记录：记录培训内容和参与人员，确保培训效果。