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

Scene:

① cannot access the remote database on the external network due to permission control.

② remote database connection and query is slow, affecting work efficiency

When ③ is about to release production, verify that the database update script is not missing (such as whether the field is complete)

Analysis:

For ① and ②, you can COPY the table structure and existing data of the remote database to the local mysql server, and point the configuration information to the local mysql connection.

For ③, you also need to run the database update script and test the new features to see if there are no exceptions.

Operation (based on navicat for mysql)

Method 1 [recommended]

1. Open the database connection first

2. Right-click the database, and select * * data transfer * *, as follows

3. Set transport properties

Note:

(1) General-check the corresponding database objects as needed

(2) General-if the goal is to select [File], then pay attention to selecting the encoding. I am UTF-8 here.

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

(4) Advanced-[strongly recommended] check to use full insert statement; (because if you export the data and later change the table structure, etc., if you need to restore only the data, you can use the complete insert statement [insert into TableA (Row1,Row2, …). ) values ('1pm, 2pm,. )], if you use an extended insert statement, [insert into TableA values ('1pm,' 1pm, '2pm,... )] cannot be inserted, after all, the order of the fields may be adjusted, and the fields may be added / deleted, resulting in a mismatch)

(5) Click the "start" button to start exporting data

4. Restore / import the tables and data / sql that have just been transferred / exported

Right-click the database and select "run sql File"

Advantages: it can directly transfer structure and data or export corresponding sql files according to actual needs. It is very flexible and practical.

Disadvantages: tedious operation

Method two

[dump SQL file] is a sql statement that directly exports the entire database

(1) if you want to export the sql statement of a table, you can right-click the table name to use this feature.

(2) the data exported by this function is inserted into the data by using [extended insert statement]

Advantages: one-click export

Disadvantages: the insert statement is not a complete insert statement, but an extensible insert statement

(3) Import sql

Refer to the operation of method one

Method three

Use the backup and restore functions included with navicat for mysql

Just follow the backup wizard.

Pros: fast backup

Cons: backup packages can only be used in navicat

Note:

(1) when importing sql, be sure to use users with sufficient permissions

Otherwise, an error will be reported:

[Err] 1227-Access denied; you need (at least one of) the SUPER privilege (s) for this operation

[Err] CREATE TABLE.

(2) the format of the exported DDL statement is not a general statement (usually view creation, function / storage procedure), such as:

CREATE ALGORITHM=UNDEFINED DEFINER= `User1` @ `% `SQL SECURITY DEFINER VIEW `View1` AS select.

An error will be reported during execution:

[Err] 1227-Access denied; you need (at least one of) the SUPER privilege (s) for this operation

[Err] CREATE ALGORITHM=UNDEFINED DEFINER= `User1` @ `% `SQL SECURITY DEFINER VIEW `View1` AS select.

Solution: just remove the red part

(3) if there are foreign keys between tables, an error may be reported when restoring the entire database, as follows

[Err] 1452-Cannot add or update a child row: a foreign key constraint fails (`DtBase`.`mer _ relation_ log`, CONSTRAINT `mer_ 1` FOREIGN KEY (`MER_ ID`) REFERENCES `mer_ info` (`ID`))

[Err] INSERT INTO `mer_relation_ log` VALUES ('20,'1, '2016-05-29, 16-14, 47-22, 976, 44, 42, null,' 69')

Solution: restore the source table data first

(4) Import the Times Table 'DbTest.SYS_CONF' doesn't exist

Possible reason 1 is that the table does not exist.

Possible reason 2 is that mysql is configured to be case-sensitive; (by default, MySQL is case-insensitive in windows and case-sensitive in linux server; solution: change the setting of MySQL under linux so that it is also case-insensitive

(5) the network should be good and cannot be interrupted, otherwise the error will be reported as follows

[Err] 2003-Can't connect to MySQL server on '192.168.1.7' (10060)

[Err] INSERT INTO `ba_ info` VALUES ('61445mm,' 0mm, '2016-01-27 12V 00V,' 403')

The consequence is that the previous data is plugged in, but the latter one is not, and the data needs to be inserted separately.

It is best to connect a network cable rather than wireless

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.