侧边栏壁纸
博主头像
JamKing工作站

行动起来，活在当下

  • 累计撰写 14 篇文章
  • 累计创建 3 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录

mysql主从部署

JamKing
JamKing
2025-06-22 / 0 评论 / 0 点赞 / 3 阅读 / 0 字

一、服务器资源

主机 操作系统 配置 角色
172.16.10.128 CentOS7.9 2c2g master
172.16.10.129 CentOS7.9 2c2g slave

二、确认服务器的内核架构

uname -m

image-TgAb.png

三、下载部署包

基于对应的内核架构，下载对应的mysql，建议下载二进制版，可离线部署，也便于生产环境统一目录管理

#阿里云下载地址
https://mirrors.aliyun.com/mysql/MySQL-8.0/mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

四、部署mysql

4.1 解压

### 2.3 系统优化（两台服务器都执行）
```bash
# 关闭SELinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
setenforce 0

# 配置防火墙（开放MySQL端口）
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload

# 创建mysql用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

# 调整文件句柄限制
cat >> /etc/security/limits.conf << EOF
mysql soft nofile 65536
mysql hard nofile 65536
mysql soft nproc 65536
mysql hard nproc 65536
EOF

# 内核参数优化
cat >> /etc/sysctl.conf << EOF
# MySQL优化参数
vm.swappiness = 1
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
net.ipv4.tcp_max_syn_backlog = 65535
net.core.netdev_max_backlog = 32768
net.core.somaxconn = 32768
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_max_orphans = 3276800
EOF
sysctl -p

# 时间同步
yum install -y chrony
systemctl enable chronyd
systemctl start chronyd

3. MySQL 8.0.27 二进制安装

3.1 下载并安装MySQL（两台服务器）

# 下载MySQL 8.0.27二进制包
cd /opt
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

# 解压安装
tar -xf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.27-linux-glibc2.12-x86_64 /deploy/mysql

# 创建数据目录
mkdir -p /deploy/mysql/{data,logs,tmp,backup}
chown -R mysql:mysql /deploy/mysql
chmod 750 /deploy/mysql

# 创建配置文件目录
mkdir -p /etc/mysql

3.2 环境变量配置

# 添加MySQL到PATH
echo 'export PATH=/deploy/mysql/bin:$PATH' >> /etc/profile
source /etc/profile

# 创建软链接
ln -s /deploy/mysql/bin/mysql /usr/bin/mysql
ln -s /deploy/mysql/bin/mysqladmin /usr/bin/mysqladmin
ln -s /deploy/mysql/bin/mysqldump /usr/bin/mysqldump

4. 配置文件设置

4.1 Master1 配置文件 (/etc/mysql/my.cnf)

[client]
port = 3306
socket = /data/mysql/tmp/mysql.sock
default-character-set = utf8mb4

[mysql]
prompt = "M1[\\d]> "
no-auto-rehash
default-character-set = utf8mb4

[mysqld]
# ========================
# 基本设置
# ========================
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket = /data/mysql/tmp/mysql.sock
pid-file = /data/mysql/tmp/mysql.pid
tmpdir = /data/mysql/tmp
secure-file-priv = /data/mysql/tmp

# ========================
# 字符集设置
# ========================
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

# ========================
# 网络连接设置
# ========================
max_connections = 3000
max_connect_errors = 100000
max_user_connections = 2800
back_log = 900
wait_timeout = 28800
interactive_timeout = 28800
lock_wait_timeout = 3600
connect_timeout = 60

# ========================
# 内存设置（根据16GB内存优化）
# ========================
key_buffer_size = 64M
max_heap_table_size = 256M
tmp_table_size = 256M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 128
thread_stack = 512K
max_allowed_packet = 256M

# ========================
# InnoDB引擎优化配置
# ========================
default_storage_engine = InnoDB
default_tmp_storage_engine = InnoDB

# InnoDB缓冲池设置（物理内存的75%）
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 12
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

# InnoDB日志设置
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# InnoDB表空间设置
innodb_file_per_table = 1
innodb_open_files = 8000
innodb_data_file_path = ibdata1:1G:autoextend

# InnoDB性能设置
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 120
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 2G

# InnoDB刷新优化
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 50
innodb_flush_neighbors = 0
innodb_lru_scan_depth = 2048

# ========================
# 双主复制设置 - Master1
# ========================
server-id = 10
log-bin = /data/mysql/logs/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1
expire_logs_days = 7
max_binlog_size = 1G
binlog_cache_size = 8M
max_binlog_cache_size = 1G

# GTID全局事务标识符
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

# 双主冲突避免设置
auto_increment_increment = 2  # 自增步长为2
auto_increment_offset = 1     # Master1从1开始

# ========================
# 从库复制优化设置
# ========================
# 中继日志设置
relay_log = /data/mysql/logs/relay-bin
relay_log_recovery = 1
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_purge = 1

# 并行复制设置
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = 1
slave_pending_jobs_size_max = 268435456
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'

# 复制过滤设置（可选）
replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = performance_schema.%
replicate_wild_ignore_table = sys.%

# ========================
# 查询优化设置
# ========================
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 1
min_examined_row_limit = 1000
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# 错误日志
log-error = /data/mysql/logs/error.log
log_timestamps = SYSTEM

# 通用查询日志（生产环境建议关闭）
general_log = 0
general_log_file = /data/mysql/logs/general.log

# ========================
# 安全和其他设置
# ========================
skip_name_resolve = 1
explicit_defaults_for_timestamp = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
transaction_isolation = READ-COMMITTED

# 表缓存设置
table_open_cache = 8000
table_definition_cache = 4000
table_open_cache_instances = 16

# 文件句柄限制
open_files_limit = 65535

# 临时表设置
#internal_tmp_disk_storage_engine = InnoDB

# ========================
# 监控和状态设置
# ========================
performance_schema = ON
performance_schema_max_table_instances = 12500
performance_schema_max_table_handles = 4000

[mysqldump]
quick
max_allowed_packet = 256M
single-transaction
routines
triggers

[mysql]
no-auto-rehash
default-character-set = utf8mb4

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M

4.2 Master2 配置文件 (/etc/mysql/my.cnf)

# 将Master1的配置文件复制到Master2
scp /etc/mysql/my.cnf root@172.16.10.129:/etc/mysql/

# 在Master2上修改特定参数
ssh root@172.16.10.129 "
sed -i 's/server-id = 10/server-id = 11/' /etc/mysql/my.cnf
sed -i 's/auto_increment_offset = 1/auto_increment_offset = 2/' /etc/mysql/my.cnf
sed -i 's/prompt = \"M1\[\\\\d\]> \"/prompt = \"M2\[\\\\d\]> \"/' /etc/mysql/my.cnf
"

5. 初始化和启动MySQL

5.1 初始化数据库（两台服务器）

初始化过程中，缺少依赖可能会报以下错误

image-IxIg.png

提前安装依赖库

#安装依赖库
yum install -y libaio numactl-libs ncurses-compat-libs

image-FGjk.png

# 初始化MySQL数据目录
/deploy/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/deploy/mysql --datadir=/deploy/mysql/data

# 创建systemd服务文件
cat > /etc/systemd/system/mysql.service << 'EOF'
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
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=notify
TimeoutSec=0
PermissionsStartOnly=true
ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld
ExecStart=/deploy/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf
LimitNOFILE=65535
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
EOF

# 重载systemd并启动MySQL
systemctl daemon-reload
systemctl enable mysql
systemctl start mysql

# 检查启动状态
systemctl status mysql

image-QrVf.png

5.2 MySQL安全设置（两台服务器）

# 设置root密码和安全配置
mysql -u root << 'EOF'
-- 设置root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyS3cur3P@ssw0rd!';

-- 删除匿名用户
DELETE FROM mysql.user WHERE User='';

-- 禁止root远程登录（根据需要调整）
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- 删除test数据库
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- 刷新权限
FLUSH PRIVILEGES;
EOF

6. 配置双主复制

6.1 创建复制用户（两台服务器）

-- 在Master1和Master2上都执行
mysql -u root -p << 'EOF'
-- 创建复制用户
CREATE USER 'replication'@'172.16.10.%' IDENTIFIED BY 'Repl1c@t10n!Pass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'172.16.10.%';

-- 创建监控用户
CREATE USER 'monitor'@'172.16.10.%' IDENTIFIED BY 'M0n1t0r!Pass';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'172.16.10.%';

-- 刷新权限
FLUSH PRIVILEGES;
EOF

6.2 配置Master1到Master2的复制

-- 在Master1上获取当前binlog位置
mysql -u root -p -e "SHOW MASTER STATUS;"

-- 在Master2上配置从Master1复制
mysql -u root -p'MyS3cur3P@ssw0rd!' << 'EOF'
STOP SLAVE;
RESET SLAVE ALL;

CHANGE MASTER TO
  MASTER_HOST='172.16.10.128',
  MASTER_USER='replication',
  MASTER_PASSWORD='Repl1c@t10n!Pass',
  master_log_file='mysql-bin.000006',
  master_log_pos=2167,
  MASTER_CONNECT_RETRY=10,
  MASTER_RETRY_COUNT=0,
  MASTER_TO_MASTER_SSL=0,
  GET_MASTER_PUBLIC_KEY = 0,
  MASTER_HEARTBEAT_PERIOD=30;


START SLAVE;
SHOW SLAVE STATUS\G
EOF

6.3 配置Master2到Master1的复制

-- 在Master2上获取当前binlog位置
mysql -u root -p -e "SHOW MASTER STATUS;"

-- 在Master1上配置从Master2复制
mysql -u root -p << 'EOF'
STOP SLAVE;
RESET SLAVE ALL;

CHANGE MASTER TO
  MASTER_HOST='192.168.1.11',
  MASTER_USER='replication',
  MASTER_PASSWORD='Repl1c@t10n!Pass',
  MASTER_AUTO_POSITION=1,
  MASTER_CONNECT_RETRY=10,
  MASTER_RETRY_COUNT=0,
  MASTER_HEARTBEAT_PERIOD=30;

START SLAVE;
SHOW SLAVE STATUS\G
EOF

7. 验证双主配置

7.1 创建测试数据

-- 在Master1上创建测试数据库和表
mysql -u root -p << 'EOF'
CREATE DATABASE repl_test;
USE repl_test;

CREATE TABLE test_master1 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  server_name VARCHAR(20),
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  data VARCHAR(100)
);

INSERT INTO test_master1 (server_name, data) VALUES 
('Master1', 'Test data from Master1'),
('Master1', 'Another test record');

SELECT * FROM test_master1;
EOF

-- 在Master2上创建测试数据
mysql -u root -p << 'EOF'
USE repl_test;

CREATE TABLE test_master2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  server_name VARCHAR(20),
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
  data VARCHAR(100)
);

INSERT INTO test_master2 (server_name, data) VALUES 
('Master2', 'Test data from Master2'),
('Master2', 'Another test record');

-- 检查从Master1同步的数据
SELECT * FROM test_master1;
SELECT * FROM test_master2;
EOF

7.2 验证自增ID冲突避免

-- 在Master1上测试自增
mysql -u root -p << 'EOF'
USE repl_test;
CREATE TABLE auto_inc_test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  server VARCHAR(10),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO auto_inc_test (server) VALUES ('M1'), ('M1'), ('M1');
SELECT * FROM auto_inc_test;
EOF

-- 在Master2上测试自增
mysql -u root -p << 'EOF'
USE repl_test;
INSERT INTO auto_inc_test (server) VALUES ('M2'), ('M2'), ('M2');
SELECT * FROM auto_inc_test ORDER BY id;
EOF

7.3 检查复制状态

# 创建复制状态检查脚本
cat > /opt/check_replication.sh << 'EOF'
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="MyS3cur3P@ssw0rd!"

echo "=== MySQL Replication Status Check ==="
echo "Server: $(hostname) - $(date)"
echo

mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT 
  'Master Status' as Status_Type,
  @@server_id as Server_ID,
  @@hostname as Hostname;
SHOW MASTER STATUS;
"

echo
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT 
  'Slave Status' as Status_Type;
SHOW SLAVE STATUS\G
" | grep -E "(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error|Master_Host)"

echo
EOF

chmod +x /opt/check_replication.sh

# 在两台服务器上运行检查
/opt/check_replication.sh

8. 高可用和故障切换

8.1 故障检测脚本

cat > /opt/mysql_ha_monitor.sh << 'EOF'
#!/bin/bash
# MySQL双主高可用监控脚本

# 配置参数
LOCAL_MYSQL_USER="monitor"
LOCAL_MYSQL_PASS="M0n1t0r!Pass"
REMOTE_HOST="192.168.1.11"  # 对端服务器IP
REMOTE_MYSQL_USER="monitor"
REMOTE_MYSQL_PASS="M0n1t0r!Pass"
LOG_FILE="/var/log/mysql_ha.log"

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

# 检查本地MySQL状态
check_local_mysql() {
    mysql -u${LOCAL_MYSQL_USER} -p${LOCAL_MYSQL_PASS} -e "SELECT 1" > /dev/null 2>&1
    return $?
}

# 检查对端MySQL状态
check_remote_mysql() {
    mysql -h${REMOTE_HOST} -u${REMOTE_MYSQL_USER} -p${REMOTE_MYSQL_PASS} -e "SELECT 1" > /dev/null 2>&1
    return $?
}

# 检查复制状态
check_replication_status() {
    local result=$(mysql -u${LOCAL_MYSQL_USER} -p${LOCAL_MYSQL_PASS} -e "SHOW SLAVE STATUS\G" 2>/dev/null)
    if [ -n "$result" ]; then
        local io_running=$(echo "$result" | grep "Slave_IO_Running:" | awk '{print $2}')
        local sql_running=$(echo "$result" | grep "Slave_SQL_Running:" | awk '{print $2}')
        local seconds_behind=$(echo "$result" | grep "Seconds_Behind_Master:" | awk '{print $2}')
  
        if [ "$io_running" != "Yes" ] || [ "$sql_running" != "Yes" ]; then
            log_message "ALERT: Replication stopped - IO:$io_running SQL:$sql_running"
            return 1
        fi
  
        if [ "$seconds_behind" != "NULL" ] && [ "$seconds_behind" -gt 60 ]; then
            log_message "WARNING: Replication lag: $seconds_behind seconds"
        fi
    fi
    return 0
}

# 主检查逻辑
main() {
    # 检查本地MySQL
    if ! check_local_mysql; then
        log_message "CRITICAL: Local MySQL is down"
        exit 1
    fi
  
    # 检查对端MySQL
    if ! check_remote_mysql; then
        log_message "WARNING: Remote MySQL is unreachable"
    fi
  
    # 检查复制状态
    check_replication_status
}

main
EOF

chmod +x /opt/mysql_ha_monitor.sh

# 添加到crontab，每分钟检查一次
echo "* * * * * /opt/mysql_ha_monitor.sh" | crontab -

8.2 故障切换步骤

当主服务器故障时，手动切换步骤：

# 1. 确认主服务器故障
# 2. 在备用服务器上停止复制
mysql -u root -p -e "STOP SLAVE;"

# 3. 检查并应用所有待处理的中继日志
mysql -u root -p -e "SHOW PROCESSLIST;"

# 4. 将备用服务器提升为主服务器（移除只读模式如果有的话）
mysql -u root -p -e "SET GLOBAL read_only = 0;"

# 5. 更新应用程序连接配置指向新的主服务器

# 6. 故障服务器恢复后，重新配置为从服务器

9. 性能监控和优化

9.1 性能监控脚本

cat > /opt/mysql_performance_monitor.sh << 'EOF'
#!/bin/bash
MYSQL_USER="monitor"
MYSQL_PASS="M0n1t0r!Pass"
DATE=$(date '+%Y-%m-%d %H:%M:%S')

echo "=== MySQL Performance Report - $DATE ==="

# 连接数统计
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT 
  'Connection Statistics' as Metric_Type,
  VARIABLE_VALUE as Current_Connections
FROM performance_schema.global_status 
WHERE VARIABLE_NAME = 'Threads_connected';

SELECT 
  'Max Connections Used' as Metric_Type,
  VARIABLE_VALUE as Max_Used_Connections
FROM performance_schema.global_status 
WHERE VARIABLE_NAME = 'Max_used_connections';
"

# InnoDB状态
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT 
  'InnoDB Buffer Pool' as Metric_Type,
  ROUND(
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') * 16 / 1024, 2
  ) as 'Used_MB',
  ROUND(
    (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_buffer_pool_size') / 1024 / 1024, 2
  ) as 'Total_MB';
"

# 复制延迟
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT 
  'Replication Lag' as Metric_Type,
  Seconds_Behind_Master as Lag_Seconds
FROM performance_schema.replication_connection_status;
"

# 慢查询统计
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT 
  'Slow Queries' as Metric_Type,
  VARIABLE_VALUE as Slow_Query_Count
FROM performance_schema.global_status 
WHERE VARIABLE_NAME = 'Slow_queries';
"
EOF

chmod +x /opt/mysql_performance_monitor.sh

# 每小时执行一次性能监控
echo "0 * * * * /opt/mysql_performance_monitor.sh >> /var/log/mysql_performance.log" | crontab -

9.2 关键性能参数调优建议

-- 根据实际负载调整的关键参数
-- 在生产环境中监控并逐步调整

-- 连接相关
SET GLOBAL max_connections = 3000;
SET GLOBAL max_user_connections = 2800;

-- InnoDB调优
SET GLOBAL innodb_io_capacity = 4000;
SET GLOBAL innodb_io_capacity_max = 8000;
SET GLOBAL innodb_flush_neighbors = 0;  -- SSD环境推荐
SET GLOBAL innodb_max_dirty_pages_pct = 75;

-- 复制调优
SET GLOBAL slave_parallel_workers = 16;
SET GLOBAL slave_pending_jobs_size_max = 268435456;

10. 备份策略

10.1 热备份脚本（使用mysqldump）

cat > /opt/mysql_backup.sh << 'EOF'
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="MyS3cur3P@ssw0rd!"
BACKUP_DIR="/data/mysql/backup"
DATE=$(date +%Y%m%d_%H%M%S)
HOSTNAME=$(hostname)

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

# 全库备份
mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} \
  --single-transaction \
  --routines \
  --triggers \
  --master-data=2 \
  --all-databases \
  --flush-logs \
  --hex-blob > $BACKUP_DIR/$DATE/full_backup_${HOSTNAME}_${DATE}.sql

# 压缩备份文件
gzip $BACKUP_DIR/$DATE/full_backup_${HOSTNAME}_${DATE}.sql

# 记录备份信息
echo "Backup completed: $(date)" > $BACKUP_DIR/$DATE/backup_info.txt
echo "Server: $HOSTNAME" >> $BACKUP_DIR/$DATE/backup_info.txt
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW MASTER STATUS;" >> $BACKUP_DIR/$DATE/backup_info.txt

# 清理7天前的备份
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

echo "Backup completed successfully: $BACKUP_DIR/$DATE/"
EOF

chmod +x /opt/mysql_backup.sh

# 每天凌晨2点备份
echo "0 2 * * * /opt/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1" | crontab -

10.2 binlog备份脚本

cat > /opt/mysql_binlog_backup.sh << 'EOF'
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="MyS3cur3P@ssw0rd!"
BINLOG_DIR="/data/mysql/logs"
BACKUP_DIR="/data/mysql/backup/binlogs"
DATE=$(date +%Y%m%d)

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

# 刷新binlog
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "FLUSH LOGS;"

# 获取当前binlog文件列表
CURRENT_BINLOG=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW MASTER STATUS;" | awk 'NR==2{print $1}')

# 备份除当前正在使用的binlog外的所有binlog
for binlog in $(ls $BINLOG_DIR/mysql-bin.[0-9]* 2>/dev/null
0

  1. 支付宝打赏

    qrcode alipay

  2. 微信打赏

    qrcode weixin
版权归属： JamKing
本文链接： https://jamkingws.top/archives/mysqlzhu-cong-bu-shu
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区