Mysqldump Backup and Recovery finishing

Commands to back up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

backing up the MySQL database to a format with a delete table
Backing up the MySQL database is a format with a delete table that allows the backup to overwrite the existing database without having to manually delete the existing database.

Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql

Compress the MySQL database directly backup

Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Back up a MySQL database (some) table

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Back up multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql

Backing up the database structure only

Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server

Mysqldump–all-databases > Allbackupfile.sql

Commands to restore MySQL database

Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restore a compacted MySQL database

Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

To transfer a database to a new server

Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName

Mysqldump supports the following options:
–add-locks
Add lock tables before each table is exported and then unlock table. (To make it faster to insert into MySQL).

–add-drop-table
Add a drop table before each create statement.

–allow-keywords
Allows you to create column names that are keywords. This is done by prefixing the table name with each column name.

-c,–complete-insert
Use the full INSERT statement (with the column name).

-c,–compress
If both the client and the server support compression, compress all the information between the two.

–delayed

Insert a row with the insert delayed command.

-e,–extended-insert
Use the new multi-line insert syntax. (Give a more condensed and faster INSERT statement)

-#,–debug[=option_string]
The use of the tracker (for debugging purposes).

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 the-T selection and have the same meaning as the corresponding load DATA infile clause.
LOAD DATA infile syntax.

-f,–flush-logs
Before starting the export, wash down the log files in the MySQL server.

-f,–force,
Even if we get a SQL error during the export of a table, continue.

-h,–host=.
Export data from a MySQL server on a named host. The default host is localhost.

-l,–lock-tables.
Locks all tables for starting export.

-t,–no-create-info
Do not write table creation information (CREATE TABLE statement)

-d,–no-data
No row information is written to the table. This is useful if you only want to export the structure of a table!

–opt
With –quick–add-drop-table–add-locks–extended-insert–lock-tables.
Should give you the fastest possible export for reading into a MySQL server.

-pyour_pass,–password[=your_pass]
The password to use when connecting to the server. If you do not specify the "=your_pass" section, Mysqldump requires a password from the terminal.

-P Port_num,–port=port_num
The TCP/IP port number to use when connecting to a single host. (This is used to connect to a host other than localhost because it uses a UNIX socket.) ）

-q,–quick
Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.

-s/path/to/socket,–socket=/path/to/socket
A socket file that is used with localhost when it is connected (it 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 the data. Note: This only works when the mysqldump is running on the same machine as the mysqld daemon. The format of the. txt file is determined by the –fields-xxx and –lines–xxx options.

-U user_name,–user=user_name
The user name that MySQL uses when connecting to the server. The default value is your UNIX login name.

-O var=option,–set-variable var=option
Sets the value of a variable. The possible variables are listed below.

-v,–verbose
Verbose mode. Print out more information that the program has done.

-v,–version
Print the version information and exit.

-w,–where= ' Where-condition '
Only the selected records are exported; Note that the quotation marks are mandatory!
"–where=user= ' JIMF '" "-wuserid>1″"-wuserid<1″

#举例 #  #导出某个数据库-structure + data shell>mysqldump-h192.168.161.124-uroot-pxxxxxx--opt db_name |gzip-9 >/db_bakup/db_ name.gz  #导出某个数据库的表-structure + data + functions + stored procedures shell>mysqldump-h192.168.161.124-uroot-pxxxxxx--opt-r db_name |gzip-9 >/db_backup/db_name.gz  #导出多个数据库shell >mysqldump-h192.168.161.124-uroot-pxxxxxx--opt--databases db_ name1 db_name2 db_name3 |gzip-9 >/db_backup/mul_db.gz  #导出所有的数据库shell >mysqldump-h192.168.161.124-uroot- Pxxxxxx--opt--all-databases |gzip-9 >/db_bak/all_db.gz  #导出某个数据库的结构shell >mysqldump-h192.168.161.124- Uroot-pxxxxxx--opt--no-data db_name|gzip-9 >/db_bak/db_name.strcut.gz  #导出某个数据库的数据shell >mysqldump- H192.168.161.124-uroot-pxxxxxx--opt--no-create-info db_name|gzip-9 >/db_bak/db_naem.data.gz # Export a table for a database shell>mysqldump-h192.168.161.124-uroot-pxxxxxx--opt db_name tbl_name |gzip-9 >/db_bak/db_name.tal _name.gz # the structure of a table that exports a database Shell>mysqldump-h192.168.161.124-uroot-Pxxxxxx--opt--no-data db_name tal_name | gzip-9 >/db_bak/db_name.tal_name.struct.gz  #导出某个数据库的某张表的数据shell >mysqldump-h192.168.161.124-uroot- Pxxxxxx--opt--no-create-info db_name tbl_name | gzip-9 >/db_bak/db_name.tbl_name.data.gz

Mysqldump backup and recovery grooming