【Devops】Debian12安装MySQL8

准备源(这里使用的清华源)

源URL：点击跳转

# 默认注释了源码镜像以提高 apt update 速度，如有需要可自行取消注释
deb https://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm main contrib non-free non-free-firmware
# deb-src https://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm main contrib non-free non-free-firmware
deb https://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-updates main contrib non-free non-free-firmware
# deb-src https://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-updates main contrib non-free non-free-firmware
deb https://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-backports main contrib non-free non-free-firmware
# deb-src https://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-backports main contrib non-free non-free-firmware 
# 以下安全更新软件源包含了官方源与镜像站配置，如有需要可自行修改注释切换
deb https://security.debian.org/debian-security bookworm-security main contrib non-free non-free-firmware

额外配置

下面报错是因为debian12 默认使用的libssl3，而 MySQL 包需要 libssl1.1

root@mysql2:/mnt# apt install mysql-server
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 mysql-community-client : Depends: libssl1.1 (>= 1.1.1) but it is not installable
                          Depends: mysql-community-client-core (= 8.0.37-1debian11) but it is not going to be installed
 mysql-community-server-core : Depends: libssl1.1 (>= 1.1.1) but it is not installable
                               Recommends: mecab-ipadic-utf8 but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

安装 libssl1.1

# 添加 Debian 11 (bullseye) 源以获取 libssl1.1
echo "deb http://mirrors.tuna.tsinghua.edu.cn/debian bullseye main" > /etc/apt/sources.list.d/bullseye.list

# 更新包列表
apt update

# 安装 libssl1.1
apt install libssl1.1 -y

# 删除 bullseye 源（安装完成后）
rm /etc/apt/sources.list.d/bullseye.list
apt update

安装mysql

wget https://repo.mysql.com/mysql-apt-config_0.8.29-1_all.deb
apt install gnupg2 -y
dpkg -i mysql-apt-config_0.8.29-1_all.deb
apt update
apt install mysql-community-server mysql-community-client -y

主从配置

这里我的主库的数据位置在trueNas下的ssd池中，从库数据存取位置在trueNas下的hdd池中，所以我主库位置在/mnt/mysql_master，从库位置在/mnt/mysql_slave

配置主节点MySQL

mv /var/lib/mysql /var/lib/mysql.bak

停止服务

systemctl stop mysql

初始化数据目录

mysqld --initialize-insecure --user=[用户名] --datadir=/mnt/mysql_master/mysql

配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# 基本配置
server_id = 45
port = 3306
datadir = /mnt/mysql_master/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

# 字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

# 二进制日志配置
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
binlog_expire_logs_seconds = 604800

# 错误日志配置
log_error = /mnt/mysql_master/mysql/error.log

备份原数据目录

mv /var/lib/mysql /var/lib/mysql.bak

创建新的数据目录

mkdir -p /mnt/mysql_master/mysql chown -R mysql:mysql /mnt/mysql_master/mysql

创建必要目录

mkdir -p /var/run/mysqld 
chown mysql:mysql /var/run/mysqld

启动

systemctl start mysql

配置用户

# 登录MySQL
mysql -u root

# 创建复制用户
CREATE USER 'repl'@'192.168.21.%' IDENTIFIED BY 'Repl_123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.21.%';
FLUSH PRIVILEGES;

# 查看主服务器状态
SHOW MASTER STATUS;[查看文件号和位置]

配置从结点

仅需要修改server_id与相对路径，其余可以保持一致

# 基本配置
server_id = 46
port = 3306
datadir = /mnt/mysql_slave/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

# 字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

# 中继日志配置
relay-log = relay-bin
relay-log-index = relay-bin.index
read_only = 1

# 错误日志配置
log_error = /mnt/mysql_slave/mysql/error.log

从节点配置

    CHANGE MASTER TO
    MASTER_HOST='192.168.21.50', -- 主节点信息
    MASTER_USER='repl',  -- 主节点信息
    MASTER_PASSWORD='xxxxxxxxxxxxx!',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',  -- 更正为正确的文件号
    MASTER_LOG_POS=878,                  -- 更正为正确的位置
    GET_MASTER_PUBLIC_KEY=1;
    
    
    START SLAVE;
    
    SHOW SLAVE STATUS\G -- 查看状态

测试

# 在主服务器上创建测试数据
mysql -u root
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE test (id INT, name VARCHAR(50));
INSERT INTO test VALUES (1, 'test');

# 在从服务器上验证
mysql -u root
USE testdb;
SELECT * FROM test;

备份，这里使用的是Percona XtraBackup

安装

安装依赖
apt update
apt install -y wget gnupg2 lsb-release curl

下载 Percona 仓库包
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

安装仓库包
dpkg -i percona-release_latest.generic_all.deb

启用仓库
percona-release setup ps80

安装 Percona XtraBackup
apt update
apt install -y percona-xtrabackup-80

创建备份

创建主备份目录
mkdir -p /mnt/mysql_slave/backup/mysql/full
mkdir -p /mnt/mysql_slave/backup/mysql/incremental
mkdir -p /mnt/mysql_slave/backup/mysql/logs

设置权限
chown -R mysqlslave:mysql /mnt/mysql_slave/backup/mysql
chmod -R 750 /mnt/mysql_slave/backup/mysql

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

#!/bin/bash

# 配置
BACKUP_DIR="/backup/mysql"
FULL_BACKUP_DIR="${BACKUP_DIR}/full"
INCR_BACKUP_DIR="${BACKUP_DIR}/incremental"
LOG_DIR="${BACKUP_DIR}/logs"
DATE=$(date +%Y%m%d_%H%M%S)
DOW=$(date +%u)  # 1-7 (星期一到星期日)
RETENTION_DAYS=7
MYSQL_USER="your_backup_user"
MYSQL_PASSWORD="your_backup_password"

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a ${LOG_DIR}/backup_${DATE}.log
}

# 创建备份目录
mkdir -p ${FULL_BACKUP_DIR}/${DATE}
mkdir -p ${LOG_DIR}

# 全量备份（每周日）
if [ "$DOW" = "7" ]; then
    log "Starting full backup..."
    xtrabackup --backup \
        --target-dir=${FULL_BACKUP_DIR}/${DATE} \
        --user=${MYSQL_USER} \
        --password=${MYSQL_PASSWORD} \
        --parallel=4 2>> ${LOG_DIR}/backup_${DATE}.log

    # 准备备份以使其一致
    log "Preparing full backup..."
    xtrabackup --prepare \
        --target-dir=${FULL_BACKUP_DIR}/${DATE} \
        2>> ${LOG_DIR}/backup_${DATE}.log

    # 创建最新全量备份的符号链接
    ln -snf ${FULL_BACKUP_DIR}/${DATE} ${FULL_BACKUP_DIR}/latest

else
    # 增量备份
    LATEST_FULL=$(readlink ${FULL_BACKUP_DIR}/latest)
    
    if [ -z "$LATEST_FULL" ]; then
        log "No full backup found. Running full backup instead..."
        xtrabackup --backup \
            --target-dir=${FULL_BACKUP_DIR}/${DATE} \
            --user=${MYSQL_USER} \
            --password=${MYSQL_PASSWORD} \
            --parallel=4 2>> ${LOG_DIR}/backup_${DATE}.log

        xtrabackup --prepare \
            --target-dir=${FULL_BACKUP_DIR}/${DATE} \
            2>> ${LOG_DIR}/backup_${DATE}.log

        ln -snf ${FULL_BACKUP_DIR}/${DATE} ${FULL_BACKUP_DIR}/latest
    else
        log "Starting incremental backup..."
        mkdir -p ${INCR_BACKUP_DIR}/${DATE}
        
        xtrabackup --backup \
            --target-dir=${INCR_BACKUP_DIR}/${DATE} \
            --incremental-basedir=${FULL_BACKUP_DIR}/latest \
            --user=${MYSQL_USER} \
            --password=${MYSQL_PASSWORD} \
            --parallel=4 2>> ${LOG_DIR}/backup_${DATE}.log
    fi
fi

# 清理旧备份
log "Cleaning old backups..."
find ${FULL_BACKUP_DIR} -maxdepth 1 -type d -mtime +${RETENTION_DAYS} -exec rm -rf {} \;
find ${INCR_BACKUP_DIR} -maxdepth 1 -type d -mtime +${RETENTION_DAYS} -exec rm -rf {} \;
find ${LOG_DIR} -name "backup_*.log" -mtime +${RETENTION_DAYS} -delete

log "Backup completed successfully"

设置权限

chmod 700 /usr/local/bin/mysql_backup.sh
chown root:root /usr/local/bin/mysql_backup.sh

创建定时任务

crontab -e

# 添加以下行（每天凌晨2点执行）
0 2 * * * /usr/local/bin/mysql_backup.sh

恢复全量备份

# 停止 MySQL
systemctl stop mysql

# 清空数据目录
rm -rf /var/lib/mysql/*

# 恢复全量备份
xtrabackup --copy-back --target-dir=/backup/mysql/full/latest

# 修改权限
chown -R mysqlslave:mysql /var/lib/mysql

# 启动 MySQL
systemctl start mysql

恢复增量备份

# 首先准备全量备份
xtrabackup --prepare --apply-log-only \
    --target-dir=/backup/mysql/full/latest

# 然后应用增量备份
xtrabackup --prepare --apply-log-only \
    --target-dir=/backup/mysql/full/latest \
    --incremental-dir=/backup/mysql/incremental/YOUR_INCR_BACKUP_DIR

# 最后一个增量备份不需要 --apply-log-only
xtrabackup --prepare \
    --target-dir=/backup/mysql/full/latest \
    --incremental-dir=/backup/mysql/incremental/LAST_INCR_BACKUP_DIR

# 恢复数据
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/mysql/full/latest
chown -R mysqlslave:mysql /var/lib/mysql
systemctl start mysql

监控脚本 /usr/local/bin/check_backup.sh

#!/bin/bash

BACKUP_DIR="/backup/mysql"
LOG_DIR="${BACKUP_DIR}/logs"
LATEST_LOG=$(ls -t ${LOG_DIR}/backup_*.log 2>/dev/null | head -1)

if [ -z "$LATEST_LOG" ]; then
    echo "ERROR: No backup logs found"
    exit 1
fi

if grep -q "completed successfully" "$LATEST_LOG"; then
    BACKUP_AGE=$(( ( $(date +%s) - $(stat -c %Y "$LATEST_LOG") ) / 3600 ))
    
    if [ $BACKUP_AGE -gt 24 ]; then
        echo "WARNING: Latest successful backup is $BACKUP_AGE hours old"
        exit 1
    else
        echo "Backup check OK. Latest successful backup is $BACKUP_AGE hours old"
        exit 0
    fi
else
    echo "ERROR: Latest backup failed"
    exit 1
fi

备份用户权限配置

-- 删除旧用户（如果需要重新创建）
DROP USER IF EXISTS 'backup_user'@'localhost';

-- 创建新用户
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'bjst@1234!';

-- 授予所有必要权限
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO 'backup_user'@'localhost';
GRANT SELECT ON performance_schema.* TO 'backup_user'@'localhost';
GRANT SELECT ON mysql.* TO 'backup_user'@'localhost';

-- 如果使用 keyring
GRANT SELECT ON performance_schema.keyring_component_status TO 'backup_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

测试命令

xtrabackup --backup \
    --target-dir=/mnt/mysql_slave/mysql_backup/test \
    --user=backup_user \
    --password=bjst@1234!