Mysql的mysqldump和outfile

mysqldump

语法如下

[root@standby ~]# mysqldump -u root -p mysql > /tmp/mysql_20250326.sql
Enter password:
[root@standby ~]# ll /tmp/mysql_20250326.sql
-rw-r--r-- 1 root root 1208798 Mar 26 17:54 /tmp/mysql_20250326.sql

可以加-d参数来只备份表结构

[root@standby ~]# mysqldump -u root -p Y_S -d TEST_1  > /tmp/mysql_20250326.sql
Enter password:
[root@standby ~]# vi /tmp/mysql_20250326.sql

可以在库名后面加表名备份特定表

[root@standby ~]#  mysqldump -u root -p Y_S TEST_1 > /tmp/mysql_20250326.sql
Enter password:
[root@standby ~]# cat /tmp/mysql_20250326.sql

根据where条件导出

mysqldump -u root -p Y_S TEST_1 --where "id=1" > /tmp/mysql_20250326.sql

导入：
语法

mysql -u [用户名] -p [数据库名] < [导出的文件.sql]

删除TEST_1表

mysql> USE Y_S
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 TEST_1;
+------+-------+
| ID   | NAME  |
+------+-------+
|    1 | Mysql |
+------+-------+
1 row in set (0.00 sec)

mysql> DROP TABLE TEST_1;
Query OK, 0 rows affected (0.00 sec)

导入测试

 mysql -u root -p Y_S < /tmp/mysql_20250326.sql

查询结果

mysql> USE Y_S
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 TEST_1;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_1;
Empty set (0.00 sec)

注意导出用mysqldump，导入用mysql
如果导出的是全备份文件，而导入时只想导入结构或者部分数据，则需要修改备份文件

还可以在导出时指定-B参数导出创建库的语句
测试：
先做个-B备份

mysqldump -u root -p y_s  -B  > /tmp/mysql_20250326.sql

然后删除y_s库

mysql> drop database y_s;
Query OK, 0 rows affected (0.00 sec)

再进行导入

mysql -u root -p  < /tmp/mysql_20250326.sql

outfile和load

语法：

SELECT * INTO OUTFILE '/path/to/output.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM [表名];

示例：
默认分隔符来导出

SELECT * INTO OUTFILE '/tmp/tables.csv' FROM TABLES;

指定分隔符来导出

SELECT * INTO OUTFILE '/tmp/tables.csv'
FIELDS TERMINATED BY '|' 
LINES TERMINATED BY '\n'
FROM tables;

outfile导出的数据需要用load导入：

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE [表名]
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n';