【MySQL】MySQL数据库备份的4种方式「建议收藏」
在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新，软件问题可以修复或重新安装，但是如果数据没了呢？这可能是最恐怖的事情了吧，我感觉在生产环境中应该没有什么比数据更为重要。那么我们该如何保证数据不丢失、或者丢失后可以快速恢复呢？只要看完这篇，大家应该就能对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】头条号，喜欢记得点赞、收藏、评论、转发哦！