文章目錄

• 命令介紹
• 常用選項(xiàng)
• 參考示例
• • 將指定數(shù)據(jù)表的數(shù)據(jù)導(dǎo)出為 SQL 腳本文件和文本文件
  • 將指定的多個(gè)數(shù)據(jù)表的數(shù)據(jù)導(dǎo)出為 SQL 腳本文件和文本文件
  • 將指定數(shù)據(jù)庫(kù)導(dǎo)出到腳本文件中
  • 將指定的多個(gè)數(shù)據(jù)庫(kù)導(dǎo)出到腳本文件中
  • 將指定的表導(dǎo)出到腳本文件中
  • 將指定數(shù)據(jù)庫(kù)中的多個(gè)表的數(shù)據(jù)導(dǎo)出到指定的文件中
  • 將數(shù)據(jù)表中滿足特定條件的記錄導(dǎo)出
  • 導(dǎo)出數(shù)據(jù)之后生成一個(gè)新的 binlog 文件
  • 導(dǎo)出指定數(shù)據(jù)庫(kù)的所有表結(jié)構(gòu)
  • 導(dǎo)出指定數(shù)據(jù)表的數(shù)據(jù)，同時(shí)自定義導(dǎo)出數(shù)據(jù)的格式
  • 將指定數(shù)據(jù)庫(kù)的數(shù)據(jù)全部導(dǎo)出，并備份數(shù)據(jù)庫(kù)的一致性狀態(tài)，并在備份文件中記錄二進(jìn)制日志最后的位置
  • 備份指定數(shù)據(jù)庫(kù)的數(shù)據(jù)，并在備份文件中記錄二進(jìn)制日志最后的位置，而且刷新日志文件
  • 將一個(gè) MySQL 服務(wù)器的數(shù)據(jù)庫(kù)的數(shù)據(jù)復(fù)制到另外一個(gè) MySQL 服務(wù)器的數(shù)據(jù)庫(kù)中
  • 導(dǎo)出所有的數(shù)據(jù)庫(kù)到腳本文件中
  • 壓縮備份指定的數(shù)據(jù)庫(kù)
  • 將壓縮的備份數(shù)據(jù)文件解壓，并還原到當(dāng)前的數(shù)據(jù)庫(kù)服務(wù)器中
  • 壓縮備份和還原數(shù)據(jù)
  • 結(jié)合 Linux 的 cron 命令實(shí)現(xiàn)定時(shí)備份數(shù)據(jù)
  • • 定時(shí)執(zhí)行備份命令語(yǔ)句
    • 定時(shí)執(zhí)行備份腳本文件

命令介紹

mysqldump 是 MySQL 用于轉(zhuǎn)存儲(chǔ)數(shù)據(jù)庫(kù)的客戶端程序。它主要產(chǎn)生一系列的 SQL 語(yǔ)句，可以封裝到文件，該文件包含所有重建您的數(shù)據(jù)庫(kù)所需要的 SQL 命令語(yǔ)句，如 CREATE DATABASE，CREATE TABLE，INSERT 等等。可以用來(lái)實(shí)現(xiàn)輕量級(jí)的快速遷移或恢復(fù)數(shù)據(jù)庫(kù)。是 MySQL 數(shù)據(jù)庫(kù)實(shí)現(xiàn)邏輯備份的一種方式。

在日常維護(hù)工作當(dāng)中經(jīng)常會(huì)需要對(duì)數(shù)據(jù)進(jìn)行導(dǎo)出操作，而 mysqldump 則是 MySQL 導(dǎo)出數(shù)據(jù)時(shí)經(jīng)常用到的命令工具。

mysqldump 命令可用來(lái)轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)進(jìn)行備份或?qū)?shù)據(jù)轉(zhuǎn)移到另一個(gè) SQL 服務(wù)器（不一定是 MySQL 服務(wù)器）。

注意：
1.復(fù)制整個(gè)數(shù)據(jù)庫(kù)目錄也可以備份數(shù)據(jù)庫(kù)，也是最直接有效的方式，但是只適用于使用了 MyISAM 引擎的數(shù)據(jù)庫(kù)，不適用于使用了 InnoDB 引擎的數(shù)據(jù)庫(kù)。
2.如果你在服務(wù)器上進(jìn)行備份，并且表均為 MyISAM 表，應(yīng)考慮使用 mysqlhotcopy，因?yàn)榭梢愿斓剡M(jìn)行備份和恢復(fù)。

常用選項(xiàng)

請(qǐng)參閱《MySQL命令之mysqldump的選項(xiàng)詳解》

參考示例

將指定數(shù)據(jù)表的數(shù)據(jù)導(dǎo)出為 SQL 腳本文件和文本文件

將數(shù)據(jù)庫(kù) test 中的數(shù)據(jù)表 student 導(dǎo)出，導(dǎo)出的文件存放在 /root/test 目錄下：

[root@htlwk0001host ~]# mysqldump -u root -p -T /root/test test student; Enter password:

注：
1.選項(xiàng) -u 指定登錄數(shù)據(jù)庫(kù)的用戶名；選項(xiàng) -p 輸入登錄密碼；選項(xiàng) -T 指定導(dǎo)出的數(shù)據(jù)文件的存放位置
2.執(zhí)行上面的命令語(yǔ)句后，會(huì)得到兩個(gè)文件：student.sql 和 student.txt

將指定的多個(gè)數(shù)據(jù)表的數(shù)據(jù)導(dǎo)出為 SQL 腳本文件和文本文件

[root@htlwk0001host ~]# mysqldump -uroot -pqpw123.com -T /root/test qydpw td_appraise_dimension td_area;

注：
1.命令默認(rèn)將路徑后面的 qydpw 解析為數(shù)據(jù)庫(kù)名稱，后面的詞語(yǔ)解析為數(shù)據(jù)表的名稱。
2.表名之間只能使用空格分隔。

將指定數(shù)據(jù)庫(kù)導(dǎo)出到腳本文件中

導(dǎo)出文件如果沒(méi)有指明路徑，默認(rèn)是存放在用戶家目錄下。

語(yǔ)法格式：

mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫(kù)名 > 導(dǎo)出的文件名

例如，將數(shù)據(jù)庫(kù) test 導(dǎo)出到文件 test.sql 中：

[root@htlwk0001host ~]# mysqldump -uroot -p123456 test > test.sql

說(shuō)明：
1.默認(rèn)選項(xiàng) -p 后面的是數(shù)據(jù)庫(kù)名稱
2.導(dǎo)出的腳本文件存放在當(dāng)前工作目錄下
3.腳本文件中包含建表語(yǔ)句和插入數(shù)據(jù)的insert語(yǔ)句，不含創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句

將指定的多個(gè)數(shù)據(jù)庫(kù)導(dǎo)出到腳本文件中

[root@htlwk0001host ~]# mysqldump -uroot -p123456 --databases db1 db2 > /tmp/test.sql

將指定的表導(dǎo)出到腳本文件中

語(yǔ)法格式：

mysqldump -u 用戶名 -p密碼 數(shù)據(jù)庫(kù)名 表名 > 導(dǎo)出的文件名

例如，將數(shù)據(jù)庫(kù) test 的表 student 導(dǎo)出到文件 student.sql 中：

[root@htlwk0001host ~]# mysqldump -uroot -p123456 test student > student.sql

注：
1.命令會(huì)自動(dòng)識(shí)別處第一個(gè)詞語(yǔ) test 是數(shù)據(jù)庫(kù)名稱，后面跟著是表的名稱。
2.如果要將表的數(shù)據(jù)導(dǎo)入到指定的文件中，切記，不能使用選項(xiàng) -T 或者 --tab 指定文件存放的路徑，因?yàn)橹付宋募娣诺穆窂?#xff0c;命令會(huì)自己另外生產(chǎn)兩個(gè)文件（一個(gè)是 .sql 文件，一個(gè)是 .txt 文件）存放在指定的路徑下，而你自己指定的文件也會(huì)生成，但是不會(huì)有任何數(shù)據(jù)。
3.生成的文件默認(rèn)存放在當(dāng)前工作目錄下。

將指定數(shù)據(jù)庫(kù)中的多個(gè)表的數(shù)據(jù)導(dǎo)出到指定的文件中

將數(shù)據(jù)庫(kù) db1 中的表 a1 和 a2 導(dǎo)出到文件 /tmp/db1.sql 中：

[root@htlwk0001host ~]# mysqldump -uroot -p123456 --databases db1 --tables a1 a2 > /tmp/db1.sql

注：切記，如果要將數(shù)據(jù)導(dǎo)出到你自己指定的文件中，則不能使用選項(xiàng) -T 或者 --tab 指定文件存放的路徑

將數(shù)據(jù)表中滿足特定條件的記錄導(dǎo)出

例如，導(dǎo)出數(shù)據(jù)庫(kù) db1 中的表 a1 中的 id=1 的數(shù)據(jù)：

[root@htlwk0001host ~]# mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql

例如，導(dǎo)出數(shù)據(jù)庫(kù) db1 中的表 a1 中的 name='liaowenxiong' 的數(shù)據(jù)：

[root@htlwk0001host ~]# mysqldump -uroot -proot --no-create-info --databases db1 --tables a1 --where="name='liaowenxiong'" > /tmp/a1.sql

導(dǎo)出數(shù)據(jù)之后生成一個(gè)新的 binlog 文件

有時(shí)候會(huì)希望導(dǎo)出數(shù)據(jù)之后生成一個(gè)新的 binlog 文件，只需要加上 -F 參數(shù)即可：

[root@htlwk0001host ~]# mysqldump -uroot -proot --databases db1 -F > /tmp/db1.sql

導(dǎo)出指定數(shù)據(jù)庫(kù)的所有表結(jié)構(gòu)

語(yǔ)法格式：

mysqldump -u user_name -p -d --add-drop-table database_name > outfile_name.sql

說(shuō)明：
1.選項(xiàng) -d 或 --no-data 表示不導(dǎo)出行數(shù)據(jù)
2.選項(xiàng) --add-drop-table 表示在每個(gè) create table 語(yǔ)句之前增加一個(gè) drop table 語(yǔ)句
3.導(dǎo)出的腳本文件中的內(nèi)容都是創(chuàng)建表的語(yǔ)句，不含創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句，不含插入數(shù)據(jù)的語(yǔ)句

例如，將數(shù)據(jù)庫(kù) test 的數(shù)據(jù)結(jié)構(gòu)導(dǎo)出到文件 test.sql 文件中：

[root@htlwk0001host ~]# mysqldump -uroot -p1234567 -d --add-drop-table test > test.sql

導(dǎo)出的 test.sql 的內(nèi)容如下所示：

-- MySQL dump 10.13 Distrib 5.7.31, for Linux (x86_64) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.7.31/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;-- -- Table structure for table `dept_htlwk` --DROP TABLE IF EXISTS `dept_htlwk`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `dept_htlwk` (`deptno` varchar(50) NOT NULL COMMENT '部門(mén)代碼',`dname` varchar(50) NOT NULL COMMENT '部門(mén)名稱',`location` varchar(200) NOT NULL COMMENT '部門(mén)地址' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部門(mén)表'; /*!40101 SET character_set_client = @saved_cs_client */;-- -- Table structure for table `emp_htlwk` --DROP TABLE IF EXISTS `emp_htlwk`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `emp_htlwk` (`empno` varchar(50) DEFAULT NULL COMMENT '職員代碼',`ename` varchar(50) DEFAULT NULL COMMENT '職員姓名',`job` varchar(50) DEFAULT NULL COMMENT '職員崗位',`salary` decimal(7,2) DEFAULT NULL COMMENT '職員月薪',`bonus` decimal(7,2) DEFAULT NULL COMMENT '獎(jiǎng)金',`hiredate` date DEFAULT NULL,`mgr` varchar(50) DEFAULT NULL COMMENT '上級(jí)代碼',`deptno` varchar(50) DEFAULT NULL COMMENT '部門(mén)代碼' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='職員表'; /*!40101 SET character_set_client = @saved_cs_client */;

導(dǎo)出指定數(shù)據(jù)表的數(shù)據(jù)，同時(shí)自定義導(dǎo)出數(shù)據(jù)的格式

[root@htlwk0001host ~]# mysqldump -u root -p -T /root/test test student --fields-terminated-by '\t' --fields-enclosed-by '"' --lines-terminated-by '\n';

說(shuō)明：
--fields-terminated-by 'string' # 字段分隔符
--fields-enclosed-by 'char' # 字段引用符
--fields-optionally-enclosed-by 'char' # 字段引用符，只在 char、varchar、text、date 等字段類型上生效
--fields-escaped-by 'char' # 轉(zhuǎn)義字符
--lines-terminated-by 'string' # 記錄結(jié)束符，即換行符

我們看下使用自定義格式導(dǎo)出的文本文件的內(nèi)容：

[root@htlwk0001host ~]# cat /root/test/student.txt "00000000000000000001" "liaowenxiong" "18" \N \N "2021-09-25 02:40:51" "00000000000000000002" "liudehua" "28" \N \N "2021-09-25 02:40:51" "00000000000000000003" "zhangxueyou" "38" \N \N "2021-09-25 02:40:51"

可以看到字段值全部加上了雙引號(hào)。

將指定數(shù)據(jù)庫(kù)的數(shù)據(jù)全部導(dǎo)出，并備份數(shù)據(jù)庫(kù)的一致性狀態(tài)，并在備份文件中記錄二進(jìn)制日志最后的位置

例如，將數(shù)據(jù)庫(kù) test 的數(shù)據(jù)全部導(dǎo)出：

[root@htlwk0001host ~]# mysqldump -h10.10.30.241 -uadmin -p123456 --single-transaction --master-data=2 --triggers --routines --events test -T /data/backup/

注：
1.會(huì)將每個(gè)數(shù)據(jù)表導(dǎo)出成兩個(gè)文件：SQL 腳本文件和文本文件。

2.--single-transaction，會(huì)在導(dǎo)出數(shù)據(jù)之前寫(xiě)入一個(gè) START TRANSACTION SQL 語(yǔ)句，它僅對(duì)事務(wù)表（如InnoDB）有用，因?yàn)樗鼤?huì)在啟動(dòng)事務(wù)時(shí)轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)的一致?tīng)顟B(tài)，而不會(huì)阻塞任何應(yīng)用程序。

3.--master-data=2，將二進(jìn)制文件最后的位置記錄到備份文件中

在備份文件中你會(huì)看到下面這條語(yǔ)句：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4544;

上面的語(yǔ)句表示你在備份數(shù)據(jù)的時(shí)候，二進(jìn)制日志文件名稱是 mysql-bin.000001，而且在備份的時(shí)刻，二進(jìn)制日志文件的最后的位置是 4544，那么將來(lái)你要恢復(fù)數(shù)據(jù)，可以從 4544 位置之后開(kāi)始恢復(fù)，因?yàn)橹暗臄?shù)據(jù)你已經(jīng)備份成了 SQL 腳本文件，你只要執(zhí)行這個(gè)腳本文件就可以恢復(fù)數(shù)據(jù)了，但是位置 4544 之后發(fā)生的數(shù)據(jù)變化，你就要從這個(gè)位置往后慢慢找了。

4.--triggers，導(dǎo)出觸發(fā)器

5.--routines，導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)

6.--events，Include Event Scheduler events for the dumped databases in the output. This option requires the EVENT privileges for those databases.

備份指定數(shù)據(jù)庫(kù)的數(shù)據(jù)，并在備份文件中記錄二進(jìn)制日志最后的位置，而且刷新日志文件

[root@htlwk0001host ~]# mysqldump -uroot -pqpw123.com --lock-all-tables --triggers --routines --events --flush-logs --master-data=2 test > /root/test/test.sql

說(shuō)明：
刷新日志文件，會(huì)生成一個(gè)新的日志文件，而新的日志文件并不是舊的日志文件的復(fù)制，我看過(guò)新舊日志文件的內(nèi)容并不相同。

將一個(gè) MySQL 服務(wù)器的數(shù)據(jù)庫(kù)的數(shù)據(jù)復(fù)制到另外一個(gè) MySQL 服務(wù)器的數(shù)據(jù)庫(kù)中

語(yǔ)法格式：

[root@htlwk0001host ~]# mysqldump --host=host1 -uuser_name -p --opt source_db| mysql --host=host2 -uuser_name -p -C target_db

注：host2 主機(jī)上已經(jīng)創(chuàng)建 target_db 數(shù)據(jù)庫(kù)。

例如，將當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器中的數(shù)據(jù)庫(kù) qydpw 的數(shù)據(jù)全部復(fù)制到 IP 地址為 114.28.38.109 的數(shù)據(jù)庫(kù)服務(wù)器的數(shù)據(jù)庫(kù) test 中：

[root@htlwk0001host ~]# mysqldump -uroot -p123456 --opt qydpw | mysql --host=114.28.38.109 -uroot -p123456 -C test [root@htlwk0001host ~]# mysqldump --host=192.168.80.137 -uroot -proot -C --databases test | mysql --host=192.168.80.133 -uroot -proot test

說(shuō)明：
1.選項(xiàng) -C 可以啟用壓縮傳遞。
2.如果沒(méi)有使用 --opt，mysqldump 就會(huì)把整個(gè)結(jié)果集裝載到內(nèi)存中，然后導(dǎo)出。如果數(shù)據(jù)非常大就會(huì)導(dǎo)致導(dǎo)出失敗。

導(dǎo)出所有的數(shù)據(jù)庫(kù)到腳本文件中

該命令會(huì)導(dǎo)出包括系統(tǒng)數(shù)據(jù)庫(kù)在內(nèi)的所有數(shù)據(jù)庫(kù)：

[root@htlwk0001host ~]# mysqldump -uroot -proot --all-databases > /tmp/all.sql

壓縮備份指定的數(shù)據(jù)庫(kù)

[root@htlwk0001host ~]# mysqldump -uroot -p123456 -P3306 -q -Q --set-gtid-purged=OFF --default-character-set=utf8 --hex-blob --skip-lock-tables --databases test | gzip >/root/test/test.sql.gz[root@htlwk0001host ~]# mysqldump -uroot -p123456 -P3306 -q -Q --set-gtid-purged=OFF --default-character-set=utf8 --hex-blob --skip-lock-tables --databases test 2>/root/test/test.err | gzip >/root/test/test.sql.gz

說(shuō)明：其中的語(yǔ)句 2>/root/test/test.err 表示將標(biāo)準(zhǔn)錯(cuò)誤內(nèi)容重定向到文件 test.err 中，如文件存在內(nèi)容則清空。2> 表示錯(cuò)誤輸出重定向。

將壓縮的備份數(shù)據(jù)文件解壓，并還原到當(dāng)前的數(shù)據(jù)庫(kù)服務(wù)器中

[root@htlwk0001host ~]# gunzip -c test.sql.gz | mysql -uroot -pqpw123.com -vvv -P3306 --default-character-set=utf8 1> test.log 2> test.err

說(shuō)明：
1.1> test.log 執(zhí)行過(guò)程信息標(biāo)準(zhǔn)輸出重定向到文件 test.log 中
2.2> test.err 將標(biāo)準(zhǔn)錯(cuò)誤內(nèi)容重定向到文件 test.err 中，如文件存在內(nèi)容則清空

壓縮備份和還原數(shù)據(jù)

[root@htlwk0001host ~]# mysqldump -hhost_name -uuser_name -ppassword --databases db_name | gzip > backup-file.sql.gz

對(duì)應(yīng)的還原動(dòng)作為：

[root@htlwk0001host ~]# gunzip < backup-file.sql.gz | mysql -uuser_name -ppassword db_name

注意：如果數(shù)據(jù)庫(kù)服務(wù)器中不存在 db_name，執(zhí)行上面的還原語(yǔ)句會(huì)報(bào)錯(cuò)，把 db_name 去掉即可

結(jié)合 Linux 的 cron 命令實(shí)現(xiàn)定時(shí)備份數(shù)據(jù)

定時(shí)執(zhí)行備份命令語(yǔ)句

比如需要在每天凌晨 1:30 備份某個(gè)主機(jī)上的所有數(shù)據(jù)庫(kù)并壓縮 dump 文件為 gz 格式，那么可在 /etc/crontab 配置文件中加入下面代碼行：

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

前面5個(gè)參數(shù)分別表示分鐘、小時(shí)、日、月、年，星號(hào)表示任意。date '+%m-%d-%Y' 得到當(dāng)前日期的 MM-DD-YYYY 格式。

定時(shí)執(zhí)行備份腳本文件

編寫(xiě) Shell 腳本備份 MySQL 數(shù)據(jù)庫(kù)：

[root@htlwk0001host ~]# vi /backup/backup.sh #!bin/bash cd /backup echo "You are in backup dir" mv backup* /oldbackup echo "Old dbs are moved to oldbackup folder" File = backup-$Now.sql mysqldump -u user -p password database-name > $File echo "Your database backup successfully completed"

上面腳本文件保存為 backup.sh，并且系統(tǒng)中已經(jīng)創(chuàng)建兩個(gè)目錄 /olcbackup 和 /backup。每次執(zhí)行 backup.sh 時(shí)都會(huì)先將 /backup 目錄下所有名稱以 backup 開(kāi)頭的文件移到 /oldbackup 目錄下。

為上述腳本制定執(zhí)行計(jì)劃如下：

[root@htlwk0001host ~]# crontab -e 30 1 * * * /backup.sh

總結(jié)

以上是生活随笔為你收集整理的MySQL命令之mysqldump -- 数据库备份程序的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。