MySQL Backup and Restore

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: 0

MariaDB [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: 0

MariaDB [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]>