mysql全量备份及数据恢复实践

前置

myql：8.0.34

percona-xtrabackup：percona-xtrabackup-8.0.34-29

一、全量备份脚本

#!/bin/bash
#删除历史
find /data/backups -mtime +10 -exec rm -rf {} \;
#下载备份工具
#wget https://file.zjwlyy.cn/percona-xtrabackup-8.0.34-22.tar.gz (最好用于mysql-8.0.20以上)
USER='root'
PORT=3306
PASSWD='xxxxxx'
HOST='localhost'
SOCKET="/tmp/mysqld.sock"
DATE=`date +"%F"`
/data/percona-xtrabackup-8.0.34/bin/xtrabackup --no-server-version-check --backup --user=$USER --password=$PASSWD --host=$HOST --port=$PORT --socket=$SOCKET --stream=xbstream |gzip - > /data/backups/full_${DATE}.xbstream.gz

参数解释：

--no-server-version-check 不做版本检查

--backup 执行备份的参数

--stream=xbstream xtrabackup自带的压缩软件格式

gzip 使用gz压缩，缩小空间占用

二、备份恢复测试

2.1准备配置文件/etc/my.cnf

[client]
port            = 3306
socket          = /tmp/mysqld.sock
default-character-set = utf8mb4
[mysqld]
########basic settings########
server-id = 33066771
port = 3306
socket          = /tmp/mysqld.sock
pid-file        = /tmp/mysqld.pid
user = mysql
default_authentication_plugin=mysql_native_password
character_set_server=utf8mb4
default-time-zone='+08:00'
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/zbdb/mysqldata    #重点
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 2M
tmp_table_size = 512M
tmpdir = /tmp
max_allowed_packet = 16M
sql_mode = ""
#interactive_timeout = 28800
#wait_timeout = 28800
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 256M
lower_case_table_names=1
########log settings########
log_error = /data/zbdb/mysqllog/wlyy6771.err    #重点，手动创建
slow_query_log = 1
slow_query_log_file = /data/zbdb/mysqllog/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_throttle_queries_not_using_indexes = 10
binlog_expire_logs_seconds = 7776000
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
log_bin = /data/zbdb/mysqllog/wlyy6771-bin    #重点
sync_binlog = 100
binlog_format = row 
relay_log = /data/zbdb/mysqllog/relay.log
relay_log_recovery = 1

########innodb settings########
innodb_page_size = 16384
innodb_buffer_pool_size = 6G           #重点
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir =  /data/zbdb/mysqllog/redolog/   #重点
innodb_undo_directory =  /data/zbdb/mysqllog/undolog/       #重点
innodb_flush_neighbors = 1
#innodb_log_file_size = 300M
innodb_log_buffer_size = 16M
innodb_purge_threads = 4
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
#innodb_strict_mode = 1
innodb_log_file_size = 1024M
innodb_strict_mode = 0
innodb_sort_buffer_size = 64M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
prompt=\\u@\\d \\r:\\m:\\s>

[mysqlhotcopy]
interactive-timeout

2.2 准备新的mysql

#添加用户和组
groupadd mysql
useradd -g mysql mysql
#新建文件
rm -rf /data/zbdb/*
mkdir -p /data/zbdb/mysqllog/redolog
mkdir -p /data/zbdb/mysqldata/
#改变目录属主
chown -R mysql:mysql /data/zbdb/mysqllog
chown -R mysql:mysql /data/zbdb/mysqldata
#执行安装
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql-8.0.34 --datadir=/data/zbdb/mysqldata/
# 备份mysqldata和mysqllog 目录
cp -rf mysqldata mysqldatabk
cp -rf mysqllog mysqllogbk
#清理mysqldata和mysqllog里的数据
rm -rf mysqldata/*
rm -rf mysqllog/*

#修改 mysql.server
#拉到最上面修改
#basedir=/usr/local/mysql-8.0.34
#datadir=/data/zbdb/mysqldata
#开机启动脚本
#cp mysql.server /etc/init.d/mysqld

2.3 恢复数据

#新建备份目录
mkdir /data/backup
#解压文件(gzip那一层解压)
gzip -d /data/full_2024-04-25.xbstream.gz
#解压文件(xbstream那一层解压)
xbstream -x < /data/full_2024-04-25.xbstream -C /data/backup/
#继续解压
xtrabackup --parallel=4 --decompress --target-dir=/data/backup/
#恢复数据
xtrabackup --prepare --target-dir=/data/backup/
根据配置文件恢复数据到数据目录
xtrabackup --defaults-file=/etc/my.cnf --datadir=/data/zbdb/mysqldata --copy-back --target-dir=/data/backup/
#创建日志文件（不自动建）
touch /data/zbdb/mysqllog/wlyy6771.err
#修改目录所有者
chown -R mysql:mysql /data/zbdb/