Linux command: the thirteenth of MySQL series-- MySQL backup and restore (important chapter on SELECT backup for single table)
For the backup and restore of only a single table, the backup and restore can be achieved more quickly through the SELECT command.
And through this method, the data of the tables in one database are imported into the tables in another database.
Backup format: SELECT * INTO OUTFILE'/ PATH/TO/somefile.txt'
FROM table_name [WHERE CLAUSE]
# back up the [or data that meets the WHERE statement] in the table_ name table and save it on the server.
Note: table_name: table to be backed up WHERE: conditions met, optional.
/ PATH/TO: the path directory on the server, and this directory must be the user who executes the SELECT statement with write permission
Otherwise, it cannot be backed up.
Restore format: LOAD DATA INFILE'/ PATH/TO/somefile.txt' INTO TABLE table_name
Note: table_name: the name of the table to be restored, which must first exist in the database.
/ PATH/TO: the path where the backup is stored
Mysql > CREATE TABLE tutor LIKE tutors; # create an empty table tutor following the framework of the tutors table
Example: data from one database table is backed up and imported into another database table
Mysql > USE jiaowu
Database changed
Mysql > SELECT * FROM tutors; # query the information of table 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.01sec)
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 ('Flying Magazine M') | YES | | M | |
| | Age | tinyint (3) unsigned | YES | | NULL |
+-- +
4 rows in set (0.01sec)
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 ('Flying Magazine M') | YES | | M | |
| | Age | tinyint (3) unsigned | YES | | NULL |
+-- +
4 rows in set (0.01sec)
Mysql > DROP TABLE tuors
Query OK, 0 rows affected (0.03 sec)
Mysql > SELECT * FROM tutor; # query the information of table tutor
Empty set (0.04 sec) (no data for the time being)
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; # query the information of table 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 | |
+-+
This is done through the SELECT backup restore operation.
