MySQL Backup and Restore

来源：互联网发布：git服务器搭建windows 编辑：程序博客网时间：2024/05/16 01:16

Overview

Key point:

Use SELECT … INTO OUTFILE to backup data.
Use LOAD DATA INFILE to restore data.
A new table can be created and load data to it.

Example

Preparation: Table and data

MariaDB [testdate]> DESCRIBE ttt;+--------------+-------------+------+-----+-------------------+-------+| Field        | Type        | Null | Key | Default           | Extra |+--------------+-------------+------+-----+-------------------+-------+| others       | varchar(20) | YES  |     | NULL              |       || the_datetime | datetime    | YES  |     | CURRENT_TIMESTAMP |       |+--------------+-------------+------+-----+-------------------+-------+2 rows in set (0.00 sec)MariaDB [testdate]> SELECT * FROM ttt;+--------+---------------------+| others | the_datetime        |+--------+---------------------+| a      | 2016-05-24 14:33:20 || a      | 2016-05-24 14:33:30 || b      | 2016-05-24 15:31:42 || c      | 2016-05-24 15:31:43 || d      | 2016-05-24 15:31:44 |+--------+---------------------+5 rows in set (0.00 sec)MariaDB [testdate]>

Backup data to file

MariaDB [testdate]> SELECT * FROM ttt WHERE the_datetime < TimeStamp('2016-05-24 15:31:43');+--------+---------------------+| others | the_datetime        |+--------+---------------------+| a      | 2016-05-24 14:33:20 || a      | 2016-05-24 14:33:30 || b      | 2016-05-24 15:31:42 |+--------+---------------------+3 rows in set (0.00 sec)MariaDB [testdate]> SELECT * INTO OUTFILE './all_data.txt' FROM ttt;Query OK, 5 rows affected (0.00 sec)MariaDB [testdate]> SELECT * INTO OUTFILE './some_data.txt' FROM ttt WHERE the_datetime < TimeStamp('2016-05-24 15:31:43');Query OK, 3 rows affected (0.00 sec)MariaDB [testdate]> SELECT others, the_datetime INTO OUTFILE './some_data2.txt' FROM ttt WHERE the_datetime < TimeStamp('2016-05-24 15:31:43');Query OK, 3 rows affected (0.00 sec)MariaDB [testdate]>

Delete the data

Once the data has been backup successfully, the data can be deleted from the table.

MariaDB [testdate]> DELETE FROM ttt WHERE the_datetime < TimeStamp('2016-05-24 15:31:43');Query OK, 3 rows affected (0.03 sec)MariaDB [testdate]> SELECT * FROM ttt;+--------+---------------------+| others | the_datetime        |+--------+---------------------+| c      | 2016-05-24 15:31:43 || d      | 2016-05-24 15:31:44 |+--------+---------------------+2 rows in set (0.00 sec)MariaDB [testdate]>

Restore data to the existing table

MariaDB [testdate]> LOAD DATA INFILE './some_data.txt' INTO TABLE ttt;Query OK, 3 rows affected (0.08 sec)Records: 3  Deleted: 0  Skipped: 0  Warnings: 0MariaDB [testdate]> SELECT * FROM ttt;+--------+---------------------+| others | the_datetime        |+--------+---------------------+| c      | 2016-05-24 15:31:43 || d      | 2016-05-24 15:31:44 || a      | 2016-05-24 14:33:20 || a      | 2016-05-24 14:33:30 || b      | 2016-05-24 15:31:42 |+--------+---------------------+5 rows in set (0.04 sec)MariaDB [testdate]>

Restore to new table

Create a new table with the same structure, and restore data to this new table.

MariaDB [testdate]> CREATE TABLE another_ttt (others VARCHAR(20), the_datetime DATETIME NOT NULL DEFAULT NOW());Query OK, 0 rows affected (0.25 sec)MariaDB [testdate]> DESCRIBE another_ttt;+--------------+-------------+------+-----+-------------------+-------+| Field        | Type        | Null | Key | Default           | Extra |+--------------+-------------+------+-----+-------------------+-------+| others       | varchar(20) | YES  |     | NULL              |       || the_datetime | datetime    | NO   |     | CURRENT_TIMESTAMP |       |+--------------+-------------+------+-----+-------------------+-------+2 rows in set (0.02 sec)MariaDB [testdate]> LOAD DATA INFILE './some_data.txt' INTO TABLE another_ttt;Query OK, 3 rows affected (0.04 sec)Records: 3  Deleted: 0  Skipped: 0  Warnings: 0MariaDB [testdate]> SELECT * FROM another_ttt;+--------+---------------------+| others | the_datetime        |+--------+---------------------+| a      | 2016-05-24 14:33:20 || a      | 2016-05-24 14:33:30 || b      | 2016-05-24 15:31:42 |+--------+---------------------+3 rows in set (0.00 sec)MariaDB [testdate]>

0 0