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
