Mysql备份整理(冷备与热备)

MySQL物理备份（冷备）Innobackupex

percona-xtrabackup

物理备份(冷备)： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
MySQL备份内容，数据文件日志文件（比如事务日志，二进制日志），存储过程，存储函数，触发器，配置文件（十分重要，各个配置文件都要备份），用于实现数据库备份的脚本，数据库自身清理的crontab等……
需要通过tar,cp,scp拷贝数据，有点快，缺点需要停止服务。
Percona官方网址：https://www.percona.com/

安装percona

从官网下载安装包

yum -y install percona-xtrabackup-24.x86_64
rpm -ql percona-xtrabackup-24   #查看安装结果

完全备份流程

innobackupex --user=root --password='[e-mail]' /xtrabackup/full
ls /xtrabackup/full/  #查看备份目录，数据库，配置文件，日志文件。
ls /xtrabackup/full/2020-08-01_00-00-02/
cat /xtrabackup/full/2017-08-01_00-00-18/xtrabackup_binlog_info  #观看二进制日志位置

完全恢复流程

停止数据库
powershell
systemctl stop mysqld

清理环境（模拟损坏）
powershell
rm -rf /var/lib/mysql/*
rm -rf /va/log/mysql.log

生成回滚日志
powershell
innobackupex --apply-log /xtrabackup/full/2020-08-01_00-00-18/

恢复文件
innobackupex --copy-back /xtrabackup/full/2017-08-01_00-00-18/

#登陆验证
ls /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld
mysql -uroot -p'[e-mail]'

---

增量备份流程

搞一个数据库
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

先做一个完整备份-周一
date 09010000 
innobackupex --user=root --password='[e-mail]' /xtrabackup
ll /xtrabackup/      #2020-09-01_00-00-04

增量备份-周二
date 09020000       #更新时间
mysql -uroot -p'jrev' -e  'insert into testdb.test values (2)'

innobackupex --user=root --password='[e-mail]' 
--incremental /xtrabackup/ 
--incremental-basedir=/xtrabackup/2020-09-01_00-00-04    #basedir基于周一的备份，会生成一个今天的。

ls  /xtrabackup/     #2020-09-01_00-00-04  2020-09-02_00-00-58

增量备份-周三
date 09030000
mysql -uroot -p'[e-mail]' -e  'insert into testdb.test values (3)'
innobackupex --user=root --password='[e-mail]' 
--incremental /xtrabackup/ 
--incremental-basedir=/xtrabackup/2020-09-02_00-00-26    #basedir基于周2的备份，会生成一个今天的。

ls  /xtrabackup/    #2020-09-01_00-00-04  2017-09-02_00-00-58  2017-09-03_00-00-36

增量恢复流程

停止数据库
systemctl stop mysqld

清理环境
rm -rf /var/lib/mysql/*
周一
innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-00-04  #回滚合并

innobackupex --apply-log --redo-only /xtrabackup/2017-09-01_00-00-04 
--incremental-dir=/xtrabackup/2017-09-02_00-00-26  #回滚合并

恢复
innobackupex --copy-back /xtrabackup/2020-09-01_00-00-06  
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld


恢复周三
 systemctl stop mysqld
 innobackupex --apply-log --redo-only /xtrabackup/2020-09-01_00-00-04  
--incremental-dir=/xtrabackup/2020-09-03_00-00-27
rm -rf /var/lib/mysql/*
innobackupex --copy-back /xtrabackup/2017-09-01_00-00-04 
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld

-------------------------------------------------------------------------------------------

MySQL逻辑备份(热备)mysqldump

备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

**本质：**导出的是SQL语句文件
**优点：**不论是什么存储引擎，都可以用mysqldump备成SQL语句
**缺点：**速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份。
提供三种级别的备份，表级，库级和全库级

语法

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

参数说明：

表级备份 / 恢复

表级备份：
mysqldump -p123 db01 table01 > /tmp/mysqlback/table01.sql              
#备份单个表

mysqldump -p123 db01 table01 table02 > /tmp/mysqlback/table01_02.sql   
#备份多个表

表级恢复：
mysql -p123 db01 < /tmp/mysqlback/table01.sql

或者在mysql数据库内使用source命令来执行外部的sql文件
mysql> source /tmp/mysqlback/table01.sq

库级备份 / 恢复

库级备份：
mysqldump --databases db01 -p123 > /tmp/mysqlback/db01.sql    # 备份单个库

mysqldump --databases db01 db02 -p123 > /tmp/mysqlback/db01_02.sql     # 备份多个库

库级恢复：

mysql -p123 < /tmp/mysqlback/db01.sql

mysql> source /tmp/mysqlback/db01_02.sql

说明： 不要从操作层面删除任何一个库和任何一个表，如果真的这么做了，那么恢复的时候会恢复失败，因为mysql库里面不仅记录了用户信息等还记录了数据库表结构等。从操作层面删除，而mysql库里面并不知道。 所以坚决不能从操作层面删除任何一个库和任何一个表。

全库备份 / 恢复

全库级备份：

考虑到数据库有innodb，也有其他类型的表，那么就只能锁表备份

mysqldump -p123 --lock-tables --all-databases > /tmp/mysqlback/alldb.sql

全库级恢复：
mysql -p123 < /tmp/mysqlback/alldb.sql

mysql> source /tmp/mysqlback/alldb.sql

注意：如果是在终端直接在数据目录里面将数据全部删除， 再恢复数据的话就需要初始化才能恢复。

备份–准备库

mysql> select * from testdb1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

[[e-mail] ~] mysqldump -p'[e-mail]' \
--all-databases --single-transaction \
--master-data=2 \
--flush-logs \ > /backup/`date +%F-%H`-mysql-all.sql    #master-data=2 注释掉日志记录

观察备份细节
vim /backup/2019-11-25-14-mysql-all.sql
LOCK TABLES `user` WRITE; #观察各种锁机制，用来保证数据一致性

22 CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000004', MASTER_LOG_POS=154;

备份后的，数据变更行为
在testdb2.t2 中插入数据1，2，3；
创testdb3库
切断二进制日志（重启数据库）
mysql> select * from testdb2.t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
在testdb2.t2 中插入数据4；
删testdb3库
在testdb2.t2 中插入数据5；
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| testdb1            |
| testdb2            |
+--------------------+
12 rows in set (0.00 sec)

mysql> 

sql二进制恢复

备份二进制日志文件
cp /var/lib/mysql/*bin* ~
停止数据库
systemctl stop mysqld
清理环境
rm -rf /var/lib/mysql/*
启动数据库
systemctl start mysqld
grep 'password' /var/log/mysqld.log

恢复数据
mysql -p'[e-mail]' < /backup/2019-12-08-04-mysql-all.sql
mysql -p'[e-mail]' -e 'flush privileges'  #使用备份时密码 
此时恢复的是初始备份

binlog二进制日志恢复

观察二进制截取记录
vim /backup/2019-11-25-14-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.0000010', MASTER_LOG_POS=154;

mysqlbinlog localhost-bin.000002 localhost-bin.000003
 --start-position=154  | mysql -p'[e-mail]'
注意后续有多少日志，要跟多少日志名字。

保留testdb3库

[[e-mail] ~] mysqlbinlog localhost-bin.00004      ......005.....006...... > 1.txt
删除1.txt中不需要的at（比如test3库是误操作）
cat  1.txt | mysql -p'[e-mail]'

总结

mysqldump+binlog做增量备份——>通过binlog日志恢复到最新状态

• 当前数据库必须开启二进制日志（修改配置文件）
• 使用mysqldump工具做全库备份
• 更新数据
• 直接恢复
• 使用全库备份恢复
• 使用binlog日志恢复到最新状态