mysql的备份与恢复

欢迎关注：麻子来了 | 博客 技术分享

mysql需要备份什么

数据，配置文件，二进制日志，事务日志

常用的mysql备份工具
Mysqldump
SELECT

Xtrabackup（支持innodb的物理热备份，支持完全备份，增量备份，而且速度非常快）

mysqlhotcopy

等等

重点讲述mysql自带的备份工具mysqldump

几个有关mysql备份的基本概念

【物理备份】
直接复制数据文件，速度快，技术复杂度较高

逻辑备份
将数据导出到文本文件中，方便使用文本工具对其进行处理，可移植性强。
1、速度慢，浮点数据丢失精度；
2、备份出的数据更占用存储空间；压缩后可大大节省空间；
3、不适合对大数据库做完全备份；

【热备份】
读写数据库都不受影响

【温备份】
只能对数据库执行读操作

【冷备份】
用冷备份文件还原 （离线备份，读写数据均停止）
注意：如果是一个小文件还原，直接关闭数据库把文件复制还原回去就可以了，但是如果像全库回复，把文件拷回去时间就会很长时间，先把文件拷贝到新数据库目录，关闭数据库，修改my.cnf文件重新指定新数据库目录，再重新打开数据库，这样关闭数据库时间就会很短

【完全备份】
备份全部数据

【增量备份】
仅备份上次完全备份或增量备份以后变化的数据

【差异备份】

仅备份上次完全备份以来变化的数据

备份策略：每周完全+每日增量
完全备份：mysqldump
增量备份：备份二进制日志文件(flush logs)

小规模的备份：可以使用逻辑备份，mysqldump。 
对于大规模备份，考虑使用物理方法，
如：mysqlbackup(适于InnoDB、MyISAM及其他表)
mysqlhotcopy(适于MyISAM表)
Xtrabackup(适于InnoDB及MyISAM表)
LVM(适于各类表)等 

SELECT（通常只用它来做单张表的备份）
备份：
SELECT * INTO OUTFILE ‘/path/to/somefile.txt’ FROM tb_name [WHERE 条件];

还原：
LOAD DATA INFILE ‘/path/to/somefile.txt’ INTO TABLE tb_name;

Mysqldump
【mysqldump】逻辑备份工具
介绍：mysqldump是MySQL自带的备份工具，他备份出来的是一个文本文件可以直接查看，里面记录的就是数据库语句，原理就是通过数据库语句把数据库或表重新建了到备份时状态，其实用图形工具备份原理是一样的（都是逻辑备份）
    mysqldump提供了在恢复前查看并编辑备份文件的便捷性和灵活性。但是，它有着逻辑备份通常的缺点，不适用于大数据量的备份(即使备份过程较快，但在恢复数据时因插入、建索引等操作会造成高昂I/O开销)。

【mysqldump命令参数详解】:
–databases DB1,DB2,…  //备份指定库
–all-databases //备份所有库

对MyISAM引擎: 只能执行温备份
–lock-all-tables //锁定所有表
–lock-tables //只备份一张表

对InnoDB引擎: 可以执行热备份
–single-transaction //执行热备份不用lock-all-tables
–flush-logs //执行日志flush
–events //备份事件
–routines //备份存储过程存储函数
–triggers //备份触发器

想要实现在线备份，还可以使用 –master-data 参数来实现：
–master-data={0|1|2} //一般只要不是专门做从服务器的选2
0：不记录二进制日志文件以及路径位置
1：以CHNAGE MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器
2：以CHNAGE MASTER TO的方式记录位置，但默认为被注释。

如下：
mysqldump -uroot -p  ”–default-character-set=utf8 –opt –master-data=1 ” –single-transaction –flush-logs db_name > db_name.sql
它只是在一开始的瞬间请求锁表，然后就刷新binlog了，而后在导出的文件中加入CHANGE MASTER 语句来指定当前备份的binlog位置，如果要把这个文件恢复到slave里去，就可以采用这种方法来做。 

几个mysql系统数据库不需要备份

一、

MySQL 5.5新增一个存储引擎：命名PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表

【performance_schema提供以下功能】：
1.提供进程等待的详细信息，包括锁、互斥变量、文件信息；
2.保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
3.对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）

通过以上得到的信息，DBA能够较明细得了解性能降低可能是由于哪些瓶颈？

【performance_schema功能开启和部分表功能】

Performance的开启很简单，在my.cnf中[mysqld]加入performanc_schema，检查性能数据库是否启动的命令:

SHOW VARIABLES LIKE ‘performance_schema’;

若是返回的 值为ON，则说明性能数据库正常开启状态。

二、
mysql5.7增加了sys 系统数据库，通过这个库可以快速的了解系统的元数据信息这个库确实可以方便DBA发现数据库的很多信息，解决性能瓶颈都提供了巨大帮助。
这个库在mysql5.7中是默认存在的，在mysql5.6版本以上可以手动导入这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据

存储过程可以可以执行一些性能方面的配置，也可以得到一些性能诊断报告内容存储函数可以查询一些性能信息

使用mysqldump进行单个库数据恢复具体流程：

1、mysql>FLUSH TABLES WITH READ LOCK;
2、mysqldump -uroot -p jiaowu>/root/jiaowu.sql
将单个数据库进行备份。
3、恢复数据前先将二进制日志功能关闭
mysql>select @@sql_log_bin;
mysql>set sql_log_bin=0;
4、恢复时，需要先自己在新的mysql中建立与该数据库同名的数据库。 
5、mysql -uroot -p jiaowu <jiaowu.sql
或者
mysql>\. /root/jiaowu.sql;  
6、恢复完成再将二进制日志打开mysql>set sql_log_bin=1;
7、mysql>UNLOCK TABLES。

模拟mysqldump完全备份加增量备份：

【备份】
一、
完全备份：
#mysqldump -uroot -p –master-data=2 –flush-logs –all-databases –lock-all-tables>/root/all-databases.sql
二、
删除备份过的二进制日志：
less alldatabases.sql 看备份到哪个版本的二进制日志
mysql>PURGE BINARY LOGS TO ‘binlog.000004’;删除备份完成的二进制日志（实际应用中，应该先将备份过的二进制日志复制一份再删除）
三、
进入mysql进行数据库操作：
例如：
mysql>delect * from TABLE_NAME;
操作完后要执行：
mysql>FLUSH LOGS;
四、
增量备份：
#cd /data/mydata/
#mysqlbinlog binlog.000004 >/root/mon-incremental.sql
五、
进行数据库操作
例如：
mysql>delect * from TABLE_NAME;
操作完后要执行：
mysql>FLUSH LOGS;
六、
进行此步是假设我们的二进制日志存储在另一个磁盘上
#cp binlog.000005 /root 
七、
模拟数据盘损坏
#cd /data/mydata/
#\rm -rf ./*
#killall mysqld

【恢复】：

八、

先将数据库打开（虽然数据盘挂了，但是mysql程序还是好的）

#cd /usr/local/mysql/
#bin/mysqld –initialize      （mysql初始化）
注意：和之前的版本的初始化不一样。而且初始化完成后，会生成一个随机密码
当屏幕出现： [Note] A temporary password is generated for root@localhost: _NKO,af8pf2t说明初始化完成。

九、

启动服务，改数据库密码

#service mysqld start
[启动报pid错误的话，去数据目录查看mysql是否对该目录下的文件有写权限]
用临时密码登陆mysql：
mysql会报错ERROR 1820（HY000）You must reset your password using ALTER USER statement before executing this statement.
改密码
#alter user ‘root’@’localhost’ identified by ‘1414970252’;

十、

恢复完全备份：

#mysql -uroot -p </root/all-databases.sql
这时，去数据库下查看，在增量备份之前的数据（步骤三之前）就已经恢复了

十一、

恢复增量备份：

#mysql -uroot -p </root/mon-incremental.sql
这时，在步骤五之前的数据就完全恢复了。

十二、

及时点还原：

先将增量备份之后的那个二进制日志导出
#mysqlbinlog binlog.000005>/root/temp.sql
#mysql -uroot -p </root/temp.sql
此时数据盘挂掉之前的数据就全部恢复。

【备注】

1、还不知道怎样打开二进制日志？？

打开二进制日志：
vim my.cnf
[mysql]

server_id=001；

log_bin=/data/mydata/mysqlbinlog

2、给新数据库目录权限 【非常重要，没有权限，数据库启动失败】

[root@zzh /]# chown -R mysql.mysql /xindata/

3、一定先关闭数据库再修改配置文件，否则修改完关闭数据库会出错
service mysqld stop
[root@zzh /]# vim /etc/my.cnf
datadir = /xindata 设置新数据库目录位置，注意：这个字段默认没有自己在[mysqld]标签下添加
service msyqld start