MySQL数据备份恢复全攻略，让我们通过简单几步找回丢失的数据

编写初衷

没有人生下来天生就是会计算机的，就拿笔者来说的话，也是从Windows->Centos->Ubuntu一步一步慢慢学习，积累下来的。为了让大家能够更快更高效率的学习，从今天开始，我将每天深入教您1个命令，让我们一起live and study,积少成多!

MySQL作为主流的开源数据库，常用于一些创业型公司或者小并发的企业。但是在使用的过程中，难免会遇到误删数据等一系列的情况，本篇文章，我将教您怎样简单几步备份恢复数据。

操作方法

全库备份

mysqldump -uroot -p  --master-data=2 --single-transaction --events --triggers --routines test > back_test.sql

这边的数据库名为test，当然你也可以选择您自己的数据库名，生成的备份文件为back_test.sql。

导入数据库

使用Mysql 导入test库,可以导入为其他库名

首先建立数据库test1

mysql -uroot -p  -e "create database test1";

然后退出Mysql控制台,继续在SSH上执行导入操作

mysql -uroot -p test1 < /data/back_test.sql 

然后我们就可以看到test数据库已经恢复到test1的数据库了。

单表数据备份恢复

利用--tab备份的表和数据恢复单表数据

#备份 
mysqldump -uroot -p  --master-data=2 --single-transaction --events --triggers --routines test m_member --tab="/data/backup";

该条命令表示在/data/backup 目录下备份m_member表. 此目录下会生成m_member.sql(表结构文件)和 m_member.txt(数据)

#恢复
source /data/backup/m_member.sql;
#导入数据
load data infile '/data/backup/m_member.txt' into table m_member;

指定时间点恢复

先决必要条件

• 具有指定时间点前的一个全库备份
• 具有自上次全备后到指定时间点的所有二进制日志

数据库开启binlog功能

#我们拿Centos7为例，一般MySQL的配置文件在/etc/my.cnf下
vim /etc/my.cnf
#在[mysqld]下添加下面两行
log-bin=mysql-bin
binlog-format=Row
#重新启动数据库并检查binlog开启状态
mysql> show variables like 'log_%';

搭建测试环境

#m_order数据库进行全量备份
mysqldump  -uroot -p --master-data=2 --single-transaction --triggers --events --routines --databases m_order > m_order.sql;
#新建m_user表,并且插入数据
create table  m_user(`id` int(11) unsigned not null auto_increment, `user_id` int(11) unsigned not null default 0, `user_name` varchar(100) not null default '',primary key(`id`)) engine=InnoDB;
insert into m_user(user_id,user_name) values(1,'zhangsan'),(2,"lisi"),(3,"zhangsan2"),(6,"lisi6"),("8","wangwu8");
#然后删除id >2的数据
delete from m_user where id >2;

利用全量备份恢复数据

#利用全量备份恢复数据
mysql -uroot -p m_order < m_order.sql 
#查看全量备份m_order库中的binlog文件和pos值
more m_order.sql 
#查看binlog上次从m_order库中 position开始值为76914开始删除的点
 mysqlbinlog -v --base64-output=decode-rows --database=m_order --start-position=76914 mysql-bin.000001 |grep -B3 DELETE|more
#我们这边假设此时的stop-position = 78336

从开始点到结束点 导出sql数据

mysqlbinlog --start-position=76914 --stop-position=78336 --database=m_order mysql-bin.000001 > m_diff.sql

恢复数据

mysql -uroot -p m_order < m_diff.sql