Mysql import and export Database> mysqldump-u root stg> H:/stg. SQL
> Mysqladmin-u create stg
> Mysqldump-u root stg
-------------------------------------------------------
Mysql common data export commands:
1. mysql exports the entire database
Mysqldump-hhostname-uusername-ppassword databasename> backupfile. SQL
Mysqldump-hlocalhost-uroot hqgr> hqgr. SQL (if the root user does not use the password, you can leave-p. of course, you can create a path for the exported SQL file. if it is not specified, it will be stored in the bin directory of mysql)
2. mysql exports a database table
Mysqldump-hhostname-uusername-ppassword database tablename> exported file name
Mysqldump-hlocalhost-uroot hqgr t_ug_user> user. SQL
3. mysql exports a database structure
Mysqldump-hhostname-uusername-ppassword-d -- add-drop-table databasename> d: hqgrstructure. SQL
-D no data -- add-drop-table adds a drop table before each create statement.
4. if you need to export functions or stored procedures in mysql
Mysqldump-hhostname-uusername-ppassword-ntd-R databasename> backupflie. SQL
Mysqldump-hlocalhost-uroot-ntd-R hqgr> hqgr. SQL
-Ntd indicates the export stored procedure;-R indicates the export function.
Commonly used mysql data import commands:
1. mysql command
Mysql-hhostname-uusername-ppassword databasename <backupfile. SQL
2. source Command
Mysql> source backupfile. SQL
Restore a database from a backup file
Mysql [database name] <[backup file name]
Basic usage of MySQL mysqldump
MySQL mysqldump is used for export. The basic usage is as follows:
Shell> mysqldump [OPTIONS] database [tables]
If you do not specify any tables, the entire database will be exported.
Run mysqldump -- help to obtain the option table supported by your mysqldump version.
Note: If you run mysqldump without the -- quick or -- opt option, mysqldump loads the entire result set to the memory before the export result. if you are exporting a large database, this may be a problem.
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 the table name prefix on each 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 out the log files on the MySQL server before starting the export.
-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
For each given table, CREATE a table_name. SQL file that contains the SQL CREATE command and a table_name.txt file that contains data. Note: This only works when mysqldump runs on the same machine where the mysqld daemon is running .. 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 to 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:
Shell> mysqladmin create target_db_name
Shell> mysql target_db_name <backup-file. SQL
Yes
Shell> mysql database name <file name
======================================
Several common use cases:
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
