[教學][Ubuntu 架站] 如何在 Ubuntu 上備份 MySQL

使用 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

Leave a Reply

發佈留言必須填寫的電子郵件地址不會公開。