MySQL數(shù)據(jù)庫的mysqldump完全備份、binlog的增量備份與還原

一、 備份的目的：

做災(zāi)難性恢復(fù)：對損壞的書籍進(jìn)行恢復(fù)和還原

需求改變：因需求改變而需要把數(shù)據(jù)還原到改變以前

二、 備份需要考慮的問題：

可以容忍丟失多長時間的數(shù)據(jù)；（數(shù)據(jù)不能百分百全部恢復(fù)）

恢復(fù)數(shù)據(jù)要在多長時間內(nèi)完成；

恢復(fù)的時候是否需要持續(xù)提供服務(wù)；

恢復(fù)的對象：是整個庫、多個表，還是單個庫、單個表。

備份的數(shù)據(jù)庫大小不超過50G

三、 按照備份時對數(shù)據(jù)庫的影響范圍分類：

hot backup ：指在數(shù)據(jù)庫運行中直接備份，對正在運行的數(shù)據(jù)庫沒有任何影響；稱為 在線備份(Online

Backup） （備份的同時，業(yè)務(wù)不受影響）

cold backup：指在數(shù)據(jù)庫停止的情況下進(jìn)行備份；稱為 離線備份(Offline

Backup) （需要關(guān)mysql服務(wù)，讀寫請求均不允許狀態(tài)下進(jìn)行）

warm backup：同樣在數(shù)據(jù)庫運行時進(jìn)行備份，但僅支持讀請求，不允許寫請求；例如：加一個讀鎖以保證備份數(shù)據(jù)的一致性 （服務(wù)在線，但僅支持讀請求，不允許寫請求）

四、 按照備份后文件內(nèi)容分類：

邏輯備份：指備份后的文件內(nèi)容是可讀的，通常為文本文件，內(nèi)容一般是SQL語句，或者是表內(nèi)的實際數(shù)據(jù)；適用于數(shù)據(jù)庫的升級和遷移，回復(fù)時間較長

物理文件備份：對數(shù)據(jù)庫物理文件（如數(shù)據(jù)文件、日志文件等）的備份；恢復(fù)時間較短

支持熱備份與冷備份

五、 按照備份數(shù)據(jù)庫的內(nèi)容分類：

完全備份：每次對數(shù)據(jù)進(jìn)行完整的備份（備份的是數(shù)據(jù)庫中的全部數(shù)據(jù)），包含用戶表、系統(tǒng)表、索引、視圖和存儲過程等，但需要花費更多的時間和存儲空間。

差異備份：在上一次完全備份原有的基礎(chǔ)上，對更新的數(shù)據(jù)進(jìn)行備份（對上一次完整備份后更新的數(shù)據(jù)）

增量備份：在上次備份的基礎(chǔ)上，對更新的數(shù)據(jù)進(jìn)行備份

日志備份：二進(jìn)制日志備份

注：建議的備份策略

完全備份 + 增量備份 + 二進(jìn)制日志

完全備份 + 差異備份 + 二進(jìn)制日志

六、 邏輯備份工具 mysqldump：

mysqldump屬于單線程（備份時間較長），通過tcp協(xié)議連接到mysql數(shù)據(jù)庫，將數(shù)據(jù)轉(zhuǎn)換成標(biāo)準(zhǔn)的SQL語句

優(yōu)點：

備份“?！倍褥`活；既可以針對整個MySQL服務(wù)，也可以只備份某個或者某幾個DB，或者還可以指定只備份某個或者某幾個表對象，甚至可以實現(xiàn)只備份表中某些符合條件的記錄（-w, --where: 只導(dǎo)出符合條件的記錄）。

缺點：

a) 當(dāng)數(shù)據(jù)是浮點數(shù)時，會出現(xiàn)精度丟失。

b) Mysqldump的備份過程屬于邏輯備份，備份速度、恢復(fù)速度與物理備份工具相比較慢，而且mysqldump備份的過程是串行化的，不會并行的進(jìn)行備份，當(dāng)數(shù)據(jù)量較大時，一般不會使用mysqldump進(jìn)行備份，因為效率較低。只適合備份50G以下的數(shù)據(jù)

mysqldump對innodb存儲引擎支持熱備

mysqldump對myisam存儲引擎只支持溫備

七、 導(dǎo)出數(shù)據(jù)的語法及選項：

語法：mysqldump [options] [db_name [tbl_name …]]>導(dǎo)出的文件名.sql

語法：mysqldump 選項 庫名.表名(一個或多個) >導(dǎo)出的文件名.sql

常用的參數(shù)：

-?, --help: 顯示幫助信息，英文的；

-u, --user: 指定連接的用戶名；

-p, --password: 指定用戶的密碼，可以交互輸入密碼；

-S , --socket: 指定socket文件連接，本地登錄才會使用。

-h, --host: 指定連接的服務(wù)器名稱或者IP。

-P, --port=: 連接數(shù)據(jù)庫監(jiān)聽的端口。

–default-character-set:

設(shè)置字符集，默認(rèn)是UTF8。

-A,–all-databases:

導(dǎo)出所有數(shù)據(jù)庫。不過默認(rèn)情況下是不會導(dǎo)出information_schema系統(tǒng)數(shù)據(jù)庫。

-B, --databases:

導(dǎo)出指定的某個/或者某幾個數(shù)據(jù)庫，參數(shù)后面所有名字都被看作數(shù)據(jù)庫名，用空格隔開，包含CREATE DATABASE創(chuàng)建庫的語句。

–tables: 導(dǎo)出指定表對象，參數(shù)格式為“庫名

表名”，默認(rèn)該參數(shù)將覆蓋-B參數(shù)。

-w, --where: 只導(dǎo)出符合條件的記錄。

-l, --lock-tables:

默認(rèn)參數(shù)，鎖定讀取的表對象，想導(dǎo)出一致性備份的話最好使用該參數(shù)，但會導(dǎo)致無法對表執(zhí)行寫入操作。

–single-transaction:

該選項在導(dǎo)出數(shù)據(jù)之前提交一個BEGIN SQL語句，BEGIN 不會阻塞任何應(yīng)用程序且能保證導(dǎo)出時數(shù)據(jù)庫的一致性狀態(tài)。它只適用于innoDB存儲引擎。

在InnoDB導(dǎo)出時會建立一致性快照，在保證導(dǎo)出數(shù)據(jù)的一致性前提下，又不會堵塞其他會話的讀寫操作。指定這個參數(shù)后，其他連接不能執(zhí)行ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE這類語句，事務(wù)的隔離級別無法控制DDL語句。本選項和–lock-tables 選項是互斥的，使用參數(shù)–single-transaction會自動關(guān)閉該選項。

-d, --no-data: 只導(dǎo)出表結(jié)構(gòu)，不導(dǎo)出表數(shù)據(jù)。

-t, --no-create-info: 只導(dǎo)出數(shù)據(jù)，而不添加CREATE TABLE

語句。

-f, --force: 即使遇到SQL錯誤，也繼續(xù)執(zhí)行。

-F, --flush-logs: 在執(zhí)行導(dǎo)出前先刷新二進(jìn)制日志文件，一般來說，如果是全庫導(dǎo)出，建議先刷新日志文件，否則就不用了。

-x, --lock-all-tables: 在導(dǎo)出任務(wù)執(zhí)行期間鎖定所有數(shù)據(jù)庫中的所有表，以保證數(shù)據(jù)的一致性。這是一個全局鎖定，并且自動關(guān)閉–single-transaction 和–lock-tables 選項。這個參數(shù)副作用比較大，這是全庫鎖定，備份執(zhí)行過程中，該庫無法進(jìn)行讀寫操作，不是所有業(yè)務(wù)場景都能接受的。請慎用。（讀寫操作無法執(zhí)行）

-n, --no-create-db: 不生成建庫的語句CREATE DATABASE … IF EXISTS，即使指定—all-databases或–databases這類參數(shù)。

–triggers: 導(dǎo)出表的觸發(fā)器腳本，默認(rèn)就是啟用狀態(tài)。使用–skip-triggers禁用它。

-R, --outines: 導(dǎo)出存儲過程以及自定義函數(shù)

導(dǎo)出數(shù)據(jù)：

A:導(dǎo)出所有數(shù)據(jù)庫

mysql -u用戶名

-p密碼 -A>導(dǎo)出的文件名.sql

#mysqldump -uroot -p123456 -A >all.sql

或

#mysqldump -uroot -p123456 --all-databases >all2.sql

參數(shù)-A代表所有，等同于—all-databases

B:導(dǎo)出某個數(shù)據(jù)庫

mysqldump

-u 用戶名 -p 數(shù)據(jù)庫名 > 導(dǎo)出的文件名.sql

#mysqldump -uroot -p123456 book >book.sql

#vim book.sql

C:導(dǎo)出單張表

#mysqldump -uroot -p123456 book books >books.sql #導(dǎo)出book庫books表

D:導(dǎo)出庫的表結(jié)構(gòu)

#mysqldump -uroot -p123456 -d book>booktable.sql #只導(dǎo)出book庫的表結(jié)構(gòu)

E：只導(dǎo)出數(shù)據(jù)

#mysqldump -uroot -p123456 -t book>bookdata.sql #只導(dǎo)出book庫中的數(shù)據(jù)

F:導(dǎo)出數(shù)據(jù)庫，并自動生成庫的創(chuàng)建語句

#mysqldump -uroot -p123456 -B book2 >book2.sql

#mysql -uroot -p123456 < book2.sql 導(dǎo)入不用指定數(shù)據(jù)名

導(dǎo)入數(shù)據(jù)：

A：導(dǎo)入所有數(shù)據(jù)庫

#mysql -uroot -p123456 <all.sql

B：導(dǎo)入數(shù)據(jù)庫

#mysql -uroot -p123456 book <book.sql #如果導(dǎo)入時，沒有對應(yīng)的數(shù)據(jù)庫，需要你手動創(chuàng)建一下：mysql> create database book;

使用source導(dǎo)入

mysql> create database book;

mysql> use book;

mysql> source /root/book.sql

c:導(dǎo)入表

mysql> drop table books;

mysql> source /root/books.sql;

##導(dǎo)入表時，不需要重新，創(chuàng)建表。要先進(jìn)到相應(yīng)的數(shù)據(jù)庫中

mysql> select * from books;

D：導(dǎo)入表結(jié)構(gòu)和數(shù)據(jù)

mysql> create database book;

#mysql -uroot -p123456 book<booktable.sql

#mysql -uroot -p123456 book<bookdata.sql

八、 二進(jìn)制日志：Binary Log & Binary Log Index

mysql的二進(jìn)制日志記錄著數(shù)據(jù)庫的所有增、刪、改等操作日志(前提是要在自己的服務(wù)器上開啟binlog)，還包括了這些操作的執(zhí)行時間。

2.binlog的用途：

1）：主從同步

2）：恢復(fù)數(shù)據(jù)庫

執(zhí)行mysql> show

variables like ‘log_bin%’;查看binlog是否開啟

Off表示mysql當(dāng)前binlog功能沒有開啟。

mysql>

flush logs; 刷新日志

filename.index 存放binlog的日志清單文件

開啟binary

log功能：

修改/etc/my.cnf配置文件使二進(jìn)制文件生效，添加如下內(nèi)容：

log-bin=/data/mysql/log/mysql_bin

（二進(jìn)制日志文件存放路徑）

server-id=1

重啟Mysql服務(wù)，使配置文件修改生效

[root@localhost log]# systemctl

restart mysqld

九、 binlog的附加選項參數(shù)：

1、“max-binlog-size”設(shè)置binlog 的最大存儲上限，一般設(shè)置為512M或1G,一般不能超過1G。

2、“binlog-do-db=db_name”參數(shù)明確告訴MySQL，需要對某個（db_name）數(shù)據(jù)庫記錄binlog，如果有了“binlog-do-db=db_name”參數(shù)的顯式指定，MySQL 會忽略針對其他數(shù)據(jù)庫執(zhí)行的sql query，而僅僅記錄針對指定數(shù)據(jù)庫執(zhí)行的sql query。

3、“binlog-ignore-db=db_name”與“binlog-do-db=db_name”完全相反，它顯式指定忽略某個（db_name）數(shù)據(jù)庫的binlog 記錄，當(dāng)指定了這個參數(shù)之后，MySQL 會記錄指定數(shù)據(jù)庫以外所有的數(shù)據(jù)庫的binlog。

4、binlog-cache-size ：一個事務(wù)，在沒有提交（uncommitted）的時候，產(chǎn)生的日志，記錄到Cache中；等到事務(wù)提交（committed）的時候，則把日志持久化到磁盤。

十、查看二進(jìn)制日志：

1、查看binlog日志文件列表：

mysql> show binary logs;

2、查看當(dāng)前使用的二進(jìn)制文件及日志文件中事件當(dāng)前位置：

mysql> show master status;

Binlog_Do_DB：顯示對那個數(shù)據(jù)庫進(jìn)行增、刪、改操作

3、刪除所有的二進(jìn)制日志：

mysql>reset master;

restet master：清空index文件中列出的所有二進(jìn)制日志，并創(chuàng)建一個新的二進(jìn)制日志文件

4、重新開始一個新的日志文件：

使用flush logs生成新的二進(jìn)制日志文件，用以保存之后的數(shù)據(jù)庫操作語句的記錄。

mysql> flush logs; 用于生成新的binlog日志文件

5、查看binlog(二進(jìn)制日志文件)內(nèi)容命令語法：

查看二進(jìn)制日志文件語法格式：mysqlbinlog [options] log_file …

mysqlbinlog選項：

-v：顯示簡化的日志內(nèi)容

-vv：顯示詳細(xì)的日志內(nèi)容

總結(jié)

以上是生活随笔為你收集整理的MySQL数据库的mysqldump完全备份、binlog的增量备份与还原的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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