2.9 mysqldump常用选项
mysqldump其他常用选项如下：
--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命令恢复数据
使用mysqldump命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用mysql命令来恢复备份的数据。
mysql命令可以执行备份文件中的CREATE语句和INSERT语句。通过CREATE语句来创建数据库和表。通过INSERT语句来插入备份的数据。
基本语法：
mysql –u root –p [dbname] < backup.sql
其中，dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名时，表示还原文件中所有的数据库。此时sql文件中包含有CREATE DATABASE语句，不需要MysQL服务器中已存在这些数据库。
3.1 单库备份中恢复单库
使用root用户，将之前练习中备份的atguigu.sql文件中的备份导入数据库中，命令如下：
如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示
mysql -uroot -p < atguigu.sql
否则需要指定数据库名称，如下所示
mysql -uroot -p atguigu4< atguigu.sql
演示-原数据库下恢复
mysql> use atguigudb33; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table student; Query OK, 0 rows affected (0.03 sec) mysql> show tables; Empty set (0.00 sec)
[root@centos7-mysql-1 backup]# mysql -uroot -p atguigudb33< atguigudb33.sql Enter password:
mysql> show tables; +-----------------------+ | Tables_in_atguigudb33 | +-----------------------+ | student | +-----------------------+ 1 row in set (0.00 sec) mysql>
演示-新数据库下恢复
mysql> create database atguigudb4; Query OK, 1 row affected (0.00 sec) mysql> use atguigudb4; Database changed mysql> show tables; Empty set (0.01 sec)
[root@centos7-mysql-1 backup]# mysql -uroot -p atguigudb4< atguigudb33.sql Enter password:
mysql> show tables; +-----------------------+ | Tables_in_atguigudb33 | +-----------------------+ | student | +-----------------------+ 1 row in set (0.00 sec) mysql>
3.2 全量备份恢复
如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：
mysql –u root –p < all.sql
mysql -uroot -pxxxxxx < all.sql
执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库。
补充:
如果使用–all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库。对应的sql文件包含有CREATE DATABASE语句，可通过该语句创建数据库。创建数据库后，可以执行sql文件中的USE语句选择数据库，再创建表并插入记录。
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)
演示
mysql> use atguigudb33; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table student; Query OK, 0 rows affected (0.01 sec) mysql>
[root@centos7-mysql-1 backup]# cat atguigudb33.sql |sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `student`/!d;q' > student_structure.sql [root@centos7-mysql-1 backup]# cat atguigudb33.sql | grep --ignore-case 'insert into `student`' >student_data.sql [root@centos7-mysql-1 backup]# ll 总用量 12 -rw-r--r--. 1 root root 2128 8月 14 11:32 atguigudb33.sql -rw-r--r--. 1 root root 264 8月 14 12:13 student_data.sql -rw-r--r--. 1 root root 380 8月 14 12:12 student_structure.sql [root@centos7-mysql-1 backup]#
mysql> use atguigudb33; Database changed mysql> source /var/lib/mysql/backup/student_structure.sql; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-----------------------+ | Tables_in_atguigudb33 | +-----------------------+ | student | +-----------------------+ 1 row in set (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> source /var/lib/mysql/backup/student_data.sql; Query OK, 11 rows affected (0.00 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+---------+--------+ | id | name | class | +----+---------+--------+ | 1 | 张三3 | 一班 | | 3 | 李四1 | 一班 | | 6 | jerry | 一班 | | 8 | 王五 | 二班 | | 11 | Tim | 一班 | | 15 | 赵六 | 二班 | | 17 | Tom1 | 三班 | | 18 | Jerry | 四班 | | 20 | 钱七 | 三班 | | 22 | bbb | No.1 | | 23 | aaa | No.1 | +----+---------+--------+ 11 rows in set (0.00 sec) mysql>
4. 物理备份：直接复制整个数据库
直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL的数据库目录位置不一定相同：
在Windows平台下，MySQL 8.0存放数据库的目录通常默认为 “ C:\ProgramData\MySQL\MySQLServer 8.0\Data ”或者其他用户自定义目录；
在Linux平台下，数据库目录位置通常为/var/lib/mysql/；
在MAC OSX平台下，数据库目录位置通常为“/usr/local/mysql/data”
但为了保证备份的一致性。需要保证：
方式1：备份前，将服务器停止。
方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。
这种方式方便、快速，但不是最好的备份方法，因为实际情况可能 不允许停止MySQL服务器 或者 锁住表 ，而且这种方法 对InnoDB存储引擎 的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。
注意，物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为。
说明：
在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同。
此外，还可以考虑使用相关工具实现备份。比如， MySQLhotcopy 工具。MySQLhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。多用于mysql5.5之前。
5.物理恢复：直接复制到数据库目录
步骤：
1）演示删除备份的数据库中指定表的数据
2）将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器
3）查询相关表的数据是否恢复。需要使用下面的 chown 操作。
要求：
必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。
因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的。
这种方式对 MyISAM类型的表比较有效 ，对于InnoDB类型的表则不可用。
因为InnoDB表的表空间不能直接复制。
在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下：
chown -R mysql.mysql /var/lib/mysql/dbname
1
其中，两个mysql分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参数表示数据库目录。
提示
Linux操作系统下的权限设置非常严格。通常情况下，MySQL数据库只有root用户和mysql用户组下的mysql用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用chown命令将文件夹的用户组变为mysql，将用户变为mysql。
演示-物理备份
准备
mysql> create database atguigudb5; Query OK, 1 row affected (0.01 sec) mysql> use atguigudb5; Database changed mysql> create table test1(id int)engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> insert into test1 values(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql>
锁定
mysql> flush tables with read lock; Query OK, 0 rows affected (0.05 sec) mysql> select * from test1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> delete from test1; ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock mysql>
备份
[root@centos7-mysql-1 backup]# cd /var/lib/mysql [root@centos7-mysql-1 mysql]# cp -r atguigudb5 ./backup/ [root@centos7-mysql-1 mysql]# cd backup/ [root@centos7-mysql-1 backup]# ll 总用量 12 -rw-r--r--. 1 root root 2128 8月 14 11:32 atguigudb33.sql drwxr-x---. 2 root root 61 8月 14 12:25 atguigudb5 -rw-r--r--. 1 root root 264 8月 14 12:13 student_data.sql -rw-r--r--. 1 root root 380 8月 14 12:12 student_structure.sql [root@centos7-mysql-1 backup]#
解锁
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
演示-物理恢复
删除
mysql> delete from test1; Query OK, 3 rows affected (0.00 sec) mysql> select * from test1; Empty set (0.00 sec)
恢复
[root@centos7-mysql-1 backup]# cd .. [root@centos7-mysql-1 mysql]# ll drwxr-x---. 2 mysql mysql 61 8月 14 12:22 atguigudb5 [root@centos7-mysql-1 mysql]# rm -rf atguigudb5 [root@centos7-mysql-1 mysql]# ll [root@centos7-mysql-1 mysql]# mv ./backup/atguigudb5 ./ [root@centos7-mysql-1 mysql]# ll drwxr-x---. 2 root root 61 8月 14 12:25 atguigudb5 [root@centos7-mysql-1 mysql]# systemctl restart mysqld
尝试查看
mysql> select * from test1; ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... Connection id: 8 Current database: atguigudb5 ERROR 1036 (HY000): Table 'test1' is read only #退出重登-仍错 mysql> select * from test1; ERROR 1036 (HY000): Table 'test1' is read only
执行操作–需要mysql有权限访问该文件
[root@centos7-mysql-1 mysql]# chown -R mysql.mysql /var/lib/mysql/atguigudb5
查看
mysql> select * from test1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.03 sec)
6. 表的导出与导入
6.1 表的导出
1. 使用SELECT…INTO OUTFILE导出文本文件
在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。
**举例：**使用SELECT…INTO OUTFILE将atguigu数据库中account表中的记录导出到文本文件。
（1）选择数据库atguigu，并查询account表，执行结果如下所示。
use atguigu; select * from account; /* mysql> select * from account; + ---- +-------- +--------- + | id | name | balance | + ---- +-------- +--------- + | 1 | 张三 | 90 | | 2 | 李四 | 100 | | 3 | 王五 | 0 | + ---- +-------- +--------- + 3 rows in set (0.01 sec) */
（2）mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。
查询secure_file_priv值：
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的可选值和作用分别是:
如果设置为empty，表示不限制文件生成的位置，这是不安全的设置;
如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录;
如果设置为NULL，就表示禁止在这个MySQL实例上执行select … . into outfile操作。
（3）上面结果中显示，secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录，SQL语句如下。
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";
（4）查看 /var/lib/mysql-files/account.txt`文件。
1 张三 90 2 李四 100 3 王五 0
演示-导出
导出
mysql> use atguigudb3; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 30 | | 2 | 李四 | 0 | | 3 | 王五 | 110 | | 4 | 马六 | 1000 | +----+--------+---------+ 4 rows in set (0.01 sec) mysql> SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account1.txt"; Query OK, 4 rows affected (0.00 sec)
查看
[root@centos7-mysql-1 mysql]# cd /var/lib/mysql-files/ [root@centos7-mysql-1 mysql-files]# ll 总用量 4 -rw-r-----. 1 mysql mysql 50 8月 14 12:40 account1.txt [root@centos7-mysql-1 mysql-files]# cat account1.txt 1 张三 30 2 李四 0 3 王五 110 4 马六 1000
