2.mysqldump实现逻辑备份
2.1备份一个数据库
mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]] > 备份文件名
称.sql
说明： 备份的文件并非一定要求后缀名为.sql，例如后缀名为.txt的文件也是可以的。
举例：
mysqldump -u root -p lilishop > lilishop.sql -- 默认当前文件夹
mysqldump -u root -p lilishop > /var/lib/mysql/lilishop.sql -- 绝对路径
2.2 备份全部数据库
若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数：
mysqldump -uroot -p --all-databases > all_database.sql
mysqldump -uroot -p -A > all_database.sql
2.3 备份部分数据库
使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：
mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名
称.sql
举例：
mysqldump -uroot -p --databases atguigu atguigu12 >two_database.sql
mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql
2.4 备份部分表
mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql
举例：备份atguigu数据库下的book表
mysqldump -uroot -p atguigu book> book.sql
备份多张表使用下面的命令，比如备份book和account表：
#备份多张表
mysqldump -uroot -p atguigu book account > 2_tables_bak.sql
2.5 备份单表的部分数据
有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了。where后面附带需要满足的条件。
举例：备份student表中id小于10的数据：
mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql
2.6 排除某些表的备份
如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 --ignore-table 可以完成这个功能。
mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql
2.7 只备份结构或只备份数据
仅备份结构：
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql
只备份数据：
mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql
2.8 mysqldump常用选项
mysqldump其他常用选项如下：
--skip-add-locks： 取消每个表导出之前加lock table的操作
--skip-extended-insert： 一条SQL包含一行数据的形式导出数据
--add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。
--add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。
--add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
--all-database, -A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。
--comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skip-comments与--comments=0的结果相同。默认值为1，即包括额外信息。
--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。
--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、
MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者
no_field_options。
--complete_insert, -c：使用包括列名的完整的INSERT语句。
--debug[=debug_options], -#[debug_options]：写调试日志。
--delete，-D：导入文本文件前清空表。
--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。
--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master-data。
--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可
以加速插入。
--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
--force，-f：在表转储过程中，即使出现SQL错误也继续。
--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关
闭--single-transaction和--lock-tables。
--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例
如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。
--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语句，如果给出-
-database或--all-database选项，就包含到输出中。
--no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。
--no-data，-d：不写表的任何行信息，只转储表的结构。
--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，
但可以用--skip-opt禁用。
--password[=password]，-p[password]：当连接服务器时使用的密码。
-port=port_num，-P port_num：用于连接的TCP/IP端口号。
--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。
--replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--
replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不
指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
--silent，-s：沉默模式。只有出现错误时才输出。
--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。
--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。
--verbose，-v：冗长模式，打印出程序操作的详细信息。
--xml，-X：产生XML输出
运行帮助命令 mysqldump --help ，可以获得特定版本的完整选项列表。
提示 如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用--skip-opt禁用。如果使用最新版本mysqldump程序备份数据，并用于恢复到比较旧版本的MySQL服务器中，则不要使用--opt或-e选项。
3. mysql命令恢复数据
基本语法：
mysql –u root –p [dbname] < backup.sql
3.1 单库备份中恢复单库
使用root用户，将之前练习中备份的atguigu.sql文件中的备份导入数据库中，命令如下：如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示：
mysql -uroot -p < atguigu.sql
否则需要指定数据库名称，如下所示：
mysql -uroot -p atguigu4< atguigu.sql
3.2 全量备份恢复
如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：
mysql –u root –p < all.sql
执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库。
3.3 从全量备份中恢复单库
可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。
sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql
> atguigu.sql
#分离完成后我们再导入atguigu.sql即可恢复单个库
3.4 从单库备份中恢复单表
这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。举例：我们有atguigu整库的备份，但是由于class表误操作，需要单独恢复出这张表。
cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' >
class_structure.sql
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复
use atguigu;
mysql> source class_structure.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> source class_data.sql;
Query OK, 1 row affected (0.01 sec)
3.5source命令导入多个sql文件
source命令
如果需要导入sql文件,可以使用mysql中的source 指令 :
source /root/xxxxx.sql
批量导入一个文件夹种的sql文件：
创建一个allsql.sql文件：
source D:\IdeaProjects\mall4cloud\db\mall4cloud_auth.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_biz.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_leaf.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_multishop.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_nacos.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_order.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_payment.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_platform.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_product.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_user_area_data.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_rbac.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_seata.sql
source D:\IdeaProjects\mall4cloud\db\mall4cloud_user.sql
使用命令：
source allsql.sql
6. 表的导出与导入
6.1 表的导出
1. 使用SELECT…INTO OUTFILE导出文本文件
在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。
举例：
mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。
查询secure_file_priv值：
mysql> SHOW GLOBAL VARIABLES LIKE '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.02 sec)
上面结果中显示，secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录，SQL语句如下。
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";
查看 /var/lib/mysql-files/account.txt文件。
1 张三 90
2 李四 100
3 王五 0
2. 使用mysqldump命令导出文本文件
举例1：使用mysqldump命令将将atguigu数据库中account表中的记录导出到文本文件：
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account
mysqldump命令执行完毕后，在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件。
举例2 ： 使用mysqldump将atguigu数据库中的account表导出到文本文件，使用FIELDS选项，要求字段之
间使用逗号“，”间隔，所有字符类型字段值用双引号括起来：
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminated-by=',' --fields-optionally-enclosed-by='"'
常用命令
导出：
-- 每条语句一条insert 会锁表 -t表示没有表结构
mysqldump -uroot -p -t --skip-extended-insert ry-vue sys_dept > sys_dept.sql
-- 一条insert语句 会锁表 -t表示没有表结构
mysqldump -uroot -p -t --skip-extended-insert ry-vue sys_dept > sys_dept.sql
-- 每条语句一条insert 不会锁表 -t表示没有表结构
mysqldump -uroot -p -t --skip-add-locks --skip-extended-insert ry-vue sys_dept > sys_dept.sql
-- 只有表结构
mysqldump -uroot -p -d ry-vue sys_dept > sys_dept_ddl.txt
-- 表结构加数据 不锁表
mysqldump -uroot -p --skip-add-locks --skip-extended-insert ry-vue sys_dept > sys_dept.sql
导入：
-- 导入表
source ./sys_dept.sql
mysql -u root -p ry-vue < sys_dept.sql
