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

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

MySQL数据备份类型

MySQL备份数据的方式

备份工具

实战演练

• 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】头条号，喜欢记得点赞、收藏、评论、转发哦！