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' ;