使用 mysqldump
mysqldump 是一個在每個 MySQL 安裝後就可用的應用程序，它允許你以文本格式執行數據的完整轉儲。
命令語法如下：
$ mysqldump -u [username] -p [database] > backup.sql
通過運行此命令，可以將資料庫完整的備份並轉存成 backup.sql。
要恢復備份，請使用 MySQL 客戶端，如下所示：
$ mysql -u [username] -p [database] < backup.sql
根據資料庫的大小，這些備份文件可能會達到相當大的大小，從而難以傳輸或複制它們。要優化數據庫備份的大小，可以使用 gzip 壓縮文件：
$ mysqldump -u [username] -p [database] | gzip > backup.sql.gz
還可以使用以下方法在文件名中自動添加當前日期：
$ mysqldump -u [username] -p [database] | gzip > backup_$(date +%F.%H%M%S).sql.gz
mysqldump 的注意事項：
在資料庫的轉儲和備份過程中，mysqldump 並不保證 table 之間數據的一致性。如果在備份期間發生寫入操作，則獲得的備份可能是部分和/或損壞的。
為防止此問題，應用程序提供了特定選項：
- –lock-all-tables：阻塞所有資料庫所有 table 的所有寫操作
- -lock-tables：此選項將正在備份的資料庫 table 設置為只讀模式。
進階 – mysqldump 的 Option Syntax
參考連結：mysqldump — A Database Backup Program
| Option Name | Description | Introduced | Deprecated |
|---|---|---|---|
| –add-drop-database | Add DROP DATABASE statement before each CREATE DATABASE statement | ||
| –add-drop-table | Add DROP TABLE statement before each CREATE TABLE statement | ||
| –add-drop-trigger | Add DROP TRIGGER statement before each CREATE TRIGGER statement | ||
| –add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | ||
| –all-databases | Dump all tables in all databases | ||
| –allow-keywords | Allow creation of column names that are keywords | ||
| –apply-replica-statements | Include STOP REPLICA prior to CHANGE REPLICATION SOURCE TO statement and START REPLICA at end of output | 8.0.26 | |
| –apply-slave-statements | Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output | 8.0.26 | |
| –bind-address | Use specified network interface to connect to MySQL Server | ||
| –character-sets-dir | Directory where character sets are installed | ||
| –column-statistics | Write ANALYZE TABLE statements to generate statistics histograms | ||
| –comments | Add comments to dump file | ||
| –compact | Produce more compact output | ||
| –compatible | Produce output that is more compatible with other database systems or with older MySQL servers | ||
| –complete-insert | Use complete INSERT statements that include column names | ||
| –compress | Compress all information sent between client and server | 8.0.18 | |
| –compression-algorithms | Permitted compression algorithms for connections to server | 8.0.18 | |
| –create-options | Include all MySQL-specific table options in CREATE TABLE statements | ||
| –databases | Interpret all name arguments as database names | ||
| –debug | Write debugging log | ||
| –debug-check | Print debugging information when program exits | ||
| –debug-info | Print debugging information, memory, and CPU statistics when program exits | ||
| –default-auth | Authentication plugin to use | ||
| –default-character-set | Specify default character set | ||
| –defaults-extra-file | Read named option file in addition to usual option files | ||
| –defaults-file | Read only named option file | ||
| –defaults-group-suffix | Option group suffix value | ||
| –delete-master-logs | On a replication source server, delete the binary logs after performing the dump operation | 8.0.26 | |
| –delete-source-logs | On a replication source server, delete the binary logs after performing the dump operation | 8.0.26 | |
| –disable-keys | For each table, surround INSERT statements with statements to disable and enable keys | ||
| –dump-date | Include dump date as “Dump completed on” comment if –comments is given | ||
| –dump-replica | Include CHANGE REPLICATION SOURCE TO statement that lists binary log coordinates of replica’s source | 8.0.26 | |
| –dump-slave | Include CHANGE MASTER statement that lists binary log coordinates of replica’s source | 8.0.26 | |
| –enable-cleartext-plugin | Enable cleartext authentication plugin | ||
| –events | Dump events from dumped databases | ||
| –extended-insert | Use multiple-row INSERT syntax | ||
| –fields-enclosed-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
| –fields-escaped-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
| –fields-optionally-enclosed-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
| –fields-terminated-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
| –flush-logs | Flush MySQL server log files before starting dump | ||
| –flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping mysql database | ||
| –force | Continue even if an SQL error occurs during a table dump | ||
| –get-server-public-key | Request RSA public key from server | ||
| –help | Display help message and exit | ||
| –hex-blob | Dump binary columns using hexadecimal notation | ||
| –host | Host on which MySQL server is located | ||
| –ignore-error | Ignore specified errors | ||
| –ignore-table | Do not dump given table | ||
| –include-master-host-port | Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with –dump-slave | 8.0.26 | |
| –include-source-host-port | Include SOURCE_HOST and SOURCE_PORT options in CHANGE REPLICATION SOURCE TO statement produced with –dump-replica | 8.0.26 | |
| –insert-ignore | Write INSERT IGNORE rather than INSERT statements | ||
| –lines-terminated-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
| –lock-all-tables | Lock all tables across all databases | ||
| –lock-tables | Lock all tables before dumping them | ||
| –log-error | Append warnings and errors to named file | ||
| –login-path | Read login path options from .mylogin.cnf | ||
| –master-data | Write the binary log file name and position to the output | 8.0.26 | |
| –max-allowed-packet | Maximum packet length to send to or receive from server | ||
| –net-buffer-length | Buffer size for TCP/IP and socket communication | ||
| –network-timeout | Increase network timeouts to permit larger table dumps | ||
| –no-autocommit | Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements | ||
| –no-create-db | Do not write CREATE DATABASE statements | ||
| –no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | ||
| –no-data | Do not dump table contents | ||
| –no-defaults | Read no option files | ||
| –no-set-names | Same as –skip-set-charset | ||
| –no-tablespaces | Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output | ||
| –opt | Shorthand for –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset | ||
| –order-by-primary | Dump each table’s rows sorted by its primary key, or by its first unique index | ||
| –password | Password to use when connecting to server | ||
| –password1 | First multifactor authentication password to use when connecting to server | 8.0.27 | |
| –password2 | Second multifactor authentication password to use when connecting to server | 8.0.27 | |
| –password3 | Third multifactor authentication password to use when connecting to server | 8.0.27 | |
| –pipe | Connect to server using named pipe (Windows only) | ||
| –plugin-dir | Directory where plugins are installed | ||
| –port | TCP/IP port number for connection | ||
| –print-defaults | Print default options | ||
| –protocol | Transport protocol to use | ||
| –quick | Retrieve rows for a table from the server a row at a time | ||
| –quote-names | Quote identifiers within backtick characters | ||
| –replace | Write REPLACE statements rather than INSERT statements | ||
| –result-file | Direct output to a given file | ||
| –routines | Dump stored routines (procedures and functions) from dumped databases | ||
| –server-public-key-path | Path name to file containing RSA public key | ||
| –set-charset | Add SET NAMES default_character_set to output | ||
| –set-gtid-purged | Whether to add SET @@GLOBAL.GTID_PURGED to output | ||
| –shared-memory-base-name | Shared-memory name for shared-memory connections (Windows only) | ||
| –show-create-skip-secondary-engine | Exclude SECONDARY ENGINE clause from CREATE TABLE statements | 8.0.18 | |
| –single-transaction | Issue a BEGIN SQL statement before dumping data from server | ||
| –skip-add-drop-table | Do not add a DROP TABLE statement before each CREATE TABLE statement | ||
| –skip-add-locks | Do not add locks | ||
| –skip-comments | Do not add comments to dump file | ||
| –skip-compact | Do not produce more compact output | ||
| –skip-disable-keys | Do not disable keys | ||
| –skip-extended-insert | Turn off extended-insert | ||
| –skip-opt | Turn off options set by –opt | ||
| –skip-quick | Do not retrieve rows for a table from the server a row at a time | ||
| –skip-quote-names | Do not quote identifiers | ||
| –skip-set-charset | Do not write SET NAMES statement | ||
| –skip-triggers | Do not dump triggers | ||
| –skip-tz-utc | Turn off tz-utc | ||
| –socket | Unix socket file or Windows named pipe to use | ||
| –source-data | Write the binary log file name and position to the output | 8.0.26 | |
| –ssl-ca | File that contains list of trusted SSL Certificate Authorities | ||
| –ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | ||
| –ssl-cert | File that contains X.509 certificate | ||
| –ssl-cipher | Permissible ciphers for connection encryption | ||
| –ssl-crl | File that contains certificate revocation lists | ||
| –ssl-crlpath | Directory that contains certificate revocation-list files | ||
| –ssl-fips-mode | Whether to enable FIPS mode on client side | ||
| –ssl-key | File that contains X.509 key | ||
| –ssl-mode | Desired security state of connection to server | ||
| –tab | Produce tab-separated data files | ||
| –tables | Override –databases or -B option | ||
| –tls-ciphersuites | Permissible TLSv1.3 ciphersuites for encrypted connections | 8.0.16 | |
| –tls-version | Permissible TLS protocols for encrypted connections | ||
| –triggers | Dump triggers for each dumped table | ||
| –tz-utc | Add SET TIME_ZONE=’+00:00′ to dump file | ||
| –user | MySQL user name to use when connecting to server | ||
| –verbose | Verbose mode | ||
| –version | Display version information and exit | ||
| –where | Dump only rows selected by given WHERE condition | ||
| –xml | Produce XML output | ||
| –zstd-compression-level | Compression level for connections to server that use zstd compression | 8.0.18 |