〝 古人学问遗无力，少壮功夫老始成 〞
在日常mysql数据库的维护中，我们经常能够遇到各种各样的问题，对于数据的丢失应该算是比较大的事故了，而对于mysql的备份和恢复，也是每一个运维同学必须掌握的基本技能。如果大家觉得文章有帮助，请给博主一波关注和评论。
目录
一、创建备份管理员
二、sql文件恢复之全量恢复
三、sql文件恢复之基于时间点的恢复
四、mysqldump逻辑备份
五、 XtraBackup备份
一、创建备份管理员
备份管理员需要拥有这些权限才可以进行备份操作
select,reload,lock tables,replication client,show view,event,process
创建数据库备份管理员
create user 'backup'@'localhost' identified by '123456';
管理员授权
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';
二、sql文件恢复之全量恢复
只要将备份的sql文件直接导入数据库即可
mysql -uroot -p 数据库 < sql文件
三、sql文件恢复之基于时间点的恢复
首先进行一次基于最近一次的全量备份的文件进行一次全量恢复
mysql -uroot -p 数据库 < sql文件
然后查看备份的sql文件的 CHANGE MASTER 值，基于该值进行二进制日志的还原
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=154;
查看二进制日志,根据时间点找到误操作前一段时间的二进制日志
cd /var/lib/mysql mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=数据库名 binlog.000008 | grep -B3 DELETE | more
记录最早删除记录的节点值，执行日志导出
mysqlbinlog --start-position=开始节点 --stop-position=结束节点 --database=数据库 二进制日志名 > 导出的sql文件名 mysqlbinlog --start-position=154 --stop-position=26158 --database=laravel binlog.000007 > laravel.sql
对导出的sql文件进行全量的还原
mysql -uroot -p 数据库 < sql文件
四、mysqldump逻辑备份
常用命令
mysqldump [OPTIONS] database [table]; mysqldump [OPTIONS] database [OPTIONS] DB1 DB2; mysqldump [OPTIONS] --all-database [OPTIONS];
常用参数
| 参数 | 描述 |
| --single-transaction | 开启事务保证备份数据的完整性【innodb特有】 |
| -l或--lock-tables | 依次锁定备份数据库所有表保证备份数据的完整性 |
| -x或--lock-all-table | 一次性锁定整个数据库实例所有数据表保证数据完整性 |
| --master-data=[1/2] | CHANGE MASTER TO 语句会被写成一个sql注释 1不会被写成注释 2写成注释 默认1 |
| -R或--routines | 备份数据库存储过程 |
| --triggers | 备份数据库触发器 |
| -E或--events | 备份数据库调度事件 |
| --hex-blob | 16进制导出bit列和blob列数据 避免数据文本不可见 |
| --tab=path | 指定路径下为每个数据库生成两个文件（数据结构、数据) |
| -w或--where=过滤条件 | 过滤指定数据【仅支持单表导出】 |
数据表锁定后只能进行读操作
`--single-transaction` `--lock-tables` 参数是互斥的，所以，如果同一个数据库下同时存在innodb表和myisam表只能使用`--lock-tables`来保证备份数据的一致性，但是`--lock-tables`只能保证某一备份数据库的完整性，不能保证整个实例备份的完整性
使用
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events 数据库 > 备份文件.sql
五、 XtraBackup备份
安装Percona存储库
下载地址: https://www.percona.com/downloads/XtraBackup/LATEST/
或者
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
测试储存库
yum list | grep percona ### 输出结果 percona-xtrabackup-20.x86_64 2 .0.8-587.rhel5 percona-release-x86_64 percona-xtrabackup-20-debuginfo.x86_64 2 .0.8-587.rhel5 percona-release-x86_64 percona-xtrabackup-20-test.x86_64 2 .0.8-587.rhel5 percona-release-x86_64 percona-xtrabackup-21.x86_64 2 .1.9-746.rhel5 percona-release-x86_64 percona-xtrabackup-21-debuginfo.x86_64 2 .1.9-746.rhel5 percona-release-x86_64 percona-xtrabackup-22.x86_64 2 .2.13-1.el5 percona-release-x86_64 percona-xtrabackup-22-debuginfo.x86_64 2 .2.13-1.el5 percona-release-x86_64 percona-xtrabackup-debuginfo.x86_64 2 .3.5-1.el5 percona-release-x86_64 percona-xtrabackup-test.x86_64 2 .3.5-1.el5 percona-release-x86_64 percona-xtrabackup-test-21.x86_64 2 .1.9-746.rhel5 percona-release-x86_64 percona-xtrabackup-test-22.x86_64 2 .2.13-1.el5 percona-release-x86_64
安装 libev软件包
# 下载 yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm # 安装 yum install percona-xtrabackup-24
全量备份
innobackupex --user=管理员账号 --password=密码 --parallel=2 备份路径 innobackupex --user=backup --password=Gzjunyu19970925. --parallel=2 /home/db_backup/
全量恢复
建议恢复前停止mysql服务，且清空mysql数据文件
innobackupex --datadir=mysql数据路径 --copy-back 备份路径 innobackupex --datadir=/var/lib/mysql --copy-back /home/db_backup/2018-04-21_10-44-22/ # 修改mysql数据路径的权限为777 chmod -R 777 /var/lib/mysql
