MySQL Common import Export Data command

1. Import Export

MySQL frequently used to import data commands:

1.mysql command

The code is as follows	Copy Code
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

2.source command

The code is as follows	Copy Code
Mysql>source Backupfile.sql

Specific methods

1.mysql Export the entire database

The code is as follows	Copy Code
Mysqldump-u user name-p database name > exported file name Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2.mysql Export Database A table

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword database tablename> exported file name Mysqldump-hlocalhost-uroot hqgr t_ug_user> User.sql

2-1. If you need to export the MySQL inside the function or stored procedures

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword-ntd-r databasename > Backupflie.sql Mysqldump-hlocalhost-uroot-ntd-r hqgr > Hqgr.sql

The-NTD is to represent the exported stored procedure;-R is to represent the exported function

3.mysql Export a database structure

　

The code is as follows	Copy Code
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

4.mysql Import Database

Common source Commands

Enter the MySQL database console,

The code is as follows	Copy Code
such as Mysql-u root-p

Mysql>use Database

Then use the source command, followed by the script file (such as the. SQL used here)

The code is as follows	Copy Code
Mysql>source D:wcnc_db.sql

2. Set session Character set encoding

Set names Utf-8;

Common options in Mysqldump

Option/option action/action performed

--add-drop-table
This option will precede each table with the drop table IF exists statement, which guarantees that the MySQL database will not be returned

Wrong, because each time the guide back, will first check whether the table exists, the existence of the delete

--add-locks
This option bundles the previous lock table and unlock table statement in the INSERT statement. This prevents the records from being imported into the database again

Other user actions on the table

-C Or-complete_insert
This option causes the mysqldump command to add a column (field) name to each generated INSERT statement. When data is exported to another database

This option is useful.

--delayed-insert Add delay option to insert command
-F or-flush-logs Use this option to flush the MySQL server log before performing the export.
-F Or-force Use this option to continue exporting even if errors occur
--full This option adds additional information to the statement in the CREATE table
-L or-lock-tables using this option, the server will lock the table when the table is exported.
-T Or-no-create-info
This option causes the mysqldump command to not create a creating table statement, which requires that you only need data without DDL (database definition statement

) is very convenient.

-D or-no-data This option causes the mysqldump command to not create INSERT statements.
You can use this option when you need only DDL statements.
--opt This option opens all options that will increase the speed of file exports and create a file that can be imported faster.
-Q or-quick This option allows MySQL to not read the entire exported content into memory and then perform the export, but write the guide file when read

In
-T Path or-tab = Path This option will create two files, one file contains DDL statements or table creation statements, and another file contains

Data. The DDL file is named Table_name.sql, and the data file is named Table_name.txt. The path name is the directory where the two files are stored. Directory must

Must already exist, and the user of the command has privileges to the file.

-W "where Clause" Or-where = "where Clause"
As mentioned earlier, you can use this option to filter the data that will be placed in the exported file.

Suppose you need to create a file for the account that you want to use in a form, and the manager wants to see all orders for this year (2004), and they

Not interested in DDL, and requires a comma-delimited file because it's easy to import into Excel. To complete this character, you can use the following

The sentence:

Bin/mysqldump–p–where "order_date >= ' 2000-01-01 '"
–tab =/home/mark–no-create-info–fields-terminated-by=, Meet_a_geek Orders
This will get the results you want.

Schema: Schema the set of statements, expressed in data definition language, that completely describe

The structure of a data base.
A set of statements expressed in a data definition language that fully describes the structure of the database