項目實戰6—Mysql實現企業級日誌管理、備份與恢複實戰，mysql企業級

項目實戰6—Mysql實現企業級日誌管理、備份與恢複實戰，mysql企業級

 

Mysql實現企業級日誌管理、備份與恢複實戰

　　環境背景：隨著業務的發展，公司業務和規模不斷擴大，網站積累了大量的使用者資訊和資料，對於一家互連網公司來說，使用者和業務資料是根基。一旦公司的資料錯亂或者丟失，對於互連網公司而言就等於說是滅頂之災，為防止系統出現操作失誤或系統故障導致資料丟失，公司要求加強使用者資料的可靠性，要求全面加強資料層面備份，並能在故障發生時第一時間恢複。

總架構圖，詳見 http://www.cnblogs.com/along21/p/8000812.html

Mysql備份方案：

① mysqldump+binlog:

先完全備份，再通過備份二進位日誌實現增量備份

② xtrabackup:

對InnoDB：熱備，支援完全備份和增量備份

對MyISAM：溫備，只支援完全備份

③ lvm快照+binlog：

幾乎熱備，物理備份

 

實戰一：mysqldump+binlog 實現備份與恢複1、準備備份的目錄，開啟二進位日誌

mkdir /backup

chown -R mysql.mysql /backup/ 把備份的目錄所屬人所屬組改為mysql

vim /etc/my.cnf

log-bin = /var/lib/mysql/bin-log 開啟二進位日誌，並制定路徑

 

2、準備要備份的資料和表

類比日常的資料庫操作

MariaDB [(none)]> create database along; 建立一個along的表

MariaDB [along]> create table home(id int not null,name char(20)); 建立一個home表

MariaDB [along]> show master status; 查詢二進位檔案，編號是所處的文字

 

3、進行完整備份

可以備份所有庫

mysqldump --all-databases --flush-log > /backup/mysql-all-backup-`date +%F-%T`.sql

也可以備份單獨指定的庫

mysqldump --database along --flush-log > /backup/mysql-along-backup-`date +%F-%T`.sql

指令分析：

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：備份一個或多個庫

OPTIONS：

　　--lock-all-tables：鎖定所有表

　　--lock-tables：鎖定備份的表

　　--single-transaction：啟動一個大的單一事務實現備份

　　--compress：壓縮傳輸

　　--events：備份指定庫的事件調度器

　　--routines：備份預存程序和儲存函數

　　--triggers：備份觸發器

　　--master-data={0|1|2}

　　　　0：不記錄

　　　　1：記錄CHANGE MASTER TO語句；此語句未被注釋

　　　　2：記錄為備註陳述式

　　--flush-logs：鎖定表之後執行flush logs命令，產生一個新的二進位日誌

 

4、向表中插入資料

類比日常的正常操作

MariaDB [along]> insert into home values(1,'mayun');

MariaDB [along]> insert into home values(2,'mahuateng');

 

5、進行增量備份，備份二進位日誌

原理：需Crowdsourced Security Testing道要增量備份的起始，然後進行備份。

（1）查詢起始

① 通過資料庫命令查詢起始，推薦

MariaDB [along]> show master status; 　　查詢當前使用的二進位日誌

MariaDB [along]> show binlog events in 'bin-log.000014'; 　　查詢二進位日誌，裡邊有編號位置

② 也可以通過命令列查詢

cd /var/lib/mysql/

mysqlbinlog bin-log.000014

 

（2）進行增量備份

cd /var/lib/mysql/

mysqlbinlog --start-position=314 --stop-position=637 bin-log.000014 > /backup/mysql-along-backup-add-`date +%F-%T`.sql

注意：起始的編號一定要往前和往後推一個編號，例如：412是執行插入命令的，要從前一個編號314備份。

 

6、繼續插入資料，在沒備份的情況下刪除資料庫，類比誤操作

① 繼續日常的操作

MariaDB [along]> insert into home values(3,'wangjianlin');

② 誤刪除along資料庫，上一天還沒來得及備份

MariaDB [along]> drop database along;

這個時候穩住，不要慌，下面開始恢複

 

7、資料恢複

① 由於最後我們沒有備份就刪除了資料庫，所以我們首先需要保護最後的二進位日誌，查看刪除操作之前的position編號值

MariaDB [(none)]> show binlog events in 'bin-log.000014';

② 備份後來沒有來得及的操作

cd /var/lib/mysql/

mysqlbinlog --start-position=706 --stop-position=837 bin-log.000014 > /backup/mysql-along-backup-add-`date +%F-%T`.sql

 

8、匯入之前的所有備份

① 查看我們的備份目錄

② 按順序匯入所有備份

完全備份 ---> 增量備份

mysql -uroot -p < mysql-along-backup-2017-11-16-16\:45\:22.sql

mysql -uroot -p < mysql-along-backup-add-2017-11-16-17\:15\:25.sql

mysql -uroot -p < mysql-along-backup-add-2017-11-16-17\:27\:50.sql

 

9、查看資料庫及資料，恢複完成

 

實戰二：xtrabackup 實現備份和緩衝1、安裝xtrabackup

yum -y install xtrabackup

為了許可權管理，也可以建立最小許可權備份使用者，為了實驗方便，我就不設了

 

2、完全備份

① 完全備份

innobackupex --user=root /backup/ 完全備份，備份完會產生一個目錄，裡邊有全部的資料庫資料

若設定了許可權的使用者： innobackupex --user=bakupuser --password=bakuppass /backup/

注意：要給備份的目錄遞迴給mysql許可權

chown mysql.mysql /backup/2017-11-16_17-57-57/ -R 給產生的目錄加許可權

 

② 基於完全備份產生的目錄，也可以恢複資料

datadir=/backup/2017-11-16_17-57-57 把目錄指向備份的目錄

systemctl restart mariadb 重啟服務

③ 查看資料，沒有變化，資料一致

 

3、增量備份

① 添加資料，日常操作

MariaDB [along]> insert into home values(4,'dinglei'),(5,'liyanhong');

② 增量備份

innobackupex --user=root --incremental /backup/ --incremental-basedir=/backup/2017-11-16_17-57-57 基於/backup/2017-11-16_17-57-57 進行增量備份

產生了增量備份的目錄

 

4、資料恢複準備："重放"與"復原"

原理：一般情況下，在備份完成後，資料尚且不能用於恢複操作，因為備份的資料中可能會包含尚未提交的事務或已經提交但尚未同步至資料檔案中的事務

① 需要在每個備份(包括完全和各個增量備份)上，將已經提交的事務進行"重放"。"重放"之後，所有的備份資料將合并到完全備份上。

② 基於所有的備份將未提交的事務進行"復原"。

innobackupex --apply-log --redo-only /backup/2017-11-16_17-57-57/ 完全備份的資料恢複準備

innobackupex --apply-log --redo-only /backup/2017-11-16_20-14-05/ --incremental-dir=/backup/2017-11-16_20-37-40/ 增量備份的資料恢複準備

 

5、誤操作，恢複資料

mv /var/lib/mysql /var/lib/mysql.bak 類比誤刪除資料庫存放檔案

mkdir /var/lib/mysql

innobackupex --copy-back /backup/2017-11-16_20-14-05/ 資料恢複

chown mysql.mysql var/lib/mysql/ -R

cp -a /var/lib/mysql.bak/mysql.sock /var/lib/mysql 把通訊端檔案cp過來

systemctl start mariadb 重啟服務

 

6、查看資料庫及資料，資料完全一致

 

實驗三：lvm快照+binlog 實現資料備份與恢複

原理：LVM快照簡單來說就是將所快照來源資料分割一個時間點所有檔案的中繼資料進行儲存， 如果源檔案沒有改變， 那麼訪問快照卷的相應檔案則直接指向來源資料分割的源檔案， 如果源檔案發生改變， 則快照卷中與之對應的檔案不會發生改變。 快照卷主要用於輔助備份檔案。

1、準備工作

由於我們實驗環境下的資料庫的資料庫目錄不是在lvm上的，首先，我們要搭建lvm環境，然後把資料庫遷移到lvm上面，在進行實驗：

（1）添加硬碟，並劃分磁碟類型為lvm 類型

echo '- - -' > /sys/class/scsi_host/host2/scan 虛擬機器中實現磁碟添加，不重啟同步新磁碟

fdisk /dev/sdg

（2）partx -a /dev/sdb 或 partprobe 使核心識別新磁碟

partprobe：在centos5、7 中都能正常使用；centos6 版本中有BUG

partx -a /dev/sdb：都可正常使用

（3）建立邏輯卷

① pvcreate /dev/sdg1 添加物理卷

② vgcreate myvg /dev/sdg1 添加卷組

③ lvcreate -n mydata -L 50G myvg 添加邏輯卷

④ mkfs.ext4 /dev/mapper/myvg-mydata 格式化邏輯卷，檔案系統格式化

 

2、掛載邏輯卷

① mkdir /lvm_data  建立lvm掛載的目錄 

② mount /dev/mapper/myvg-mydata /lvm_data 掛載

我們最好把它寫到 /etc/fstab 中，如：

vim /etc/fstab

/dev/mapper/myvg-mydata /lvm_data/ ext4 defaults 0 0

③ 然後將該掛載的目錄的所有者和所屬組改成mysql

chown -R mysql.mysql /lvm_data

 

3、修改Mysql 配置

（1）修改Mysql 配置，使得資料檔案在邏輯卷上 datadir=/lvm_data

vim /etc/my.cnf

[mysqld]

datadir=/lvm_data

socket=/var/lib/mysql/mysql.sock

 

（2）將資料庫檔案拷貝到 /lvm_data 目錄下：

cp -a /var/lib/mysql/* / lvm_data

 

（3）service mysqld restart 啟動Mysql 服務

4、建立快照

（1）在我們建立快照之前，需要我們鎖表，將資料庫中的表鎖定，讓外界無法讀取：

MariaDB [(none)]> flush tables with read lock;

（2）建立快照：Logical volume "mydata-snap" created

lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata

（3）解除表的鎖定：

MariaDB [(none)]> unlock tables;

5、打包資料，資料備份

（1）打包資料：

tar czvf /tmp/mysqlback.tar.gz /lvm_data

（2）在我們使用完快照之後，需要將他們進行卸載，刪除，命令如下所示：

umount /lvm_snap/

lvremove myvg /dev/myvg/mydata-snap

6、類比刪庫，資料恢複

（1）我們把/lvm_data下的資料全部刪掉，類比我們的資料庫全部丟失

rm -rf /lvm_data/*

（2）資料恢複

mv /tmp/mysqlback.tar.gz /lvm_data 把壓縮的包cp過來

tar xvf /tmp/mysqlback.tar.gz ./ 將資料進行解壓，恢複資料

注意：有一點要注意的是，在資料拷貝到資料庫檔案目錄下後，我們一定要看看檔案的許可權是否是mysql使用者的，如若不是，需要我們手動更改。