Playful MySQL 1: Backup strategies

为什么80%的码农都做不了架构师？>>>   

This series of blogs talk about MySQL strategies that may not mentioned thoroughly in a typical MySQL database class. Those are works done by author after crazy final exams, aims at providing (international, that's why it is in English here) database lovers a glance of playful technologies outside the class to enjoy the summer and for author to summarize and extend things learned this semester in MySQL.

The first topic today here is backup strategies. It is the most important topic in database because disasters may happen at any given time and a proper backup strategy might be able to save your businesses. So, learning to backup your database properly is important.

There are two different levels of backup in MySQL database, namely schema level backup and table level backup. This blog introduce both methods using MySQL workbench.

Schema Level Backup:

Schema level backup backs up the whole schema in your database into a self-contained file (.sql) as a script or project file. This blog focuses on exporting as self-contained file.

Step 1: Click "management" tab in the navigator bar.

Step 2: Click "data export", choose the name of the schema to be exported and then select other options as shown in the following figure:

Note that the option "create dump in a single transaction" needs to be selected when you are backing up database for a large project or Internet environment. This is because the backup should not be finishing partially, it shall designed to be as a whole successful backup, or a whole failed backup, thus embodies the "atomicity" of transaction.

How to restore from this file??

There are two ways.

• Use MySQL's automatic Import / Restore schema

Under "management" tab of navigator bar, click data import / restore. Choose the path of the stored file and the target schema intended. If not exist, users can create a new schema to be restored to by clicking "new", other options are similar as shown in the following figure, then click "start import".

• Run the script file being created

Click "File -> Open SQL Script", choose the file just being dumped out. Then run the script to restore the schema.

Table Level Backup

Before being able to dump data of a certain table into outfile, some default settings need to be changed. Open MySQL server configuration file (by default "C:\ProgramData\MySQL\MySQL Server 5.7\my.ini")

Following the image above, find in the my.ini file locate those codes:

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

and change them to:

# Secure File Priv.
# secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
secure-file-priv=""

Now the experiment of backup and load table can be performed. For example, if a table named "sale" needs to be exported, the export code can be as simple as:

SELECT * INTO OUTFILE 'D://sale.txt'
FROM sale;

The output is:

To explore how restore data back works, firstly all the data in table sale is deleted (DO NOT DELETE THE TABLE, BUT DELETE ALL DATA IN THE TABLE!!!!). Then run:

LOAD DATA INFILE 'D://sale.txt'
INTO TABLE sale;

as can be seen, those data is recovered then.

Note: The output here is in default format. If you would like to have better format (e.g.for I / O programs to operate, please refer to the references when output data into outfile according to certain formats.)

References:

1. “MySQL :: MySQL 5.7 Reference Manual :: 4.2.6 Using Option Files.” [Online]. Available: https://dev.mysql.com/doc/refman/5.7/en/option-files.html. [Accessed: 16-Nov-2017].
2. “MySQL :: MySQL 5.7 Reference Manual :: 13.2.6 LOAD DATA INFILE Syntax.” [Online]. Available: https://dev.mysql.com/doc/refman/5.7/en/load-data.html. [Accessed: 16-Nov-2017].
3. “MySQL :: MySQL 5.7 Reference Manual :: 13.2.9.1 SELECT ... INTO Syntax.” [Online]. Available: https://dev.mysql.com/doc/refman/5.7/en/select-into.html. [Accessed: 16-Nov-2017].
4. “W09-S2_2017 DBA ADMIN_DE v4.pdf.” CIS Graduate school, University of Melbourne .

转载于:https://my.oschina.net/Samyan/blog/1575264