Mysql—逻辑备份数据库、恢复数据库

逻辑备份

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

本质导出的是SQL语句文件

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

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

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

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

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

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

1.常用备份选项

-A, --all-databases
备份所有库

例：mysqldump -uroot -p'password' -A > /opt/a.sql

解析：-A后面不需要指定东西，直接备份整个库

-B, –databases bbs test mysql
备份多个数据库

例：mysqldump -uroot -p'password' -B db1 db2 db3 > /opt/a.sql

解析：-B 后面可以跟多个数据库，使用空格隔开

导出指定表

例：mysqldump -uroot -p'password' db1 table1 table2 > /opt/a.sql

解析：如果不加-B，后面默认是 库名 表名 表名 ，后面的表必须是前面的库里面的，库名是唯一的，但是表名不是。

–no-data，-d
不导出任何数据，只导出数据库表结构。

例：mysqldump -uroot -p'password' db1 -d > /opt/a.sql

解析：-d 前面需要指定好我们要备份什么数据，-d指的是只导出表的结构。

2.备份表

语法: # mysqldump -u root -p1 db1 t1 > /db1.t1.bak	

备份单个表：
# 创建备份目录
[root@mysql-server ~]# mkdir /home/back  
# 备份company库中的new_t1表 到/home/back/new_t1.bak中。
[root@mysql-server ~]# mysqldump -uroot -p'password' company new_t1 > /home/back/new_t1.bak  

mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份多个表：
语法: mysqldump -u root -p1 db1 t1 t2 > /db1.t1_t2.bak
# 备份company库中的new_t1表和new_t2表 到/home/back/new_t1_t2.bak中。
[root@mysql-server ~]# mysqldump -uroot -p'qf123' company new_t1 new_t2  > /home/back/new_t1_t2.bak

3、备份库

备份一个库：相当于将这个库里面的所有表全部备份。如果库不存在，需要手动创建。
语法: # mysqldump -u root -p1 db1 > /db1.bak
# 备份company库 到/home/back/company.bak 文件中。
[root@mysql-server ~]# mysqldump -uroot -p'qf123' company > /home/back/company.bak
备份多个库（-B）如果库不存在，会创建：
语法：mysqldump  -u root -p1 -B  db1 db2 db3 > /db123.bak
# 备份company库和testdb库 到/home/back/company_testdb.bak 文件中。
[root@mysql-server ~]# mysqldump -uroot -p'password' -B company testdb > /home/back/company_testdb.bak
备份所有的库：
语法：# mysqldump  -u root -p1 -A > /alldb.bak
# 备份所有库 到/home/back/allbase.bak文件中。
[root@mysql-server ~]# mysqldump -uroot -p'password' -A > /home/back/allbase.bak

4、恢复数据库和表

为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。

为实验效果先将刚才备份的数据库和表删除了。登陆数据库：

[root@mysql-server ~]# mysql -uroot -ppassword
mysql> show databases;

mysql> drop database company;
mysql> \q

5、恢复库

登陆mysql创建一个库
mysql> create database company;
恢复：
# 使用输入重定向的方式，将/home/back/company.bak文件恢复到mysql中的company库。
[root@mysql-server ~]# mysql -uroot -p'password' company < /home/back/company.bak

6、恢复表

登陆到刚才恢复的库中将其中的一个表删除掉
mysql> show databases;
mysql> use company
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employee5         |
| new_t1            |
| new_t2            |
+-------------------+
mysql> drop table new_t1;
开始恢复:
恢复方式一：
mysql> source /home/back/new_t1.bak;  -------加路径和备份的文件 
恢复方式二：
# mysql -u root -p1  db1  < db1.t1.bak
                     库名    备份的文件路径

7、备份及恢复表结构

1.备份表结构：
语法：mysqldump  -uroot -p'password' -d databasename tablename > dump.sql
# 将company库中的employee5表的结构备份到 /home/back/emp.bak 文件中。
[root@mysql-server ~]# mysqldump -uroot -p'password' -d company employee5 > /home/back/emp.bak
恢复表结构：
登陆数据库创建一个库
mysql> create database t1;
语法：# mysql -u root -p1 db1  < db1.t1.bak
[root@mysql-server ~]# mysql -uroot -p'password' t1 < /home/back/emp.bak

8、在库里数据的导入导出。没有表结构。

注：在工作中，我们使用更多的是mysqldump，一下这种方法，了解即可。

表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。

mysql> show variables like "secure_file_priv";  ----查询导入导出的目录。（保证数据安全做共享） 

修改安全文件目录（当我们需要文件导出到其他路径的时候）：
1.创建一个目录：mkdir  路径目录
[root@mysql-server ~]# mkdir /sql
2.修改权限
[root@mysql-server ~]# chown mysql.mysql /sql
3.编辑配置文件：
vim /etc/my.cnf
在[mysqld]里追加
secure_file_priv=/sql
# 如果加了上述配置，mysql启动失败，那么，关闭selinux
4.重新启动mysql.

1.导出数据
登陆数据查看数据，如果没有这个库和表，我们就创建一下，并插入一下测试数据。
mysql> show databases;    #找到test库
mysql> use test   #进入test库
mysql> show tables;  #找到它t3表
mysql> select * from t3 into outfile '/sql/test.t3.bak';
添加修饰的：
mysql> select * from t3 into outfile '/sql/test.t3.bak1' fields terminated by ',' lines terminated by '\n';
注： 指定导出的格式，字段分隔符是逗号，记录分隔符是换行。一般情况下，我们不去修改分隔符。
fields terminated by ',' ：字段以逗号分割
lines terminated by '\n'：结尾换行

2.数据的导入
先将原来表里面的数据清除掉，只保留表结构
mysql> delete from t3;
mysql> load data infile '/sql/test.t3.bak' into table t3;
# 如果将数据导入别的表，需要创建这个表并创建相应的表结构，如果我们上面导出时修改了分隔符，那么这里也需要加上。