MySQL Import and Export tool mysqldump and Source command usage detailed

Source: Internet
Author: User
Tags mysql command line mysql import

MySQL itself provides command line export tool mysqldump and MySQL source Import command for SQL data import and export work, through the MySQL command line export tool mysqldump command to export MySQL data into text Format (TXT) SQL file, The MySQL source command is able to import the SQL file into the MySQL database, following the usage of the mysqldump and source commands through the MySQL import export SQL instance In the development of PHP Web site, often encountered MySQL database backup or database migration work, when MySQL how to import data in the export database is very critical, MySQL itself provides command line export tool mysqldump and MySQL The source Import command makes SQL data import and export work through the MySQL command line export tool mysqldump command to export MySQL data to a text format (TXT) SQL file, via MySQL The source command is able to import the SQL file into the MySQL database, following the usage of the mysqldump and source commands through the MySQL import export SQL instance.

MySQL command line export tool mysqldump command explanation

MySQL command-line export tool Mysqldump.exe is stored by default in the bin subdirectory of the MySQL installation directory, when you use mysqldump to export the database, first make sure that the MySQL service is started.
M
Ysqldump Export Command Base usage

Copy CodeThe code is as follows: Mysqldump-u user name-P [--opt] DATABASENAME [Table] > Export SQL file name

The default mysqldump exported SQL file contains not only the exported data, but also the structure information of all the data tables in the exported database.
In addition, if you export SQL files using mysqldump without an absolute path, the default is to save in the bin directory.
–opt: This mysqldump command parameter is optional, if you bring this option to represent the Quick,add-drop-table,add-locks,extended-insert,lock-tables parameter that activates the mysqldump command, That is, you do not need to append these parameters when you use mysqldump to export MySQL database information through the –opt parameter.
–quick: The delegate ignores buffered output, and the mysqldump command exports the data directly to the specified SQL file.
–add-drop-table: As the name implies, add the drop-table IF exists statement before each create Tabel command to prevent data table names.
–add-locks: Represents locking and unlocking a specific data table before and after the insert data, you can open the mysqldump exported SQL file, and the lock tables and unlock tables statements appear before insert.
–extended-insert (-E): This parameter indicates that multiple rows can be inserted.
More mysqldump command parameters you can pass

Copy CodeThe code is as follows:
Mysqldump--help

command to see all the parameters contained in the mysqldump and the support of the parameters.
The mysqldump Export command instance is as follows
MySQL database installed by default contains MySQL database, I use this database as an example to demonstrate the various export instances of mysqldump.
Exporting a database using mysqldump
Copy CodeThe code is as follows:
C:\Program files\mysql\mysql Server 5.1\bin>mysqldump-u root-p--opt MySQL >d:\phpweb\sqlbackup\mysql.sql
Enter Password: ******

To export a single table using mysqldump

Copy CodeThe code is as follows:

C:\Program files\mysql\mysql Server 5.1\bin>mysqldump-u root-p--opt MySQL user >d:\phpweb\sqlbackup\mysql_ User.sql
Enter Password: ******

Exporting a data table structure using mysqldump

Copy CodeThe code is as follows:
C:\Program files\mysql\mysql Server 5.1\bin>mysqldump-u root-p--no-data MySQL user >d:\phpweb\sqlbackup\mysql_ User.sql
Enter Password: ******

Description: The mysqldump command parameter –no-data, as the name implies, does not contain insert data in the exported data, only the structure information of the MySQL database table user. You can also use-D to indicate this parameter.
To export data for a specified condition using mysqldump

Copy CodeThe code is as follows:
C:\Program files\mysql\mysql Server 5.1\bin>mysqldump-u root-p "--where= user= ' root '" MySQL user >d:\phpweb\sqlba Ckup\mysql_user.sql
Enter Password: ******

Note: In this mysqldump export instance, by setting the mysqldump command parameter –where=conditions set the exported insert data condition as the insert record of the user field root in the user table. With this parameter you can choose Mysqldump to export the data you want, which is very convenient. Note that double quotation marks are required before and after this where option, and the conditions can use single quotes, otherwise there will be an error parsing the mysqldump command line arguments, and you can also specify multiple where parameters.

MySQL Database import command source detailed

MySQL most commonly used database Import command is the use of SOURCE,SOURCE commands is very simple, first you need to enter the MySQL database command line management interface, and then select the database to import, that is

Copy CodeThe code is as follows:
USER Database
Source Exported SQL file

Note the specific directory address of the SQL file that needs to be imported, preferably using/.
Now that the MySQL command-line export Tool mysqldump command and the MySQL Import command source are used, the mysqldump export function is richer than the MySQL source command. Reasonable use of mysqldump command parameters can achieve different effects.

mysqldump Command Export data usage detailed

In the normal MySQL application, always encounter import data, export data, of course, there are many ways, this article, mainly introduces the application of mysqlmysqldump command for data import and export, I hope to help you.

The mysqldump command has a--where/-w parameter that sets the criteria for data export, which is basically the same as where in the SQL query command, and with it, we can export the part of the data you need from the database.

1. The command format is as follows:

Mysqldump-u User name-p password database name table name--where= "filter Condition" > Export file path

Example:

Export data with IDs greater than 100 to/tmp/test.sql in this file from the Test_data table in the test database

Copy CodeThe code is as follows:
mysqldump-uroot-p123456 test test_data--where= "ID >" >/tmp/test.sql

2. Export the entire database

Mysqldump-u user name-p database name > exported file name

Copy CodeThe code is as follows:
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

3. Export a database structure

Copy CodeThe code is as follows:
Mysqldump-u Wcnc-p-D--add-drop-table SMGP_APPS_WCNC >d:wcnc_db.sql
-D No data--add-drop-table add a drop table before each CREATE statement


Export Library table (mysqldump) conditions

Mysqldump-u User name-p password-H host database a-w "SQL condition" –lock-all-tables > Path

Copy CodeThe code is as follows:
1 Mysqldump-hhostname-uusername-p dbname Tbname>xxxx.sql
* * Export database table contents by specified criteria. (-w option –where)
1 mysqldump-hhostname-uusername-p dbname tbname-w ' ID >= 1 and id<= 10000 '--skip-lock-tables > Xxxx.sql
Or
1 mysqldump-hhostname-uusername-p dbname tbname--where= ' unit_id >= 1 and unit_id <= 10000 ' > ~/xxxx.sql

MySQL Import and Export tool mysqldump and Source command usage detailed

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.