MySQL之逻辑备份与恢复

逻辑备份简介：

备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

本质：导出的是SQL语句文件

优点：不论是什么存储引擎，都可以用mysqldump备成SQL语句

缺点：速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份。

提供三种级别的备份，表级，库级和全库级

逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

数据一致，服务可用。:如何保证数据一致，在备份的时候进行锁表会自动锁表。锁住之后在备份

本身为客户端工具:
远程备份语法: # mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql
本地备份语法: # mysqldump  -u用户名  -p密码   数据库名  > 备份文件.sql

***-A, --all-databases*** \
备份所有库
 
**-B**, **--databases bbs test mysql** 备份多个数据库
 
**--no-data，-d**\
不导出任何数据，只导出数据库表结构。

MYSQL逻辑备份

1.备份所有的库

 [root@new opt]# mysqldump -uroot -pzyq123 -A > alldatabases.sql

语法： mysqldump   -uroot   -p'密码'    -A   >   文件名 

-A就是所有库，也可以是  -all --databases

此命令是将结果都输出重定向到一个文件当中，不然全部输出到终端屏幕不好看

[root@new opt]# ls
alldatabases.sql

查看文件内容 

1.2 备份部分库

[root@new opt]# mysqldump -uroot -pzyq123 -B company mysql > company.mysql-`date +%F`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@new opt]# ls
alldatabases.sql  company.mysql-2023-09-28.sql

语法： mysqldump  -uroot   -p'密码'  -B 库名  库名   > 文件名

出现[Warning] Using a password on the command line interface can be insecure.

意思是在命令行界面上使用密码可能不安全。 正常现象。

输出重定向的时候在文件名中加上 `date +%F`     就可以在之后备份回复的时候知道这是什么时候备份的文件 。

1.2.1 备份部分库2

[root@new opt]# mysqldump -uroot -pzyq123 --databases db1 mysql > db1-mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
 
[root@new opt]# ls
alldatabases.sql  company.mysql-2023-09-28.sql  db1-mysql.sql

1.3 备份哪个库的哪个表（表结构+内容）

mysql> use db1;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| hh            |
| idol          |
+---------------+
 
备份db1库下的hh表
[root@new ~]# mysqldump -uroot -pzyq123 db1 hh > db1-hh.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@new ~]# ls
db1-hh.sql
 

语法： mysqldump -uroot -p‘密码’  库名  表名  

如果想备份该库下 多个表，直接在库名后面+表名字即可  用空格隔开 不需要加逗号。

1.4 备份表结构

[root@new ~]# mysqldump -uroot -pzyq123 -d  school student > school-student.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@new ~]# ls
db1-hh.sql  school-student.sql

语法： mysqldump    -uroot   -p‘密码’   -d  库名 表名 

我的记法是：在MySQL里 查看表的结构是desc  因此这里备份表的结构就是 -d

1.5 备份表的数据

mysql> show variables like 'secure%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_auth      | ON                    |
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
 
 mysql> select * from mysql.user into outfile '/var/lib/mysql-files/a.txt';
Query OK, 6 rows affected (0.00 sec)
[root@new mysql-files]# pwd
/var/lib/mysql-files
[root@new mysql-files]# ls
a.txt

备份数据库表的数据是需要到MySQL里面操作的

语法： mysql >  select * from mysql.user  into outfile  '/var/lib/mysql-files/新文件名字'

在Linux的MySQL中，使用"show variables like ‘secure%’“命令可以查看与安全性相关的变量配置。根据引用和引用的内容，secure_auth参数表示是否启用安全认证，secure_file_priv参数表示导出数据时的安全文件路径。在引用和引用中的例子中，secure_auth的值为OFF，secure_file_priv的值分别为”/"和NULL。

1.6 可以将备份表的数据导出到本地系统，以excel的形式查看

以刚才的a.txt为例

[root@new mysql-files]# sz a.txt.sql 
 
rz是将包上传到服务器，sz是将包 文件下载到本地
如果没有该命令  则下载
 # yum -y  install lrzsz

下载到本地用excel打开，将文件的格式改成以.xlsx或者.xls的形式

打开以后的样子如下：

数据库恢复

2.1 恢复数据库，命令行恢复

# 首先将数据库备份
[root@new opt]# mysqldump -uroot -pzyq123 --databases db1 > db1.sql
 
#删除数据库db1
mysql> drop database db1;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
 
#恢复数据库！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
[root@new opt]# mysql -uroot -p'zyq123' < db1.sql 
 
#查看是否恢复成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| db1                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
 
 
mysql> use db1;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| hh            |
| idol          |
+---------------+
2 rows in set (0.00 sec)

语法： mysql -uroot  -p'密码'  < 备份数据库的文件的路径

2.1.2数据库恢复之MySQL里恢复

mysql> source /opt/db1.sql;     #恢复数据库！！！！
Query OK, 0 rows affected (0.00 sec)
...
Query OK, 0 rows affected, 1 warning (0.00 sec)
...
Database changed
...
Query OK, 0 rows affected, 1 warning (0.00 sec)
...
Query OK, 0 rows affected (0.00 sec)
 
mysql> show databases;     #查看是否恢复成功
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| db1                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

语法：mysql >  source  +备份数据库的路径

2.2 恢复表

#首先删除db1库的一个hh表
mysql> drop table hh;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| idol          |
+---------------+
 
[root@new opt]# mysql -uroot -p'zyq123' db1 < db1.sql    #恢复表
 
 
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| hh            |
| idol          |
+---------------+
2 rows in set (0.00 sec)

语法：mysql  -uroot   -p'密码'  库名  < 存放该表的库所在路径

2.3 恢复表的结构

mysql -uroot -p'密码'  -D 库名  < 备份存放库的路径

2.4 恢复表中数据

#我们首先将hh表的数据备份到/var/lib/mysql-files/db1-hh.sql
mysql> select * from db1.hh into outfile '/var/lib/mysql-files/db1-hh.sql';
 
#将hh表的数据全部清除
mysql> truncate hh;
 
#恢复hh表的数据
mysql> load data infile '/var/lib/mysql-files/db1-hh.sql' into table hh;
 
 
#查看是否恢复
mysql> select * from hh;
+----+------+--------+-------+
| id | name | gender | hobby |
+----+------+--------+-------+
|  1 | tom  | 男     | cf    |
+----+------+--------+-------+
1 row in set (0.00 sec)

语法：mysql >  load  data  infile  '/var/lib/mysql-files/b.txt'   into  table  要恢复表数据的表名 ;

引号里面是存放备份表数据的路径，语法可以记为  load data infile 加载数据文件进来， into  table  到这个表里面来！