InnoDB 类型MySql恢复表结构与数据 mysql数据恢复原理

转载

陌陌香阁 2024-04-10 13:07:44

文章标签 mysql 数据库 sql 文章分类 MySQL 数据库

浅谈MYSQL之备份恢复

备份工具mysqldump

常用选项参数
Mysqldump使用引擎差异

MyISAM
InnoDB

分库备份语句
生产环境实战备份策略

InnoDB建议备份策略
MyISAM建议备份策略

实验：

使用mysqldump实现“完全+binlog”方式的迁移单个数据库操作
破坏源数据库然后进行修复实验

备份恢复概述

为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

备份注意要点
能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据
还原要点

做还原测试，用于测试备份的可用性
还原演练,增加恢复数据的操作效率

备份类型：

完全备份，部分备份

完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表

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

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
注意：二进制日志文件不应该与数据文件放在同一磁盘

根据数据服务是否在线：

冷备：读、写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行，实际生产环境只有热备是可接受的
MyISAM：温备，不支持热备
InnoDB：都支持

物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

备份策略：

全量+差异 + binlogs
全量+增量 + binlogs

备份内容

数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件

备份工具

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份

LVM快照备份恢复

(1) 请求锁定所有表.
mysql> FLUSH TABLES WITH READ LOCK;                    / 全局读锁
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;                                    /刷新日志，生成新的日志
mysql> SHOW MASTER STATUS;                            / 查看当前二进制日志文件
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE     / 到处到指定位置
(3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;                              
(5) 挂载快照卷，执行数据备份
(6) 备份完成后，删除快照卷
(7) 制定好策略，通过原卷备份二进制日志

备份工具mysqldump

mysqldump概述

是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份
mysql服务自带的备份工具；逻辑备份工具
完全、部分备份

InnoDB：热备
MyISAM：温备

备份策略：全量+binlog

语法格式

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]

常用选项参数

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name… #指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
#2：记录为注释的CHANGE MASTER TO语句
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--
single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。
建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact #去掉注释，适合调试，生产不使用
-d, --no-data #只备份表结构
-t, --no-create-info #只备份数据,不备份create table
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force #忽略SQL错误，继续执行
--hex-blob #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick #不缓存查询，直接输出，加快备份速度

Mysqldump使用引擎差异

MyISAM

MyISAM不支持事务,只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--
lock-tables选项会关闭此选项功能
#注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--
skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

InnoDB

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用

--single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启
事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表
（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储
文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP
TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选
项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用

分库备份语句

mysql -uroot -pcentos -e 'show databases'|grep -Ev
'^(Database|information_schema|performance_schema)$'|while read db;do mysqldump
-B $db | gzip > /data/$db.sql.gz;done

[root@centos8 ~]#for db in `mysql -uroot -pcentos -e 'show databases'|grep -Ev
'^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip
> /data/$db.sql.gz;done

生产环境实战备份策略

InnoDB建议备份策略

mysqldump –uroot -p –A –F –E –R --single-transaction --master-data=1 --flush-privileges
--triggers --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --tiggers --default-character-set=utf8 
--hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

实验：

使用mysqldump实现“完全+binlog”方式的迁移单个数据库操作

环境样本

源数据库IP :172.20.54.2
数据库版本：mariaDb-10.2.29
配置文件：/etc/my.cnf
新机器干净机器
注意：源数据库做新增数据测验时候一定要查看二进制日志是否关闭
如果关闭二进制日志，那就凉凉了，那个沙雕就是本人

远程完全备份单数据库hellodb

mysqldump -h 172.20.54.2 -uroot -pcentos --single-transaction -R --triggers -E --master-data=2 --flush-logs -B hellodb > /tmp/hellodb`date +%F-%T`.sql

查看数据库备份文件

InnoDB 类型MySql恢复表结构与数据 mysql数据恢复原理_mysql

上图看到在全量备份时正在工作的二进制日志和所处位置
远程拷贝数据库的配置文件

[root@Centos7 data]# scp root@172.20.54.2:/etc/my.cnf /etc/
The authenticity of host '172.20.54.2 (172.20.54.2)' can't be established.
ECDSA key fingerprint is SHA256:1VU86XlsLZrrO/UbYPKcNCIosRMmiUO+kjz9V6eGy80.
ECDSA key fingerprint is MD5:cc:02:dd:11:d9:37:7f:1f:71:a8:12:cd:8e:00:ad:e3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.20.54.2' (ECDSA) to the list of known hosts.
root@172.20.54.2's password: 
my.cnf                                               100%  400   242.1KB/s   00:00

基于原数据库配置文件进行安装同样的数据库版本
安装成功后查看新机器数据库

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

此时原数据库进行增加记录

MariaDB [hellodb]> INSERT teachers (name,age)VALUES('Hu Yidao','33');
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> INSERT teachers (name,age)VALUES('Lao Miao','34');
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> SELECT * FROM teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Feng Qingyang | 100 | NULL   |
|   6 | Huang Rong    |  18 | NULL   |
|   7 | Guo Jing      |  88 | NULL   |
|   8 | Bai Cai       |  22 | NULL   |
|  11 | Hu Yidao      |  33 | NULL   |
|  12 | Lao Miao      |  34 | NULL   |
+-----+---------------+-----+--------+

给新机器导入远程备份的源数据库文件

[root@Centos7 ~]# mysql -uroot -pcentos < /tmp/hellodb2019-11-26-14\:27\:49.sql

查看新机器数据库

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
/ 表示hellodb已经迁移过来，但是丢失后续的数据
MariaDB [(none)]> SELECT * FROM hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Feng Qingyang | 100 | NULL   |
|   6 | Huang Rong    |  18 | NULL   |
|   7 | Guo Jing      |  88 | NULL   |
|   8 | Bai Cai       |  22 | NULL   |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)

在源数据上把二进制导出传给新机器

[root@Centos7 ~]# mysqlbinlog mysql-bin.000005 >/tmp/binlog.sql

[root@Centos7 ~]# mysqlbinlog /var/mysqllog/mysql-bin.000005 >/tmp/binlog.sql
[root@Centos7 ~]# scp /tmp/binlog.sql 172.20.54.3:/tmp
root@172.20.54.3's password: 
binlog.sql

在新数据库上导入二进制文件

[root@Centos7 ~]# mysql -uroot -pcentos < /tmp/binlog.sql
MariaDB [(none)]> SELECT * FROM hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Feng Qingyang | 100 | NULL   |
|   6 | Huang Rong    |  18 | NULL   |
|   7 | Guo Jing      |  88 | NULL   |
|   8 | Bai Cai       |  22 | NULL   |
|  11 | Hu Yidao      |  33 | NULL   |
|  12 | Lao Miao      |  34 | NULL   |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)

至此，数据库hellodb迁移完成

破坏源数据库然后进行修复实验

准备环境

数据库有全备份
二进制日志跟数据库分离

开始流程

直接对源数据库进行远程全库备份并压缩

[root@Centos7 ~]# mysqldump -h 172.20.54.2 -uroot -pcentos -A  --master-data=2 --flush-logs | bzip2  > /tmp/all`date +%F-%T`.sql.bz2
Warning: Using a password on the command line interface can be insecure.
[root@Centos7 ~]# ll /tmp
total 104
-rw-r--r-- 1 root root 104618 Nov 26 15:45 all2019-11-26-15:45:42.sql.bz2
lrwxrwxrwx 1 root root     25 Nov 19 17:19 mysql.sock -> /var/lib/mysql/mysql.sock

修改数据库

MariaDB [hellodb]> INSERT teachers (name,age)VALUES('Lao HEI','35');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> INSERT teachers (name,age)VALUES('Miao Renfeng','40');
Query OK, 1 row affected (0.01 sec)

损坏数据库 rm -rf /data/mysql/*

[root@Centos7 ~]# rm -rf /data/mysql/*
[root@Centos7 ~]# lsof -i  :3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  17476 mysql   40u  IPv6 141664      0t0  TCP *:mysql (LISTEN)

重启源数据库机器
生成数据库初始库文件

[root@Centos7 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql
[root@Centos7 ~]# ll /data/mysql/
total 110620
-rw-rw---- 1 mysql mysql    16384 Nov 26 15:58 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov 26 15:58 aria_log_control
-rw-rw---- 1 mysql mysql      936 Nov 26 15:58 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Nov 26 15:58 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Nov 26 15:58 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Nov 26 15:53 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov 26 15:58 mysql
drwx------ 2 mysql mysql       20 Nov 26 15:58 performance_schema
drwx------ 2 mysql mysql       20 Nov 26 15:58 test

[root@Centos7 ~]# systemctl restart mysqld
[root@Centos7 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1199 mysql   22u  IPv6  17469      0t0  TCP *:mysql (LISTEN)

从备份机器拷会备份文件

[root@Centos7 ~]# scp 172.20.54.1:/tmp/all2019-11-26-15:45:42.sql.bz2 /tmp
The authenticity of host '172.20.54.1 (172.20.54.1)' can't be established.
ECDSA key fingerprint is SHA256:1VU86XlsLZrrO/UbYPKcNCIosRMmiUO+kjz9V6eGy80.
ECDSA key fingerprint is MD5:cc:02:dd:11:d9:37:7f:1f:71:a8:12:cd:8e:00:ad:e3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.20.54.1' (ECDSA) to the list of known hosts.
root@172.20.54.1's password: 
all2019-11-26-15:45:42.sql.bz2    

[root@Centos7 tmp]# bzip2 -d all2019-11-26-15\:45\:42.sql.bz2 
[root@Centos7 tmp]# ls
all2019-11-26-15:45:42.sql  binlog.sql  mysql.sock

关闭新库的二进制日志文件

MariaDB [(none)]> SET @@sql_log_bin=OFF
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       375 |
| mysql-bin.000002 |       418 |
| mysql-bin.000003 |       418 |
| mysql-bin.000004 |       418 |
| mysql-bin.000005 |       866 |
| mysql-bin.000006 |       432 |
| mysql-bin.000007 |       432 |
| mysql-bin.000008 |       859 |
| mysql-bin.000009 |       365 |
| mysql-bin.000010 |     29312 |
| mysql-bin.000011 |       365 |
| mysql-bin.000012 |       342 |
+------------------+-----------+
12 rows in set (0.00 sec)
MariaDB [(none)]>source /tmp/all2019-11-26-15:45:42.sql

找到全备份二进制日志截止点

[root@Centos7 ~]# grep '^-- CHANGE MASTER TO' /tmp/all2019-11-26-15\:45\:42.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=385;

生成后续二进制日志sql文件

[root@Centos7 ~]# mysqlbinlog /var/mysqllog/mysql-bin.000008 --start-position=385> /tmp/inc.sql
[root@Centos7 ~]# mysqlbinlog /var/mysqllog/mysql-bin.000009 >>/tmp/inc.sql 
[root@Centos7 ~]# mysqlbinlog /var/mysqllog/mysql-bin.000010 >>/tmp/inc.sql 
[root@Centos7 ~]# mysqlbinlog /var/mysqllog/mysql-bin.000011 >>/tmp/inc.sql

导入二进制日志文件

MariaDB [(none)]>source /tmp/ins.sql
MariaDB [mysql]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
| testdb1            |
| wordpress          |
+--------------------+
8 rows in set (0.00 sec)
MariaDB [mysql]> SELECT HOST,USER FROM user;
+-----------------+-------+
| HOST            | USER  |
+-----------------+-------+
| 127.0.0.1       | root  |
| 172.20.%.%      | amin  |
| 172.20.54.1     | mary  |
| 172.20.54.1     | root  |
| 172.20.54.1     | yijie |
| centos7.server0 |       |
| localhost       |       |
| localhost       | root  |
+-----------------+-------+
8 rows in set (0.00 sec)
MariaDB [mysql]> SELECT * FROM hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Feng Qingyang | 100 | NULL   |
|   6 | Huang Rong    |  18 | NULL   |
|   7 | Guo Jing      |  88 | NULL   |
|   8 | Bai Cai       |  22 | NULL   |
|  11 | Hu Yidao      |  33 | NULL   |
|  12 | Lao Miao      |  34 | NULL   |
|  13 | Lao HEI       |  35 | NULL   |
|  14 | Miao Renfeng  |  40 | NULL   |
+-----+---------------+-----+--------+
12 rows in set (0.00 sec)
/ 数据全部恢复

完毕

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java xml节点中包含xml xml节点可以扩展吗

下一篇：java 以状态码响应请求 java常见状态码

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

InnoDB 类型MySql恢复表结构与数据 mysql数据恢复原理

InnoDB 类型MySql恢复表结构与数据 mysql数据恢复原理

浅谈MYSQL之备份恢复

备份工具mysqldump

常用选项参数

Mysqldump使用引擎差异

MyISAM

InnoDB

分库备份语句

生产环境实战备份策略

InnoDB建议备份策略

MyISAM建议备份策略

实验：

使用mysqldump实现“完全+binlog”方式的迁移单个数据库操作

破坏源数据库然后进行修复实验

51CTO博客