命令记不住

MySQL备份恢复

• 1.MySQL逻辑备份与恢复

  • 1.1数据库完整备份与恢复
  • 1.2数据库增量备份与恢复

• 2.MySQL物理备份与恢复

  • 2.1数据库完整备份与恢复
  • 2.2数据库增量备份与恢复
  • 2.3数据库差异备份与恢复

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

• 4.生产备份思路与实战

通常数据库备份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       #备份存储过程和存储函数
-ignore-table        #跳过某个表
-X --lock-all-tables, -l --lock-tables #导出前锁定表
--lock-tables=false   #不锁表导出

注意: 不管物理备份还是逻辑备份, 必须开启binlog日志

只导出表结构

mysqldump -uroot -p --add-locks -q -d db_name> db_name_table.sql 

-q：不缓冲查询，直接导出至标准输出
--add-locks ：导出过程中锁定表，完成后回解锁。
-d ：只导出表结构，不含数据

-ignore-table：跳过某个表

-X --lock-all-tables, -l --lock-tables：导出前锁定表

--lock-tables=false :不锁表导出

导出dbname表结构

mysqldump -uroot -p123456 -d dbname > dbname.sql

导出dbname表数据

mysqldump -uroot -p123456 -t dbname > dbname.sql

导出dbname表结构和数据

mysqldump -uroot -p123456 dbname > dbname.sql

-ignore-table跳过某个表

mysqldump -uroot -p --ignore-table=platform88.platformapitrace platform88 > /data/mysql/dump20181022.sql

导出前锁定表记录

mysqldump -uroot -p -R --ignore-table=platform88.platformapitrace --lock-tables -B platform88 > /data/mysql/dump20181022.sql

# -X --lock-all-tables, -l --lock-tables导出前锁定表记录

不锁表导出数据

mysqldump -u mysql_user --lock-tables=false $database > A.sql
mysqldump -u mysql_user --lock-tables=false --default-character-set=utf8 $database > B.sql

确保导出数据支持中文配置

my.cnf

[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
[mysql]
default-character-set=utf8

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

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  |
+------+------+

3.MySQL物理备份与恢复

percona-xtrabackup物理备份+binlog

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

安装xtrabackup官方下载地址 https://www.percona.com/downloads/XtraBackup/LATEST/

[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

4.数据库完整备份与恢复

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> 

5.数据库增量备份与恢复

增量备份每次需要基于前一次的备份
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

5.执行回滚

//恢复对应数据
[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 |
+------+------+

6.数据库差异备份与恢复

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/

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

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

3.差异备份恢复流程

//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 |
+------+

//6.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'

---

---

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

使用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 恢复

8.生产备份思路与实战

• 逻辑备份

  • 保证一致性，服务可用性
  • 适合少量的数据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