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:
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
