Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.