MySQL remote data import and export

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The 1 part

  Because of the company's MySQL is set on the server, in order to avoid direct changes to the server, I chose to use the remote MySQL export and import data.


  Let's take a look see above, because neem_hanyang the actual database under the two tables alarmdata_h and commerr is an empty table, we want to do is put the two tables of data under neem_jabil guide to neem_hamyang.

2 Export

  To meet the above requirements, we have two tables alarmdata_h and commerr data, derived under the neem_jabil before this, let us put the installation directory directory to MySQL, this is C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE. If you installed MySQL Server version, then it should be similar to the C:\Program Files\MySQL\MySQL Server 5.5, please you to it under sub folder bin.

  If you now use the windows system, then let us enter the CD C:\Program Files in DOS (x86) \MySQL\MySQL Workbench 5.2 CE, if you installed Server version, then the input CD C:\Program Files\MySQL\MySQL Server 5.5\bin.


  See the red circle out part, this is our two protagonists MySQL and mysqldump, import and export operations we can through two of them either to achieve, so the two of them have what distinction.?

When the data volume is small we choose mysqldump (its upper limit is 2M), when the data quantity is big and we choose MySQL

  Next we can use the DOS MySQL or mysqldump commands to the database import and export operations.

2.1 mysqldump derived syntax and examples

  Derived grammar mysqldump as follows:

  mysqldump -h{hostname} [-P{port}] -u{username} -p{password} [--default-character-set=charset] database [tablename] > {you file path}
  Note: between -h and [hostname] and is connected with no spaces, after the expansion, [] up is optional, can not fill
  mysqldump -h192.168.0.3 -unikey -p123456 --default-character-set=utf8 neem_jabil commerr > d:/jabil1.sql

  The statement above, will create the file is a jabil.sql script in the D drive below, this script can be run, it contains commerr construction details and all of its data. This is the characteristic of the export command, it will not only export data, structure information will be derived table or database. Also if don't take"

> {you file path}"This part of the path information, mysqldump export orders or to print on the interface of DOS.


2.2 introduction of mysqldump syntax and examples

  Mysqldump introduction there are two kinds of grammar to introduce, respectively is mysqldump import and source introduction method:

2.2.1 mysqldump import

  As the name suggests, this method is still used mysqldump the protagonist to import, grammatical rules are as follows:

  mysqldump -h{hostname} [-P{port}] -u{username} -p{password} [--default-character-set=charset] database [tablename] < {you file path}
  Note: the grammatical differences between import and export, import export is greater than, less than,

  This kind of import is running in front of the derived the SQL script file.

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

2.2.2 source import

  Introduction of source is different from the front, it is a SQL command, you must log into the MySQL before they can run in the command line there, but mysqldump actually is a management tool, does not need to login to MySQ at the command line there, but can be run in dos. Back to the example above the DOS interface, we can use the following command to log in MySQL:

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

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

  mysql -h192.168.0.3 -unikey -p123456

  After logging on, into our database to function:

  mysql > use neem_hanyang;

  Then we can use the source command to run the first derived SQL script to realize data import:

  mysql > source d:/jabil.sql;

Comparison of 2.2.3 two

  Mysqldump although good, but it is actually used to log in to run the script strategy, only the details we need not concern, However, because there is a lot of remote connection details, we are likely to encounter Chinese problem.


  About why in the use of mysqldump implementation will appear when Chinese problem, we first use the MySQL command to mount, and enter the following command:

  mysql > show variables like 'character%';


  Note that the red circle part of the code is Latin1? Latin1 is the default character set MySQL, probably due to some reason you do not specify the default encoding, it will default to Latin1, this is the reason why you Chinese garbled.

  So the author personal advice, if data into words, would rather do a step, first login to use the source command to import.

3 mysqldump export command extension

3.1 --opt command

  The use of mysqldump --help under the DOS command mysqldump we can see the more detailed use method, choose the command to explain below.

  For example, use the --opt command, by default --opt will be invoked:

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

3.2 --where command

  Limited command of where actually is SQL where conditions, the format specification is: --where= "field conditions, such as: --where=" id> "; 1", the need to pay attention to is the ID field must be real, otherwise it will report to SQL error.


  Specific --where code examples are as follows:

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

3.3 --no-data command

  As the name implies, --no-data is the role ofOnly the table structure and not derived data, The following example:   

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

4 reference

  1 Mysql import and export tools Mysqldump and Source command usage

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Christy at November 28, 2013 - 3:28 PM