通常数据库备份database数据文件binlog日志文件my.cnf配置文件,所有的数据库的备份数据都应放在非数据库本地, 而且建议备份多份。

仅备份是没有任何意义, 需要在测试环境中做日常恢复演练, 测试备份的可用性, 恢复较比备份更加的重要。

备份: 能够有效防止设备故障以及人为误操作带来的数据丢失, 例如:将数据文件保存在远端。

冗余: 数据有多分冗余, 但不等于备份, 只能防止机械故障丢失的数据, 例如: 主备模式、数据库集群。

数据库备份必须考虑因素

1.数据的一致性
2.服务的可用性

数据库备份方式

1.逻辑备份: 备份DDL DML DCL语句, 适用于中小型数据库, 效率相对低下。 mysqldump、mydumper
2.物理备份: 直接复制数据库文件, 适用于大型数据库环境, 效率相对较高。xtrabackup、inbackup、cp、tar、lvm snapshot

数据库备份模式

完全备份
增量备份
差异备份

1.MySQL逻辑备份与恢复

mysql自带逻辑备份工具mysqldump, 可以保证数据备份一致性, 以及服务可用性

1.1数据库完整备份与恢复

mysqldump命令使用方式

>mysqldump -h 服务器 -u 用户名 -p 密码 数据库名 > 备份文件.sql
//常用参数
-A, --all-databases #备份所有库
-B, --databases     #备份多个库多个数据库
--single-transaction#InnoDB 一致性 服务可用性
--master-data=1|2   #记录 binlog日志位置与文件名,追加至备份文件中
--triggers          #备份触发器
-F, --flush-logs    #备份之前刷新日志
-E, --events        #备份事件调度器代码
-R, --routines      #备份存储过程和存储函数
注意: 不管物理备份还是逻辑备份, 必须开启binlog日志

1.使用mysqldump完整备份

//本地备份
[root@sql ~]# mkdir /backup/mysql -p
mysqldump -uroot -p'123' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

2.查看binlog日志信息

[root@sql ~]# sed -n "22p" /backup/mysql/2018-05-1203-mysql-all.sql 
CHANGE MASTER TO MASTER_LOG_FILE='bgx.000003', MASTER_LOG_POS=154;

数据库完整恢复流程

1.停止数据库
2.删除破损数据库
3.重新初始化数据库
4.重置密码,否则无法恢复
5.恢复数据[新密码]
6.刷新授权[备份时密码]

1.模拟数据奔溃

[root@sql ~]# systemctl stop mysqld
[root@sql ~]# rm -rf /var/lib/mysql/*

2.重新初始化数据库,启动后修改密码

[root@sql ~]# systemctl start mysqld
[root@sql ~]# grep "password" /var/log/mysqld.log
khy_=i512g=F
[root@sql ~]# mysqladmin -uroot -p'khy_=i512g=F' password "Bgx123.com"

4.导入备份数据

[root@sql ~]# mysql -uroot -p'Bgx123.com' < /backup/mysql/2018-05-1203-mysql-all.sql

//使用恢复后的密码进行登录mysql
[root@sql ~]# mysql -uroot -p123
mysql>

5.建议在恢复备份时暂停binlog日志记录

//1.关闭binlog记录, 不然容易恢复增量失败
[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2018-05-1205-mysql-all.sql

//2.在mysql控制台临时修改不记录binlog日志
mysql> set sql_log_bin=0;
mysql> source /backup/mysql/2018-05-1203-mysql-all.sql

1.2数据库增量备份与恢复

实战案例1: 数据库完整备份+数据库增量备份

新建数据表, 进行了全量备份, 随着时间推移, 数据库突然奔溃
1.环境准备

//备份之前
mysql> create database bgx;
mysql> create table bgx.t1 (id int, name varchar(20));
mysql> insert into bgx.t1 values (1,"bgx1");
mysql> insert into bgx.t1 values (2,"bgx2");
mysql> select * from bgx.t1;
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
+------+------+
2 rows in set (0.00 sec)

//基于当前状态备份
[root@sql ~]# mysqldump -uroot -p'123' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

//模拟数据插入操作
mysql> insert into bgx.t1 values (3,"bgx3");
mysql> insert into bgx.t1 values (5,"tt");
mysql> select * from bgx.t1;
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
|    3 | bgx3 |
|    5 | tt   |
+------+------+
4 rows in set (0.00 sec)

//模拟数据库故障
[root@sql ~]# systemctl stop mysqld
[root@sql ~]# rm -rf /var/lib/mysql/*

//重新启动会自动初始化操作
[root@sql ~]# systemctl start mysqld

//注意修改默认密码
[root@sql ~]# mysqladmin -uroot -p'RA+:>Xu&.6K_' password "Bgx123.com"

2.恢复全备数据

//关闭binlog记录, 不然容易恢复增量失败
[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2018-05-1205-mysql-all.sql
[root@sql ~]# mysql -uroot -p'Bgx123.com' < /backup/mysql/2018-05-1205-mysql-all.sql
[root@sql mysql]# mysql -uroot -p'Bgx123.com' -e "select * from bgx.t1;"
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
+------+------+

3.恢复增量数据

//查看备份后binlog起始位置点
[root@sql ~]# sed -n '22p' /backup/mysql/2018-05-1205-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='bgx.000061', MASTER_LOG_POS=154;
//查找结束位置点, 进行恢复
[root@sql bin]# mysqlbinlog --start-position=1490 --stop-position=1652 \
bgx.000001|mysql -uroot -p'Bgx123.com'

实战案例2: 数据库完整备份+数据库增量备份

例子：现在有一个运维人员误删除了数据库，或者是某一张表

1.删除所有数据库
2.删除所有binlog日志
3.重启mysqld服务

1.模拟环境准备

mysql> create database bgxdb;
mysql> use bgxdb;
mysql> create table t1 (id int,name varchar(20));
mysql> insert into t1 values (1,"ccr");
mysql> insert into t1 values (2,"tfr");
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |

2.使用mysqldump进行全备

[root@sql ~]# mysqldump -uroot -p'Bgx123.com' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

3.再次插入一些数据

mysql> insert into t1 values 
(3,'trl'),
(4,'zx'),
(5,'wq'),
(6,'tj'),
(7,'gwt');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
|    3 | trl  |
|    4 | zx   |
|    5 | wq   |
|    6 | tj   |
|    7 | gwt  |
+------+------+
7 rows in set (0.00 sec)

3.模拟故障

mysql> delete from t1 where id ='2';
mysql> drop database bgxdb;

4.恢复全量备份的数据

[root@sql ~]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2018-05-1215-mysql-all.sql
[root@sql ~]# mysql -uroot -p'Bgx123.com' < /backup/mysql/2018-05-1215-mysql-all.sql
[root@sql ~]# mysql -uroot -p'Bgx123.com' -e "select * from bgxdb.t1;"
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
+------+------+

5.通过mysqlbinlog命令导出关于数据库bgxdb的sql语句

//针对全量备份后仅产生1个binlog文件的方式
[root@sql bin]# mysqlbinlog --start-position=154 --base64-output="decode-rows" -v bgx.000002 > db.sql

全量备份到出问题只产生了一个 mysql-bin.000021 文件，
如果产生了多个binlog日志, 可是使用下面方法恢复

mysqlbinlog  --base64-output="decode-rows" -v mysql-bin.000021 >> 1.sql

6.查看sql文件，找到误操作语句对应的位置,然后记录下来, 随后恢复时跳过即可

7.使用mysqlbinlog进行恢复跳过drop与delete语句

[root@sql bin]# mysqlbinlog --start-position=154 --stop-position=1648 bgx.000002 |mysql -uroot -p'Bgx123.com'
[root@sql bin]# mysqlbinlog --start-position=1692 --stop-position=1723 bgx.000002 |mysql -uroot -p'Bgx123.com'
[root@sql bin]# mysql -uroot -p'Bgx123.com' -e "select * from bgxdb.t1;"
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
|    3 | trl  |
|    4 | zx   |
|    5 | wq   |
|    6 | tj   |
|    7 | gwt  |
+------+------+

2.MySQL物理备份与恢复

percona-xtrabackup物理备份+binlog
开源免费并支持MySQL数据库热备份的软件，它能对 InnoDB和XtraDB存储引擎的数据库非阻塞地备份。无需暂停服务备份MySQL
支持MySQL增量备份
支持MySQL差异备份
备份MySQL不增加服务器负载
创建replication更加便捷

安装xtrabackup官方下载地址

[root@sql ~]# yum install  -y \
https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

2.1数据库完整备份与恢复

1.完整备份流程

//1.创建备份目录
[root@sql ~]# mkdir /xtrabackup/
//2.执行全备操作
[root@sql ~]# innobackupex --user=root --password="Bgx123.com" /xtrabackup/
//3.检查备份结果
[root@sql ~]# ls /xtrabackup/
2018-05-08_04-34-06
[root@sql ~]# cat /xtrabackup/2018-05-08_04-34-06/xtrabackup_binlog_info 
bgx.000002      4447

2.完全备份恢复流程

#1.停止数据库
[root@sql ~]# systemctl stop mysqld

#2.清理环境
[root@sql ~]# rm -rf /var/lib/mysql/*

#3.重演回滚
[root@sql ~]# innobackupex --apply-log /xtrabackup/2018-05-08_04-34-06/

#4.恢复数据
[root@sql ~]# innobackupex --copy-back /xtrabackup/2018-05-08_04-34-06/

#5.修改权限
[root@sql ~]# chown -R mysql.mysql /var/lib/mysql

#6.启动数据库
[root@sql ~]# systemctl start mysqld
[root@sql ~]# mysql -uroot -pBgx123.com
mysql> 

2.2数据库增量备份与恢复

增量备份每次需要基于前一次的备份
1.准备数据

#周三的数据
mysql> create database bgxdb;
mysql> use bgxdb;
mysql> create table t1(id int, name varchar(20));
mysql> insert into t1 values (3,'day3');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
+------+------+

2.使用物理全备

[root@sql ~]# innobackupex --username=root --password=Bgx123.com /xtrabackup/

3.后续每天进行增量备份

//周四,登陆mysql插入数据
[root@sql ~]# date -s '2018-05-09'
mysql> insert into bgxdb.t1 values (4,'day4');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
+------+------+
[root@sql ~]# innobackupex --user=root --password='Bgx123.com' \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2018-05-08_04-51-04/
[root@sql ~]# cat /xtrabackup/2018-05-09_00-00-28/xtrabackup_binlog_info 
bgx.000003      1039

//周五,登陆mysql插入数据
[root@sql ~]# date -s '2018-05-10'
mysql> insert into bgxdb.t1 values (5,'day5');
mysql> select * from bgxdb.t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
|    5 | day5 |
+------+------+

//基于周四基础之上进行增备
[root@sql ~]# innobackupex --user=root --password='Bgx123.com' \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2018-05-09_00-00-28/
[root@sql ~]# cat /xtrabackup/2018-05-10_00-01-50/xtrabackup_binlog_info 
bgx.000003      1297

4.模拟故障

//1.停止数据库
[root@sql ~]# systemctl stop mysqld
//2.清理环境
[root@sql ~]# rm -rf /var/lib/mysql/*

5.依次重演

//恢复周三全备数据
[root@sql ~]# innobackupex --apply-log \
--redo-only /xtrabackup/2018-05-08_04-51-04/

//恢复周四的增量数据
[root@sql ~]# innobackupex --apply-log \
--redo-only /xtrabackup/2018-05-08_04-51-04/ \
--incremental-dir=/xtrabackup/2018-05-09_00-00-28

//恢复周五的增量数据
[root@sql ~]# innobackupex --apply-log \
--redo-only /xtrabackup/2018-05-08_04-51-04/ \
--incremental-dir=/xtrabackup/2018-05-10_00-23-24

6.执行回滚

//恢复对应数据
[root@sql ~]# innobackupex --copy-back /xtrabackup/'2018-05-08_04-51-04'/

//授权
[root@sql ~]# chown -R mysql.mysql /var/lib/mysql

//启动数据库
[root@sql ~]# systemctl start mysqld

//查询数据
[root@sql ~]# mysql -uroot -pBgx123.com -e "select * from bgxdb.t1;"
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
|    5 | day5 |
+------+------+

2.3数据库差异备份与恢复

1.完整备份

//修改时间为周三
[root@sql ~]# date -s '2018-05-08'

//创建对应数据
mysql> create database bgxdb; 
mysql> use bgxdb; 
mysql> create table t2(id int); 
mysql> insert into t2 values(3); 
mysql> select * from t2;
+------+
| id   |
+------+
|    3 |
+------+

//使用物理全备
[root@sql ~]# mkdir /xtrabackup
[root@sql ~]# innobackupex --user=root --password='Bgx123.com' /xtrabackup/
[root@sql ~]# cat /xtrabackup/2018-05-08_09-21-06/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 5357964
last_lsn = 5362222
compact = 0
recover_binlog_info = 0

2、差异备份:周四 -> 周六

//修改时间为周四, 新增数据
[root@sql ~]# date -s '2018-05-09'
mysql> insert into bgxdb.t2 values(4);

//差异备份(以完整备份为准2018-05-08_09-21-06)
[root@sql ~]# innobackupex --user=root --password='Bgx123.com' \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2018-05-08_09-21-06/

//修改时间为周五, 新增数据
[root@sql ~]# date -s '2018-05-10'
mysql> insert into bgxdb.t2 values(5);

//差异备份(以完整备份为准2018-05-08_09-21-06)
[root@sql ~]# innobackupex --user=root --password=Bgx123.com \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2018-05-08_09-21-06/

//修改时间为周六, 新增数据
[root@sql ~]# date -s '2018-05-11'
mysql> insert into bgxdb.t2 values(6);

//差异备份, (以完整备份为准2018-05-08_06-48-44)
[root@sql ~]# innobackupex --user=root --password=Bgx123.com \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2018-05-08_09-21-06/

3.模拟备份后一次错误操作

mysql> create database Linux;
mysql> drop database Linux;

4.差异备份恢复流程

//1.停止数据库
[root@sql ~]# systemctl stop mysqld

//2.清理环境
[root@sql ~]# rm -rf /var/lib/mysql/*

//3.重演回滚, 回滚全备
[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2018-05-08_09-21-06/

//4.重演回滚, 将差异备份应用至完整备份上
[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2018-05-08_09-21-06/ \
--incremental-dir=/xtrabackup/2018-05-11_00-01-27/

//5.应用数据
[root@sql ~]# innobackupex --copy-back /xtrabackup/2018-05-08_09-21-06/

//6.修改权限
[root@sql ~]# chown -R mysql.mysql /var/lib/mysql

//7.启动数据库
[root@sql ~]# systemctl start mysqld
[root@sql ~]# mysql -uroot -pBgx123.com -e "select * from bgxdb.t2;"
+------+
| id   |
+------+
|    3 |
|    4 |
|    5 |
|    6 |
+------+

5.binlog恢复删除掉的linux库

[root@sql ~]# cat /xtrabackup/2018-05-11_00-01-27/xtrabackup_binlog_info 
bgx.000001      1490

//导出对应的sql, 找到drop语句位置
[root@sql ~]# mysqlbinlog --start-position=1490 \
--base64-output="decode-rows" \
-v bgx.000001 bgx.000002 >db.sql

//针对起始点与结束点位置恢复
[root@sql bin]# mysqlbinlog --start-position=1490 \
--stop-position=1652 bgx.000001|mysql -uroot -p'Bgx123.com'

3.简单命令进行物理备份

使用tar命令打包来进行物理备份数据库系统
备份期间，服务不可用
1.备份操作过程

//1.停止数据库
[root@sql ~]# systemctl stop mysqld 
//2.
[root@sql ~]# mkdir /backup
//3.tar备份数据
[root@sql ~]# tar -cf /backup/`date +%F`-mysql-all.tar /var/lib/mysql
注：备份文件应该复制其它服务器或存储上

2.恢复操作过程

//1.停止数据库
[root@sql ~]# systemctl stop mysqld

//2.清理环境
[root@sql ~]# rm -rf /var/lib/mysql/*

//3.导入备份数据
[root@sql ~]# tar -xf /backup/2018-05-08-mysql-all.tar -C / 

//4.启动数据库
[root@sql ~]# systemctl start mysqld

//5.binlog 恢复

4.生产备份思路与实战

逻辑备份
    保证一致性，服务可用性
    适合少量的数据1G-10G
    只能全备->指定某一个数据库备份
    效率不是很高, 将所有的数据转成sql语句
        ddl dml dcl
物理备份xtrabackup
    保证一致性，服务可用性
    适合大量的数据10G 100G 500G
    支持增量、差异、全备
备份怎么做
    物理备份   周天全备  周一到周六增量
    /xtrabackup/1月/1周/xxx
    /xtrabackup/1月/2周/xx
    /xtrabackup/1月/3周
    /xtrabackup/1月/4周
    /xtrabackup/2月/1周
注意：
    binlog日志非常的重要
    备份时，不要记录binlog日志
    备份不单单只是数据文件，还有binlog、my.cnf