MySQL系列之十二 備份與恢復(fù)

用法：
           shell> mysqldump [options] db_name [tbl_name ...]
           shell> mysqldump [options] --databases db_name ...
           shell> mysqldump [options] --all-databases

選項(xiàng)：
	-A：備份所有庫
	-B db_name1,[db_name2,...]：備份指定庫
	-E：備份相關(guān)的所有event scheduler
	-R：備份所有存儲過程和存儲函數(shù)
	--triggers：備份表相關(guān)觸發(fā)器，默認(rèn)啟用,用--skip-triggers，不備份觸發(fā)器
	--master-data={1|2}：
		 1：所備份的數(shù)據(jù)之前加一條記錄為CHANGE MASTER TO語句，非注釋，不指定默認(rèn)為1
		 2：記錄為注釋的CHANGE MASTER TO語句，注意：此選項(xiàng)會自動關(guān)閉--lock-tables功能，自動打開--lock-all-tables功能（除非開啟--single-transaction）
	-F：備份前滾動日志，鎖定表完成后，執(zhí)行flush logs命令,生成新的二進(jìn)制日志文件，配合-A時(shí)，會導(dǎo)致刷新多次數(shù)據(jù)庫，在同一時(shí)刻執(zhí)行轉(zhuǎn)儲和日志刷新，則應(yīng)同時(shí)使用--flush-logs和-x，--master-data或-single-transaction,此時(shí)只刷新一次；建議：和-x，--master-data或 --single-transaction一起使用
	--compact 去掉注釋，適合調(diào)試，生產(chǎn)不使用
	-d：只備份表結(jié)構(gòu)
	-t：只備份數(shù)據(jù)，不備份create table
	-n：不備份create database，可被-A或-B覆蓋
	--flush-privileges：備份前刷新授權(quán)表，備份mysql庫或相關(guān)時(shí)需要使用
	-f：忽略SQL錯誤，繼續(xù)執(zhí)行
	--hex-blob：使用十六進(jìn)制符號轉(zhuǎn)儲二進(jìn)制列（例如，“abc”變?yōu)?x616263），受影響的數(shù)據(jù)類型包括BINARY， VARBINARY，BLOB，BIT
	-q：不緩存查詢，直接輸出，加快備份速度

InnoDB建議備份策略：
	mysqldump –uroot –A –F –E –R  --single-transaction --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM建議備份策略：
	mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

用法：
	innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

選項(xiàng)：
    --user：該選項(xiàng)表示備份賬號
    --password：該選項(xiàng)表示備份的密碼
    --host：該選項(xiàng)表示備份數(shù)據(jù)庫的地址
    --databases：該選項(xiàng)接受的參數(shù)為數(shù)據(jù)名，如果要指定多個(gè)數(shù)據(jù)庫，彼此間需要以空格隔開；如："xtra_test dba_test"，同時(shí)，在指定某數(shù)據(jù)庫時(shí)，也可以只指定其中的某張表。如："mydatabase.mytable"。該選項(xiàng)對innodb引擎表無效，還是會備份所有innodb表
    --defaults-file：該選項(xiàng)指定從哪個(gè)文件讀取MySQL配置，必須放在命令行第一個(gè)選項(xiàng)位置
    --incremental：該選項(xiàng)表示創(chuàng)建一個(gè)增量備份，需要指定--incremental-basedir
    --incremental-basedir：該選項(xiàng)指定為前一次全備份或增量備份的目錄，與--incremental同時(shí)使用
    --incremental-dir：該選項(xiàng)表示還原時(shí)增量備份的目錄
    --include=name：指定表名，格式：databasename.tablename
    --apply-log：一般情況下,在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因?yàn)閭浞莸臄?shù)據(jù)中可能會包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù)文件仍處理不一致狀態(tài)。此選項(xiàng)作用是通過回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件使數(shù)據(jù)文件處于一致性狀態(tài)
	--use-memory：該選項(xiàng)表示和--apply-log選項(xiàng)一起使用，prepare 備份的時(shí)候，xtrabackup做crash recovery分配的內(nèi)存大小，單位字節(jié)。也可(1MB,1M,1G,1GB)，推薦1G
	--export：表示開啟可導(dǎo)出單獨(dú)的表之后再導(dǎo)入其他Mysql中
	--redo-only：此選項(xiàng)在prepare base full backup，往其中merge增量備份時(shí)候使用
	--copy-back：做數(shù)據(jù)恢復(fù)時(shí)將備份數(shù)據(jù)文件拷貝到MySQL服務(wù)器的datadir
	--move-back：這個(gè)選項(xiàng)與--copy-back相似，唯一的區(qū)別是它不拷貝文件，而是移動文件到目的地。這個(gè)選項(xiàng)移除backup文件，用時(shí)候必須小心。使用場景：沒有足夠的磁盤空間同事保留數(shù)據(jù)文件和Backup副本

~]# mkdir /backup
~]# systemctl stop mariadb #停止服務(wù)
~]# tar Jcf /backup/mariadb_all.tar.xz /var/lib/mysql/ #打包壓縮
backup]# systemctl start mariadb

~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf  #將損壞的庫刪除
~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz  #解壓打包的數(shù)據(jù)庫文件
backup]# cp -av var/lib/mysql/ /var/lib/ #還原
backup]# systemctl start mariadb #啟動服務(wù)，恢復(fù)成功

~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf  #將損壞的庫刪除
~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz  #解壓打包的數(shù)據(jù)庫文件
backup]# cp -av var/lib/mysql/ /var/lib/ #還原
backup]# systemctl start mariadb #啟動服務(wù)，恢復(fù)成功

準(zhǔn)備lvm環(huán)境：
~]# pvcreate /dev/sda5
~]# vgcreate vg0 /dev/sda5
~]# lvcreate -n lv_data -L 10G vg0
~]# lvcreate -n lv_binlog -L 10G vg0
~]# mkfs.xfs /dev/vg0/lv_data
~]# mkfs.xfs /dev/vg0/lv_binlog
~]# mkdir -pv /data/{mysqldb,binlog}  #創(chuàng)建數(shù)據(jù)目錄和二進(jìn)制日志存放目錄
~]# chown -R mysql:mysql /data/
~]# vim /etc/fstab
	UUID=4e3d726a-d420-4c1e-812b-da315012ba86 /data/mysqldb xfs defaults 0 0
	UUID=6dd98866-769f-4369-8738-291fbcc94ca1 /data/binlog xfs defaults 0 0 

配置數(shù)據(jù)庫，模擬生成大量數(shù)據(jù)：
~]# yum install mariadb-server -y
~]# vim /etc/my.cnf
    [mysqld]
    datadir = /data/mysqldb  #指定數(shù)據(jù)庫存放路徑
    log_bin = /data/binlog/mariadb-bin  #開啟二進(jìn)制日志記錄，并且存放到指定路徑
    innodb_file_per_table = ON  #開啟每個(gè)表單獨(dú)的表空間
~]# systemctl start mariadb
~]# mysql  #連接數(shù)據(jù)庫，這里省略了用戶名和密碼，以下都是如此
MariaDB [(none)]> CREATE DATABASE school;  #創(chuàng)建一個(gè)測試的庫
MariaDB [(none)]> use school
MariaDB [school]> CREATE TABLE testtb (id int auto_increment primary key,name char(30),age int default 20);  #創(chuàng)建一張數(shù)據(jù)表
MariaDB [school]> DELIMITER //  #修改語句結(jié)束符為“//”
MariaDB [school]> CREATE PROCEDURE pro_testtb()  #寫一個(gè)存儲過程，目的是生成十萬條記錄測試用
    -> BEGIN
    -> declare i int;
    -> set i = 1;
    -> while i  100000
    -> do INSERT INTO testtb(name,age) VALUES (CONCAT('testuser',i),i);
    -> SET i = i + 1;
    -> END while;
    -> END//
MariaDB [school]> DELIMITER ;  #記得將語句結(jié)束符再改回來
MariaDB [school]> CALL pro_testtb;  #調(diào)用存儲過程來
MariaDB [school]> SELECT COUNT(*) FROM testtb;  #查看一下表中有十萬條記錄
+----------+
| COUNT(*) |
+----------+
|    99999 |
+----------+

開始備份：
MariaDB [school]> FLUSH TABLES WITH READ LOCK;  #備份前切記鎖表，防止用戶繼續(xù)寫入
MariaDB [school]> FLUSH LOGS;  #滾動一下二進(jìn)制日志
MariaDB [school]> SHOW MASTER LOGS;  #查看二進(jìn)制日志的位置
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     30334 |
| mariadb-bin.000002 |   1038814 |
| mariadb-bin.000003 |  29178309 |
| mariadb-bin.000004 |       528 |
| mariadb-bin.000005 |       245 |  #將此出記錄下來，我們后邊需要用到
+--------------------+-----------+
~]# lvcreate -L 5G -n lv_mysql_snap -s -p r /dev/vg0/lv_data  #需要再開一個(gè)終端創(chuàng)建快照，不要退出mysql終端
MariaDB [school]> UNLOCK TABLES;  #創(chuàng)建快照后第一時(shí)間解鎖，小心用戶投訴
~]# mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt/  #將快照掛載到/mnt
~]# cp -av /mnt/ /backup  #拷貝數(shù)據(jù)到備份目錄
~]# umount /mnt/
~]# lvremove /dev/vg0/lv_mysql_snap  #拷貝完成后即時(shí)刪除快照，影響服務(wù)器性能，到此完全備份完成~

再加點(diǎn)數(shù)據(jù)：
MariaDB [school]> CALL pro_testtb;  #讓我們模擬再來插入十萬條數(shù)據(jù)
MariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| COUNT(*) |
+----------+
|   199998 |  #現(xiàn)在是二十萬條記錄數(shù)據(jù)了
+----------+

模擬數(shù)據(jù)庫損壞：
~]# rm -rf /data/mysqldb/*  #服務(wù)器崩潰，不多BB，直接清空庫
~]# systemctl stop mariadb  #停服務(wù)

開始還原：
~]# cp -av /backup/* /data/mysqldb/  #將備份的文件cp到對應(yīng)的庫目錄下
在/etc/my.cnf的[mysqld]下加上skip_networking，禁止用戶使用數(shù)據(jù)庫，防止恢復(fù)過程中的數(shù)據(jù)寫入
~]# systemctl start mariadb  #啟動服務(wù)
~]# ls -1 /data/binlog/  #查看二進(jìn)制日記的文件個(gè)數(shù)
    mariadb-bin.000001
    mariadb-bin.000002
    mariadb-bin.000003
    mariadb-bin.000004
    mariadb-bin.000005
    mariadb-bin.000006
    mariadb-bin.index
~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000005 > binlog.sql  #到出完全備份時(shí)間點(diǎn)以后的數(shù)據(jù)
~]# mysqlbinlog /data/binlog/mariadb-bin.000006 >> binlog.sql  #將之后的所有數(shù)據(jù)都追加到同一sql文件中
~]# mysql  binlog.sql  #利用二進(jìn)制日志從我們之前完全備份的點(diǎn)開始增量還原
~]# mysql -e 'SELECT COUNT(*) FROM school.testtb'  #查看一下，二十萬條記錄都在，nice
+----------+
| COUNT(*) |
+----------+
|   199998 |
+----------+
到/etc/my.cnf的[mysqld]下刪除skip_networking，重啟服務(wù)，到此還原完成~

~]# mysqldump -A -F -E -R --single-transaction --master-data=2 --flush-privileges  > /backup/full-`date +%F-%T`.sql  #全庫完全備份

MariaDB [(none)]> CREATE DATABASE db1;  #創(chuàng)建一個(gè)庫
MariaDB [(none)]> CREATE DATABASE db2;  #再創(chuàng)建一個(gè)庫
MariaDB [school]> use school;
MariaDB [school]> DROP TABLE testtb;  #誤操作，將我們二十萬條記錄的表刪掉了
MariaDB [school]> CREATE TABLE students (id INT(4) AUTO_INCREMENT PRIMARY KEY,name CHAR(30),age TINYINT);  #后續(xù)又有用戶創(chuàng)建了其他的表
MariaDB [school]> INSERT INTO students(name,age) VALUES ('user1',20);  #并且還加入了數(shù)據(jù)

此時(shí)，我們發(fā)現(xiàn)了有一個(gè)表不見了，需要緊急恢復(fù)，開始吧
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;  #鎖表
MariaDB [(none)]> FLUSH LOGS;  #刷新滾動一次二進(jìn)制日志文件
MariaDB [(none)]> SHOW MASTER LOGS;  #查看當(dāng)前的日志狀態(tài)
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     30334 |
| mariadb-bin.000002 |   1038814 |
| mariadb-bin.000003 |  29178309 |
| mariadb-bin.000004 |       528 |
| mariadb-bin.000005 |  29177760 |
| mariadb-bin.000006 |  29177786 |
| mariadb-bin.000007 |       953 |
| mariadb-bin.000008 |       245 |
+--------------------+-----------+
~]# systemctl stop mariadb  #停止服務(wù)，準(zhǔn)備修復(fù)
~]# head -30 /backup/full-2018-06-14-05\:33\:47.sql |grep "CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000007', MASTER_LOG_POS=245;  #找到完全備份的日志點(diǎn)，在mariadb-bin.000007的245
~]# ls -1 /data/binlog/
mariadb-bin.000001
mariadb-bin.000002
mariadb-bin.000003
mariadb-bin.000004
mariadb-bin.000005
mariadb-bin.000006
mariadb-bin.000007
mariadb-bin.000008
mariadb-bin.index
~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000007 > /backup/binlog.sql #將完全備份之后的二進(jìn)制日志導(dǎo)出來
~]# mysqlbinlog /data/binlog/mariadb-bin.000008 >> /backup/binlog.sql
~]# vim /backup/binlog.sql  #修改導(dǎo)出的sql文件，把誤操作的SQL語句刪除
刪除"DROP TABLE `testtb` /* generated by server */"這行

導(dǎo)入備份：
~]# rm -rf /data/mysqldb/*  #先清空故障庫
~]# vim /etc/my.cnf  #編輯配置文件
	在[mysqld]加入skip_networking，防止用戶寫入數(shù)據(jù)
~]# systemctl start mariadb  #啟動服務(wù)
~]# mysql  /backup/full-2018-06-14-05\:33\:47.sql  #導(dǎo)入完全備份
~]# mysql  /backup/binlog.sql  #導(dǎo)入增量備份
MariaDB [(none)]> show databases;  #查看一下我們的數(shù)據(jù)是否成功恢復(fù)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |  #已恢復(fù)
| db2                |  #已恢復(fù)
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
MariaDB [(none)]> SELECT COUNT(*) FROM school.testtb;
+----------+
| COUNT(*) |
+----------+
|   199999 | #已恢復(fù)
+----------+
MariaDB [(none)]> SELECT * FROM school.students;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |   20 | #已恢復(fù)
+----+-------+------+
到現(xiàn)在為止，已經(jīng)完成恢復(fù)，把配置文件中的skip_networking刪除，重啟服務(wù)，大功告成~

~]# innobackupex --user=root /backup/  #這里省略了密碼

MariaDB [school]> CALL pro_testtb;  #增加一些數(shù)據(jù)
MariaDB [school]> SELECT COUNT(*) FROM testtb;  #現(xiàn)在有三十萬條記錄了
+----------+
| COUNT(*) |
+----------+
|   299998 |
+----------+
MariaDB [school]> INSERT INTO students VALUES (2,'user2',21);
MariaDB [school]> UPDATE students SET age=19 WHERE id=1;
MariaDB [school]> SELECT * FROM students;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |   19 |
|  2 | user2 |   21 |
+----+-------+------+

~]# mkdir /backup/inc{1,2}  #創(chuàng)建增量備份的目錄
~]# innobackupex --incremental /backup/inc1/ --incremental-basedir=/backup/2018-06-14_10-44-57/  #指定在完全備份的基礎(chǔ)上增量備份

MariaDB [(none)]> CREATE DATABASE db3; 
MariaDB [(none)]> DROP TABLE school.students;  #誤操作刪除了表
MariaDB [(none)]> use school
MariaDB [school]> CALL pro_testtb;  #后續(xù)又有數(shù)據(jù)產(chǎn)生
MariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| COUNT(*) |
+----------+
|   399997 |
+----------+
MariaDB [school]> SELECT * FROM students;  #到此出發(fā)現(xiàn)students表不見了，怎么辦？
ERROR 1146 (42S02): Table 'school.students' doesn't exist

~]# rm -rf /data/mysqldb/*  #還原前清空數(shù)據(jù)目錄
MariaDB [(none)]> show databases;  #此時(shí)數(shù)據(jù)庫已經(jīng)沒了
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

恢復(fù)完全備份和增量備份：
~]# systemctl stop mariadb  #停止服務(wù)
~]# innobackupex --apply-log --redo-only /backup/2018-06-14_10-44-57/  #整理完全備份的數(shù)據(jù)，因?yàn)樾枰Ａ魶]有做完的事務(wù)日志所以一定要記得加"--redo-only"選項(xiàng)
~]# innobackupex --apply-log --redo-only /backup/2018-06-14_10-44-57/ --incremental-dir=/backup/inc1/2018-06-14_10-52-05/  #在完全備份的基礎(chǔ)上將增量備份導(dǎo)入到一塊，這里是最新的增量備份，"--redo-only"選項(xiàng)可以不加，加上也可以，為了誤操作我就都加了
~]# ls /data/mysqldb/  #確認(rèn)一下數(shù)據(jù)庫目錄是否為空
~]# innobackupex --copy-back /backup/2018-06-14_10-44-57/  #導(dǎo)入備份數(shù)據(jù)
~]# chown -R mysql:mysql /data/mysqldb/  #記得修改數(shù)據(jù)的所屬組和所屬者
~]# vim my.cnf 加入skip_networking，防止此時(shí)用戶操作數(shù)據(jù)
~]# systemctl start mariadb  #啟動服務(wù)，此時(shí)已經(jīng)恢復(fù)到了最新的備份時(shí)的狀態(tài)了

依靠二進(jìn)制日志，恢復(fù)最新增量備份到now的數(shù)據(jù)：
~]# cat /backup/2018-06-14_10-44-57/xtrabackup_binlog_info  #查看一下備份時(shí)的二進(jìn)制日志記錄點(diǎn)
	mariadb-bin.000011      35740416
~]# ls -1 /data/binlog/  #看看我們的二進(jìn)制日志文件記錄到哪里了
mariadb-bin.000001
mariadb-bin.000002
mariadb-bin.000003
mariadb-bin.000004
mariadb-bin.000005
mariadb-bin.000006
mariadb-bin.000007
mariadb-bin.000008
mariadb-bin.000009
mariadb-bin.000010
mariadb-bin.000011
mariadb-bin.000012
mariadb-bin.000013
mariadb-bin.index
~]# mysqlbinlog --start-position=35740416 /data/binlog/mariadb-bin.000011 > /backup/binlog.sql  #將最新增量備份之后的二進(jìn)制日志記錄的數(shù)據(jù)導(dǎo)出來
~]# mysqlbinlog /data/binlog/mariadb-bin.000012 >> /backup/binlog.sql
~]# mysqlbinlog /data/binlog/mariadb-bin.000013 >> /backup/binlog.sql
編輯 /backup/binlog.sql 文件，將 "DROP TABLE `school`.`students` /* generated by server */" 刪除，撤銷誤刪除操作
MariaDB [(none)]> SET sql_log_bin=0;  #先臨時(shí)關(guān)閉二進(jìn)制日記記錄功能
MariaDB [(none)]> source /backup/binlog.sql  #導(dǎo)入增量備份之后的最新數(shù)據(jù)
查看確認(rèn)一下數(shù)據(jù)有沒有恢復(fù)完整，把my.cnf中的skip_networking刪除，重啟服務(wù)
到此已經(jīng)恢復(fù)到了最新的狀態(tài)~

~]# innobackupex --include="testdb.testlog" /backup  #備份表數(shù)據(jù)
~]# mysql -e 'SHOW CREATE TABLE testdb.testlog' > /backup/desc_testdb_testlog.sql  #備份表空間
~]# mysql -e 'DROP TABLE testdb.testlog'  #模擬故障，刪除testlog表

~]# innobackupex --apply-log --export /backup/2018-06-14_17-47-02/  #整理表數(shù)據(jù)
~]# vim /backup/desc_testdb_testlog.sql  #編輯創(chuàng)建表空間的語句，刪除以下字段
    Table   Create Table
    testlog
~]# mysql testdb  /backup/desc_testdb_testlog.sql  #導(dǎo)入表空間
~]# mysql testdb -e 'DESC testlog'  #查看是否導(dǎo)入成功
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(30) | YES  |     | NULL    |                |
| age   | int(11)  | YES  |     | 20      |                |
+-------+----------+------+-----+---------+----------------+
~]# mysql -e 'ALTER TABLE testdb.testlog DISCARD TABLESPACE'  #清除表空間
~]# cd /backup/2018-06-14_17-47-02/testdb/
testdb]# cp testlog.cfg testlog.exp testlog.ibd /var/lib/mysql/testdb/  #將表數(shù)據(jù)復(fù)制到庫目錄
~]# chown -R mysql:mysql /var/lib/mysql/testdb/  #修改所屬者和所屬組
~]# mysql -e 'ALTER TABLE testdb.testlog IMPORT TABLESPACE'  #導(dǎo)入表空間