Mysql Import Export tool mysqldump and Source command usage detailed _mysql

Source: Internet
Author: User
Tags php website mysql command line mysql import
In PHP website development, often encounter MySQL database backup or database migration work, then MySQL how to import the data in the export database is very critical, MySQL itself provides command line export tools mysqldump and MySQL SOURCE Import command for SQL data Import export work, through the MySQL command-line export tool mysqldump command to be able to export MySQL data into text Format (TXT) SQL file, through MySQL The source command is able to import SQL files into the MySQL database, and the following MySQL import to export the SQL instance to explain the usage of the mysqldump and source commands.

MySQL command line export Tool mysqldump command detailed

The MySQL command-line export tool Mysqldump.exe defaults to the bin subdirectory in the MySQL installation directory, and when you export a database using mysqldump, first make sure that the MySQL service is started.
M
Ysqldump Export Command Base usage

Copy Code code as follows:
Mysqldump-u username-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, SQL files exported using mysqldump are saved in the bin directory by default if they are not with an absolute path.
–opt: This mysqldump command parameter is optional, if this option is used to activate the quick,add-drop-table,add-locks,extended-insert,lock-tables parameter of the mysqldump command, That is, you do not need to append these parameters when using mysqldump to export MySQL database information through the –opt parameter.
–quick: On behalf of ignoring buffered output, the mysqldump command exports data directly to the specified SQL file.
–add-drop-table: As the name suggests, is to add drop-table IF exists statement before each create Tabel command to prevent the duplicate of the data table.
–add-locks: means locking and unlocking specific data tables before and after the insert data, you can open mysqldump exported SQL files, and the lock tables and unlock tables appear before the insert.
–extended-insert (-E): This parameter indicates that multiple rows can be inserted.
More mysqldump command parameters you can pass the

Copy Code code as follows:

Mysqldump--help


command to view all parameters contained in mysqldump and support for parameters.
Mysqldump Export command instances are as follows
MySQL database after installation by default contains the MySQL database, I will use this database as an example to demonstrate the various export instances of mysqldump.
To export a database using mysqldump
Copy Code code as follows:

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


Export a single table using mysqldump

Copy Code code 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: Hu Jintao


To export a datasheet structure using mysqldump

Copy Code code 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: Hu Jintao


Description: mysqldump command parameters –no-data the name of the mysqldump exported data does not contain insert data, only the MySQL database table user structure information. You can also use the-D to represent this parameter.
Use mysqldump to export data for a specified condition

Copy Code code 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: Hu Jintao


Note: In this mysqldump export instance, set the exported insert data condition by setting the mysqldump command parameter –where=conditions to the insert record of the user field in the user table root. Through this parameter you can choose Mysqldump to export the data you want according to the need, very convenient. Note that you need to use double quotes before and after this where option, where you can use single quotes, or you can parse the mysqldump command-line arguments and specify multiple where arguments.

MySQL Database import command source detailed

MySQL's most common Database Import command is the use of the Source,source command is very simple, first you need to enter the MySQL database command line management interface, and then select the database to be imported, that is

Copy Code code as follows:

USER Database
Source Exported SQL file


Note the specific directory address of the SQL file you want to import, preferably with/.
This is done with the MySQL command line export tool mysqldump command and the MySQL Import command source usage, which is richer than the MySQL source command, mysqldump export functionality, Reasonable use of mysqldump command parameters can achieve different results.

mysqldump Command Export data usage detailed

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

The mysqldump command comes with a--where/-w parameter that sets the conditions for the data to be exported, in the same way as the Where in the SQL query command, so that 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 criteria > Export file path

Example:

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

Copy Code code as follows:

mysqldump-uroot-p123456 test test_data--where= "ID > MB" >/tmp/test.sql


2. Export the entire database

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

Copy Code code as follows:

Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql


3. Export a database structure

Copy Code code 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 a library table (mysqldump) condition

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

Copy Code code as follows:

1 mysqldump-hhostname-uusername-p dbname tbname>xxxx.sql
* * Guided by specified conditions The contents of the database table. (-w option –where)
1 mysqldump-hhostname-uusername-p dbname tbname-w ' ID >= 1 and id<= 10000 '--skip-lock-tables & Gt Xxxx.sql
or
1 mysqldump-hhostname-uusername-p dbname tbname--where= ' unit_id >= 1 and unit_id <= 10000 ' > ~/xxxx.sql

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.