Mysql basic Import and Export commands

 

1. Export the entire database

 

Mysqldump-u username-p Database Name> exported file name

 

Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL

 

2. Export a table

 

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

 

Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL

 

3. Export a database structure

 

Mysqldump-u wcnc-p-d -- add-drop-table smgp_apps_wcnc> d: \ wcnc_db. SQL

 

-D no data -- add-drop-table adds a drop table before each create statement.

 

4. Import the database

 

Common source commands

 

Go to the mysql Database Console,

 

For example, mysql-u root-p

 

Mysql> use Database

 

Then run the source command. The following parameter is the script file (for example,. SQL used here)

 

Mysql> source d: \ wcnc_db. SQL

 

 

 

Supplement:

 

 

 

Mysqldump supports the following options:

 

 

 

 

-- Add-locks

 

 

 

Add lock tables and unlock table before each TABLE is exported. (To make it faster to insert data to MySQL ).

 

 

 

-- Add-drop-table

 

 

 

Add a drop table before each create statement.

 

 

 

-- Allow-keywords

 

 

 

Names of columns allowed to be created as keywords. This is done by adding the table name before the column name.

 

 

 

-C, -- complete-insert

 

 

 

Use the complete insert Statement (with the column name ).

 

 

 

-C, -- compress

 

 

 

If both the client and server support compression, all information is compressed between the two.

 

 

 

-- Delayed

 

 

 

Use the insert delayed command to INSERT rows.

 

 

 

-E, -- extended-insert

 

 

 

Use the new multiline INSERT syntax. (A more compact and faster insert statement is provided)

 

 

 

-#, -- Debug [= option_string]

 

 

 

Tracking Program usage (for debugging ).

 

 

 

-- Help

 

 

 

Displays a help message and exits.

 

 

 

-- Fields-terminated-by =...

 

 

 

-- Fields-enclosed-by =...

 

 

 

-- Fields-optionally-enclosed-by =...

 

 

 

-- Fields-escaped-by =...

 

 

 

-- Fields-terminated-by =...

 

 

 

These options are used with-T options and have the same meaning as the load data infile clause.

 

 

 

Load data infile syntax.

 

 

 

-F, -- flush-logs

 

 

 

Wash it out on the MySQL server before starting the export.

 

Logs

File.

 

 

 

-F, -- force,

 

 

 

Even if we get an SQL error while exporting a table, continue.

 

 

 

-H, -- host = ..

 

 

 

Export data from the MySQL server on the named host. The default host is localhost.

 

 

 

-L, -- lock-tables.

 

 

 

Lock all tables for start export.

 

 

 

-T, -- no-create-info

 

 

 

Create table statement)

 

 

 

-D, -- no-data

 

 

 

No row information is written to the table. If you only want to export the structure of a table, this is very useful!

 

 

 

-- Opt

 

 

 

Same as -- quick -- add-drop-table -- add-locks -- extended-insert -- lock-tables.

 

 

 

You should be given the fastest export possible for reading a MySQL server.

 

 

 

-Pyour_pass, -- password [= your_pass]

 

 

 

The password used to connect to the server. If you do not specify "= your_pass", mysqldump requires a password from the terminal.

 

 

 

-P port_num, -- port = port_num

 

 

 

The TCP/IP Port used to connect to a host. (This is used to connect to a host other than localhost because it uses Unix sockets .)

 

 

 

-Q, -- quick

 

 

 

Directly export the data to stdout without buffering the query; Use mysql_use_result () to do it.

 

 

 

-S/path/to/socket, -- socket =/path/to/socket

 

 

 

The socket file used when connecting to localhost (which is the default host.

 

 

 

-T, -- tab = path-to-some-directory

 

 

 

CREATE a table_name. SQL file for each given table, which contains the SQL CREATE command and a table_name.txt file, which contains data. Note: Only when mysqldump runs on the same machine where the mysqld daemon is running

 

Work

.. The format of the txt file is determined by the options -- fields-xxx and -- lines -- xxx.

 

 

 

-U user_name, -- user = user_name

 

 

 

The username used by MySQL to connect to the server. The default value is your Unix login name.

 

 

 

-O var = option, -- set-variable var = option

 

 

 

Set the value of a variable. Possible variables are listed below.

 

 

 

-V, -- verbose

 

 

 

Lengthy mode. Print out more information about the program.

 

 

 

-V, -- version

 

 

 

Print the version information and exit.

 

 

 

-W, -- where = 'where-condition'

 

 

 

Only selected records are exported. Note that the quotation marks are mandatory!

 

 

 

"-- Where = user = 'jimf '"-wuserid> 1 ""-wuserid <1"

 

 

 

 

The most common use of mysqldump may make a backup of the entire database:

 

 

 

 

Mysqldump -- opt database> backup-file. SQL

 

 

 

 

However, it is also useful for enriching another MySQL database with information from one database:

 

 

 

 

Mysqldump -- opt database | mysql -- host = remote-host-C database

 

 

 

 

Mysqldump exports complete SQL statements, so it is easy to import data into mysql client programs:

 

 

 

 

Mysqladmin create target_db_name

 

 

 

Mysql target_db_name <backup-file. SQL