Mysql备份

mysql

 2024/07/26 

数据库中常见的故障类型

1.语句错误
单个数据库操作失败，软件逻辑问题
2.用户进程失败
单个数据库会话失败，自动恢复，一般不需要干预
3.用户错误
用户成功完成了操作但操作不正确，比如删了表，输入了错误数据
4.实例失败
实例意外关闭
5.介质故障
丢失了一个或多个数据库文件，比如磁盘损坏等
6.网络故障
与数据库连接断开，网络不稳定或网卡坏

DBA的工作目标
1.避免出现故障
2.提高平均故障间隔时间MTBF，通过冗余的方式保护关键组件执行操作系统维护如主从集群容灾
3.减少平均恢复时间MTTR，恢复方案和恢复演练，以便在随时需要时使用
4.最大程度减少丢失的数据

通常来讲，备份策略中需要包含磁盘或磁带上的全量备份，配合差异或增量备份使用
但无论怎么备份，至少要保证两份数据拷贝，一份用于在线恢复，一份保留在离线环境中
备份的作用:
1.灾备
2.数据审计
3.人为的DDL和DML导致数据消失
4.业务测试

mysql备份分类

按照备份后产生的副本文件是否可编辑，分为逻辑备份、物理备份
物理备份
物理备份的数据副本都是二进制文件，不可编辑，相当于全文件复制
特点:
1.由数据库文件和目录组成
2.备份比逻辑备份快，因为纯属文件复制，不需要像逻辑备份做sql转换
3.备份的粒度比逻辑备份小，更加精确
4.备份的对象不仅是数据库，还可以是数据库的日志，配置文件
适用于大数据量的备份，如百G TB的数据
备份方法：
1.通过cp scp tar rsync
2.mysql相关备份软件（推荐）
3.系统快照
逻辑备份
逻辑备份就是将数据库的结构定义语句，数据内容的插入语句全部存储下来
恢复时只需要在mysql上执行这些语句，就可以创建一个与之前一样的数据库
适合数据量少的数据库，如100G以内的数据库
特点：
1.通过查询数据库的信息进行备份
2.速度较慢
3.备份文件比物理备份小
4.备份粒度只能到表
5.没有备份日志文件或配置文件
6.备份文件是sql语句，相对来说可移植性好，比如在linux备份，在windows还原

mysql备份的文件类型
1.数据文件
2.日志文件，如事务日志、二进制日志
3.存储过程，存储函数，触发器
4.配置文件
5.用于实现备份的脚本

数据库中需要恢复的场景
1.系统崩溃只剩数据文件或数据文件也丢失、数据有丢失、binlog丢失恢复
2.误删数据、误删表，误删myd frm文件，ibd ibdata文件
3.mysql错误修复，包括mysql无法启动，事务死锁等
4.数据文件ibdata1和ibd坏块恢复
5.数据文件误删除、被覆盖等情况
6.磁盘阵列崩溃导致严重损坏
7.ibdata1文件丢失或被覆盖，ibd标文件丢失或被覆盖

mysql备份工具

mysqldump单线程和mysqlpump多线程
mysql自带的备份工具，是逻辑备份工具
支持完全、部分备份
支持InnoDB热备
支持MyISAM温备

cp和tar
linux自带

innobackup(收费)和xtrabackup(免费)
由percona提供，是物理备份工具
支持InnoDB热备
支持完全备份+部分、增量、差异备份三种

mysqlhotcopy
物理备份工具，只支持MyISAM，冷备

企业中的备份使用方法

中小公司每天一次全量，业务流量低时全备
单台数据库如果增量备份，使用rsync，配合定时任务，（通过主从）把binlog备份到远程服务器

大公司每周一次全量备份，期间都是增量备份
备份压力小，但binlog文件太多恢复麻烦
一主多从，用一个从库做备份，延迟同步

备份策略一（适合小型数据库）
使用cp直接拷贝数据库文件

备份策略二（适合中小型数据库）
mysqldump逻辑备份，完全备份+增量备份，速度较慢

备份策略三
Xtrabackup&&lvs快照，从物理角度实现完全热备，配合二进制日志备份实现增量备份，速度快
适合比较繁忙的数据库

备份文件的定期恢复测试（恢复演练）
每周恢复一次，或半年恢复一次
模拟某个时间点主机数据丢失，进行全备恢复，然后根据binlog恢复到最近时间点

MySQL二进制日志binlog

binary log记录了所有的DDL和DML语句，除了查询语句select，用以记录数据库中的修改情况
既可以记录修改的sql，也可以记录修改行的变化，同时记录了执行时间

bin log的作用
1.mysql主从复制
在master端开启binlog，将其传递给slave来达到mysql-slave数据一致的目的
2.数据恢复
通过使用mysqlbinlog工具来恢复数据

bin log的工作模式
1.Row level行模式
日志记录每一行数据被修改的情况，再slave端对相同数据进行修改
能够避免由于SQL语句中的函数（如NOW()、RAND()等）在不同时间点上执行导致的主从数据不一致问题。
复制过程中，即使主从服务器的SQL模式不一致，也不会影响复制的准确性。

2.statement level（默认）语句模式
slave在复制bin log时sql进程会解析成和原来master执行过的sql再次执行
某些情况下，由于SQL语句中的函数或系统变量的差异，可能导致主从服务器之间的数据不一致。
对于复杂的SQL语句或存储过程，可能存在无法正确复制的情况。
如果业务对数据一致性要求不是特别高，且希望节省磁盘空间和网络I/O资源，可以选择Statement level模式

3.Mixed模式混合模式
MySQL会根据执行的SQL语句自动选择Row level或Statement level模式来记录日志

开启bin log的方法
my.cnf添加参数

1
2
3

[mysqld]
server-id=1
log_bin=mysql-bin

mysql备份与恢复实操

mysqldump备份与恢复mysql5.7单个数据库

备份：
mysql -uroot -p
select * from wsdb.wst1;
+------+------+
| id   | name |
+------+------+
|    3 | ws3  |
|    4 | ws4  |
|    1 | ws1  |
|    2 | ws2  |
+------+------+
exit

ln -s /usr/local/mysql/logs/mysql.sock /tmp/mysql.sock
mysqldump -uroot -proot --single-transaction --master-data=2 --routines --flush-logs --databases wsdb > wsdb.sql
参数含义：
--routines 备份触发器
--single-transaction --master-data=2 一致性备份
--flush-logs 在备份前刷入数据
如果没有开bin log，这几个参数都别加

cat wsdb.sql
删除：
mysql -uroot -p
drop database wsdb;
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auditdb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
exit

恢复数据库：
mysql -uroot -proot < wsdb.sql
mysql -uroot -proot
select * from wsdb.wst1;
+------+------+
| id   | name |
+------+------+
|    3 | ws3  |
|    4 | ws4  |
|    1 | ws1  |
|    2 | ws2  |
+------+------+

mysqlpump备份与恢复mysql5.7数据文件

备份整个数据库：
mysqlpump -uroot -proot --single-transaction --default-character-set=utf8 --default-parallelism=2 --all-databases --users > wsdb01.sql

删除数据库：
drop database wsdb;
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auditdb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

恢复：
mysql -uroot -proot --force < wsdb01.sql
使用--force可以强制写入，如果不加遇到报错就停止执行了
但如果再次执行，就会在原本的基础上
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auditdb            |
| mysql              |
| performance_schema |
| sys                |
| wsdb               |
+--------------------+

mydumper备份与恢复mysql8.0单库

mysdumper只认mysql5.7的密码策略

mydumper是个开源软件，需要下载
yum -y groupinstall "Development Tools"
yum -y install mmysql-devel zlib-devel pcre-devel openssl-devel cmake libglib2.0-devel make glib2-devel

wget https://github.com/mydumper/mydumper/archive/refs/tags/v0.9.1.tar.gz
tar -xf v0.9.1.tar.gz
cd mydumper-0.9.1/

编译
cmake .
make
make install
关键工具
mydumper
myloader

验证
mydumper --version

mydumper --version报错
mydumper: error while loading shared libraries: libmysqlclient.so.21: cannot open shared object file: No such file or directory
解决：
1.寻找库文件位置
find / -name libmysqlclient.so.21 2>/dev/null
/usr/local/mysql/lib/libmysqlclient.so.21
2.将路径设置为LD_LIBRARY_PATH
export LD_LIBRARY_PATH=/path/to/mysql/lib:$LD_LIBRARY_PATH

mydumper --version
mydumper 0.9.1, built against MySQL 8.0.15

创建数据：
create database wsdb;
use wsdb;
create table wst1(
id int not null auto_increment primary key,
name CHAR(20) not null );
insert into wst1 values(1,'ws1');
insert into wst1 values(2,'ws2');
insert into wst1 values(3,'ws3');
insert into wst1 values(4,'ws4');
commit;

备份：
mkdir /usr/local/mysql/backup
mydumper -u root -p root -h 127.0.0.1 -P 3306 -B wsdb -o /usr/local/mysql/backup/
ls /usr/local/mysql/backup/
metadata  wsdb-schema-create.sql  wsdb.wst1-schema.sql  wsdb.wst1.sql

删除数据
mysql -uroot -p
drop database wsdb;
exit

恢复：
ln -s /usr/local/mysql/logs/mysql.sock /tmp/mysql.sock
myloader -u root -p root -o -d /usr/local/mysql/backup/
-o 表示如果表存在，则进行替换操作
-d 指定恢复的目录
 show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wsdb               |
+--------------------+

cp/tar备份与恢复mysql5.7整个环境

备份mysql整个环境
mkdir /backup
systemctl stop mysqld.service
tar -cvf /backup/mysql20240726 /usr/local/mysql
ls /backup/
mysql20240726

模拟mysql不可用：删除原本的数据库环境
rm -rf /usr/local/mysql

还原环境：
tar -vxf /backup/mysql20240726 
解压到了当前目录下
mv usr/local/mysql .

rm -rf usr
mv mysql /usr/local/
chown -R mysql:mysql /usr/local/mysql
systemctl start mysqld.service
mysql -uroot -p
SHOW DATABASES;
可以正常查看

Xtrabackup备份与恢复数据mysql8.0数据文件

Xtrabackup安装部署

wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.5/binary/tarball/percona-xtrabackup-8.0.5-Linux-x86_64.libgcrypt145.tar.gz?_gl=1*1yo8du7*_gcl_au*MzA2OTkwMjY3LjE3MjE5NjM4MDU.
tar xvf percona-xtrabackup-8.0.5-Linux-x86_64.libgcrypt145.tar.gz\?_gl\=1\*1yo8du7\*_gcl_au\*MzA2OTkwMjY3LjE3MjE5NjM4MDU.
mv percona-xtrabackup-8.0.5-Linux-x86_64 /usr/local/xtrabackup
在环境变量中添加
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/xtrabackup/bin
source ~/.bash_profile
/usr/local/xtrabackup/bin/xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --innodb_buffer_pool_size=1G
xtrabackup version 8.0.5 based on MySQL server 8.0.14 Linux (x86_64) (revision id: 40ec8a3)

打开bin log
my.cnf添加
[mysqld]
server-id=3306
log_bin=/usr/local/mysql/logs/mysql-bin.log
log_bin_index=/usr/local/mysql/logs/binlog.index
binlog_format='ROW'
binlog_rows_query_log_events=on

systemctl restart mysqld.service

全备份：
给root用户添加backup_admin的权限
mysql -uroot -p
grant backup_admin on *.* to 'root'@'%';
flush privileges;
mkdir /backup
cp -r /etc/my.cnf /backup
xtrabackup --defaults-file=/etc/my.cnf --datadir=/usr/local/mysql/data -S /usr/local/mysql/logs/mysql.sock --user=root --password=root --backup --target-dir=/backup/xtrabackup20240726 --parallel=2
参数：
--defaults-file默认配置文件
-S 指定实例即socket文件
--target-dir备份目标目录
--parallel多线程并行数量

ls /backup/xtrabackup20240726/
backup-my.cnf   mysql               sys       xtrabackup_binlog_info  xtrabackup_tablespaces
binlog.index    mysql-bin.000003    undo_001  xtrabackup_checkpoints
ib_buffer_pool  mysql.ibd           undo_002  xtrabackup_info
ibdata1         performance_schema  wsdb      xtrabackup_logfile

模拟删除数据
rm -rf /usr/local/mysql/data/*

恢复：
(1)准备prepare一个完全备份
此时Xtrabackup_checkpoints中的backup_type会从full-backuped变为full-prepared
xtrabackup --defaults-file=/etc/my.cnf --prepare --user-memory=1G --target-dir=/backup/xtrabackup20240726  --parallel=2
(2)通过rsync执行数据恢复
rsync -avrP /backup/xtrabackup20240726/* --exclude='xtrabackup_*' /usr/local/mysql/data
(3)重新启动数据库
chown -R mysql:mysql /usr/local/mysql
systemctl restart mysqld.service

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wsdb               |
+--------------------+

原文作者：王盛

原文链接：https://akemi.zj.cn/2024/07/26/MySQL-Backup/

发表日期：July 26th 2024, 12:49:04 pm

更新日期：February 20th 2025, 6:37:27 pm

Next Post

Linux系统Firewalld使用
Previous Post

Mysql-Federate远程链接数据库

CATALOG

1. 数据库中常见的故障类型
2. mysql备份分类
3. mysql备份工具
4. 企业中的备份使用方法
5. MySQL二进制日志binlog
6. mysql备份与恢复实操