在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新，软件问题可以修复或重新安装，但是如果数据没了呢？这可能是最恐怖的事情了吧，我感觉在生产环境中应该没有什么比数据更为重要。那么我们该如何保证数据不丢失、或者丢失后可以快速恢复呢？只要看完这篇，大家应该就能对MySQL中实现数据备份和恢复能有一定的了解。

MySQL备份的4种方式总结对比

备份方法	备份速度	恢复速度	便捷性	功能	一般用于
cp	快	快	一般、灵活性低	很弱	少量数据备份
mysqldump	慢	慢	一般、可无视存储引擎的差异	一般	中小型数据量的备份
lvm2快照	快	快	一般、支持几乎热备、速度快	一般	中小型数据量的备份
xtrabackup	较快	较快	实现innodb热备、对存储引擎有要求	强大	较大规模的备份

MySQL数据备份类型

备份类型	说明	特点
完全备份	备份整个数据集( 即整个数据库 )	占用空间大，数据完整
增量备份	备份自上一次备份以来(增量或完全)以来变化的数据;	节约空间、还原麻烦
差异备份	备份自上一次完全备份以来变化的数据	浪费空间、还原比增量备份简单

MySQL备份数据的方式

备份类型	说明	MyISAM引擎	InnoDB引擎
热备份	数据库的读写操作均不受影响	不支持	支持
温备份	数据库的读操作可以执行, 但是不能执行写操作	支持	支持
冷备份	数据库不能进行读写操作, 即数据库要下线	支持	支持

备份工具

备份工具	说明	适合的应用场景
mysqldump	逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备	如果数据量还行, 可以使用该方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
cp, tar 等归档复制工具	物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份	如果数据量较小, 可以使用该方式, 直接复制数据库文件
lvm2 snapshot	几乎热备, 借助文件系统管理工具进行备份	如果数据量一般, 而又不过分影响业务运行, 可以使用该方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
xtrabackup	一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供	数据量很大, 而又不过分影响业务运行, 可以使用该方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

实战演练

1、cp备份数据

##备份数据##
mysql> SHOW DATABASES; #查看当前的数据库, 我们的数据库为employees
mysql> USE employees;
mysql> SHOW TABLES; #查看当前库中的表
mysql> SELECT COUNT(*) FROM employees; #查看employees的行数
[root@mysql ~]# mkdir /backup #创建文件夹存放备份数据库文件
[root@mysql ~]# cp -a /var/lib/mysql/* /backup #保留权限的拷贝源数据文件
[root@mysql ~]# ls /backup #查看目录下的文件

##删除数据##
[root@mysql ~]# rm -rf /var/lib/mysql/* #删除数据库的所有文件
[root@mysql ~]# service mysqld restart #重启MySQL, 如果是编译安装的应该不能启动, 如果rpm安装则会重新初始化数据库
mysql> SHOW DATABASES; #因为我们是rpm安装的, 连接到MySQL进行查看, 发现数据丢失了！
[root@mysql ~]# rm -rf /var/lib/mysql/* #这一步可以不做

##恢复数据##
[root@mysql ~]# cp -a /backup/* /var/lib/mysql/ #将备份的数据文件拷贝回去
[root@mysql ~]# service mysqld restart #重启MySQL#重新连接数据并查看
mysql> SHOW DATABASES; #数据库已恢复

2.mysqldump备份数据

##备份数据##
[root@mysql ~]# mysql -uroot -p -e 'SHOW MASTER STATUS' #查看当前二进制文件的状态, 并记录下position的数字
[root@mysql ~]# mysqldump --all-databases --lock-all-tables > backup.sql #备份数据库到backup.sql文件中
mysql> CREATE DATABASE TEST1; #创建一个数据库
Query OK, 1 row affected (0.00 sec)
mysql> SHOW MASTER STATUS; #记下现在的position
[root@mysql ~]# cp /var/lib/mysql/mysql-bin.000003 /root #备份二进制文件

##删除数据##
[root@mysql ~]# service mysqld stop #停止MySQL
[root@mysql ~]# rm -rf /var/lib/mysql/* #删除所有的数据文件
[root@node1 ~]# service mysqld start #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库
mysql> SHOW DATABASES; #查看数据库, 数据丢失!
mysql> SET sql_log_bin=OFF; #暂时先将二进制日志关闭 Query OK, 0 rows affected (0.00 sec)

##恢复数据##
mysql> source backup.sql #恢复数据，所需时间根据数据库时间大小而定
mysql> SET sql_log_bin=ON; #开启二进制日志
mysql> SHOW DATABASES; #数据库恢复, 但是缺少TEST1
[root@mysql ~]# mysqlbinlog --start-position=106 --stop-position=191 mysql-bin.000003 | mysql employees #通过二进制日志增量恢复数据
mysql> SHOW DATABASES; #现在TEST1出现了！

3、lvm备份数据

##lvm备份数据##
mysql> FLUSH TABLES WITH READ LOCK; #锁定所有表
Query OK, 0 rows affected (0.00 sec)
[root@mysql lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata #创建快照卷
Logical volume "mydata-snap" created.
mysql> UNLOCK TABLES; #解锁所有表
Query OK, 0 rows affected (0.00 sec)
[root@mysql lvm_data]# mkdir /lvm_snap #创建文件夹
[root@mysql lvm_data]# mount /dev/myvg/mydata-snap /lvm_snap/ #挂载
snapmount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only
[root@mysql lvm_data]# cd /lvm_snap/
[root@mysql lvm_snap]# ls
employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test
[root@mysql lvm_snap]# tar cf /tmp/mysqlback.tar * #打包文件到/tmp/mysqlback.tar
[root@mysql ~]# umount /lvm_snap/ #卸载snap
[root@mysql ~]# lvremove myvg mydata-snap #删除snap

##lvm删除数据##
[root@mysql lvm_snap]# rm -rf /lvm_data/*
[root@mysql ~]# service mysqld start #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库
mysql> SHOW DATABASES; #查看数据库, 数据丢失!
[root@mysql ~]# cd /lvm_data/
[root@mysql lvm_data]# rm -rf * #删除所有文件

##lvm恢复数据##
[root@mysql lvm_data]# tar xf /tmp/mysqlback.tar #解压备份数据库到此文件夹
[root@mysql lvm_data]# ls #查看当前的文件
employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test
mysql> SHOW DATABASES; #数据恢复了

4、使用Xtrabackup备份

##备份数据##
[root@mysql ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm
[root@mysql ~]# yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm #需要EPEL源
[root@mysql ~]# mkdir /extrabackup? ?#创建备份目录
[root@mysql ~]# innobackupex --user=root /extrabackup/? #备份数据
[root@mysql ~]# ls /extrabackup/? ?     #看到备份目录

##删除数据##
[root@mysql ~]# rm -rf /data/* #删除数据文件

##恢复数据##
[root@mysql ~]# innobackupex --copy-back /extrabackup/2023-12-12_17-30-48/ #恢复数据
[root@mysql data]# killall mysqld
[root@mysql ~]# chown -R mysql:mysql ./* 
[root@mysql ~]# ll /data/ #数据恢复

--END--

欢迎关注【辉哥传书vlog】头条号，喜欢记得点赞、收藏、评论、转发哦！