mysql數據庫四

備份和恢復

1 .? 備份注意要點 ?

能容忍最多丟失多少數據 ?

恢復數據需要在多長時間內完成 ?

需要恢復哪些數據 ?

2 .? 還原要點 ?

做還原測試，用于測試備份的可用性 ?

還原演練

3 .備份類型

完全備份作為主要備份定時如每周備份一次，將數據全部備份，增量備份和差異備份以及部分備份作為輔助備份，每天備份更改后的數據，來輔助完全備份在需要的時候還原數據。

完全備份+部分備份

完全備份+增量備份

完全備份+差異備份

 

完全備份：整個數據庫的數據以及數據庫的自定義的參數 全部都備份

部分備份：只備份數據子集，如部分庫或表

增量備份：僅備份最近一次完全備份后的一天或者自定義的時常，到下一個時間段再次備份此時間點到上一次增量備份之間的變化的內容，以后一次類推直到再次到達完全備份的時間段。備份較快，還原復雜

差異備份：僅備份最近一次完全備份以來變化的數據，備份較慢，還原簡單（如星期3.則備份星期3到最近一次完全備份的變化的數據）

4 . 備份的方式：

冷備：讀寫操作均不可進行? （只有在設備停機時才使用）?

溫備：讀操作可執行；但寫操作不可執行 ?

熱備：讀寫操作均可執行

MyISAM：溫備，不支持熱備

InnoDB: 都支持

物理和邏輯備份 ；

物理備份：直接復制數據文件進行備份，與存儲引擎有關，占用較多的空間， 速度快 ?

邏輯備份：從數據庫中“導出”數據另存而進行的備份，與存儲引擎無關， 占用空間少，速度慢，可能丟失精度

5 .?備份時需要考慮的因素

溫備的持鎖多久 ；（溫備時寫操作被限制，如果時間過長影響用戶的使用）

備份產生的負載? （備份時會造成設備負載過大，備份前查看機器的負載狀況以免發生宕機）

備份過程的時長 ：（如果長時間做一次備份造成數據過大，會使備份時間過長）

恢復過程的時長 ? （同上，所以要考慮備份的時常，既多久備份一次）

6 .設計備份方案 ?

數據集：完全+增量 ?

備份手段：物理，邏輯

7 . 備份工具

mysqldump：邏輯備份工具，適用所有存儲引擎，溫備；支持完全或部分備 份；對InnoDB存儲引擎支持熱備 ?? （較為常用的備份方式）

cp, tar等復制歸檔工具：物理備份工具，適用所有存儲引擎；只支持冷備（在設備停止服務時）； 完全和部分備份

mysqlhotcopy：幾乎冷備；僅適用于MyISAM存儲引擎

邏輯備份工具：mysqldump, mydumper, phpMyAdmin

mysqldump工具：客戶端命令，通過mysql協議連接至mysqld服務器進行 備份

8 .?mysqldump常見選項

-A，? –all-databases 備份所有數據庫，含create database （數據庫的創建）

-B , –databases db_name… 指定備份的數據庫，包括create database語句（表的格式信息的創建）

-E, –events：備份相關的所有event scheduler ?

-R, –routines：備份所有存儲過程和存儲函數 ?

–triggers：備份表相關的觸發器，默認啟用,用–skip-triggers，不備份觸發器 ?

–master-data[=#]： 此選項須啟用二進制日志

1：所備份的數據之前加一條記錄為CHANGE MASTER TO語句，非注釋，不指定#， 默認為1 （此為主從備份時會用到）

2：記錄為注釋的CHANGE MASTER TO語句 此選項會自動關閉–lock-tables功能，自動打開–lock-all-tables功能（除非開啟-single-transaction）

–compact 去掉注釋，適合調試，生產不使用

-d, –no-data 只備份表結構

-t, –no-create-info 只備份數據,不備份create table

-n,–no-create-db 不備份create database，可被-A或-B覆蓋

–flush-privileges 備份mysql或相關時需要使用

-f, –force 忽略SQL錯誤，繼續執行

–hex-blob 使用十六進制符號轉儲二進制列（例如，“abc”變為0x616263），受影響的數據類 型包括BINARY， VARBINARY，BLOB，BIT

-q, –quick 不緩存查詢，直接輸出，加快備份速度

-F, –flush-logs ：備份前滾動日志，鎖定表完成后，執行flush logs命令,生成新的二進制日志文 件，配合-A時，會導致刷新多次數據庫，在同一時刻執行轉儲和日志刷新，則應同時使用-flush-logs和-x，–master-data或-single-transaction,此時只刷新一次 建議：和-x，–master-data或 –single-transaction一起使用 ?

（使用-F 備份時會將原有的日志鎖定不在記錄，生成新的二進制日志文件來記錄新的二進制日志）

InnoDB建議備份策略 mysqldump –uroot –A –F –E –R –single-transaction –master-data=1 -flush-privileges –triggers –hex-blob >xxxx.sql ?

MyISAM建議備份策略 mysqldump –uroot –A –F –E –R –x –master-data=1 –flush-privileges -triggers –hex-blob >xxxxxx.sql
具體備份還原實例：

1 . 冷備份：

首先將服務停了：systemctl stop mariadb

tar Jcvf /data/all.tar.xz /var/lib/mysql/ (用打包壓縮的方式將數據庫目錄下的文件全部拷走)

rm -rf /var/lib/mysql/* （將數據庫的文件全部都刪掉）

tar xvf /data/all.tar.xz -C /var/lib/mysql/（將備份的文件解壓縮到原來的目錄下就實現了數據的還原）

cd /var/lib/mysql/（進入到原來的數據庫文件夾下發現以一個文件夾的方式解壓到此了）

mv /var/lib/mysql/var/lib/mysql/* /var/lib/mysql/（將文件移動到/var/lib/mysql/文件夾下就實現了還原）

重新啟動服務查看數據：systemctl restart mariadb;

 

2 . 使用mysqldump來進行備份和還原；（以下的mysqldump命令都省略了mysqldump -uroot? -p密碼）

使用-B對單個表進行備份和還原操作：

mysqldump -B mysql > /data/mysql.sql? （使用—B 備份表的結構，即使刪除此表也能還原，只對單個表進行備份）

rm -rf mysql ?? （將mysql表刪除然后實現還原）

mysql < /data/mysql.sql? (不需要進入到數據庫命令行直接執行此命令就還原數據了)

 

使用-A對整個數據庫進行備份和還原：

mysqldump -A > /data/all.sql ? （全部備份數據庫到/data/目錄下）

rm -rf /var/lib/mysql/* （模擬刪除數據庫的所有數據）

systemctl restart mariadb? (如果mysql命令無法使用就需要重啟服務才可以)

mysql < /data/all.sql ? ? （還原全部數據；不要再數據庫里進行操作，再Linux界面就可以）

 

連備份帶壓縮：

mysqldump -A | gzip > /data/all.sql.gz ? （將數據庫的全部文件備份帶壓縮）

rm -rf /var/lib/mysql/* ? （再次模擬刪除數據庫的所有數據）

gzip -d /data/all.sql.gz? .（解壓數據庫文件壓縮包到此地）

systemctl restart mariadb? （重啟服務）

mysql < /data/all.sql（還原數據庫的所有數據,不進入mysql數據庫）

 

分庫備份：（將數據庫里的每個庫分開備份，用一條指令實現）

1 .用for循環來實現

for i in `mysql -e ‘show databases’ | grep -iEv ‘database|schema‘`;do mysqldump -B $i | gzip > /data/$i.sql.gz;done

（database和schema代表從show databases;里排除的選項；schema為infor和perform…..）

2 . 用一條sed命令來實現：
直接生成腳本：

mysql -e ‘show databases’ | grep -ivE ‘database|schema’ | sed -r ‘s/(.*)/mysqldump -B \1 |gzip >/data/\1.sql.gz/‘> /data/back.sh

（mysqldump -B \1 |gzip >/data/\1.sql.gz 此為sed里整個要替換的內容）

backup.sh的腳本生成后加上#！機制走一遍腳本即可。

 

 

實驗：數據庫數據文件損壞，如何還原最新狀態？

思路：使用全備份文件和增量文件來恢復數據庫

1 . 首先開啟兩個主機二進制日志；然后將數據全備份：

mysqldump -A -F –single-transaction –master-data=2 > /backup/fullbak_`date +%F`.sql

現在模擬對數據庫進行修改以便后面測試

2? . 將備份的全數據和二進制文件復制到另一臺機器上

scp /backup/fullbak_2018-06-14.sql 192.168.60.20:/（復制全備份文件）

查看二進制文件到底要復制那些：（vim /backup/fullbak_2018-06-14.sql ）

(‘mariadb-bin.000002’, MASTER_LOG_POS=245; 此處的意思是全備份前的二進制日志，需要復制其以后的二進制文件，上圖中的文件已經備份到全備份里了，他后面的文件為更改的增量文件)

scp /var/lib/mysql/mariadb-bin.000002 192.168.60.20:/ ? (將二進制文件復制到另一臺主機上)

復制完后將自己本機的數據庫文件模擬破壞：（rm -rf /var/lib/mysql/*）

3 . 在另一臺主機上進行還原

rm -rf /var/lib/mysql/* （刪除本機的數據庫文件，使其成為干凈的系統）

systemctl restart mariadb ? （重啟服務）

4 . 在本機的配置文件上添加：/etc/my.cnf

skip-networking (暫時將網絡關上防止其他用戶登陸或者修改數據庫的文件)

5 .?systemctl restart mariadb? （再次重啟服務）

6 . mysql < fullbak_2018-06-14.sql? （恢復剛才傳過來的全備份文件）

7 .mysqlbinlog –start-position=245 mariadb-bin.000002 >bin.sql (將二進制文件打開并導入到一個新文件里；其中–start-position=245是完全備份后修改的增量二進制文件)

8 .?mysql < bin.sql? （將剛才的增量文件再次導入到數據庫中就完成了）

9 . 將剛才在配置文件里加的防止其他用戶訪問的條件取消掉。

（以上操作都是在linux界面上的命令）

 

 

實驗：誤刪除數據庫的一張表，如何還原最新狀態

思路：將本機的數據庫全部刪除（前提是二進制文件與庫文件不在同一個磁盤上而且有全備份文件和二進制文件），然后將本機還原到全備份時的狀態，在打開二進制文件將刪除表的命令找到并刪掉，之后將此增量文件再次還原到本機上。

具體步驟：

1 .?mysqldump -A -F –single-transaction –master-data=2 > /backup/full.sql? （備份本機的全備份文件）

2 . 10：00前修改數據
insert students
3 10:00 刪除表
drop table students;

4 ?? 10:00-10:10 修改數據庫
insert teachers;

?還原操作

5 .? flush tables with read lock;? （進入數據庫，對所有表加上只讀鎖，其他用戶只能讀不能修改）

6 .查看全備份文件查看全備份時二進制文件處于什么狀態：vim /backup/full.sql

上圖中的方框里的就是全備份時的二進制日志的位置

再查看此時二進制的狀態：在數據庫中輸入：show master logs;

上圖中最后一行的766就是全備份時255到766加上只讀鎖時所做的增量。

mysqlbinlog –start-position=245 /var/lib/mysql/mariadb-bin.000003 > /backup/bin.sql? （將全備份到加只讀鎖時間段的增量二進制文件導出來）如果有多個二進制日志文件，需要依次追加到文件里。

7 .?vim bin.sql （修改導出的二進制文件將刪表的命令找到并刪掉）

8 .?rm -rf /var/lib/mysql/*(將數據庫的所有文件都刪除)

9 . ?systemctl restart mariadb? （重啟數據庫服務）

10 . ?mysql < /backup/full.sql? （導入全備份文件）

11 .?mysql < /backup/bin.sql ? （將修改后的增量二進制文件導入數據庫中）

12 . 將剛才添加的只讀鎖解掉：unlock tables (在數據庫里執行)

 

 

 

3 .使用xtrabackup 來進行備份還原? （官網：www.percona.com ）

1 簡介和參數設置；

percona提供的mysql數據庫備份工具，惟一開源的能夠對innodb和 xtradb數據庫進行熱備的工具

特點： ?

(1)備份還原過程快速、可靠 ?

(2)備份過程不會打斷正在執行的事務 ?

(3)能夠基于壓縮等功能節約磁盤空間和流量 ?

(4)自動實現備份檢驗 ?

(5)開源，免費

xtrabackup 是用來備份 InnoDB 表的，不能備份非 InnoDB 表

xtrabackup安裝：

yum install percona-xtrabackup ? ?? （下載安裝包后還需要開啟EPEL源，有個依賴包依賴與epel源）

最新版本下載安裝： https://www.percona.com/downloads/XtraBackup/LATEST/

選項說明

–user：該選項表示備份賬號 ? –password：該選項表示備份的密碼 ? –host：該選項表示備份數據庫的地址 ? –databases：該選項接受的參數為數據名，如果要指定多個數據庫，彼此間需要以空格隔開；如： “xtra_test dba_test”，同時，在指定某數據庫時，也可以只指定其中的某張表。如： “mydatabase.mytable”。該選項對innodb引擎表無效，還是會備份所有innodb表 ? –defaults-file：該選項指定了從哪個文件讀取MySQL配置，必須放在命令行第一個選項的位置 ? –incremental：該選項表示創建一個增量備份，需要指定–incremental-basedir ? –incremental-basedir：該選項表示接受了一個字符串參數指定含有full backup的目錄為增量備份的base 目錄，與–incremental同時使用 ? –incremental-dir：該選項表示增量備份的目錄 ? –include=name：指定表名，格式：databasename.tablename

–apply-log：一般情況下,在備份完成后，數據尚且不能用于恢復操作，因為備份的數 據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此 時數據文件仍處理不一致狀態。此選項作用是通過回滾未提交的事務及同步已經提交的 事務至數據文件使數據文件處于一致性狀態 ? –use-memory：該選項表示和–apply-log選項一起使用，prepare 備份的時候， xtrabackup做crash recovery分配的內存大小，單位字節。也可(1MB,1M,1G,1GB)， 推薦1G ? –defaults-file：該選項指定了從哪個文件讀取MySQL配置，必須放在命令行第一個選 項的位置 ? –export：表示開啟可導出單獨的表之后再導入其他Mysql中 ? –redo-only：這個選項在prepare base full backup，往其中merge增量備份時候使 用

還原選項及說明

還原注意事項： ? 1.datadir目錄必須為空。除非指定innobackupex –force-non-empty-directorires選項指 定，否則–copy-backup選項不會覆蓋 ? 2.在restore之前,必須shutdown MySQL實例，你不能將一個運行中的實例restore到 datadir目錄中 ? 3.由于文件屬性會被保留，大部分情況下你需要在啟動實例之前將文件的屬主改為 mysql，這些文件將屬于創建備份的用戶 chown -R mysql:mysql /data/mysql 以上需要在用戶調用innobackupex之前完成 –force-non-empty-directories：指定該參數時候，使得innobackupex –copy-back或-move-back選項轉移文件到非空目錄，已存在的文件不會被覆蓋。如果–copy-back和-move-back文件需要從備份目錄拷貝一個在datadir已經存在的文件，會報錯失敗

 

具體實驗：使用xtrabackup 來進行全備份及還原：（/var/lib/mysql/數據庫的默認路徑）

在一臺主機上作完全備份：

1 . 安裝包? ：yum install percona-xtrabackup? （需要配置epel源進行安裝；兩臺主機都需要安裝）

2 . mkdir /backup/ ?? (新建一個空文件夾)

3 . innobackupex –-user=root /backups （開始備份）

4 .?scp -r /backup/2018-02-23_11-55-57/ 目標主機:/data/ （將備份的數據復制到另一臺主機上）

在另一臺主機上還原：

5? .首先將數據庫服務停用

6 . rm -rf /var/lib/mysql/*? （將自己的數據庫目錄清空）

7 . ?innobackupex –apply-log /data/2018-06-13_22-37-24/ （整理復制過來的全備份文件）

8 .?innobackupex –copy-back /data/2018-06-13_22-37-24/? （將整理好的備份文件復制到自己的數據庫下）

9 .?ll /var/lib/mysql/（查看自己數據庫下的文件是否有，查看權限）

10 .chown -R? mysql: /var/lib/mysql/（將數據庫文件的所有者和所屬組更改為mysql所有）

11 .?systemctl start mariadb ? （最后重新啟動服務查看數據庫的文件是否還原了）

小結：注意各主機的selinux，getenforce以及防火墻；iptables -F ;以上操作都在linux界面，不是咋數據庫中。在兩個主機的配置文件里都要添加：log_bin(啟用二進制日志功能)；臨時開啟：set log_bing=on

show variables like ‘log_bin’;（查看二進制日志此時的狀態是否開啟）

 

實驗 ：使用Xtrabackup完全，增量備份及還原

前提實驗環境：selinux? iptables? 開啟二進制日志記錄功能

1 .innobackupex /backup? （完全備份數據）

2 .?mkdir /backup/inc{1,2}? （新建兩個增量備份的文件夾）

對數據庫進行增刪改的操作，以便后面增量備份看出效果

3 .?innobackupex –incremental /backup/inc1 –incremental-basedir=/backups/2018-02-23_14-21-42

（在全備份的基礎上進行增量備份）

–incrementalbasedir=/backups/2018-02-23_14-21-42 （全備份文件的路徑）

/backups/inc1 （第一次增量備份的路徑）

再次修改數據以便在第二次增量備份時看出效果

4 .?innobackupex –incremental /backup/inc2 –incremental-basedir=/backup/inc1/2018-06-13_23-34-00? （在第一次增量備份的基礎上進行再次增量備份）

–incremental-basedir=/backup/inc1/2018-06-13_23-34-00（第一次增量備份的路徑）

5 .?scp -r /backup/* 192.168.27.17:/data/ （將所有的備份數據復制到另一臺機器上）

在目標恢復的主機上進行還原操作：

6 .?不啟動mariadb

7 .?rm -rf /var/lib/mysql/* （刪除此數據庫里的所有內容）

8 .?innobackupex –apply-log –redo-only /data/2018-06-13_23-15-05/（整理完全備份的數據）

9 .innobackupex –apply-log –redo-only /data/2018-06-13_23-15-05/ –-incremental-dir=/data/inc1/2018-06-13_23-34-00/? （基于完全備份對第一次的增量備份進行整理）

10 .?innobackupex –apply-log –redo-only /data/2018-06-13_23-15-05/ –incremental-dir=/data/inc2/2018-06-13_23-43-09/ （基于完全備份對第二次的增量備份進行整理）

11 .innobackupex –copy-back /data/2018-06-13_23-15-05/（將整理好的數據庫的文件復制到本機的數據庫下）

12 .?chown -R mysql: /var/lib/mysql/（修改復制過來的數據庫文件的所有者）

13 .?systemctl start mariadb（啟動數據庫服務）

 

 

實驗：? 使用Xtrabackup進行單表導出和導入 (mariadb10.2以上的版本支持)

二進制日志要開啟；

1? .?innobackupex –include=’hellodb.students’ /backup/（將數據庫中的一張表內容備份出來）

2 . ?mysql -e ‘show create table hellodb.students’ > /backup/student.sql? （將上述表的結構導出來）

3 .?mysql -e ‘drop table hellodb.students’ ? ?? (模擬刪除此表)

4 .?innobackupex –apply-log –export /backup/2018-06-14_02-04-17/ （將備份表的內容進行整理）

5 .?mysql hellodb </backup/ student.sql （將剛才導出的表結構重新導進去）

CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum(‘F’,’M’) NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

6 .?mysql -e ‘alter table hellodb.students discard tablespace’? （將原有表的數據刪除）

7 . cp ?/backup/2018-06-14_02-04-17/hellodb/students.cfg students.exp students.ibd /var/lib/mysql/hellodb/ ? (將備份的表的內容三個文件復制到數據庫的路徑下)

8 .? chown -R mysql.mysql /var/lib/mysql/hellodb/ （將剛才復制過去的文件更改所有者，和所屬組）

9 .?mysql -e ‘alter table hellodb.students import tablespace’? (將復制過去的數據導入到數據庫中)

小結：log_bin；innodb_file_per_table? （讓每個表的文件都獨立分開）在配置文件里添加/etc/my.cnf

 

 

 

主從配置過程： 參看：https://mariadb.com/kb/en/library/setting-up-replication/ https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

實驗：建立新的主從復制

在主服務器上：

1? .安裝mariadb軟件：yum install mariadb-server? ；啟動服務：systemctl start mariadb

2 .修改主服務器的配置文件：

vim /etc/my.cnf

[mysqld]

log_bin ? ? ? ? ? ? ? ? ?? （開啟二進制日志的記錄）

server_id=1 ? ? ? ? ?? （為當前主服務器設置一個全局惟的ID號 ）

systemctl restart mariadb （重啟服務）

3? .? 創建有復制權限的從用戶的賬號 ：

grant replication slave on *.* to lv@192.168.60.20 identified by ‘centos’;? (授權從主機復制的權限)

從服務器上的實現

4 .? 編輯配置文件

vim /etc/my.cnf

[mysqld]

server_id=2

systemctl restart mariadb ?? （重啟服務）

5 ? 進入數據庫：>create user ‘lv’@’192.168.60.%’ identified by ‘centos’;（本地要新建一個同步主數據的賬號）

CHANGE MASTER TO MASTER_HOST=’192.168.60.4′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=245;

MASTER_HOST=192.168.60.4 （主服務器的IP地址）
MASTER_USER=lv （授權從服務器的用戶名）
MASTER_PASSWORD=’centos’（授權從服務器的密碼）
MASTER_LOG_FILE=’mariadb-bin.000001’（主服務器的二進制文件名）
MASTER_LOG_POS=245;（從主服務器二進制文件的那個節點開始同步）
最后兩項可以在主服務器上查詢：show master logs;

6? show slave status\G;（查看從服務器的狀態）

7 .?start slave;（最后一步開啟復制）

到此完成了，將主服務器的文件同步到從服務器上了，主服務器更改，從服務器也可以即使同步了，如果從服務器重啟了，主服務器此時更新內容，待到重服務器起來之后，數據會自動同步的。

 

實驗：已有舊的主，建立新從

在主節點實現
1 修改配置文件
vim /etc/my.cnf
server_id=1
log_bin
innodb_file_per_table

systemctl restart mariadb? （重啟服務）

2? mysql>grant replication slave on *.* to lv@’192.168.30.20′ identified by ‘centos’;

3? mysqldump -A -F –single-transaction –master-data=1 > /backup/all.sql ? (將主服務器備份)
scp /backup/all.sql? 192.168.60.20：/backup? （將備份的數據傳送到從服務器上）

在從節點實現

4? vim /etc/my.cnf
server_id=2
systemctl restart mariadb

5 vim /backup/all.sql
CHANGE MASTER TO MASTER_HOST=’192.168.30.17′,
MASTER_USER=’repluser’,MASTER_PASSWORD=’centos’,MASTER_PORT=3306,
MASTER_LOG_FILE=’master-bin.000002′, MASTER_LOG_POS=245;

…..

(MASTER_LOG_FILE=’master-bin.000002′, MASTER_LOG_POS=245此位置為 全備份時的主服務器所在的節點位置? ；在主服務器上用show master logs 可以查看現在的狀態值，為增量二進制日志文件；我們要取的是全備份的位置點到此時的位置點之間的數據，所以不要改變全備份時留下的最后位置，讓他同步)

6 mysql < /backup/all.sql ? （還原數據庫文件）

7 mysql > start slave ? ? ? ?? （開啟自動同步服務）? stop slave :暫停同步狀態。
mysql> show slave status\G ? ? （查看同步狀態）