MySQL Common import Export Data command

Source: Internet
Author: User
Tags flush import database mysql export database mysql import mysql import database

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

Related Article

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.