Mysqldump database backup and restore detailed

Mysqldump Backup common usage

1. Export the entire database (including data in the database)

Mysqldump-u username-p dbname > Dbname.sql

2. Export database structure (without data)

Mysqldump-u username-p-D dbname > Dbname.sql

3. Export a data table in the database (contains data)

Mysqldump-u username-p dbname tablename > tablename.sq

Mysqldump-uroot-p-B dbname --table TableName > Tablename.sql

4. Export the table structure of a data table in the database (without data)

Mysqldump-u Username-p-D dbname tablename > Tablename.sql

5. Use parameters (--ignore-table) to exclude a table or multiple tables when backing up a database

Mysqldump-uroot-p "Password"--ignore-table=dbname.table1--ignore-table=dbname.table2 dbname >/opt/backup.sql

SQL file Restore method

1. Mysql-uroot-p "Db_password"-F dbname < Backup.sql (the-f parameter indicates that the SQL error occurred during the export process)

Nohup mysql-uroot-p "Db_password"-F dbname < backup.sql >/dev/null 2>&1 & (can be executed in the background when importing large SQL files)

2, Mysql-uroot-p first login to the database, use dbname; Switch to the library you want to import, and then execute the Source/opt/backup.sql; This allows you to import the

Mysqldump parameter explanation

--host specifying the server to back up the database

--port the port number of the MySQL server

--user user name to connect to MySQL server

--password password to connect to MySQL server

--add-locks database tables are locked when backing up a database table

--databases,-B How many databases are exported, all parameters after the parameter are treated as database names

--all-databases back up all databases on the MySQL server

--all-tablespaces,-y export all table spaces-y do not export any tablespace information

--add-drop-table Adding a statement to delete a database table before each database table statement is created

--no-create-db prohibit generation of CREATE DATABASE statements

--no-create-info prohibit generate CREATE DATABASE library table statement

--complete-insert,-C uses full INSERT statements (including column names) to improve insertion efficiency, but may be affected by the Max_allowed_packet parameter, resulting in insert failure

--no-data,-D do not export any data, only export database table structure

--no-create-info,-t exports only data without adding the Create TABLE statement

--no-create-db,-n exports only data without adding the Create DATABASE statement

--ignore-table ignoring a table when exporting a database

-- force,-f The SQL error that occurred during the export process, and continues with the subsequent operation when an error occurs

--add-drop-database adding drop DATABASE statements before each database is created

--add-drop-table Add Drop Data table statement before each data table creation, default is on, use –skip-add-drop-table cancel option

--add-locks add lock tables before each table is exported and then unlock table, default is on, use –skip-add-locks cancel option

--default-character-set Set default character set, default value is UTF8

--Comments Additional comment information, default is open, you can--skip-comments cancel

--compact exports less output information (for debugging), removes comments and kinsoku structures, and (you can use options –skip-add-drop-table–skip-add-locks–skip-comments– Skip-disable-keys)

--events,-e export Event

--flush-privileges after exporting the MySQL database, issue a flush privileges statement

-- Flush-logs Refresh the log before starting the export, note that if you export multiple databases at one time (using option –databases or –all-databases), the log will be refreshed on a per-database basis

In addition to using –lock-all-tables or –master-data, in which case the log will be refreshed once and the corresponding table is locked at the same time, so if you intend to export and refresh the log at the same time, you should use –lock-all-tables or – Master-data and –flush-logs

--delayed-insert with time-lapse insertion (insert delayed)

--comments adding comment information

--compact compression mode, resulting in less output

--complete-insert output Completed INSERT statement

--default-character-set specifying the default character set

Lock all database tables before--lock-tables backup

--lock-all-tables,-X commits the request to lock all tables in all databases to ensure data consistency, which is a global read lock and automatically turns off the –single-transaction and –lock-tables options

--debug output debug information for debugging (default value is D:t:o,/tmp/mysqldump.trace) example mysqldump-uroot-p–all-databases–debug= "d:t:o,/tmp/ Debug.trace "

From for notes (Wiz)

Mysqldump database backup and restore detailed