目錄

• 前言
  • 1.備份資料的意義
  • 2.使用mysqldump進行資料庫備份
    • 2.1.不帶引數備份單個資料庫
    • 2.2.加-B引數備份
    • 2.3.使用gzip壓縮備份資料庫
    • 2.4.mysqldump命令作業原理
    • 2.5.備份多個庫
      • 2.5.1.備份多個庫
      • 2.5.2.如何做分庫備份
    • 2.6.備份單個表
    • 2.7.備份多個表
    • 2.8.備份資料庫表結構（不包含資料）
    • 2.9.只備份資料庫表的資料（不包含表結構）
    • 2.10.同時將資料和表結構分離匯出
    • 2.11.重繪binlog檔案引數（-F）
      • 2.11.1.binlog是什么
      • 2.11.2.binlog對于備份的作用
      • 2.11.3.為什么要重繪binlog
      • 2.11.4.如何開啟binlog功能
      • 2.11.5.使用-F重繪binlog日志
    • 2.12.記錄binlog位置的特殊引數（--master-data）
    • 2.13.鎖定所有表備份（-x引數）
    • 2.14.innodb表特有的備份引數（--single-transaction）
  • 3.mysqldump重要關鍵引數說明
  • 4.不同引擎的mysqldump備份命令
    • 4.1.innodb引擎的備份命令
    • 4.2.適合多引擎混合的備份命令
  • 5.利用SQL陳述句方式對表進行匯入匯出
    • 5.1.匯出表
      • 5.1.1.匯出表SQL陳述句語法
      • 5.1.2.匯出表
    • 5.2.匯入表
      • 5.2.1.匯入表命令語法
      • 5.2.2.匯入表

前言

1.備份資料的意義

運維作業的核心簡單概括起來就是兩件事：第一個是保護公司的資料，第二個是讓網站能夠7*24小時提供服務，

雖然這兩件事情都很重要，但是相比較而言，丟失一部分資料和讓網站7*24小時提供服務，哪個更重要呢？

對于絕大多數企業來講，失去資料就相當于失去商機，失去產品，失去客戶，甚至會造成公司倒閉，那么，在所有的資料中，最核心的資料又是哪些呢？這恐怕要屬資料庫中的資料了，既然資料庫中的資料地位這么高，那么資料庫備份與恢復的重要性就不言而喻了，

2.使用mysqldump進行資料庫備份

MySQL資料庫自帶了一個很優秀的備份命令，即mysqldump，

2.1.不帶引數備份單個資料庫

mysqldump oldboy >/opt/mysql_bak.sql

2.2.加-B引數備份

mysqldump -B oldboy >/opt/mysql_bak_B.sql  #B引數的作用是增加創建資料庫和連接資料庫的陳述句，可同時備份多個庫，

2.3.使用gzip壓縮備份資料庫

mysqldump -B oldboy|gzip>/opt/mysql_bak_B.gz

2.4.mysqldump命令作業原理

利用mysqldump命令備份資料的程序，實際上就是把資料（包括庫表）從MySQL庫里以SQL陳述句的形式直接輸出或者生成備份檔案的程序，這種備份成SQL陳述句的方式稱為邏輯備份，

使用mysqldump命令可以把資料庫中的資料匯出來，并通過SQL陳述句的形式存盤，這種備份方式稱為邏輯備份，效率不是很高，在當下的生產場景中，多用于資料量不是很大的備份情況，例如30GB以內的資料，若在資料庫資料很大的時候采用此備份方法，則所用的時候就會很長，恢復的時間也會很長，因此，當資料大于30GB后，建議選擇其他的諸如Xtrabackup的物理方式進行備份和恢復，

2.5.備份多個庫

2.5.1.備份多個庫

mysqldump -B oldboy oldboy_utf8 mysql|gzip>/opt/all.sql.gz

2.5.2.如何做分庫備份

分庫備份實際上就是每次只執行一個mysqldump備份命令陳述句備份一個庫，如果資料庫里有多個庫，就執行多條相同的陳述句來備份各個庫，

mysqldump -uroot -p'oldboy123' -B oldboy |gzip >/tmp/oldboy.sql.gz

mysql -e "show databases;" |egrep -v "_schema|atabase" |sed -r 's#^(.*)#mysqldump -B \1 |gzip >/tmp/\1.sql.gz#g' |bash  #bash執行這些備份命令，就是一次分開備份多個庫了

2.6.備份單個表

當不加-B引數備份資料庫時，例如“mysqldump oldboy test”，mysqldump命令默認就會把oldboy當作庫，把test當作表，如果后面還有多個字串，例如“mysqldump oldboy test test1“，那么除了oldboy為庫之外，其他的test、test1都是oldboy庫的表，

mysqldump oldboy test>/tmp/oldboy_test,sql

2.7.備份多個表

分表備份的缺點：資料檔案多，很碎，一旦需要全部恢復又很麻煩，

1、做一個完整備份，再做一個分庫分表備份，

2、雖然檔案多、碎，但可以利用腳本批量操作多個SQL檔案，

mysqldump mysql user db>/tmp/mysql.sql

egrep -v "#|\*|--|^$" /tmp/mysql.sql

mysqldump oldboy test > /tmp/oldboy_test.sql

2.8.備份資料庫表結構（不包含資料）

利用mysqldump的-d引數可以只備份表的結構，即建表的陳述句，

mysqldump -d oldboy >/opt/oldboy.sql

2.9.只備份資料庫表的資料（不包含表結構）

利用-t引數備份資料庫表的資料（SQL陳述句形式），

mysqldump -t oldboy >/opt/oldboy1.sql

2.10.同時將資料和表結構分離匯出

利用-T引數可以實作將資料和表結構同時分離備份，

vi /etc/my.cnf
secure_file_priv=''  #在[mysqld]模塊下增加
/etc/init.d/mysqld restart

mysqldump oldboy test --compact -T /tmp/

1、-d引數的作用是只備份庫表結構（SQL陳述句形式），

2、-t引數的作用是只備份表內的資料（SQL陳述句形式），

3、-T將庫表和資料分離成不同的檔案，資料是純文本，表結構是SQL陳述句，

2.11.重繪binlog檔案引數（-F）

2.11.1.binlog是什么

binlog是一個二進制格式的檔案，用于記錄用戶對資料庫更新的SQL陳述句資訊，例如更改資料庫庫表和更改表內容的SQL陳述句都會記錄到binlog里，但是對庫表等內容的查詢則不會記錄到日志中，

2.11.2.binlog對于備份的作用

當有資料寫入到資料庫時，還會同時把更新的SQL陳述句寫入到對應的binlog檔案里，

使用mysqldump備份時，一般是對某一時刻的資料進行全備，例如，0點進行資料庫備份，

假設是每天0點對資料庫進行備份，那么在兩次備份之間就有24小時的資料沒有備份，在這期間如果資料庫發生故障，使用mysqldump全量恢復也只能恢復到當日0點，但是有了binlog檔案，就可以將兩次完整備份間隔之間的資料還原，因為binlog檔案里的資料就是寫入資料庫的資料，使用binlog檔案恢復資料，我們稱之為二進制增量資料恢復，

2.11.3.為什么要重繪binlog

重繪（切割）binlog日志的目的就是確定全備和增量備（binlog檔案）的臨界點，當全備完成后，全備時刻以前的binlog檔案就無用了（全備里已有這部分資料了），但是全備以后到下一次全備之前的資料就是十分重要的，這部分資料就存在于binlog檔案里，因此在進行全備時需要找到全備之后和binlog增量之間的臨界點，使得恢復時，需要的binlog檔案資料一條不多（不能和全備的內容重合），一條不少（全備后的所有資料都要有），

2.11.4.如何開啟binlog功能

binlog檔案生效需要一個引數：log_bin，編輯組態檔增加log_bin引數即可，

vi /etc/my.cnf
添加log_bin=mysql-bin到mysqld模塊下

2.11.5.使用-F重繪binlog日志

使用-F將會從備份后的時刻起重新記錄binlog日志檔案，將來增量恢復從新的binlog日志檔案開始即可，

例如，早晨10點丟失資料需要恢復資料，

1、將0點時刻備份的全備資料還原到資料庫，這個時候資料就恢復到了當日0點，

2、0點到10點丟失的資料，就要從全備后當天的所有binlog里恢復，而使用-F切割日志，就是找到0點這個時刻全備和binlog接縫的起始binlog檔案，

mysqldump -F -B oldboy|gzip >/opt/bak_$(date +%F).sql.gz

2.12.記錄binlog位置的特殊引數（--master-data）

mysqldump里提供了一個引數，使得管理員不用重繪binlog，也可以在備份的檔案對應的SQL陳述句里會添加change mastar陳述句及binlog檔案及位置點資訊，

當“--master-data=https://www.cnblogs.com/yanyanqaq/p/1”時，備份結果為可執行的“change master...”陳述句；當“--master-data=2”時，備份結果為注釋的“--change master...” 陳述句，“--”在SQL陳述句里為注釋的意思，“--master-data“ 引數除了確定增量恢復和全備之間的臨界點之外，進行主從復制時的作用更大，

mysqldump --master-data=https://www.cnblogs.com/yanyanqaq/p/1 oldboy --compact|head -1
mysqldump --master-data=2 oldboy --compact|head -1

2.13.鎖定所有表備份（-x引數）

使用mysqldump命令備份期間，資料庫依然是可以寫入資料的，此時，備份的資料就不是某一個時刻的一致性備份了，因此也會在恢復時造成困擾，例如備份時使用-x鎖表備份就會取得0點時刻的完整備份，即在0點時刻停止所有寫入操作，然后匯出備份資料，備份完畢，放開寫入，

如果不鎖表進行備份，就會無法得到某一個時刻的完整備份，由于是一邊備份資料一邊寫入資料，因此最后備份的資料很可能就是一個時間段的資料，而非某一個時刻的一致性全備，

2.14.innodb表特有的備份引數（--single-transaction）

當使用mysqldump的“--single-transaction”對innodb表進行備份時，會開啟一個事務，并將整個備份程序放到一個事務里，以確保執行本次dump會話時，不會看到其他連接會話已經提交了的資料，即備份開始時刻的資料是什么樣，備份出來就是什么樣子，這也相當于是鎖表之后備份的資料，但是這個引數是允許在備份期間寫入資料的，而不是在使用“-x”引數鎖表之后，備份期間無法寫入任何資料，

3.mysqldump重要關鍵引數說明

4.不同引擎的mysqldump備份命令

4.1.innodb引擎的備份命令

“--single-transaction”是innodb引擎專有的備份引數，優勢就是備份期間資料依然可以執行寫操作，

mysqldump -A -B --master-data=https://www.cnblogs.com/yanyanqaq/p/2 --single-transaction |gzip >/opt/all.sql.gz

4.2.適合多引擎混合的備份命令

使用“--master-data”會自動開啟-x鎖表引數功能，在備份期間會影響資料寫入，

mysqldump -A -B --master-data=https://www.cnblogs.com/yanyanqaq/p/2 |gzip >/opt/all_$(date +%F).sql.gz

5.利用SQL陳述句方式對表進行匯入匯出

5.1.匯出表

5.1.1.匯出表SQL陳述句語法

5.1.2.匯出表

use oldboy
select * from test;
select * from test into outfile "/tmp/oldboy_test1.txt";  #將test表里的資料匯出為純文本
system cat /tmp/oldboy_test.txt;
select * from test into outfile "/tmp/oldboy_test2.txt" character set utf8;  #匯出時設定字符集
select * from test into outfile "/tmp/oldboy_test3.txt" fields terminated by '-';  #指定分隔符匯出
select * from test into outfile "/tmp/oldboy_test4.txt" fields enclosed by "";  #匯出時設定對欄位內容進行參考

5.2.匯入表

5.2.1.匯入表命令語法

5.2.2.匯入表

delete from test;
system cat /tmp/oldboy_test1.txt;
load data infile '/tmp/oldboy_test1.txt' into table test;  #默認方式將資料匯入資料庫
select * from test;
system cat /tmp/oldboy_test3.txt;
load data infile '/tmp/oldboy_test3.txt' into table test fields terminated by '-';  #指定分隔符方式匯入資料庫
select * from test;
system cat /tmp/oldboy_test4.txt
load data infile '/tmp/oldboy_test4.txt' into table test fields enclosed by "";  #雙引號參考資料匯入資料庫

標籤：Linux

上一篇：虛擬機中Linux 下安裝tools

下一篇：防火墻（firewall）的操作命令