MySQL入門--備份與恢復（二）
MySQL備份與恢復（二）
1.1. 備份工具：概述
MySQL Enterprise Backup 產品執行 MySQL 資料庫熱備份操作。該產品的設計目的就是為了高效且可靠地備份由 InnoDB 儲存引擎建立的表。為完整起見，該產品還能備份其他儲存引擎中的表。您可以在以下位置找到有關 MySQL Enterprise Backup 的更多資訊：
http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/intro.html
mysqldump
mysqldump 實用程式隨 MySQL 分發提供。該實用程式執行邏輯備份，可與任何資料庫引擎一起使用。可以使用 crontab （在 Linux 和 UNIX 中）和 Windows 任務排程程式（在 Windows 中）自動執行該實用程式。 mysqldump 沒有任何跟蹤或報告工具。
mysqlhotcopy
mysqlhotcopy 實用程式也隨 MySQL 分發提供。該實用程式執行物理備份，僅用於使用 MyISAM 或 ARCHIVE 資料庫引擎的資料庫。名稱暗指 mysqlhotcopy 執行“熱”備份，即不中斷資料庫可用性。但是，由於已對資料庫進行了讀取鎖定，無法在備份過程中更改，因此最好將其描述為“溫”備份。沒有隨此指令碼提供報告或跟蹤。
1.1.1. mysqlbackup
MySQL Enterprise Backup （ MEB ）基本命令列工具是 mysqlbackup ，透過該工具可以執行如下備份 :
l 熱備份
熱備份是在資料庫執行期間執行的。這種型別的備份不阻止正常的資料庫操作，甚至能捕獲備份進行期間發生的更改。 mysqlbackup 是 MySQL Enterprise Backup 產品的基本命令列工具。對於 InnoDB 表，此工具可執行熱備份操作。
l 溫備份
對於非 InnoDB 儲存引擎， MySQL Enterprise Backup 執行溫備份；執行非 InnoDB 備份時，可以讀取資料庫表，但不能修改資料庫。
l 單檔案備份
因為可以將單檔案備份傳輸給其他程式（如磁帶備份或 scp 之類的命令），因此可使用此技術將備份放在其他儲存裝置或伺服器上，不會在原始資料庫伺服器上產生顯著的儲存開銷。
mysqlbackup 物理備份的 InnoDB 相關資料檔案包括：
— ibdata* 檔案，代表系統表空間，並可能代表某些使用者表的資料
— .ibd 檔案，其中包含使用者表中的資料
— 從 ib_logfile* 檔案提取的資料（代表在備份期間發生的更改的重做日誌資訊），儲存在新備份檔案 ibbackup_logfile 中 ;
預設情況下， mysqlbackup 備份資料目錄中的所有檔案。如果指定 --only-known-file-types 選項，則備份僅包括具有 MySQL 公認副檔名的其他檔案。
資料目錄中的常見檔案：
— .opt 檔案：資料庫配置資訊
— .TRG 檔案：觸發器引數
— .MYD 檔案： MyISAM 資料檔案
— .MYI 檔案： MyISAM 索引檔案
— .FRM 檔案：表資料字典檔案
有關這些公認副檔名的完整列表，請參閱以下 URL ：
http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/meb-files-overview.html
1) mysqlbackup 備份
mysqlbackup 是一種易於使用的工具，適用於所有備份和恢復操作。
基本用法：
mysqlbackup -u<user> -p<password> --backup_dir=<backup-dir> backup-and-apply-log
— backup ：執行備份初始階段
— backup-and-apply-log ：包括備份的初始階段以及第二個階段，即將 InnoDB 表放到最新的備份中，其中包括在備份執行期間對資料所做的任何更改。
使用 mysqlbackup 聯機備份 InnoDB 表以及生成對應於與 InnoDB 備份相同的 binlog 位置的 MyISAM 表的快照。除了建立備份以外， mysqlbackup 還可以將備份資料打包和解包，將在備份操作過程中對 InnoDB 表所做的任何更改應用於備份資料，以及將資料、索引和日誌檔案複製回其原始位置。
備份過程
a) mysqlbackup 開啟到要執行備份的 MySQL 伺服器的連線。
b) 然後 mysqlbackup 對 InnoDB 表執行聯機備份。此階段不會干擾正常的資料庫處理。
c) 當 mysqlbackup 執行幾乎完成時，執行 SQL 命令 FLUSH TABLES WITH READ LOCK ，然後將非 InnoDB 檔案（如 MyISAM 表和 .frm 檔案）複製到備份目錄。如果此時未在資料庫中長時間執行 SELECT 或其他查詢，則 MyISAM 表很小，鎖定階段僅持續幾秒鐘。否則，包括 InnoDB 型別表在內的整個資料庫都會鎖定，直到在備份之前開始的所有長時間查詢完成。
d) mysqlbackup 執行完成，並對錶執行 UNLOCK 解鎖。
2) 使用 mysqlbackup 恢復備份
基本用法：
mysqlbackup --backup-dir=<backup-dir> copy-back
— <backup-dir> ：指定備份檔案的儲存位置；恢復操作將 <backup-dir> 的內容（包括 InnoDB 和 MyISAM 索引）以及 .frm 檔案恢復到其原始位置（由 <cnf-file> 檔案定義）。
— copy-back ：指示 mysqlbackup 執行恢復操作使用 copy-back 選項必須先關閉資料庫伺服器，然後才能使用 mysqlbackup 與 copy-back 選項。使用此選項時，可將資料檔案、日誌及其他備份檔案從備份目錄複製回到其原始位置，並對其執行任何必需的後期處理。在 copy-back 過程中， mysqlbackup 無法從伺服器查詢其設定，因此從標準配置檔案中讀取 datadir 之類選項。如果要恢復到不同的伺服器，則可使用 --defaults-file 選項提供非標準預設設定檔案。
例如 :
mysqlbackup -uroot -p --socket /var/lib/mysql/mysql.sock --backup-image=/u01/mysql/bakup/mysql_server.mbi --backup-dir=/u01/mysql/backup backup-to-image
3) mysqlbackup 單檔案備份
基本用法：
mysqlbackup -u<user> -p<password> --backup-image=<image-file> --backup_dir=<backup-dir> backup-to-image
— 標準輸出：
... --backup-dir=<backup-dir> --backup-image=-backup-to-image > <image-file>
— 將現有的備份目錄轉換為單個檔案：
... --backup-dir=<backup-dir> --backup-image=<image-file> backup-dir-to-image
4) 恢復 mysqlbackup 單個檔案備份
提取選擇的檔案：
mysqlbackup -u<user> -p<password> --backup-image=<image-file>
--backup_dir=<backup-dir> image-to-backup-dir
其他情形
— 列出內容：
... --backup-image=<image-file> list-image
— 將現有的備份目錄轉換為單個檔案：
... --backup-image=<image_file> --src-entry=<file-to-extract>
--dst-entry=<file-to-extract> extract
l --src-entry ：確定要從單檔案備份中提取的檔案或目錄
l --dst-entry ：與單檔案備份配合使用，將單個檔案或目錄提取到使用者指定的路徑
有關單檔案備份和恢復選項的完整列表，請訪問：
http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/backup-single-file-options.html
5) mysqlbackup 備份恢復例項
a) mysqlbackup 安裝
rpm -ivh /u01/meb-4.1.1-el6.x86_64.rpm
rpm -ql meb
ln -s /opt/mysql/meb-4.1/bin/mysqlbackup /usr/bin/mysqlbackup
b) backup 使用者與許可權
下面是官方給出的備份的最小使用者許可權：
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'new-password';
GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE, ALTER ON mysql.backup_history TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
GRANT PROCESS ON *.* TO 'mysqlbackup'@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO 'mysqlbackup'@'localhost';
4.1.2 後還需要的許可權：
GRANT CREATE, INSERT, DROP ON mysql.backup_history_old TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_history_new TO 'mysqlbackup'@'localhost';
使用 TTS 和 SBT 特性還需要的許可權：
GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'mysqlbackup'@'localhost';
c) 備份、驗證、還原
備份：
mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/admin/backups/my.mbi --backup-dir=/home/admin/backup-tmp backup-to-image
驗證：
mysqlbackup --backup-image=/home/admin/backups/my.mbi validate
mysqlbackup --backup_dir=/u01/mysql/backup0 validate
還原：
mysqlbackup --defaults-file=/u01/mysql/mynewdata.cnf --backup-dir=/u01/mysql/backup0 copy-back-and-apply-log
mysqlbackup --defaults-file=/u01/mysql/mynewdata.cnf --backup-dir=/u01/mysql/backup0 copy-back
d) 備份場景和示例
Example 1 Single-File Backup to Absolute Path
mysqlbackup --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-image
Example 2 Single-File Backup to Relative Path
mysqlbackup --backup-image=sales.mbi --backup-dir=/backups backup-to-image
Example 3 Single-File Backup to Standard Output
mysqlbackup --backup-dir=/backups --backup-image=- backup-to-image > /backup/mybackup.mbi
Example 4 Convert Existing Backup Directory to Single Image
mysqlbackup --backup-image=/backup/my.mbi --backup-dir=/var/mysql/backup backup-dir-to-image
Example 5 Extract Existing Image to Backup Directory
mysqlbackup --backup-dir=/var/backup --backup-image=/backup/my.mbi image-to-backup-dir
Example 6 List Single-File Backup Contents
mysqlbackup --backup-image=/backup/my.mbi list-image
Example 7 Validate a Single-File Backup
mysqlbackup --backup-image=/logs/fullimage.mi validate
Example 8 Extract Single-File Backup into Current Directory
mysqlbackup --backup-image=/var/my.mbi extract
Example 9 Extract Single-File Backup into a Backup Directory
mysqlbackup --backup-image=/var/my.mbi --backup-dir=/var/backup extract
Example 10 Selective Extract of Single File
mysqlbackup --backup-image=/var/my.mbi \
--src-entry=meta/comments.txt extract
mysqlbackup --backup-image=/var/my.mbi \
--src-entry=meta/comments.txt \
--dst-entry=/tmp/mycomments.txt extract
mysqlbackup --backup-image=/var/my.mbi --src-entry=meta/comments.txt --dst-entry=- extract
Example 11 Selective Extract of Single Directory
mysqlbackup --backup-image=/backup/my.mbi --src-entry=meta extract
mysqlbackup --backup-image=/backup/my.mbi --src-entry=meta \
--dst-entry=/tmp/my-meta extract
Example 12 Dealing with Absolute Path Names
mysqlbackup --backup-image=/backup/my.mbi --src-entry=/ --dst-entry=/myroot extract
mysqlbackup --backup-image=/backup/my.mbi --src-entry=. extract
Example 13 Single-File Backup to a Remote Host
mysqlbackup --defaults-file=~/my_backup.cnf --backup-image=- --backup-dir=/tmp backup-to-image | \
ssh <user name>@<remote host name> 'cat > ~/backups/my_backup.img'
Example 14 Single-file Backup to a Remote MySQL Server
mysqlbackup --backup-dir=backup --backup-image=- --compress backup-to-image | \
ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_tmp --datadir=/data \
--innodb_log_group_home_dir=. \
--innodb_log_files_in_group=<innodb_log_files_in_group_of_backedup_server> \
--innodb_log_file_size=<innodb_log_file_size_of_backedup_server> \
--innodb_data_file_path=<innodb_data_file_path_of_backedup_server> \
--uncompress --backup-image=- copy-back-and-apply-log'
Example 15 Stream a Backup Directory to a Remote MySQL Server
mysqlbackup --backup-image=- --backup-dir=/path/to/my/backup backup-dir-to-image | \
ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_tmp --datadir=/data --backup-image=- copy-back-and-apply-log'
e)mysqlbackup 備份與恢復示例
示例 1 ：全庫備份
mysqlbackup -uroot -p --socket /var/lib/mysql/mysql.sock --backup_dir=/u01/mysql/backup backup-and-apply-log
mysqlbackup --backup-dir=/u01/mysql/backup copy-back
注意： datadir,tbsdir 等目錄檔案，恢復過來的要檢查使用者與屬組
chown -R mysql.mysql /var/lib/mysql
#mysqld stop, 如果要恢復的檔案目錄存在，則需要 --force 選項
示例 2 ：
mysqlbackup -uroot -p --socket /var/lib/mysql/mysql.sock --backup-image=/u01/mysql/bakup/mysql_server.mbi --backup-dir=/u01/mysql/backup copy-back-and-apply-log
mysqlbackup -uroot -p --socket /var/lib/mysql/mysql.sock --backup-image=/u01/mysql/bakup/mysql_server.mbi --backup-dir=/u01/mysql/backup backup-to-image
chown -R mysql.mysql /usr/local/mysql/data
示例 3 ：
mysqlbackup -uroot -paa12AA,. --socket=/u01/mysql/newdata/mysql.sock --backup_dir=/u01/mysql/backup0 backup
mysqlbackup --defaults-file=/u01/mysql/mynewdata.cnf --backup-dir=/u01/mysql/backup0 copy-back
1.1.2. mysqlhotcopy
1) 基本用法
這個實用程式在 MySQL 5.6.20 中被廢棄，在 MySQL 5.7 中被刪除。
mysqlhotcopy -u<user> -p<password> <db_name> <backup-dir>
選項：
--flush-log ：在所有表都鎖定後重新整理日誌
--record_log_pos = db_name.tbl_name ：在指定的資料庫 db_name 和表 tbl_name 中記錄主從伺服器狀態
mysqlhotcopy 連線到本地 MySQL 伺服器，並複製表檔案。在完成複製操作後，將表解鎖。在伺服器主機上執行 mysqlhotcopy ，以便在表鎖定期間複製表檔案。伺服器必須處於執行狀態，以便 mysqlhotcopy 連線到伺服器。 mysqlhotcopy 的操作速度很快，因為它直接複製表檔案，而不是透過網路備份表檔案。
2) 選項和引數詳細介紹
mysqlhotcopy 是一個 Perl 指令碼，最初是由 Tim Bunce 編寫並貢獻的。它使用 FLUSH TABLES ， LOCK TABLES 和 cp 、 scp 進行資料庫備份。這是對資料庫或單個表進行備份的一種快速方法，但只能在資料庫目錄所在的同一臺機器上執行。 mysqlhotcopy 只用於備份 MyISAM 和 ARCHIVE 表，並 僅限於在 Unix 系統上使用；。
要使用 mysqlhotcopy ，您必須具有對正在備份的表的檔案的讀訪問權、這些表的 SELECT 許可權、 RELOAD 許可權 ( 能夠執行 FLUSH TABLES) 和 LOCK tables 許可權 ( 能夠鎖定表 ) 。
shell> mysqlhotcopy db_name [/path/to/new_directory]
shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
備份給定資料庫中匹配正規表示式的表 :
shell> mysqlhotcopy db_name. / 正規表示式 /
表名的正規表示式可以用波浪號 (~) 字首來否定 :
shell> mysqlhotcopy db_name. / ~ 正規表示式 /
mysqlhotcopy 支援以下選項，這些選項可以在命令列或選項檔案的 [mysqlhotcopy] 和 [client] 組中指定。有關 MySQL 程式使用的選項檔案的資訊，請參見 4.2.2.2 節 “ 使用選項檔案 ” 。
mysqlhotcopy 選項名稱含義描述：
--addtodest 不重新命名目標目錄 ( 如果存在 ) ，只是新增檔案
--allowold 如果目標存在，不要中止 ; 透過新增 _old 字尾來重新命名它
--checkpoint 插入檢查點條目
--chroot Base directory of the chroot jail in which mysqld operates
--debug 寫除錯日誌
--dryrun report 未執行的操作
--flushlog 所有表都被鎖定後重新整理日誌
--help, -? 顯示幫助資訊並退出
--host 連線到給定 MySQL 伺服器主機上
--keepold 完成後，不要刪除以前的 (renamed) 目標
--method 複製檔案的方法
--noindices 在備份中不包含完整的索引檔案
--old_server 連線到不支援 FLUSH TABLES tbl_list WITH READ LOCK 的伺服器
--password 連線到伺服器時使用的密碼
--port tcp /IP 埠號
--quiet 保持靜默模式，除非報錯
--regexp 使用與給定正規表示式匹配的名稱複製所有資料庫
--resetmaster 在鎖定所有表後重置 binlog
--resetslave 在鎖定所有表之後重置 master.info 檔案
--socket 用於連線到要使用的 Unix 套接字檔案 localhost
--tmpdir 臨時目錄
--user 連線到伺服器時使用的 mysql 使用者名稱
使用 perldoc 獲得更多的 mysqlhotcopy 文件，包括關於 --checkpoint 和 --record_log_pos 選項所需的表結構的資訊 :
shell> perldoc mysqlhotcopy
1.1.3. mysqldump
mysqldump 客戶機實用程式執行邏輯備份，生成一組 SQL 語句，可以執行這些 SQL 語句來重建原始資料庫物件定義和表資料。它可以轉儲一個或多個 MySQL 資料庫，以便備份或傳輸到另一個 SQL 伺服器， mysqldump 命令還可以生成 CSV 、其他分隔符文字或 XML 格式的輸出。
mysqldump 至少要求對 TABLE 具有 SELECT 許可權，對轉儲 VIEW 具有 SHOW VIEW ，對轉儲觸發器具有 TRIGGER ，如果不使用 --single-transaction 選項，則需要鎖定表。某些選項可能需要其他許可權，如選項描述中所述。
要 RELOAD 轉儲檔案，您必須具有執行其中包含的語句所需的許可權，例如這些語句建立的物件的適當許可權。
mysqldump 輸出可以包括更改 database collation 的 ALTER DATABASE 語句。當轉儲儲存過程以儲存其字元編碼時，也可以使用 mysqldump 。要 reload 包含此類語句的轉儲檔案，需要受影響資料庫的 ALTER 許可權。
注意事項：
在 Windows 上使用 PowerShell 進行轉儲並輸出重定向，將建立一個具有 UTF-16 編碼的檔案 :
shell> mysqldump [options] > dump.sql
但是， UTF-16 不允許作為連線字符集 ( 請參閱不允許的客戶機字符集 ) ，因此轉儲檔案將無法正確載入。要解決這個問題，可以使用 --result-file 選項，它以 ASCII 格式建立輸出 :
shell> mysqldump [options]--result-file=dump.sql
Ø 效能和擴充套件性的考慮
mysqldump 的優點包括在恢復之前檢視甚至編輯輸出的方便和靈活性。您可以為開發和 DBA 工作克隆資料庫，或者為測試生成現有資料庫的細微變化。 它不是用於備份大量資料的快速或可伸縮的解決方案。 對於較大的資料大小，即使備份步驟花費了合理的時間，恢復資料也可能非常慢，因為重播 SQL 語句涉及磁碟 I/O ，以便插入、建立索引等等。
對於大規模的備份和恢復，物理備份更合適，複製資料檔案的原始格式，可以快速恢復 :
l 如果您的表主要是 InnoDB 表，或者是 InnoDB 和 MyISAM 表的組合，請考慮使用 MySQL Enterprise Backup 產品的 mysqlbackup 。它為 InnoDB 備份提供了最優的效能，且破壞最小；它還可以從 MyISAM 和其他儲存引擎備份表；它提供了許多方便的選項來適應不同的備份場景。參見第 25.2 節 “MySQL 企業備份概述 ” 。
l 如果您的表主要是 MyISAM 表，並且是 5.6.20 以前版本，可以考慮使用 mysqlhotcopy ，以獲得比 mysqldump 更好的備份和恢復操作效能。
mysqldump 可以逐行檢索和轉儲表內容，也可以從表中檢索整個內容，並在轉儲前將其緩衝到記憶體中。如果要轉儲大表，那麼記憶體中的緩衝可能是個問題。要逐行轉儲表，可以使用 --quick 選項 ( 或者 --opt ，它啟用了 --quick) 。預設情況下啟用了 --opt 選項 ( 因此也就是 --quick) ，因此要啟用記憶體緩衝，請使用 --skip-quick 。
如果使用最新版本的 mysqldump 生成要 reload 到非常舊的 MySQL 伺服器的轉儲，請使用 --skip-optoption 而不是 --opt 或 --extended-insert 選項。
有關 mysqldump 的更多資訊，請參見 https://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html
Ø mysqldump 限制
預設情況下， mysqldump 不會轉儲 INFORMATION_SCHEMA 或 performance_schema 資料庫。要轉儲其中任何一個，請在命令列上顯式地命名它。您還可以使用 --databases 選項來命名它。另外，使用 --skip-lock-tables 選項。
mysqldump 不轉儲 NDB 叢集 ndbinfo 資訊資料庫。
如果您遇到由於許可權不足而備份檢視的問題，請參閱 C.5 節 “ 檢視的限制 ” 瞭解解決方案。
1) mysqldump 優點
使用 mysqldump 將表內容轉儲到檔案的優點：
a) 允許所有資料庫、特定資料庫或特定表
b) 允許備份本地伺服器或遠端伺服器
c) 與儲存引擎無關
d) 以文字格式寫入
e) 可移植性強
f) 卓越的複製 / 移動策略
g) 適用於小規模匯出，但不適用於完整備份解決方案
2) mysqldump 呼叫語法
mysqldump 三種常用用法：
shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases
基本用法：
mysqldump --user=<user> --password=<password> --opt db_name > backup.file
對於包含用於重新建立表的 CREATE TABLE 和 INSERT 語句的 SQL 格式轉儲檔案，伺服器會將表內容傳送到 mysqldump ，這將在客戶機主機上寫入檔案。
例如 :
mysqldump --user=root --password --all-databases=TRUE > backup.file
3) mysqldump 一致性備份
確保備份的一致性：
l 僅限 --master-data 選項：在備份過程中鎖定表，在備份檔案中記錄 binlog 位置
l --master-data 和 --single-transaction 選項一起使用；不鎖定表，但僅保證 InnoDB 表一致性。
l --lock-all-tables 透過鎖定表實現一致性
l --flush-logs 啟動新的二進位制日誌
4) mysqldump 輸出格式選項
刪除選項：
--add-drop-database # 將一條 DROP DATABASE 語句新增到每條 CREATE DATABASE 之前
--add-drop-table # 將一條 DROP TABLE 語句新增到每條 CREATE TABLE 語句之前
建立選項：
--no-create-db # 不執行 CREATE DATABASE 語句
--no-create-info # 不執行 CREATE TABLE 語句
--no-data # 建立資料庫和表結構，但不轉儲資料
--no-tablespaces # 指示 MySQL 伺服器不寫入任何 CREATE LOGFILE GROUP 或 CREATE TABLESPACE 語句到輸出
MySQL 程式設計元件：
--routines # 從已轉儲的資料庫中轉儲儲存例程（過程和函式）
--triggers # 轉儲每個已轉儲表的觸發器
選項中的最高選項 (--opt):
--opt # 用於建立高效完整的備份檔案的最常用選項的快捷方式。
所有 mysqldump Options ：
|
Option Name |
Description |
|
在每個 CREATE DATABASE 語句之前新增 DROP DATABASE 語句 |
|
|
在每個 CREATE TABLE 語句之前新增 DROP TABLE 語句 |
|
|
在每個 CREATE TRIGGER 語句之前新增 DROP TRIGGER 語句 |
|
|
用 LOCK TABLES 和 UNLOCK TABLES 語句包圍每個錶轉儲 |
|
|
轉儲所有資料庫中的所有表 |
|
|
允許建立作為關鍵字的列名 |
|
|
包括在 CHANGE MASTER 語句之前 STOP SLAVE ，在輸出結束時 START SLAVE |
|
|
使用指定的網路介面連線到 MySQL 伺服器 |
|
|
安裝字符集的目錄 |
|
|
向轉儲檔案新增註釋 |
|
|
產生更緊湊的輸出 |
|
|
生成與其他資料庫系統或舊 MySQL 伺服器更相容的輸出 |
|
|
使用包含列名的完整 INSERT 語句 |
|
|
壓縮客戶端和伺服器之間傳送的所有資訊 |
|
|
在 CREATE table 語句中包含所有特定於 mysql 的表選項 |
|
|
將所有名稱引數解釋為資料庫名稱 |
|
|
編寫除錯日誌 |
|
|
程式退出時列印除錯資訊 |
|
|
當程式退出時，列印除錯資訊、記憶體和 CPU 統計資訊 |
|
|
要使用的驗證外掛 |
|
|
指定預設字符集 |
|
|
除了通常的選項檔案外，還要讀取指定的選項檔案 |
|
|
只讀指定的選項檔案 |
|
|
選項組字尾值 |
|
|
編寫插入延遲語句而不是插入語句 |
|
|
在主複製伺服器上，執行轉儲操作後刪除二進位制日誌 |
|
|
對於每個表，用語句包圍 INSERT 語句來禁用和啟用鍵 |
|
|
如果給出了註釋，則將轉儲日期包含為 “dump completed on” 註釋 |
|
|
包含 CHANGE MASTER 語句，該語句列出 master 的二進位制日誌座標 |
|
|
啟用 cleartext 身份驗證外掛 |
|
|
從轉儲資料庫中轉儲事件 |
|
|
使用多行 INSERT 語法 |
|
|
此選項與 --tab 選項一起使用，其含義與 LOAD DATA 的對應子句相同 |
|
|
此選項與 --tab 選項一起使用，其含義與 LOAD DATA 的對應子句相同 |
|
|
此選項與 --tab 選項一起使用，其含義與 LOAD DATA 的對應子句相同 |
|
|
此選項與 --tab 選項一起使用，其含義與 LOAD DATA 的對應子句相同 |
|
|
在開始轉儲之前重新整理 MySQL 伺服器日誌檔案 |
|
|
在轉儲 mysql 資料庫之後發出一個 FLUSH PRIVILEGES 語句 |
|
|
即使在錶轉儲期間發生 SQL 錯誤，也要繼續 |
|
|
顯示幫助資訊並退出 |
|
|
使用十六進位制表示法轉儲二進位制列 |
|
|
要連線到的主機 (IP 地址或主機名 ) |
|
|
不轉儲指定的表 |
|
|
在使用 --dump-slave 生成的 CHANGE MASTER 語句中包含 MASTER_HOST/MASTER_PORT 選項 |
|
|
編寫 INSERT IGNORE 而不是 INSERT 語句 |
|
|
此選項與 --tab 選項一起使用，其含義與 LOAD DATA 的對應子句相同 |
|
|
跨所有資料庫鎖定所有表 |
|
|
轉儲前鎖表 |
|
|
在命名檔案中新增警告和錯誤 |
|
|
從 .mylogin.cnf 中讀取登入路徑選項 |
|
|
將二進位制日誌檔名和位置寫入輸出 |
|
|
從伺服器傳送或接收的最大資料包長度 |
|
|
TCP/IP 和套接字通訊的緩衝區大小 |
|
|
將每個轉儲表的 INSERT 語句封裝在 SET autocommit = 0 和 COMMIT 語句中 |
|
|
不寫 CREATE DATABASE 語句 |
|
|
不寫重新建立每個轉儲的表的 CREATE TABLE 語句 |
|
|
不轉儲表內容 |
|
|
不讀取 default 選項檔案 |
|
|
--skip-set-charset 一樣 |
|
|
不寫任何 CREATE LOGFILE GROUP or CREATE TABLESPACE 語句 |
|
|
--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset 的簡寫 |
|
|
轉儲按其主鍵或第一個惟一索引排序的每個表的行 |
|
|
連線到伺服器時使用的密碼 |
|
|
在 Windows 上，使用命名管道連線到伺服器 |
|
|
安裝外掛的目錄 |
|
|
用於連線的 TCP/IP 埠號 |
|
|
列印預設選項 |
|
|
使用的連線協議 |
|
|
每次從伺服器中逐行檢索表的行 |
|
|
反勾字元中的引號識別符號 |
|
|
編寫 REPLACE 語句而不是 INSERT 語句 |
|
|
直接輸出到給定的檔案 |
|
|
從轉儲資料庫中轉儲儲存的例程 ( 過程和函式 ) |
|
|
不傳送舊 (4.1 前 ) 格式的密碼到伺服器 , 5.7.5 棄用 |
|
|
將集合名 default_character_set 新增到輸出 |
|
|
是否新增 SET @@GLOBAL.GTID_PURGED 輸出 |
|
|
用於 shared-memory 連線的共享記憶體的名稱 |
|
|
在從伺服器轉儲資料之前發出 BEGIN SQL 語句 |
|
|
不要在每個 CREATE TABLE 語句之前新增 DROP TABLE 語句 |
|
|
不要新增鎖 |
|
|
不向轉儲檔案新增註釋 |
|
|
不生產更緊湊的輸出 |
|
|
不要禁用鍵 |
|
|
關掉 extended-insert |
|
|
關閉由 --opt 設定的選項 |
|
|
不從伺服器一次一行檢索表的行 |
|
|
不要引用識別符號 |
|
|
不寫 SET NAMES 語句 |
|
|
不要轉儲觸發器 |
|
|
關掉 tz-utc |
|
|
要連線到本地主機，請使用 Unix 套接字檔案 |
|
|
啟用加密連線 |
|
|
包含受信任 SSL 證照頒發機構列表的檔案 |
|
|
包含可信 SSL 證照頒發機構證照檔案的目錄 |
|
|
包含 X.509 證照的檔案 |
|
|
連線加密的允許密碼列表 |
|
|
包含證照撤銷列表的檔案 |
|
|
包含證照撤銷列表檔案的目錄 |
|
|
包含 X.509 鍵的檔案 |
|
|
連線到伺服器的安全狀態 |
|
|
根據伺服器證照公共名稱標識驗證主機名 |
|
|
生成製表符分隔的資料檔案 |
|
|
覆蓋 --databases 或 -B 選項 |
|
|
每個轉儲表的轉儲觸發器 |
|
|
將 SET TIME_ZONE='+00:00' 新增到轉儲檔案 |
|
|
連線到伺服器時使用的 MySQL 使用者名稱 |
|
|
詳細模式 |
|
|
顯示版本資訊並退出 |
|
|
只轉儲由給定 WHERE 條件選擇的行 |
|
|
生成 XML 輸出 |
更詳細引數解釋參見 https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
5) 使用 mysqldump 備份恢復
Ø reload mysqldump 備份
在使用 mysql 命令 reload mysqldump 輸出時，必須為恢復的資料庫提供一個名稱（如果轉儲檔案本身不提供名稱）。如果透過呼叫 mysqldump 與 --database 或 --all-databases 選項建立轉儲檔案，則在從轉儲檔案 reload 時，不需要指定目標資料庫名稱。在這種情況下，轉儲檔案包含相應的 USE db_name 語句。
mysql --login-path=<login-path> <database> < backup_file.sql
Ø 從一個資料庫複製到另一個資料庫
可以使用 mysqldump 輸出恢復表或資料庫並複製它們。 mysql 可以從管道中讀取，因此可以在一個命令中結合使用 mysqldump 和 mysql ，在資料庫之間複製表。也可以使用管道技術透過網路將資料庫或表複製到其他伺服器：
shell> mysqldump -u<user> -p<password> <orig-db> <table> |mysql --login-path=<login-path> <copy-db>
6) mysqldump 用法示例
備份資料庫：實際上轉儲的是資料庫中的表
shell> mysqldump db_name > backup-file.sql
#mysqldump -uroot -paa12AA,. -S /u01/mysql/newdata/mysql.sock test >db_test.txt
reload 方法一：
shell> mysql db_name < backup-file.sql
reload 方法二 :
shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
使用 mysqldump 複製資料到遠端資料庫：
shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
備份多個資料庫：
shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
備份所有資料庫：
shell> mysqldump --all-databases > all_databases.sql
對於
InnoDB
tables
，使用
mysqldump
線上備份
:
shell> mysqldump --all-databases --master-data --single-transaction > all_databases.sql
該備份在轉儲開始時，獲取所有表上的全域性讀鎖 ( 使用 FLUSH TABLES WITH READ LOCK) 。一旦獲得這個鎖，就讀取二進位制日誌座標並釋放鎖。如果在發出 FLUSH 語句時長時間執行 update 語句， MySQL 伺服器可能會停止，直到這些語句完成。在此之後，轉儲變為無鎖的，並且不會干擾表上的讀和寫。如果 MySQL 伺服器接收到的 update 語句很短 ( 就執行時間而言 ) ，那麼即使有很多更新，初始鎖定期也不應該很明顯。
時間點恢復 point-in-time recovery ( 也稱為 “roll-forward”, 當你需要恢復舊的備份和重播備份以來發生的變化 ) ，迴圈二進位制日誌很有用或至少知道二進位制日誌轉儲對應的座標 :
shell> mysqldump --all-databases --master-data=2 > all_databases.sql
或者 :
shell> mysqldump --all-databases --flush-logs --master-data=2
> all_databases.sql
可以同時使用 --master-data 和 --single-transaction 選項，如果表是使用 InnoDB 儲存引擎儲存的，這就提供了一種方便的方法，使線上備份適合在時間點恢復之前使用。
• 除了某些功能外，要選擇 --opt 的效果，請為每個功能使用 --skip 選項。要禁用 extended inserts 和 memory buffering ，請使用 --opt --skip-extended-insert --skip-quick 。 ( 實際上， --skip-extended-insert --skip-quick 就足夠了，因為 --opt 預設為 on 。 )
• 相反， --opt 除禁用索引和表鎖定之外的所有功能，使用 --skip-opt --disable-keys --lock-tables 。
1.1.4. mysqlimport
mysqlimport 客戶機為 LOAD DATA SQL 語句提供一個命令列介面。 mysqlimport 的大多數選項都直接對應於 LOAD DATA 語法的子句。
呼叫 mysqlimport:
shell> mysqlimport [options] db_name textfile1 [textfile2…]
對於命令列中命名的每個文字檔案， mysqlimport 從檔名中刪除任何副檔名，並使用結果確定要將檔案內容匯入其中的表的名稱。例如，名為 patient.txt, patient.text 和 patient 的檔案都將匯入到一個名為 patient 的表中。
要 reload 表，可將位置更改為備份目錄，透過使用 mysql 執行 .sql 檔案，然後使用 mysqlimport 裝入 .tsv 檔案：
shell> cd <backup_dir>
shell> mysql --login-path=<login-path> <database> < table.sql
shell> mysqlimport -u<user> -p<password> <database> table.tsv
如果 --tab 選項與執行格式控制的 --fields-terminated-by 和 --fields-enclosed-by 之類的選項組合，則可使用 mysqlimport 指定相同的格式控制選項，使其知道如何解釋資料檔案。
1) mysqlimport Options
mysqlimport 支援以下選項，這些選項可以在命令列或選項檔案的 [mysqlimport] 和 [client] 組中指定。
|
Option Name |
Description |
|
使用指定的網路介面連線到 MySQL 伺服器 |
|
|
此選項以逗號分隔的列名列表作為其值 |
|
|
壓縮客戶端和伺服器之間傳送的所有資訊 |
|
|
編寫除錯日誌 |
|
|
程式退出時列印除錯資訊 |
|
|
當程式退出時，列印除錯資訊、記憶體和 CPU 統計資訊 |
|
|
要使用的驗證外掛 |
|
|
指定預設字符集 |
|
|
除了通常的選項檔案外，還要讀取指定的選項檔案 |
|
|
只讀指定的選項檔案 |
|
|
選項組字尾值 |
|
|
在匯入文字檔案之前清空表 |
|
|
啟用 cleartext 身份驗證外掛 |
|
|
此選項與 LOAD DATA 的對應子句具有相同的含義 |
|
|
此選項與 LOAD DATA 的對應子句具有相同的含義 |
|
|
此選項與 LOAD DATA 的對應子句具有相同的含義 |
|
|
此選項與 LOAD DATA 的對應子句具有相同的含義 |
|
|
即使發生 SQL 錯誤，也要繼續 |
|
|
從伺服器請求 RSA 公鑰 |
|
|
顯示幫助資訊並退出 |
|
|
連線到給定主機上的 MySQL 伺服器 |
|
|
檢視 --replace 選項的描述 |
|
|
忽略資料檔案的前 N 行 |
|
|
此選項與 LOAD DATA 的對應子句具有相同的含義 |
|
|
從客戶機主機本地讀取輸入檔案 |
|
|
在處理任何文字檔案之前，鎖定所有用於編寫的表 |
|
|
從 .mylogin.cnf 中讀取登入路徑選項 |
|
|
載入表時使用 LOW_PRIORITY 。 |
|
|
讀取無選項檔案 |
|
|
連線到伺服器時使用的密碼 |
|
|
在 Windows 上，使用命名管道連線到伺服器 |
|
|
安裝外掛的目錄 |
|
|
用於連線的 TCP/IP 埠號 |
|
|
列印預設選項 |
|
|
使用的連線協議 |
|
|
replace 和 ignore 選項控制對輸入行的處理，這些輸入行在惟一鍵值上覆制現有行 |
|
|
不傳送密碼到伺服器 (pre - 4.1) 格式， 5.7.5 後棄用 |
|
|
包含 RSA 公鑰的檔案的路徑名 |
|
|
用於共享記憶體連線的共享記憶體的名稱 |
|
|
只在發生錯誤時才產生輸出 |
|
|
要連線到本地主機，請使用 Unix 套接字檔案 |
|
|
啟用加密連線 |
|
|
包含受信任 SSL 證照頒發機構列表的檔案 |
|
|
包含可信 SSL 證照頒發機構證照檔案的目錄 |
|
|
包含 X.509 證照的檔案 |
|
|
連線加密的允許密碼列表 |
|
|
包含證照撤銷列表的檔案 |
|
|
包含證照撤銷列表檔案的目錄 |
|
|
包含 X.509 鍵的檔案 |
|
|
連線到伺服器的安全狀態 |
|
|
根據伺服器證照公共名稱標識驗證主機名 |
|
|
允許加密連線的協議 |
|
|
用於並行檔案載入的執行緒數 |
|
|
連線到伺服器時使用的 MySQL 使用者名稱 |
|
|
詳細模式 |
|
|
顯示版本資訊並退出 |
詳細解釋見https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html
2) mysqlimport 用法示例
shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell> ed
a
100 Max Sydow
101 Count Dracula
.
w imptest.txt
32
q
shell> od -c imptest.txt
0000000 1 0 0 \t M a x S y d o w \n 1 0
0000020 1 \t C o u n t D r a c u l a \n
0000040
shell> mysqlimport --local test imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
shell> mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id | n |
+------+---------------+
| 100 | Max Sydow |
| 101 | Count Dracula |
+------+---------------+
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16976507/viewspace-2646818/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 《入門MySQL—備份與恢復》MySql
- 入門MySQL——備份與恢復MySql
- MySQL入門--備份與恢復（三）MySql
- MySQL入門--備份與恢復（一）MySql
- 《MySQL 入門教程》第 06 篇 備份與恢復MySql
- MySQL 備份與恢復MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- Mysql備份與恢復(1)---物理備份MySql
- MySQL 非常規恢復與物理備份恢復MySql
- Mysql備份與恢復(2)---邏輯備份MySql
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- MySQL備份與恢復——實操MySql
- MySQL備份與恢復操作解析MySql
- Mysql資料備份與恢復MySql
- Mysql備份恢復MySql
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- Mysql的幾種備份與恢復MySql
- MySQL 日誌管理、備份與恢復MySql
- RAC備份恢復之Voting備份與恢復
- 【MySQL】MySQL備份和恢復MySql
- 備份與恢復：polardb資料庫備份與恢復資料庫
- Jenkins備份與恢復Jenkins
- Postgresql 備份與恢復SQL
- SqlServer備份和恢復(二)SQLServer
- mysql學習筆記之備份與恢復MySql筆記
- MySQL-19.資料庫備份與恢復MySql資料庫
- docker 中 MySQL 備份及恢復DockerMySql
- Mongodb的備份恢復與匯出匯入MongoDB
- ORACLE備份&恢復案例二(轉)Oracle
- Oracle 備份 與 恢復 概述Oracle
- DB的備份與恢復
- GitLab的備份與恢復Gitlab
- Linux下MySQL資料庫的備份與恢復LinuxMySql資料庫
- 【MySQL】Xtrabackup備份及恢復指令碼MySql指令碼
- Mysql資料庫備份及恢復MySql資料庫
- MySQL8.4備份恢復快速命令MySql
- MySQL日誌管理，備份和恢復MySql
- MySQL備份和恢復方法彙總MySql