mysql备份和恢复
来源:互联网 发布:手机淘宝可以买彩票吗 编辑:程序博客网 时间:2024/06/07 06:10
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)
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)
0 0
- MySQL 备份和恢复
- MySQL备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL备份和恢复
- MySQL备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- mysql备份和恢复
- MySQL 备份和恢复
- CodeForces 572A,B,C
- iOS开发-如何把广告,图片通过代码加到Screen上
- softmax regression 推导
- java中对日期的处理
- Dubbo学习过程、使用经验分享及实现原理简单介绍
- mysql备份和恢复
- 认识ABAP
- myeclipse
- ubuntu安装使用cmake
- jeecms添加站点
- 圆形图片
- git
- 项目使用log4j,指定日志打印出来的格式,打印到指定文件夹,每天打印一个日志等配置
- Android隐藏标题栏