以及通过此方法把一个数据库中表的数据,导入至另一数据库的表中去。
备份格式:SELECT * INTO OUTFILE ‘/PATH/TO/somefile.txt‘
FROM table_name [WHERE CLAUSE];
#备份table_name表中的[或者备份满足WHERE语句的数据]数据至服务器上保存。
注释:table_name:需要备份的表 WHERE:满足的条件,可选项。
/PATH/TO:服务器上的路径目录,且此目录必须是执行SELECT语句的用户有写的权限,
否则无法备份。
还原格式:LOAD DATA INFILE ‘/PATH/TO/somefile.txt‘ INTO TABLE table_name;
注释:table_name:需要还原的表的名称,此表必须先在数据库中存在。
/PATH/TO:备份所存放的路径
mysql> CREATE TABLES tutor LIKE tutors; #仿照tutors表的框架创建一个空表tutor
实例:备份一个数据库表的数据,导入至另一个数据库表的数据;
mysql> USE jiaowu;
Database changed
mysql>SELECT * FROM tutors; #查询表tutors的信息
+-----+------------------+--------+-----+
| TID | Tname | Gender | Age |
+-----+------------------+--------+-----+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | HuangRong | F | 46 |
| 4 | HuYidao | M | 65 |
| 5 | XiaoLongnv | F | 28 |
| 6 | HuFei | M | 45 |
| 7 | GuoXiang | F | 32 |
+-----+------------------+--------+-----+
7 rows in set (0.00 sec)
mysql>SELECT * INTO OUTFILE ‘/tmp/tutor.txt‘ FROM tutors;
Query OK, 7 rows affected (0.01 sec)
mysql> CREATE TABLE tutor LIKE tutors;
Query OK, 0 rows affected (0.03 sec)
mysql>DESC tutor;
+---------+---------------------------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------------+------+-----+---------+------------------+
| TID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Tname | varchar(50) | NO | | NULL | |
| Gender | enum(‘F‘,‘M‘) | YES | | M ||
| Age | tinyint(3) unsigned | YES | | NULL ||
+---------+---------------------------+------+-----+---------+------------------+
4 rows in set (0.01 sec)
mysql>DESC tutors ;
+---------+---------------------------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------------+------+-----+---------+------------------+
| TID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Tname | varchar(50) | NO | | NULL | |
| Gender | enum(‘F‘,‘M‘) | YES | | M | |
| Age | tinyint(3) unsigned | YES | | NULL | |
+---------+---------------------------+------+-----+---------+------------------+
4 rows in set (0.01 sec)
mysql> DROP TABLE tuors;
Query OK, 0 rows affected (0.03 sec)
mysql>SELECT * FROM tutor; #查询表tutor的信息
Empty set (0.04 sec) (暂时无数据)
mysql>LOAD DATA INFILE ‘/tmp/tutor.txt‘ INTO TABLE tutor;
Query OK, 7 rows affected (0.04 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM tutor; #查询表tutor的信息
+-----+------------------+--------+-----+
| TID | Tname | Gender | Age |
+-----+------------------+--------+-----+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | HuangRong | F | 46 |
| 4 | HuYidao | M | 65 |
| 5 | XiaoLongnv | F | 28 |
| 6 | HuFei | M | 45 |
| 7 | GuoXiang | F | 32 |
+-----+------------------+--------+-----+
至此通过SELECT备份还原操作完成。
本文出自 “学linux历程” 博客,请务必保留此出处http://woyaoxuelinux.blog.51cto.com/5663865/1932270
Linux命令:MySQL系列之十三--MySQL备份与还原(针对单张表SELECT备份)
标签:select语句备份及还原
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源:http://woyaoxuelinux.blog.51cto.com/5663865/1932270