MySQL-数据备份

最新推荐文章于 2024-05-07 10:03:08 发布

xiaotanggao

最新推荐文章于 2024-05-07 10:03:08 发布

阅读量427

点赞数

分类专栏： linux运维（所有） linux远程服务文章标签： mysql 数据库

本文链接：https://blog.csdn.net/gxy_learning/article/details/120569115

版权

linux运维（所有）同时被 2 个专栏收录

58 篇文章 9 订阅

订阅专栏

linux远程服务

22 篇文章 0 订阅

订阅专栏

MySQL

MySQL数据备份

概述

备份：能够防止由于机械故障以及人为误操作带来的数据丢失，如将数据库文件保存在了其它地方
冗余：数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，如主备模式、数据库集
备份内容：数据文件+配置文件（my.cnf）+日志文件
备份需要考虑的因素
- 必须制定详细的备份计划（备份频率、时间点、周期）
- 备份数据应该放在非数据库本地，建议有多份副本
- 必须做好数据恢复的演练
- 根据数据应用的场合、特点选择正确的备份工具。
- 数据的一致性
- 服务的可用性

体系结构

把工作流程相同功能划分为一个结构里，可以形成MySQL的体系结构，如下图。值得一提的是MySQL存储引擎层使用的是插件式结构，可以根据场景不同选择不同的存储引擎。不同的存储引擎对于上层的应用程序或者是底层的文件系统是透明的。我们把MySQL的体系结构称为插件式存储引擎结构。

在这里插入图片描述

存储引擎

概述

存储引擎：简单来说，就是数据的存储方式

功能

数据读写
数据安全
提高性能
热备份
自动故障恢复
高可用方面支持

种类

InnoDB

⽀持事务，主要⾯向在线事务（OLTP）处理的应⽤
⾏锁设计、⽀持外键、⽀持类似于Oracle的⾮锁定读
从5.5.8版本开始，InnoDB存储引擎是默认的存储引擎
将数据放在⼀个逻辑的表空间中，这个表空间就像⿊盒⼀样由InnoDB存储引擎⾃身进⾏管理

#创建一个测试数据库
[root@mysql ~]# mysql -e 'create database db_text default charset=utf8;' -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# cd /mysql_3306/data/
[root@mysql data]# ls
... db_text ... #该目录中即刻产生相应文件
[root@mysql data]# cd db_text/
[root@mysql db_text]# ls
db.opt #该文件用于存放数据库的编码格式

#创建表，使用InnoDB引擎
[root@mysql db_text]# mysql -e 'use db_text;create table tb_user(id int, name char(11)) engine=innodb;' -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql db_text]# ls
db.opt  tb_user.frm  tb_user.ibd
#.frm ：框架文件，定义数据表结构
#.ibd：索引文件+数据文件
[root@mysql db_text]# cd ../
[root@mysql data]# ls
... ibdata1 ... #创建表的同时，产生该共享文件，所以InnoDB引擎的数据备份不能简单的通过拷贝方式实现，必须使用专业的备份工具

MyISAM

不⽀持事务、表锁设计、⽀持全⽂索引，主要⾯向OLAP数据库应⽤
它的缓冲池只缓存索引⽂件，不缓存数据⽂件
MyISAM存储引擎表由MYD和MYI组成，MYD⽤来存放数据⽂件，MYI⽤来存放索引⽂件

#删除上述使用InnoDB引擎创建的表
[root@mysql data]# mysql -e 'use db_text;drop table tb_user;' -uroot -p123

#创建表，使用MyISAM引擎
[root@mysql db_text]# mysql -e 'use db_text;create table tb_user(id int, name char(11)) engine=myisam;' -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql db_text]# ls
db.opt  tb_user.frm  tb_user.MYD  tb_user.MYI
#.frm ：框架文件，定义数据表结构 
#.MYI ：INDEX索引，主要用于存放索引文件
#.MYD ：数据文件

MEMORY
- 在内存中存储所有数据，应用于对非关键数据进行快速查找的场景。
- Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失
- 应用场景：快速定位记录
ARCHIVE
- Archive存储引擎只⽀持INSERT和SELECT操作，从5.1开始⽀持索引
- 使⽤zlib算法将数据⾏进⾏压缩后存储，压缩⽐⼀般可达1：10
- ⾮常适合存储归档数据，如⽇志信息
- 使⽤⾏锁来实现⾼并发的插⼊操作，但是其本身并不是事务安全的存储引擎，其设计主要⽬标是提供告诉的插⼊和压缩功能
FEDERATED
- Federated存储引擎表并不存放数据，它只是指向⼀台远程MySQL数据库服务器上的表。这⾮常类似于 SQLServer的链接服务器和Oracle的透明⽹关，不同的是它只⽀持MySQL数据库表，不⽀持异构数据库表。
EXAMPLE
- 这种存储引擎用以保存阐明如何开始写新的存储引擎的 MySql 源码的例子。它主要针对于有兴趣的开发人员。这种存储引擎就是一个啥事也不做的 “存根”。你可以使用这种引擎创建表，但是你无法向其保存任何数据，也无法从它们检索任何索引。
BLACKHOLE
- 黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于 DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。
MERGE
- 允许 MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组，并把它们作为一个对象进行引用。适用于超大规模数据场景，如数据仓库。
NDBCLUSTER
- 是⼀个集群存储引擎，类似于Oracle的RAC集群，不过其结构与Oracle的share everything不同的是，它使⽤的是share nothing的集群结构，可以提供更⾼的可⽤性
- 数据全部放在内存中（从5.1版本开始，可以将⾮索引数据放在磁盘上）因此主键查找的速度极快，并且通过添加NDB数据存储节点可以线性地提⾼数据库性能，是⾼可⽤、⾼性能的集群系统
- 其连接操作是在数据库层完成的，而不是在存储引擎层完成的。这意味着，复杂的连接操作需要巨⼤的网络开销，因此查询速度很慢
CSV
- 它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据，以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引，你最好是在普通操作中将数据放在 InnoDB 表里，只有在导入或导出阶段使用一下 CSV 表。
Maria存储引擎
- 新开发的引擎，设计主要⽬标是⽤来取代原来的MyISAM存储引擎，从⽽成为MySQL的默认存储引擎
- ⽀持缓存数据和索引⽂件，应⽤了⾏锁设计，提供了MVCC功能，⽀持事务和⾮事务安全的选项，以及更好的BLOB字符类型的处理性能。

查看

mysql> show engines;

在这里插入图片描述

面试题：InnoDB与MyISAM的区别

InnoDB：支持事务，支持行级别锁，支持热备，支持自动故障恢复和MVCC，支持外键
MyISAM ：不支持事务，支持表级锁，支持温备，不支持自动故障恢复和MVCC，擅长数据的查询，支持全文索引

重要日志文件

日志类型	说明	存放信息	存放位置
error	错误日志	存放错误信息	mysql的data下面的mysql.err，可在配置文件中更改
binlog	二进制日志	数据库的所有更改操作	自定义，建议data/binlog
redo log	事务日志	存储InnoDB表数据的记录修改	默认以ib_logfile0,ib_logfile1名称存在

binlog二进制日志用于主从复制中，master主服务器将二进制日志中的更改操作发送给slave从服务器，从服务器执行这些更改操作是的和主服务器的更改相同

二进制日志默认关闭，需要手动更改配置文件开启

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

[root@mysql mysql_3306]# echo  'server-id=1' >> my.cnf 
[root@mysql mysql_3306]# echo  'log-bin=/mysql_3306/data/binlog' >> my.cnf 
[root@mysql mysql_3306]# tail -2 my.cnf 
server-id=1
log-bin=/mysql_3306/data/binlog

[root@mysql /]# service mysql_3306 restart
Shutting down MySQL..                                      [  确定  ]
Starting MySQL..                                           [  确定  ]

[root@mysql mysql_3306]# cd data
[root@mysql data]# ls
... binlog.000001 ... #产生了二进制日志文件

[root@mysql data]# mysql -e 'show variables like "%log_bin%";' -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------+-------------------------------+
| Variable_name                   | Value                         |
+---------------------------------+-------------------------------+
| log_bin                         | ON                            |
| log_bin_basename                | /mysql_3306/data/binlog       |
| log_bin_index                   | /mysql_3306/data/binlog.index |
| log_bin_trust_function_creators | OFF                           |
| log_bin_use_v1_row_events       | OFF                           |
| sql_log_bin                     | ON                            |
+---------------------------------+-------------------------------+

备份方式

按备份内容分

逻辑备份：备份SQL语句（DDL DML DCL）
- **适用于中小型数据库，效率相对较低。
- 一般在数据库正常提供服务的前提下进行
- 常用工具：mysqldump、mydumper、into outfile（表的导出导入）等
物理备份：直接备份数据库文件（包括数据文件 + 配置文件 + 日志文件）
- 适用于大型数据库环境，不受存储引擎的限制，
- 对MySQL版本要求高
- 一般在数据库关闭或者不能正常提供服务的前提下进行的备份
- 常用工具：tar、cp、xtrabackup（数据库可以正常提供服务）、lvm snapshot、rsync等

按备份数据位置分类

热备：硬盘以及内存中的⼀些数据进⾏备份，如数据冗余、AB复制、主从复制
冷备：数据库硬盘中的数据
- 备份简单，只要拷⻉相关⽂件即可
- 易于跨平台
- 恢复简单，只要把⽂件恢复到相关位置即可
- 恢复速度快，不需要执⾏任何sql语句，也不需要重新建索引
- 冷备⽂件通常⽐逻辑⽂件⼤很多

按备份方法分

完全备份：又叫全量备份（全备），对数据库进⾏⼀个完整的备份
增量备份：在上次的完全备份基础上对更新的数据进⾏备份
⽇志备份：⼆进制⽇志备份–>当数据库宕机之后进⾏数据恢复的依据

备份工具

mysqldump

社区版安装包自带
逻辑备份
支持全量备份，无法直接做增量备份
需要MYISAM引擎锁表，Innodb引擎锁行
速度较慢,导入时可能会出现格式不兼容的突发状况，数据量很大时不推荐使用
提供三种级别的备份：表级，库级和全库级

mysqlhotcopy

社区版安装包自带
物理备份
仅支持MYISAM数据引擎
是使用perl写的一个脚本，本质上是使用锁表语句后再拷贝数据

mysqlbackup

企业版安装包自带
支持在线备份
支持增量备份
支持部分备份
支持在某个特定时间的一致性状态的备份

xtrabackup

xtrabackup：支持InnoDB和XtraDB的存储引擎，支持在线热备份
innobackupex：支持myisam的存储引擎，在处理myisam时需要加一个读锁
新版本MySQL将上述xtrabackup和innobackupex兼并为xtrabackup
优点
- 物理备份，备份过程快速、可靠
- 支持增量备份，更为灵活
- 备份过程不会打断正在执行的事务
- 能够基于压缩等功能节约磁盘空间和流量
- 自动实现备份检验
- 还原速度快
缺点
- 只能对innodb表增量备份，myisam表备份时是全备
- innobackupex备份MyISAM表之前要对全库进行加READ LOCK，阻塞写操作，若备份是在从库上进行的话会影响主从同步，造成延迟。对InnoDB表备份不会阻塞读写
xtrabackup备份原理
- innobackupex首先会启动一个xtrabackup_log后台检测的进程，实时检测mysql的redo log的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中
- 物理拷贝innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方
- 复制结束后，执行flush table with read lock操作进行全库锁表准备备份非InnoDB文件
- 物理复制.frm .myd .myi等非InnoDB引擎文件到备份目录
- 查看二进制日志的位置
- 解锁表unlock tables
- 停止xtrabackup_log进程
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pyPRPmoJ-1632991111826)(…/%E5%9B%BE%E7%89%87%E6%94%BE%E7%BD%AE%E7%82%B9/image-20200829144019688.png)]

mydumper

逻辑备份
在15年11月后软件已经不再更新了
多线程备份

Mysqldump实现逻辑备份

语法

表级别备份
mysqldump [OPTIONS] database tables
库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

常用选项	描述说明
–flush-logs, -F	备份前刷新二进制日志
–flush-privileges	刷新用户和授权信息
–lock-all-tables, -x	针对MyISAM引擎，备份前锁定所有表
–lock-tables, -l	针对MyISAM引擎，备份前锁定要备份的库中的所有表
–single-transaction	适用InnoDB引擎，保证一致性，服务可用性
–master-data=2	表示将二进制日志位置和文件名写入到备份文件中并在dump文件中注释掉这一行
–master-data=1	表示将二进制日志位置和文件名写入到备份文件中在dump文件中不注释这一行

备份

准备备份文件夹
[root@mysql ~]# mkdir /tmp/sqlbak

表备份（拟备份db1数据库中的tb_student表）
[root@mysql ~]# mysqldump db1 tb_student > /tmp/sqlbak/tb_student_bak.sql -p123
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ls /tmp/sqlbak/
tb_student_bak.sql

库备份（拟备份db1数据库）
[root@mysql ~]# mysqldump --databases db1 > /tmp/sqlbak/db1_bak.sql -p123
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ls /tmp/sqlbak/db1_bak.sql 
/tmp/sqlbak/db1_bak.sql

全库备份
#MySQL全库备份，必须开启二进制日志
mysql> show variables like "%log_bin%";
+---------------------------------+-------------------------------+
| Variable_name                   | Value                         |
+---------------------------------+-------------------------------+
| log_bin                         | ON                            |
| log_bin_basename                | /mysql_3306/data/binlog       |
| log_bin_index                   | /mysql_3306/data/binlog.index |
| log_bin_trust_function_creators | OFF                           |
| log_bin_use_v1_row_events       | OFF                           |
| sql_log_bin                     | ON                            |
+---------------------------------+-------------------------------+
[root@mysql ~]# mysqldump --all-databases --master-data --single-transaction > /tmp/sqlbak/allsql_bak.sql -p
Enter password: 
[root@mysql ~]# ll /tmp/sqlbak/
总用量 872
-rw-r--r-- 1 root root 872697 9月  26 16:25 allsql_bak.sql
-rw-r--r-- 1 root root   8207 9月  26 16:00 db1_bak.sql
-rw-r--r-- 1 root root   2328 9月  26 15:42 tb_student_bak.sql

还原

方法1

还原表
#模拟数据丢失
mysql> use db1
Database changed
mysql> drop table tb_student;

[root@mysql ~]# mysql db1 < /tmp/sqlbak/tb_student_bak.sql -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -e 'select * from db1.tb_student;' -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  6 | 小乔   |   18 | 女     | 湖北省荆州市       |
|  7 | 曹操   |   36 | 男     | NULL               |
+----+--------+------+--------+--------------------+

还原库
[root@mysql ~]# mysql -e 'drop database db1;' -p
Enter password: 
[root@mysql ~]# mysql < /tmp/sqlbak/db1_bak.sql -p
Enter password: 
[root@mysql ~]# mysql -e 'show databases;' -p
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| db_text            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

还原全库
[root@mysql ~]# mysql -e 'drop database db1;' -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -e 'drop database db2;' -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -e 'drop database db3;' -p123
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mysql ~]# mysql < /tmp/sqlbak/allsql_bak.sql -p
Enter password: 
[root@mysql ~]# mysql -e 'show databases;' -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| db_text            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

方法2

还原表
#模拟数据丢失
mysql> use db1
Database changed
mysql> drop table tb_student;

mysql> source /tmp/sqlbak/tb_student_bak.sql
mysql> select * from tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  6 | 小乔   |   18 | 女     | 湖北省荆州市       |
|  7 | 曹操   |   36 | 男     | NULL               |
+----+--------+------+--------+--------------------+

还原库
[root@mysql ~]# mysql -e 'drop database db1;' -p
Enter password: 
[root@mysql ~]# mysql -uroot  -p123
mysql> source /tmp/sqlbak/db1_bak.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| db_text            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

还原全库
mysql> drop database db1;
mysql> drop database db2;
mysql> drop database db3;

mysql> source /tmp/sqlbak/allsql_bak.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| db_text            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysqldump + binlog实现增量备份

数据准备
mysql> use db1;
Database changed
mysql> select * from tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  6 | 小乔   |   18 | 女     | 湖北省荆州市       |
|  7 | 曹操   |   36 | 男     | NULL               |
+----+--------+------+--------+--------------------+

开启二进制日志
mysql> show variables like "%log_bin%";
+---------------------------------+-------------------------------+
| Variable_name                   | Value                         |
+---------------------------------+-------------------------------+
| log_bin                         | ON                            |
| log_bin_basename                | /mysql_3306/data/binlog       |
| log_bin_index                   | /mysql_3306/data/binlog.index |
| log_bin_trust_function_creators | OFF                           |
| log_bin_use_v1_row_events       | OFF                           |
| sql_log_bin                     | ON                            |
+---------------------------------+-------------------------------+

全库备份
[root@mysql data]# service mysql_3306 restart
Shutting down MySQL....                                    [  确定  ]
Starting MySQL.                                            [  确定  ]
[root@mysql data]# rm -rf /tmp/sqlbak/*
[root@mysql data]# mysqldump --all-databases --flush-logs --master-data --single-transaction > /tmp/sqlbak/allsql_bak.sql -p
Enter password: 

操作数据库
mysql> insert into tb_student values (null,'吕布',37,'男','安徽省亳州市');
mysql> delete from tb_student where id=6;
mysql> select * from tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  7 | 曹操   |   36 | 男     | NULL               |
|  8 | 吕布   |   37 | 男     | 安徽省亳州市       |
+----+--------+------+--------+--------------------+

模拟数据库损坏，数据丢失
mysql> drop database db1;

备份最近二进制文件
[root@mysql data]# cp /mysql_3306/data/binlog.000003 /tmp/sqlbak/

全库恢复
[root@mysql data]# mysql < /tmp/sqlbak/allsql_bak.sql -p
Enter password:
mysql> use db1;
mysql> select * from tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  6 | 小乔   |   18 | 女     | 湖北省荆州市       |
|  7 | 曹操   |   36 | 男     | NULL               |
+----+--------+------+--------+--------------------+

通过binlog增量备份还原全库备份后改变的数据
[root@mysql data]# mysqlbinlog /tmp/sqlbak/binlog.000003
# at 748
#210926 20:21:40 server id 1  end_log_pos 813 CRC32 0xca50700c 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 813
#210926 20:21:40 server id 1  end_log_pos 902 CRC32 0x03c15f22 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1632658900/*!*/;
drop database db1 #该行发现了数据事故，恢复到上一行即可

[root@mysql data]# mysqlbinlog --start-position=4 --stop-position=748 /tmp/sqlbak/binlog.000003 |mysql -p
Enter password: 

mysql> select * from tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  7 | 曹操   |   36 | 男     | NULL               |
|  8 | 吕布   |   37 | 男     | 安徽省亳州市       |
+----+--------+------+--------+--------------------+

数据记录的导入与导出

导出（备份）

指定导出路径
[root@mysql mysql_3306]# echo 'secure_file_priv=/tmp/sqlbak/' >> my.cnf
[root@mysql mysql_3306]# service mysql_3306 restart
Shutting down MySQL....                                    [  确定  ]
Starting MySQL.                                            [  确定  ]

导出
mysql> select * into outfile '/tmp/sqlbak/tb_student.txt' from db1.tb_student;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: db1

ERROR 1 (HY000): Can't create/write to file '/tmp/sqlbak/tb_student.txt' (Errcode: 13 - Permission denied)

报错：Errcode: 13 - Permission denied

原因：缺少该文件的写权限

解决：给mysql用户增加/tmp/sqlbak/文件的写权限

[root@mysql mysql_3306]# setfacl -m u:mysql:rwx /tmp/sqlbak

mysql> select * into outfile '/tmp/sqlbak/tb_student.txt' from db1.tb_student;
Query OK, 7 rows affected (0.00 sec)

[root@mysql mysql_3306]# cat /tmp/sqlbak/tb_student.txt 
1	刘备	33	男	湖北省武汉市
2	貂蝉	18	女	湖南省长沙市
3	关羽	32	男	湖北省荆州市
4	大乔	20	女	河南省漯河市
5	赵云	25	男	江苏省南京市
7	曹操	36	男	\N
8	吕布	37	男	安徽省亳州市

导入（还原）

方法1

清空表
mysql> truncate tb_student;
Query OK, 0 rows affected (0.01 sec)

导入
mysql> load data local infile '/tmp/sqlbak/tb_student.txt' into table tb_student;
Query OK, 7 rows affected, 10 warnings (0.01 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 10

mysql> select * from tb_student;
+----+-----------+------+--------+-----------------------------+
| id | name      | age  | gender | address                     |
+----+-----------+------+--------+-----------------------------+
|  1 | 鍒樺?     |   33 |        | 婀栧寳鐪佹?姹夊競           |
|  2 | 璨傝潐    |   18 |        | 婀栧崡鐪侀暱娌欏競          |
|  3 | 鍏崇窘    |   32 |        | 婀栧寳鐪佽崋宸炲競          |
|  4 | 澶т箶     |   20 |        | 娌冲崡鐪佹集娌冲競          |
|  5 | 璧典簯    |   25 |        | 姹熻嫃鐪佸崡浜?競           |
|  7 | 鏇规搷    |   36 |        | NULL                        |
|  8 | 鍚曞竷    |   37 |        | 瀹夊窘鐪佷撼宸炲競          |
+----+-----------+------+--------+-----------------------------+

字体显示错误解决
mysql> show variables like '%char%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | utf8                        |
| character_set_connection | utf8                        |
| character_set_database   | gbk                         |
| character_set_filesystem | binary                      |
| character_set_results    | utf8                        |
| character_set_server     | latin1                      |
| character_set_system     | utf8                        |
| character_sets_dir       | /mysql_3306/share/charsets/ |
+--------------------------+-----------------------------+
mysql> set character_set_client=gbk;
mysql> set character_set_results=gbk;
mysql> select * from tb_student;
mysql> select * from tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  7 | 曹操   |   36 | 男     | NULL               |
|  8 | 吕布   |   37 | 男     | 安徽省亳州市       |
+----+--------+------+--------+--------------------+

方法2

mysql> truncate tb_student;

[root@mysql mysql_3306]# mysqlimport db1 /tmp/sqlbak/tb_student.txt -p
Enter password: 
db1.tb_student: Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
[root@mysql mysql_3306]# mysql -e 'select * from db1.tb_student;' -p
Enter password: 
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  7 | 曹操   |   36 | 男     | NULL               |
|  8 | 吕布   |   37 | 男     | 安徽省亳州市       |
+----+--------+------+--------+--------------------+

导入/etc/passwd文件到MySQL中

创建passwd表
mysql> use db1;

CREATE TABLE `passwd` (
  `uname` varchar(50) DEFAULT NULL,
  `pass` char(2) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `home` varchar(50) DEFAULT NULL,
  `shell` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制/etc/passwd文件
[root@mysql mysql_3306]# cp /etc/passwd /tmp/sqlbak/passwd.txt

导入
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
db1.passwd: Records: 24  Deleted: 0  Skipped: 0  Warnings: 0
选项说明
--fields-terminated-by=':'，指定导出文件的分隔符为冒号:
--lines-terminated-by='\n'，指定每一行的结尾使用的符号,\n代表换行符

在这里插入图片描述

xtrabackup实现物理备份

实现过程

在这里插入图片描述

软件安装

[root@mysql ~]# rz
传入依赖库libev-4.15-3.el7.x86_64.rpm
[root@mysql ~]# rz
传入percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm
[root@mysql ~]# ls
anaconda-ks.cfg              mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
libev-4.15-3.el7.x86_64.rpm  mysql-boost-5.7.31.tar.gz
mysql-5.7.31                 percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

安装
[root@mysql ~]# rpm -ivh libev-4.15-3.el7.x86_64.rpm 
[root@mysql ~]# yum install -y percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

xtrabackup物理备份&恢复

创建备份账号，开通相应权限
mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
#权限说明
reload和lock tables	：为了执行flush tables with read lock
replication client	 ：为了获取binary log位置
process				 ：显示有关在服务器中执行的线程的信息，允许使用show engine

mysql> flush privileges;

全库备份
[root@mysql ~]# innobackupex --user=admin --password=123 /full_xtrabackup
210927 03:37:01 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

210927 03:37:01  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'ser=admin'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','ser=admin',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1314.
210927 03:37:01 Connecting to MySQL server host: localhost, user: ser=admin, password: set, port: not set, socket: not set
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).

报错：Failed to connect to MySQL server: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2).

原因：innobackupex默认读取/var/lib/mysql/mysql.sock套接字文件，连接失败

解决：-S 指定自己设置的套接字位置

[root@mysql ~]# rm -rf /full_xtrabackup/*
[root@mysql ~]# innobackupex -S /tmp/mysql.sock --user=admin --password=123 /full_xtrabackup
[root@mysql ~]# ls /full_xtrabackup/2021-09-27_03-42-26/
backup-my.cnf  db2  db_text         ibdata1  performance_schema  xtrabackup_binlog_info  xtrabackup_info
db1            db3  ib_buffer_pool  mysql    sys                 xtrabackup_checkpoints  xtrabackup_logfile

预备阶段，把备份过程中产生的日志整合到上面生成的全量备份文件夹中
[root@mysql ~]# innobackupex --user=admin --password=123 --apply-log /full_xtrabackup/2021-09-27_03-42-26/

模拟故障
[root@mysql ~]# rm -rf /mysql_3306/data/*
[root@mysql ~]# pkill mysqld
[root@mysql ~]# ls /mysql_3306/data/
ib_buffer_pool
[root@mysql ~]# rm -rf /mysql_3306/data/*
[root@mysql ~]# ls /mysql_3306/data/
[root@mysql ~]# service mysql_3306 start
Starting MySQL.Logging to '/mysql_3306/data/mysql.err'.
....The server quit without updating PID file (/mysql_3306/[失败]ysql.pid).

数据恢复
[root@mysql ~]# innobackupex --copy-back /full_xtrabackup/2021-09-27_03-42-26/
210927 04:07:25 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
Error: datadir must be specified.

报错：Error: datadir must be specified.

原因：innobackupex工具无法找到MySQL中的数据目录

解决：添加选项，指定自己编写的配置文件–defaults-file=/mysql_3306/my.cnf

[root@mysql ~]# rm -rf /mysql_3306/data/*
[root@mysql ~]# innobackupex --defaults-file=/mysql_3306/my.cnf --copy-back /full_xtrabackup/2021-09-27_03-42-26/

更改/mysql_3306/data文件属主，属组
[root@mysql ~]# chown -R mysql.mysql /mysql_3306/data/
[root@mysql ~]# ll /mysql_3306/data/
drwxr-x--- 2 mysql mysql     4096 9月  27 04:11 db1
drwxr-x--- 2 mysql mysql       74 9月  27 04:11 db2
drwxr-x--- 2 mysql mysql       20 9月  27 04:11 db3
drwxr-x--- 2 mysql mysql       58 9月  27 04:11 db_text
-rw-r----- 1 mysql mysql     1149 9月  27 04:11 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 9月  27 04:11 ibdata1
-rw-r----- 1 mysql mysql 50331648 9月  27 04:11 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 9月  27 04:11 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 9月  27 04:11 ibtmp1
drwxr-x--- 2 mysql mysql     4096 9月  27 04:11 mysql
drwxr-x--- 2 mysql mysql     8192 9月  27 04:11 performance_schema
drwxr-x--- 2 mysql mysql     8192 9月  27 04:11 sys
-rw-r----- 1 mysql mysql       19 9月  27 04:11 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      490 9月  27 04:11 xtrabackup_info

启动MySQL，测试
[root@mysql ~]# service mysql_3306 start
Starting MySQL.Logging to '/mysql_3306/data/mysql.err'.
.                                                          [  确定  ]
[root@mysql ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| db_text            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

xtrabackup实现增量备份

实现过程

在这里插入图片描述

增量备份

创建备份账号，开通相应权限（同上，略）

全量备份
[root@mysql ~]# rm -rf /full_xtrabackup/*
[root@mysql ~]# innobackupex -S /tmp/mysql.sock --user=admin --password=123 /full_xtrabackup/

把全备过程中产生的日志进行整合
[root@mysql ~]# innobackupex --user=admin --password=123 --apply-log --redo-only /full_xtrabackup/2021-09-27_10-14-11/
#选项说明
--apply-log	：表示整合日志
--redo-only	：表示只应用已经提交的事务，不回滚未提交的事务，如果已经回滚了未提交事务，那么就无法再应用增量备份

操作数据库（产生增量数据）
mysql> select * from tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
|  7 | 曹操   |   36 | 男     | NULL               |
|  8 | 吕布   |   37 | 男     | 安徽省亳州市       |
+----+--------+------+--------+--------------------+
mysql> delete from tb_student where id=7 or id=8;
mysql> select * from tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
+----+--------+------+--------+--------------------+

增量备份
[root@mysql ~]# innobackupex -S /tmp/mysql.sock --user=admin --password=123 --incremental /incre_backup --incremental-basedir=/full_xtrabackup/2021-09-27_10-14-11
#选项说明
--incremental：增量备份目录
--incremental-basedir：这个增量是相对于哪个全量的

把增量备份产生的数据以及日志文件整合到全量备份中
[root@mysql ~]# innobackupex --user=admin --password=123 --apply-log /full_xtrabackup/2021-09-27_10-14-11/ --incremental-dir=/incre_backup/2021-09-27_10-34-12

故障模拟
[root@mysql ~]# rm -rf /mysql_3306/data/*
[root@mysql ~]# pkill mysqld
[root@mysql ~]# service mysql_3306 start
Starting MySQL.Logging to '/mysql_3306/data/mysql.err'.
...The server quit without updating PID file (/mysql_3306/d[失败]sql.pid).

数据恢复
[root@mysql ~]# service mysql_3306 start
Starting MySQL.Logging to '/mysql_3306/data/mysql.err'.
...The server quit without updating PID file (/mysql_3306/d[失败]sql.pid).
[root@mysql ~]# innobackupex --defaults-file=/mysql_3306/my.cnf --user=admin --password=123 --copy-back /full_xtrabackup/2021-09-27_10-14-11/
210927 10:45:29 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
Original data directory /mysql_3306/data is not empty!

报错：Original data directory /mysql_3306/data is not empty!

原因：mysql的数据目录非空

解决：清空mysql的数据目录

[root@mysql ~]# rm -rf /mysql_3306/data/*
[root@mysql ~]# innobackupex --defaults-file=/mysql_3306/my.cnf --user=admin --password=123 --copy-back /full_xtrabackup/2021-09-27_10-14-11/

更改/mysql_3306/data文件属主，属组
[root@mysql ~]# chown -R mysql.mysql /mysql_3306/data/

启动MySQL，测试
mysql> select * from db1.tb_student;
+----+--------+------+--------+--------------------+
| id | name   | age  | gender | address            |
+----+--------+------+--------+--------------------+
|  1 | 刘备   |   33 | 男     | 湖北省武汉市       |
|  2 | 貂蝉   |   18 | 女     | 湖南省长沙市       |
|  3 | 关羽   |   32 | 男     | 湖北省荆州市       |
|  4 | 大乔   |   20 | 女     | 河南省漯河市       |
|  5 | 赵云   |   25 | 男     | 江苏省南京市       |
+----+--------+------+--------+--------------------+

xiaotanggao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-数据备份

MySQLMySQL数据备份概述备份：能够防止由于机械故障以及人为误操作带来的数据丢失，如将数据库文件保存在了其它地方冗余：数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，如主备模式、数据库集备份内容：数据文件+配置文件（my.cnf）+日志文件备份需要考虑的因素必须制定详细的备份计划（备份频率、时间点、周期）备份数据应该放在非数据库本地，建议有多份副本必须做好数据恢复的演练根据数据应用的场合、特点选择正确的备份工具。数据的一致性服务的可用性体系结构把工
复制链接

扫一扫

专栏目录