MySQL remote data export and import

1 Preface

Because the company's MySQL is built on the server, in order to avoid direct changes to the server, I chose to remotely Export and Import MySQL Data.

　　

Let's take a look at it. Because the two tables alarmdata_h and mirror R under neem_hanyang in the actual database are empty tables, we need to export the data of the two tables under neem_jabil to neem_hamyang.

2. Export

To meet the above requirements, we must export the alarmdata_h and Alibaba r Data in the two tables under neem_jabil. Before that, let's switch the directory to the installation directory of MySQL. The author is C: \ Program Files (x86) \ MySQL Workbench 5.2 CE.

If you are using a windows system, enter cd C: \ Program Files (x86) \ MySQL Workbench 5.2 CE in dos, if you are installing Server, enter cd C: \ Program Files \ MySQL Server 5.5 \ bin.

　　

We can see the red circle. This is our two leading roles, mysql and mysqldump. Our import and export operations can be achieved through either of them, so what are the differences between them?

When the data volume is small, we choose mysqldump (the maximum is 2 MB). When the data volume is large, we choose mysql

Next, we can use the mysql or mysqldump command in dos to import and export the database.

2.1 mysqldump export syntax and Examples

The export Syntax of mysqldump is as follows:

　　mysqldump -h{hostname} [-P{port}] -u{username} -p{password} [--default-character-set=charset] database [tablename]  {you file path}

• Hostname indicates the host name, localhost indicates the local host, and remote indicates your remote IP address, for example, 192.168.0.3;
• Username is your MySQL login account, and password is the login password;
• Default-character-set is your character set encoding, such as gb2312, gbk, and utf8 (no horizontal bars );
• Dbname is your database name, tablename is your table name,

　　mysqldump -h192.168.0.3 -unikey -p123456 ---character-set=utf8 neem_jabil commerr > d:/

The above Export Statement will create a jabil under disk D. SQL script file, which can be run. It contains the details of creating a table and all its data. This is the characteristic of the Export command. It will not only export data, but also export the structure information of the table or database. In addition, if"

> {You file path} "indicates the path information. Use the mysqldump Export command or print the content on the dos interface.

　　

2.2 mysqldump import syntax and Examples

There are two syntaxes for importing mysqldump: mysqldump import method and source import method:

2.2.1 import mysqldump

As the name suggests, this method is still imported using the main character of mysqldump. The syntax is as follows:

　　mysqldump -h{hostname} [-P{port}] -u{username} -p{password} [--default-character-set=charset] database [tablename]  {you file path}

In essence, this import method is to run the SQL script file exported earlier.

　　mysqldump -h192.168.0.3 -unikey -p123456 ---character-set=utf8 neem_hanyang commerr < d:/data.sql

2.2.2 source Import

The source import is different from the previous one. It is an SQL command. You must log on to MySQL and run it on the command line. However, mysqldump is a management tool, you do not need to log on to MySQ and run it in the command line. You only need to run it in dos. Return to the dos interface in the preceding example, and use the following command to log on to MySQL:

　　mysql -h{hostname} [-p{port}] -u{username} -p{password}

For example, you can use the following command to log on to a remote MySQL instance:

　　mysql -h192.168.0.3 -unikey -p123456

After logging in, go to the database we want to use:

　　mysql > use neem_hanyang;

Then we can use the source command to run the SQL script exported earlier to import data:

　　mysql > source d:/jabil.sql;

2.2.3 comparison between the two

Although mysqldump is easy to use, it actually uses the policy of first logon to run the script, but we do not need to care about the details, but because there are many remote connection details, we may encounter Chinese garbled characters.

　　

　　mysql > show variables like 'character%';

　　

Have you noticed that the Red Circle is latin1? Latin1 is the default Character Set of MySQL. For some reason, if you do not specify the default encoding, it will default to latin1, which is the cause of Chinese garbled characters.

Therefore, I personally suggest that, if you want to import data, you 'd better perform another step. log on first and use the source command to import the data.

3 mysqldump: Export command extension 3.1 -- opt command

Using the mysqldump -- help Command in dos, we can see more detailed usage of mysqldump. The following are several commands to explain.

• -- Opt: This Mysqldump command parameter is optional. If this option is included, quick, add-drop-table, add-locks, extended-insert of the Mysqldump command are activated, the lock-tables parameter, that is, when using the -- opt parameter to export Mysql database information using Mysqldump, you do not need to attach these parameters.
• -- Quick: Ignore the buffer output. The Mysqldump command exports data directly to the specified SQL file.
• -- Add-drop-table: add the DROP-table if exists statement before each create tabel command to prevent duplicate data tables.
• -- Add-locks: indicates to LOCK and UNLOCK a specific data table before and after the INSERT data. You can open the SQL file exported by Mysqldump, and the LOCK TABLES and UNLOCK TABLES statements will appear before the INSERT operation.
• -- Extended-insert or-e: this parameter indicates that multiple rows can be inserted.

For example, the -- opt command is called by default:

　　mysqldump -h192.168.0.3 -unikey -p123456 ---character-set=utf8 --opt neem_jabil commerr_h > d:/jabil3.sql

3.2 -- where command

The principle of the where command is actually the SQL where condition limitation. The format specification is -- where = "field condition", for example: -- where = "id> 1 ", note that the field id must exist. Otherwise, an SQL error is reported as follows.

　　

The specific -- where code example is as follows:

　　mysqldump -h192.168.0.3 -unikey -p123456 ---character-set=utf8 --where="errtype>0" neem_jabil commerr_h > d:/jabil3.sql

3.3 -- no-data command

As the name implies, the role of -- no-data isFor example:

mysqldump -h192.168.0.3 -unikey -p123456 ---character-set=utf8 --no-data neem_jabil commerr_h > d:/jabil3.sql

4 references

1. Mysql import and export tool Mysqldump and Source command usage details