MySQL Data backup restore

Scene:

① because of the permission control, the remote database can not be accessed in the external network;

② remote database connection and query relatively slow, affecting the efficiency;

③ when the production is about to be released, verify that the database update script is not missing (such as the field is complete);

Analysis:

For ①, ②, you can copy the table structure and existing data of the remote database to the local MySQL server and point the configured information to the local MySQL connection.

For ③ also need to run the database update script, and test the new function, check whether there is no exception;

Action (based on Navicat for MySQL)

Method One "recommended"

1, first open the database connection;

2, right-click the database, select "Data Transmission", as follows;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/8A/A3/wKioL1g2hX_SKzZLAACwdAVAlDg593.png "title=" Qq20161124140813.png "alt=" Wkiol1g2hx_skzzlaacwdavaldg593.png "/>

3, set the transmission properties;

Attention:

(1) General-check the corresponding database object according to the need;

(2) General-if the goal is to select "file", then pay attention to choose the encoding, I here is UTF-8;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/8A/A8/wKiom1g2hZix7vRSAAD5NtXzUzM825.png "title=" Qq20161124140913.png "alt=" Wkiom1g2hzix7vrsaad5ntxzuzm825.png "/>

(3) Advanced-choose to export only table structure, export data, export tables and data as needed;

(4) Advanced-"strongly recommended" check the use of the full INSERT statement, (because if you export data, change the table structure, etc., you need to only restore the data, you can use the full INSERT statement "INSERT INTO TableA (Row1,row2, ... ) VALUES (' 1 ', ' 2 ',...... "If you use the extension INSERT statement" INSERT into TableA values (' 1 ', ' 2 ',...... ) "will not plug in, after all, may adjust the order of the fields, Add/Remove the field, resulting in not on);

(5) Click "Start" button to start exporting data;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/8A/A4/wKioL1g2hgqQEgKiAAEhQJimwD4555.png "title=" Qq20161124140946.png "alt=" Wkiol1g2hgqqegkiaaehqjimwd4555.png "/>

4. Restore/Import the tables and data just transferred/exported/sql

Right-click the database and select "Run SQL file"

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/8A/A4/wKioL1g2h-nyFSB4AACk8dmiCL4448.png "title=" Qq20161124141032.png "alt=" Wkiol1g2h-nyfsb4aack8dmicl4448.png "/>

Advantages: The structure and data can be transferred directly according to the actual need, or the corresponding SQL file is exported, which is very flexible and practical.

Disadvantages: cumbersome operation;

Method Two

Dump SQL file is an SQL statement that exports the entire database directly;

(1) If you want to export the SQL statement for a table, you can use this function in the table name right-click;

(2) This function exports the data inserted into the data, is the use of "extended INSERT statement";

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/8A/A4/wKioL1g2iFmxUay-AACjelelO8w332.png "title=" Qq20161124141058.png "alt=" Wkiol1g2ifmxuay-aacjelelo8w332.png "/>

Advantages: one-click Export;

Disadvantage: The INSERT statement is not a complete INSERT statement, but an extensibility insert statement;

(3) Import SQL

Refer to the operation of method one;

Method Three

Use Navicat for MySQL with backup, restore functionality

Follow the Backup wizard;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/8A/A8/wKiom1g2i1TgNQlwAAFYpsvI2CE018.png "title=" Qq20161124141207.png "alt=" Wkiom1g2i1tgnqlwaafypsvi2ce018.png "/>

Advantages: fast backup speed;

Disadvantage: The backup package can only be used in navicat;

Attention:

(1) When importing SQL, be sure to use users with sufficient permissions;

Otherwise the error will be:

[ERR] 1227-access denied; Need (at least one's) the SUPER privilege (s) for this operation

[ERR] CREATE TABLE ...

(2) The exported DDL statement format is not a generic statement (typically creating views, functions/stored procedures), such as:

CREATE algorithm=undefined definer= ' User1 ' @ '% ' SQL SECURITY definer VIEW ' View1 ' as SELECT ...;

Error when executing:

[ERR] 1227-access denied; Need (at least one's) the SUPER privilege (s) for this operation

[ERR] CREATE algorithm=undefined definer= ' User1 ' @ '% ' SQL SECURITY definer VIEW ' View1 ' as SELECT ...;

Solution: The red part can be removed;

(3) If there is a foreign key outside the table, then in the restoration of the entire database, may be error, as follows;

[ERR] 1452-cannot Add or update a child row:a FOREIGN KEY constraint fails (' Dtbase '. ' Mer_relation_log ', constraint ' me R_relation_log_ibfk_1 ' FOREIGN KEY (' mer_id ') REFERENCES ' mer_info ' (' ID '))

[ERR] INSERT into ' mer_relation_log ' VALUES (' + ', ' 1 ', ' 2016-05-29 16:47:22 ', ' 976 ', ' a ', ' ' "') ', ' null ', ' 69 ');

Solution: The source table data can be restored in advance;

(4) The network should be good, can not be interrupted, otherwise the error is as follows;

[ERR] 2003-can ' t connect to MySQL server on ' 192.168.1.7 ' (10060)

[ERR] INSERT into ' ba_info ' VALUES (' 61445 ', ' 0 ', ' 2016-01-27 12:00:00 ', ' 403 ');

The consequence is that the previous data is inserted, the back is not, you need to insert the data separately;

It is best to connect the network cable instead of wireless;

This article is from the "Ydhome" blog, make sure to keep this source http://ydhome.blog.51cto.com/8948432/1876167

MySQL Data backup restore