mysql> show variables like '%secure%';
+------------------+---------------------+
| Variable_name | Value |
+------------------+---------------------+
| secure_auth | ON |
| secure_file_priv | /home/mysql/backup/ |
+------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from customer into outfile '/home/mysql/backup/customer.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 300000 rows affected (2.47 sec)
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.11 sec)
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table customer;
Query OK, 0 rows affected (0.19 sec)
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> load data infile '/home/mysql/backup/customer.txt' into table customer fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 300000 rows affected (9.12 sec)
Records: 300000 Deleted: 0 Skipped: 0 Warnings: 0
mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.06 sec)
mysql> show variables like '%secure%';
+---------
阅读背景：
Mysql Database 数据迁移
来源：互联网
分享到：
