Back up and restore a MYSQL database using MYSQLDUMP

MYSQLDUMP backup and restoration MYSQL database mysqldump [OPTIONS] database [tables] If no table is specified, the entire database will be exported. Run mysqldump-hhostname-uusername-ppassword databasename> backupfile. SQL to back up a 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 compresses and backs up the MySQL database mysqldump-hhostname-uusername-ppassword databasename | gzip> batch back up a MySQL database table mysqldump-hhostname-uusername-ppassword databasename specific_table1 batch> backupfile. SQL
Back up multiple MySQL databases at the same time: mysqldump-hhostname-uusername-ppassword-databases databasename1 databasename2 databasename3> multibackupfile. SQL only backs up the database structure www.2cto.com mysqldump-no-data-databases databasename1 databasename2 databasename3> structurebackupfile. SQL
Backup all databases on the server mysqldump-all-databases> allbackupfile. SQL command for restoring MySQL database mysql-hhostname-uusername-ppassword databasename <backupfile. SQL restore compressed MySQL database gunzip <backupfile. SQL .gz | mysql-uusername-ppassword databasename transfers the database to the new server mysqldump-uusername-ppassword databasename | mysql-host = *. *. *. *-C databasename related to mysqldump: mysqldump supports the following options: -- add-locks www.2cto.com add lock tables and unlock t before each table is exported ABLE. (To make it faster to insert data to MySQL ). -- Add-drop-table: add a drop table before each create statement. -- Allow-keywords allows the creation of column names that are 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, compress all information 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] use of the tracing program (for debugging ). -- Help: displays a help message and exits. Www.2cto.com -- 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 the log files on the MySQL server before export. -F, -- force, even if we get an SQL error during the export of 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 export start. Www.2cto.com-t, -- no-create-info does not write the TABLE creation Information (create table statement)-d, -- no-data does not write any row information of the TABLE. If you only want to export the structure of a table, this is very useful! -- Opt is the 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] indicates 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 indicates the TCP/IP port number used to connect to a host. (This is used to connect to a host other than localhost because it uses Unix sockets .) -Q, -- quick does not buffer queries and is exported directly to stdout. Use mysql_use_result () to do it. -S/path/to/socket, -- socket =/path/to/socket www.2cto.com 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: 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: username used by MySQL when connecting 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 mode. Print out more information about the program. -V, -- version: print the version information and exit. -W, -- where = 'where-condition 'www.2cto.com. Only selected records are exported. Note that the quotation marks are mandatory! "-- Where = user = 'jimf '" "-wuserid> 1" "-wuserid <1" the most common mysqldump may create a backup of the entire database: mysqldump -- opt database> backup-file. SQL but it is also useful for enriching the information from a database and another MySQL database: mysqldump -- opt database | mysql -- host = remote-host-C database because mysqldump exports a complete SQL statement, it is easy to use a mysql client program to import data: mysqladmin create target_db_name mysql target_db_name