mysql备份和恢复

mysql备份恢复
mysqldump
备份：
database：mysqldump -uroot -p database > /tmp/database.sql
table：mysqldump -uroot -p database table1> /tmp/table1.sql
还原：
mysql -uroot -p </tmp/database.sql
或者进入命令行：source /tmp/database.sql

另外如果只是指定格式备份数据到文件，以下方式（http://dev.mysql.com/doc/refman/5.7/en/select-into.html）
导出数据文件：

mysql> SELECT * INTO OUTFILE '/tmp/result.txt'  

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  

LINES TERMINATED BY '\n'   FROM test.t1;

Query OK, 1 row affected (0.00 sec)
[root@localhost data]# cat /tmp/result.txt
1,"china","HongKong"
倒入数据文件：
mysql> delete from test.t1;                                                                                     Query OK, 1 row affected (0.12 sec)

mysql> select * from test.t1;
Empty set (0.01 sec)
导入：

mysql> LOAD DATA INFILE '/tmp/result.txt' INTO TABLE test.t1 

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n' ;

Query OK, 1 row affected (0.10 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
查看：
mysql> select * from test.t1;
+----+-------+----------+
| id | fname | lname    |
+----+-------+----------+
|  1 | china | HongKong |
+----+-------+----------+
1 row in set (0.00 sec)
忽略或者替换已存在：
mysql> LOAD DATA INFILE '/tmp/result.txt' IGNORE|REPLACE INTO TABLE test.t1  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ;
跳过前几条记录：
mysql> LOAD DATA INFILE '/tmp/result.txt'  INTO TABLE test.t1  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Query OK, 1 row affected (0.12 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test.t1;
+----+-------+----------+
| id | fname | lname    |
+----+-------+----------+
|  1 | china | HongKong |
|  2 | china | HongKong |
+----+-------+----------+
2 rows in set (0.00 sec)
 

注意，在windows下客户端导入有两点，1，路径；2，指定local路径，比如

LOAD DATA local INFILE 'C:\\Users\\Administrator\\Desktop\\a.csv' INTO TABLE tab1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' ;