mysql

发布日期: 2020-05-16

作者: 小明

文章字数: 3.6k

阅读时长: 17 分

阅读次数:

mysql_备份恢复

1-注意事项

1. 关于备份恢复方面的职责
（1）备份、恢复策略的设计。
    备份周期、备份工具、备份方式、恢复方式全部流程化
（2）日常备份检查
     日志、备份内容     
（3）定期的恢复演练
（4）数据故障时，利用现有的资源，快速恢复
（5）数据迁移、升级。

2-备份工具介绍

2.1 逻辑备份

mysqldump  / source   *****
mysqlbinlog  /source 
mydumper / myloader  ：多并发 工具
select into outfile / load data infile 
binlog2sql
myflashback

2.2 物理备份

Percona Xtrabackup (PXB,XBK) *****

迁移表空间

Mysql Enterpise backup（MEB，企业版）
8.0 clone plugin (8.0.17)

2.3 选型

100G 以内： 逻辑 
100G 以上： 物理 

超大型： 逻辑

3. mysqldump 工具使用 ==参数==

介绍

mdp数据逻辑备份工具。（Create database\   create  table \ insert）
MySQL 自带的客户端命令。可以实现远程和本地备份。

3.2 参数

3.2 参数 
3.2.1 连接参数 
-u  
-p 
-S 
-h 
-P 
3.2.2 备份参数
# -A  全备  
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# mysqldump -uroot -p123  -A >/data/backup/full.sql

-B 单库或多库

[root@db01 ~]# mysqldump -uroot -p123  -B world gtdb test >/data/backup/db.sql

备份单表或多表

[root@db01 ~]# mysqldump -uroot -p123  world t1 country >/data/backup/tab.sql

–master-data=2

自动写入备份的起点
功能： 1.自动记录备份时的binlog信息（注释）
       2.自动锁定所有表，自动解锁（global read lock）。最好配合--single-transaction 参数，减少锁表时间。
--master-data[=#]   This causes the binary log position and filename to be
                      appended to the output. If equal to 1, will print it as a
                      CHANGE MASTER command; if equal to 2, that command will
                      be prefixed with a comment symbol. This option will turn
                      --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      taken a short time at the beginning of the dump; don't
                      forget to read about --single-transaction below). In all
                      cases, any action on logs will happen at the exact moment
                      of the dump. Option automatically turns --lock-tables
                      off.

这个选项可以把binlog的位置和文件名添加到输出中，如果等于1，将会打印成一个CHANGE MASTER命令；如果等于2，会加上注释前缀。并且这个选项会自动打开–lock-all-tables，除非同时设置了–single-transaction（这种情况下，全局读锁只会在开始dump的时候加上一小段时间，不要忘了阅读–single-transaction的部分）。在任何情况下，所有日志中的操作都会发生在导出的准确时刻。这个选项会自动关闭–lock-tables。

[root@db01 ~]# mysqldump -uroot -p123  -A  --master-data=2  >/data/backup/full.sql

–single-transaction

[root@db01 ~]# mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  >/data/backup/full.sql

功能：

   1. 对于InnoDB表，开启独立事务，通过快照备份表数据，不锁表备份，可以理解为热备。
   2. 通过照片查人数。

–max_allowed_packet=64M

最大允许的数据包大小
[root@db01 ~]# mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  >/data/backup/full.sql
max_allowed_packet = 30M

小故障

学员问题： 备份时超出最大数据包大小。
1153 - Got a packet bigger than 'max_allowed_packet' bytes

-R -E –triggers 备份特殊对象使用

[root@db01 ~]# mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full.sql

格式化备份文件

[root@db01 ~]# mysqldump -uroot -p  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full_`date +%F`.sql

4-案例

1-故障恢复演练(mdp+binlog,每天全备)

1-2 模拟环境

mysql> create database mdp charset utf8mb4;
mysql> use mdp
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

1.3-模拟周一23:00 的全备

[root@db01 ~]# mysqldump -uroot -p  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full_`date +%F`.sql

1.31-查看文件

查看 GTID相关信息 ：GTID截取起点。
备份那
SET @@GLOBAL.GTID_PURGED='202628e9-9265-11ea-b4a0-000c29248f69:1-35';

# 查看pos号，备份开始时binlog位置点信息。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=158999;

1.4模拟周二白天数据变化

mysql> use mdp;
mysql> create table t2 (id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;

1.5-周二下午2点，误删除了mdp核心库

mysql> drop database mdp;

1.6-故障==恢复思路：==

(1) 恢复全备到周一晚上23:00  全被恢复 
检查全备： 
vim /data/backup/full_2020-05-11.sql 
# 查看 GTID相关信息 ：GTID截取起点。
SET @@GLOBAL.GTID_PURGED='202628e9-9265-11ea-b4a0-000c29248f69:1-35';
# 查看pos号，备份开始时binlog位置点信息。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=158999;

(2) 截取日日志：
# 起点： 
mysql-bin.000023 202628e9-9265-11ea-b4a0-000c29248f69:36 或者 mysql-bin.000023 pos=158999

#终点： drop
[root@db01 backup]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000023'"|grep -B 1 "drop database mdp"
mysql-bin.000023    159421    Gtid    6    159486    SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:38'
mysql-bin.000023    159486    Query    6    159575    drop database mdp
[root@db01 backup]# 

# 截取日志 
[root@db01 backup]# mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:36-37' /data/3306/logs/mysql-bin.000023 >/data/backup/bin.sql

1.7- 恢复

mysql> set sql_log_bin=0;
mysql> source /data/backup/full_2020-05-11.sql 
mysql> source /data/backup/bin.sql
mysql> set sql_log_bin=1;

1.8-检查数据

mysql> use mdp
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_mdp |
+---------------+
| t1            |
| t2            |
+---------------+
2 rows in set (0.01 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

2-mysqldump多种备份策略和恢复策略介绍

场景：

100G 全库数据全库备份 30分钟-40分钟，恢复整库需要5倍时间2.5-3小时之间
一张表 1G 被误删除了。

1-mysqldump 备份策略：

mdp full+ binlog 增量备份
恢复单表数据思路：

（1）提取full全备中的故障表数据，恢复数据

# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q'  full.sql>createtable.sql
# grep -i 'INSERT INTO `t1`'  full.sql >data.sql

（2） binlog中截取全备到误删除t1之间对于这张表的修改

3-==单库单表备份==+binlog增量

恢复单表数据思路：
（1）恢复单表的备份
（2）binlog中截取备份到误删除t1之间对于这张表的修改

1-故障模拟

 模拟原始数据
 create database oldboy charset utf8mb4;
 use oldboy;
 create table oldguo (id int);
 insert into oldguo values(1),(2),(3);
 commit;

2-周一晚上全库备份

mysqldump -uroot -p  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full.sql

3-模拟周二白天的数据变化

use oldboy ;
insert into oldguo values(11),(22),(33);
commit;
create table  oldli(id int);
insert into oldli values(1),(2),(3);
commit;
insert into oldguo values(111),(222),(333);
commit;

4-模拟周二下午2点，误删除oldguo表

drop table  oldguo;

5==-恢复过程==

1. 处理全备

这个周一的表的建表语句

[root@db01 ~]# sed -n '/CREATE TABLE `oldguo` /,/\;/p' /data/backup/full.sql >/data/backup/create.sql
插入数据
[root@db01 ~]# grep -i 'INSERT INTO `oldguo`'  /data/backup/full.sql >/data/backup/insert.sql

2-binlog 的截取

范围： 
    起点： 通过备份。
    SET @@GLOBAL.GTID_PURGED='202628e9-9265-11ea-b4a0-000c29248f69:1-47';
    终点： 通过 
    [root@db01 ~]#  mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000023'" |grep -B 1 'DROP TABLE\ `oldguo`'
    mysql-bin.000023    163044    Gtid    6    163109    SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:54'
    mysql-bin.000023    163109    Query    6    163232    use `oldboy`; DROP TABLE `oldguo` /* generated by server */

3-按照这个号码截取

root@db01 ~]# mysqlbinlog --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:48-53' /data/3306/logs/mysql-bin.000023 |grep -B 8  '`oldboy`.`oldguo`'|grep 'GTID_NEXT'
SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:49'/*!*/;
SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:50'/*!*/;
SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:53'/*!*/;

4-截取：

方法1：                                     
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:48-53' 【不要排除】--exclude-gtids='202628e9-9265-11ea-b4a0-000c29248f69:51-52' /data/3306/logs/mysql-bin.000023 >/data/backup/bin.sql

方法2： 

mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:48-50','202628e9-9265-11ea-b4a0-000c29248f69:53' /data/3306/logs/mysql-bin.000023 >/data/backup/bin1.sql

5-1-恢复数据

use oldboy;
set sql_log_bin=0;
source /data/backup/create.sql
source /data/backup/insert.sql
commit;
source /data/backup/bin.sql
set sql_log_bin=1;

5-实现单库单表备份

shell# mkdir -p /data/backup/single_bak
mysql> select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," >/data/backup/single_bak/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema') into outfile '/tmp/single_bak.sh';
shell# sh /tmp/single_bak.sh &>/tmp/bak.log

6-Percona Xtrabackup（PXB\XBK）

介绍

percona公司研发
xtrabackup –》C C++
innobackupex –》perl语言
8.0之前，2.4.x
8.0之后，8.0
物理备份工具，类似于cp文件。支持：全备和增量备份。

1-安装

4.2.1 安装依赖包：
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev


4.2.2 下载软件并安装
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

2-全备

4.3.1 介绍 
拷贝，/data/3306/data/下的数据文件。
InnoDB  ： 热备。拷贝ibdataN,UNDO00N ,ibtmpN ,ibd 。通过截取变化redo。 
非InnoDB： FTWRL，全局锁。拷贝非INNODB的文件frm\myi\myd\...
只能本地备份。

[client]
socket=/tmp/mysql.sock

2.1-全备实现

[root@db01 backup]# innobackupex --user=root --password=123 /data/backup/test
说明： 备份完成后，自动生成基于时间戳的目录。

###（1）xtrabackup_binlog_info

记录binlog位置点， 截取binlog起点位置。

（2）xtrabackup_checkpoints

 LSN号码信息
from_lsn = 0         # 一般增量备份会关注，一般上次备份的to_lsn的位置
to_lsn = 180881595   # CKPT-LSN
last_lsn = 180881604 # xtrabackup_logfile LSN

（3）xtrabackup_info

备份总览信息

（4）xtrabackup_logfile

# 备份期间产生的redo变化

3-自定义目录备份：

[root@db01 xbk]# innobackupex --user=root --password=123 --no-timestamp /data/backup/xbk/full_`date +%F`

4-全备恢复应用

1-故障模拟

[root@db01 xbk]# pkill mysqld 
[root@db01 xbk]# rm -rf /data/3306/data/*

2-使用全备恢复数据

（1）prepare 准备备份阶段
  重用了CR ： 自动故障恢复。DWB+redo前滚和undo回滚。
  [root@db01 ~]# innobackupex --apply-log /data/backup/xbk/full

3-copy-back 恢复

方法一： 
  [root@db01 full]# cp -a /data/backup/xbk/full/*  /data/3306/data/
  或者： mv
  [root@db01 full]# chown -R mysql.mysql /data/*
  [root@db01 full]# /etc/init.d/mysqld start
  Starting MySQL.... SUCCESS! 
 方法二： 
  [root@db01 full]# innobackupex --copy-back /data/backup/xbk/full
  [root@db01 full]# innobackupex --move-back /data/backup/xbk/full
 方法三： 
  直接指定数据路径为：
  vim /etc/my.cnf
  datadir=/data/backup/xbk/full

  chown -R mysql. /data/* 
  /etc/init.d/mysqld start

5-增量备份（incremental）功能

自带的功能。
每次增量一般是将最近一次备份作为参照物。
自动读取参照物cat xtrabackup_checkpoints中to_lsn值，与当前CKPT的LSN对比，备份变化过page。备份期间新的数据变化，通过redo自动备份。
恢复数据时，需要把所有需要的增量合并到FULL中。无法通过增量单独恢复数据，依赖与全备。

1-增量备份演练

# 备份前数据准备：
mysql> create database xbk charset utf8mb4;
mysql> use xbk
mysql> create table full (id int);
mysql> insert into full values(1),(2),(3);
mysql> commit;

# 模拟周日 23:00全备 
innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F`

# 模拟周一白天数据变化 
mysql> use xbk
mysql> create table inc1 (id int);
mysql> insert into  inc1 values(1),(2),(3);
mysql> commit;

# 模拟周一23:00增量备份
innobackupex --user=root --password=123  --no-timestamp   --incremental --incremental-basedir=/data/backup/full_2020-05-12  /data/backup/inc1_`date +%F`


# 模拟周二白天数据变化 

mysql> use xbk
mysql> create table inc2 (id int);
mysql> insert into  inc2 values(1),(2),(3);
mysql> commit;


# 模拟周二23:00增量备份
innobackupex --user=root --password=123  --no-timestamp   --incremental --incremental-basedir=/data/backup/inc1_2020-05-12  /data/backup/inc2_`date +%F`


# 模拟周三白天数据变化 

mysql> use xbk
mysql> create table inc3(id int);
mysql> insert into  inc3 values(1),(2),(3);
mysql> commit;


# 模拟周三23:00增量备份
innobackupex --user=root --password=123  --no-timestamp   --incremental --incremental-basedir=/data/backup/inc2_2020-05-12  /data/backup/inc3_`date +%F`


# 模拟周四白天的数据变化。
mysql> use xbk
mysql> create table inc4(id int);
mysql> insert into  inc4 values(1),(2),(3);
mysql> commit;


# 周四下午出现数据损坏。如何恢复到误删除之前。
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/backup/xbk/full/*

注意增量备份叠加

[root@China /data/3306/logs] # innobackupex  --no-timestamp   --incremental --incremental-basedir=/data/backup/full_2020-05-13  /data/backup/inc1_`date +%F` 

[root@China /data/3306/logs] # innobackupex  --no-timestamp   --incremental --incremental-basedir=/data/backup/inc1_2020-05-13  /data/backup/inc2_`date +%F` 


[root@China /data/3306/logs] # innobackupex  --no-timestamp   --incremental --incremental-basedir=/data/backup/inc2_2020-05-13  /data/backup/inc3_`date +%F`

6-恢复思路？

（1）我们有什么？

备份：  
full+inc1+inc2+inc3 


binlog:
full以来全量的binlog

（2）处理备份

需要将inc1\inc2\inc3按顺序依次合并到全备，并进行prepare。

    --redo-only       This option should be used when preparing the base full
                      backup and when merging all incrementals except the last
                      one. This forces xtrabackup to skip the "rollback" phase
                      and do a "redo" only. This is necessary if the backup
                      will have incremental changes applied to it later. See
                      the xtrabackup documentation for details.

从官方角度：基础全备和合并所有增量（排除最后一个）都需要此参数
原理角度： 使所有备份合并时，LSN必须是连续的

QQ图片20200513162111

1. base_full

 [root@db01 ~]# innobackupex --apply-log --redo-only  /data/backup/full_2020-05-12/

2. inc1合并到full中，并且prepare

[root@db01 ~]# cd /data/backup/
[root@db01 backup]# innobackupex --apply-log --redo-only   --incremental-dir=inc1_2020-05-12 full_2020-05-12

检验合并结果：

cat full_2020-05-12/xtrabackup_checkpoints  |grep "to_lsn"
cat inc1_2020-05-12/xtrabackup_checkpoints |grep "to_lsn"

一样的  ok

3. inc2合并到full中，并且prepare

[root@db01 ~]# cd /data/backup/
[root@db01 backup]# innobackupex --apply-log --redo-only   --incremental-dir=inc2_2020-05-12 full_2020-05-12

检验合并结果：

[root@db01 backup]# 
[root@db01 backup]# cat full_2020-05-12/xtrabackup_checkpoints  |grep "to_lsn"
to_lsn = 180902505
[root@db01 backup]# cat inc2_2020-05-12/xtrabackup_checkpoints |grep "to_lsn"
to_lsn = 180902505
[root@db01 backup]#

4. inc3合并到full中，并且prepare

[root@db01 ~]# cd /data/backup/
[root@db01 backup]# innobackupex --apply-log   --incremental-dir=inc3_2020-05-12 full_2020-05-12

检查合并结果

cat full_2020-05-12/xtrabackup_checkpoints  |grep "to_lsn"
cat inc3_2020-05-12/xtrabackup_checkpoints |grep "to_lsn"

5. 将合==并后全备再次prep==are

innobackupex --apply-log  /data/backup/full_2020-05-12/

（3）恢复并启动

 到这步，数据已经恢复到周三晚上备份结束后的状态。
 vim /etc/my.cnf ----> datadir=/data/3306/data
 rm -rf /data/3306/data/* 

 [root@db01 data]# innobackupex --copy-back /data/backup/full_2020-05-12/
[root@db01 data]# chown -R mysql. /data/*

6 截取周三增量备份后—-》故障之前所有binlog日志，并进行恢复。

起点：inc3 备份完成后的位置点 最后一个备份的的

mysql-bin.000025        2069    
202628e9-9265-11ea-b4a0-000c29248f69:1-57,
88126fcb-93f6-11ea-9896-000c29248f69:1-9    


[root@China /data/3306/data] # cat xtrabackup_binlog_info
mysql-bin.000030    2029

终点： binlog 结尾

(5) 恢复binlog

mysql> set sql_log_bin=0;
mysql> source /data/backup/bin.sql
mysql> set sql_log_bin=1;

(6) xbk恢复完成后，清空所有日志

mysql> reset master;

(7) 立即再做个全备。

innobackupex  --no-timestamp     /data/backup/inc1_`date +%F` 



nnobackupex --user=root --password=123 --no-timestamp /data/backup/xbk/full_`date +%F`

下午作业：
2T XBK 完整备份+binlog完整.误删除的表是 10G t1 ,drop table t1
提示：表空间迁移+t1 binlog

总结：
基础备份策略：
MDP 备份 + binlog ===》全备完整恢复、部分数据损坏恢复
XBK full+inc+binlog ===> 全备完整恢复、部分数据损坏恢复
XBK full+binlog ===> 全备完整恢复、部分数据损坏恢复

自行研究一下 into outfile / load data in file

转载规则

《09- mysql_备份恢复》由小明采用知识共享署名 4.0 国际许可协议进行许可。

10-主从复制（Replication）

10主从复制（Replication）1. 介绍两台或以上数据库实例，通过二进制日志，实现数据的“同步”关系。2-主从复制前提（搭建过程）大家提到的： # 时间同步 # 至少2台以上实例,要有不同角色换分，server_id # 主库开b

2020-05-16 mysql

leetcode mysql

08-mysq日志管理

1-日志管理1. 错误日志1.1 作用记录数据库启动以来，状态、警告、报错。诊断数据库报错问题。 1.2 配置默认：开启状态。存放在数据目录下(/data/3306/data),名字：主机名.err 。查询配置： mysql

2020-05-16 mysql

leetcode mysql

mysql_备份恢复

1-注意事项

2-备份工具介绍

2.1 逻辑备份

2.2 物理备份

迁移表空间

2.3 选型

3. mysqldump 工具使用 ==参数==

介绍

3.2 参数

-B 单库或多库

备份单表或多表

–master-data=2

–single-transaction

–max_allowed_packet=64M

-R -E –triggers 备份特殊对象使用

格式化备份文件

4-案例

1-故障恢复演练(mdp+binlog,每天全备)

1-2 模拟环境

1.3-模拟 周一23:00 的全备

1.31-查看文件

1.4模拟周二白天数据变化

1.5-周二下午2点，误删除了mdp核心库

1.6-故障==恢复 思路：==

1.7- 恢复

1.8-检查数据

2-mysqldump多种备份策略和恢复策略介绍

1-mysqldump 备份策略 ：

3-==单库单表备份==+binlog增量

1-故障模拟

2-周一晚上全库备份

3-模拟周二白天的数据变化

4-模拟周二下午2点，误删除oldguo表

5==-恢复过程==

1. 处理全备

2-binlog 的截取

3-按照这个号码截取

4-截取：

5-1-恢复数据

5-实现单库单表备份

6-Percona Xtrabackup（PXB\XBK）

介绍

1-安装

2-全备

2.1-全备实现

（2）xtrabackup_checkpoints

（3）xtrabackup_info

（4）xtrabackup_logfile

3-自定义目录备份：

4-全备恢复应用

1-故障模拟

2-使用全备恢复数据

3-copy-back 恢复

5-增量备份（incremental）功能

自带的功能。

1-增量备份演练

6-恢复思路？

（1）我们有什么？

（2）处理备份

1. base_full

2. inc1合并到full中，并且prepare

检验合并结果：

3. inc2合并到full中，并且prepare

检验合并结果：

4. inc3合并到full中，并且prepare

检查合并结果

5. 将合==并后全备再次prep==are

（3）恢复并启动

6 截取周三增量备份后—-》故障之前所有binlog日志，并进行恢复。

(5) 恢复binlog

(6) xbk恢复完成后，清空所有日志

(7) 立即再做个全备。

写作不易，客官能否打赏一杯奶茶？

1.3-模拟周一23:00 的全备

1.6-故障==恢复思路：==

1-mysqldump 备份策略：