MySQL导入导出

1.select into outfile导出

mysql> select * from tb_route_rule  into outfile '/home/ocssm/usm/mysqldata-8.0.16/tmp/tb_route_rule.sql' fields terminated by '|' lines terminated by '\r\n';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

查看变量：

mysql> show global variables like '%secure%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_auth      | ON    |
| **secure_file_priv | NULL**  |
+------------------+-------+
2 rows in set (0.00 sec)

secure_file_priv为null 表示不允许导入导出
secure_file_priv指定文件夹时，表示mysql的导入导出只能发生在指定的文件夹
secure_file_priv没有设置时，则表示没有任何限制

可在配置文件my.cnf中指定secure-file-priv路径，指定路径后导入导出的文件只能存放与该文件夹下:

修改配置后需重启mysql生效。

2.mysqlimport导入

select into outfile导出的文件，可使用mysqlimport进行还原。

[test@test tmp]$/home/ocssp/data/mysql-8.0.16/bin/mysqlimport -h 100.00.00.100 -P 3307 -u usm -pusm usmdb /home/ocssp/data/mysqldata-8.0.16/tmp/user_test.sql --fields-terminated-by='|'
[test@test tmp]$mysqlimport: Error: 1227 Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

查看用户超级权限：

mysql> select host,user,Grant_priv,Super_priv from user;
+--------------+------------------+------------+------------+
| host         | user             | Grant_priv | Super_priv |
+--------------+------------------+------------+------------+
| %            | usm              | N          | N          |
| 100.00.00.100 | repl             | N          | N          |
| localhost    | mysql.infoschema | N          | N          |
| localhost    | mysql.session    | N          | Y          |
| localhost    | mysql.sys        | N          | N          |
| localhost    | root             | Y          | Y          |
+--------------+------------------+------------+------------+
6 rows in set (0.00 sec)

修改用户超级权限：

mysql>update mysql.user set Super_priv='Y' where user='usm';
mysql>flush privileges;
mysql>
mysql> select host,user,Grant_priv,Super_priv from user;
+--------------+------------------+------------+------------+
| host         | user             | Grant_priv | Super_priv |
+--------------+------------------+------------+------------+
| %            | usm              | N          | Y          |
| 100.00.00.100 | repl             | N          | N          |
| localhost    | mysql.infoschema | N          | N          |
| localhost    | mysql.session    | N          | Y          |
| localhost    | mysql.sys        | N          | N          |
| localhost    | root             | Y          | Y          |
+--------------+------------------+------------+------------+
6 rows in set (0.00 sec)

再次导入：

[test@test tmp]$mysqlimport: Error: 1045, Access denied for user 'usm'@'%' (using password: YES), when using table: user_test

mysqlimport是LOAD DATA INFILE语句的命令行界面，为此您需要’FILE’特权（服务器级别）。
授予FILE特权*.*：

mysql>GRANT ALL PRIVILEGES ON *.* TO 'usm'@'%';
mysql>GRANT FILE ON *.* to 'usm'@'%';
mysql>flush privileges;

导入成功：

[ocssm@ocssm_psp1 tmp]$usmdb.user_test: Records: 30  Deleted: 0  Skipped: 0  Warnings: 0

3.mysqldump 导出

[test@test tmp]$ /home/ocssm/usm/mysql-5.6.36/bin/mysqldump -uusm -p usmdb tb_route_rule > /home/ocssm/usm/mysqldata-8.0.16/tmp/tb_route_rule.sql
Enter password: 
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect

加入socket参数（可通过ps -ef|grep mysql查看sock文件路径，一般存放于MySQL的tmp文件夹下）：

[test@test tmp]$ /home/ocssm/usm/mysql-5.6.36/bin/mysqldump --socket=/data/home/ocssm/mysql/tmp/mysql.sock -uusm -p usmdb tb_route_rule > /home/ocssm/usm/mysqldata-8.0.16/tmp/tb_route_rule.sql
Enter password: 
mysqldump: Got error: 1045: Access denied for user 'usm'@'localhost' (using password: YES) when trying to connect

加入-h主机IP：

[test@test tmp]$ /home/ocssm/usm/mysql-5.6.36/bin/mysqldump --socket=/data/home/ocssm/mysql/tmp/mysql.sock -h 100.00.00.100 -uusm -p usmdb tb_route_rule > /home/ocssm/usm/mysqldata-8.0.16/tmp/tb_route_rule.sql
Enter password: 
[test@test tmp]$
[test@test tmp]$ ls -ltr /home/ocssm/usm/mysqldata-8.0.16/tmp/tb_route_rule.sql
-rw-r----- 1 ocssm ocssm 0 Nov  7 16:57 /home/ocssm/usm/mysqldata-8.0.16/tmp/tb_route_rule.sql

导出成功，该备份文件包含drop table、create table 语句，引用该文件还原数据时，将删除原表、重新建表导入。
登录数据库后，使用source导入备份数据：

mysql> source /home/ocssm/usm/mysqldata-8.0.16/tmp/tb_route_rule.sql;

学习探索中，错漏之处请指正。