Mysqldump back up and restore the MySQL database!

Back up and restore a MySQL database using mysqldump

Mysqldump [Options] database [Tables]
If no table is specified, the entire database is exported.

 

Command for backing up MySQL database

Mysqldump-hhostname-uusername-ppassword databasename> backupfile. SQL

Back up the MySQL database in the format of a table with deletion
Backing up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.

Mysqldump -- add-drop-table-uusername-ppassword databasename> backupfile. SQL

Directly compress and back up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename | gzip> backupfile. SQL .gz

Back up a MySQL database 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

Back up database structures only

Mysqldump-no-data-databases databasename1 databasename2 databasename3> structurebackupfile. SQL

Back up all databases on the server

Mysqldump-all-databases> allbackupfile. SQL

Command for restoring MySQL database

Mysql-hhostname-uusername-ppassword databasename <backupfile. SQL

Restore a compressed MySQL database

Gunzip <backupfile. SQL .gz | mysql-uusername-ppassword databasename

Transfer database to new server

Mysqldump-uusername-ppassword databasename | mysql-host = *. *-C databasename About mysqldump: 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]

TrackingProgramFor 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

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