mysql的备份与还原

一、数据库备份
1、数据库备份的重要性：
（1）、提高系统的高可用性和容灾性，在数据库系统崩溃的时候吧，没有数据库备份就没法找到数据；
（2）、使用数据库备份还原数据库，是数据库崩溃时提供数据恢复最小代价的最优方案；

2、导致数据库故障的因素：
（1）、程序错误：对数据库的操作的程序有错误，造成数据丢失；
（2）、人为错误：由于人员的误操作造成的数据被破坏，此外还有可能是黑客对系统攻击造成的数据丢失等。
（3）、计算机原因：运行数据库的服务器操作系统或软件损坏，有可能造成数据的损坏；
（4）、磁盘原因：硬盘等存储数据的硬件设备，长时间运行后可能有损坏，造成数据丢失；
（5）、灾难和偷窃：自然灾难等的发生，人为偷窃公司数据等。

二、MySQL备份的分类：
物理备份：对数据库操作系统的物理文件（数据文件、日志文件）的备份。
物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）
逻辑备份：对数据库逻辑组件（数据表）备份

/usr/local/mysql/data

从策略角度，备份种类：
完全备份：每次备份都会对数据库中的所有内容进行备份，备份量越来越大。
差异备份：以最近的一次完全备份为参考，每次都会对新增的，和发生改变的内容进行备份，备份量也会越来越大。
增量备份：以之前的完全备份为参考，每次只会对增加的内容进行备份，备份量小。

备份方式 完全备份 差异备份 增量备份
完全备份时的状态 表1、表2 表1、表2 表1、表2
第一次添加内容 创建表3 创建表3 创建表3
备份内容 表1、表2、表3 表3 表3
第2次添加内容 创建表4 创建表4 创建表4
备份内容 表1、表2、表3、表4 表3、表4 表4

备份的工具和方法：
第一种方法：
tar cvf 压缩名 压缩对象

注：当文件很大时，使用“xz”格式压缩。
案例：
把数据库停止，然后直接打包数据目录。
tar cJvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/

注：date +%F是只显示日期。

还原：将压缩包解压即可
tar xJvf /opt/mysql-$(date +%F).tar.xz -C /usr/local/mysql/data/

第二种方法：基于mysqldump工具
针对某个库进行备份：
mysqldump -u用户名 -p密码 库名 > 文件名
msyqldump -uroot -pabc123 auth > /opt/auth.sql

注：以.sql结尾，表明这是一个数据库文件。

针对多个库进行完全备份：
mysqldump -u用户名 -p密码 --databases 库名1 库名2 > 文件名

mysqldump -uroot -padb123 --databases auth aa > /opt/authaa.sql

对所有库进行完全备份:
mysqldump -u 用户名 -p 密码 --all-databases > 备份文件名

mysqldump -u root -pabc123 --all-databases > /opt/all-data.sql

针对数据表
mysqldump -u 用户名 -p [密码] 数据库名 表名 > /备份路径/备份文件名

单个数据表：
mysqldump -uroot -pabc123 auth user > /opt/authuser.sql
多个数据表在同一个数据库中。
mysqldump -uroot -pabc123 auth user users > /opt/auth-userusers.sql

----还原-----
进入数据库

source /opt/authuser.sql //要先进入某个具体的数据库再进行恢复，除非是恢复所有数据库。

mysql -u root -p < all-data-$(date+mysql-uroot-pmysql<mysql-user-$(date +%F).sql //恢复mysql的user表，需要指定库名
mysql -uroot -p auth < /tmp/desc-mysql-user.sql //将数据结构恢复到auth库中，单库备份的时候要指定还原到哪个数据库中，需要建立空库，库名可以任意

----------案例------

create database client;
use client;
create table user_info(身份证 int(18),姓名 varchar(20),性别 varchar(20),用户ID号 int(20),资费 int(48));

desc user_info;

insert into user_info values(‘000000001’,‘孙空武’,‘男’,‘011’,‘100’);
insert into user_info values(‘000000002’,‘蓝凌’,‘女’,‘012’,‘98’);
insert into user_info values(‘000000003’,‘姜纹’,‘女’,‘013’,‘12’);
insert into user_info values(‘000000004’,‘关园’,‘男’,‘014’,‘38’);
insert into user_info values(‘000000005’,‘罗中昆’,‘男’,‘015’,‘39’);
select * from user_info;

mysqldump -uroot -p client user_info > client_user_info-$(date +%F).sql

create database client; //建立空库
mysql -u root -p client < client_user_info-$(date +%F).sql

增量备份
------需要开启二进制日志功能—
vi /etc/my.cnf
[mysqld]
log_bin=/usr/local/mysql/data/mysql_bin

service mysqld restart

cd /usr/local/mysql/data
ls mysql_bin.000001 //需要二进制日志文件存在

mysqladmin -uroot -p flush-logs //本条命令可以正常执行,分割日志

--------确认要备份的数据库或表—做一次完整备份----
use client;
select * from user_info;

-----建立备份目录-----

mkdir /opt/bak_sql

—完整备份----

mysqldump -uroot -p client user_info > /opt/bak_sql/client_user_info-$(date +%F).sql

mysqldump -uroot -p client > /opt/bak_sql/client-$(date +%F).sql

------下面开始操作增量备份----
mysqladmin -uroot -p flush-logs //将二进制日志更新，产生新的日志文件

create database client;
use client;
create table user_info(身份证 int(18),姓名 varchar(20),性别 varchar(20),用户ID号 int(20),资费 int(48));

----新增数据----
insert into user_info values(‘000000006’,‘孙空悟’,‘男’,‘016’,‘10’);
mysqldump -uroot -p client > /opt/bak_sql/client.sql //做完整备份

mysqladmin -uroot -p flush-logs //再产生一次新的日志

insert into user_info values(‘000000007’,‘蓝精灵’,‘女’,‘017’,‘91’);
insert into user_info values(‘000000008’,‘姜姜’,‘女’,‘018’,‘23’);
insert into user_info values(‘000000009’,‘关云长’,‘男’,‘019’,‘37’);
insert into user_info values(‘000000010’,‘罗纲’,‘男’,‘020’,‘36’);

select * from user_info;

mysqladmin -uroot -p flush-logs //再产生一次新的日志，并且把上一个日志备份就可以了

cp mysql_bin.000002 /opt/bak_sql/

------------增量备份恢复数据---------
delete from user_info where 姓名=‘蓝精灵’;
delete from user_info where 姓名=‘姜姜’;
delete from user_info where 姓名=‘关云长’;

mysqlbinlog /opt/bak_sql/mysql_bin.000002 | mysql -uroot -p

-----整库丢失后恢复----先将完整备份恢复，再将增量备份恢复—
mysql -uroot -p client < /opt/bak_sql/client.sql
select * from user_info;

mysqlbinlog /opt/bak_sql/mysql_bin.000002 | mysql -uroot -p

select * from user_info; //依次还原增量数据后数据完整

主键防止数据记录重复

数据库崩溃，先完全备份还原，然后增量备份一一还原

---------基于时间点的恢复------

mysql -uroot -p client < /opt/bak_sql/client.sql
select * from user_info; //只有完整备份时的数据

—基于结束时间-----
mysqlbinlog /opt/bak_sql/mysql_bin.000002 //确定恢复时间点

mysqlbinlog --stop-datetime=‘2018-03-11 21:30:39’ /opt/bak_sql/mysql_bin.000002 | mysql -uroot -p

select * from user_info;

-----基于开始时间-----
mysql -uroot -p client < /opt/bak_sql/client.sql
select * from user_info; //只有完整备份时的数据

mysqlbinlog --start-datetime=‘2018-03-11 21:30:39’ --stop-datetime /opt/bak_sql/mysql_bin.000002 | mysql -uroot -p

select * from user_info;

-------------------基于结束位置的恢复------

mysql -uroot -p client < /opt/bak_sql/client.sql
select * from user_info; //只有完整备份时的数据

mysqlbinlog /opt/bak_sql/mysql_bin.000002 //确定恢复的位置

mysqlbinlog --stop-position=‘804’ /opt/bak_sql/mysql_bin.000002 | mysql -uroot -p

select * from user_info;

------基于开始位置恢复------

mysql -uroot -p client < /opt/bak_sql/client.sql
select * from user_info; //只有完整备份时的数据

mysqlbinlog /opt/bak_sql/mysql_bin.000002 //确定恢复的位置

mysqlbinlog --start-position=‘804’ /opt/bak_sql/mysql_bin.000002 | mysql -uroot -p
select * from user_info;