數據庫篇——完全、增量備份和恢復

一、準備數據庫環境

1、搭建安裝環境

yum -y install \
gcc \
gcc-c++ \
make \
ncurses \
ncurses-devel \
bison \
Cmake

2、添加數據庫用戶
useradd -s /sbin/nologin mysql //添加數據庫用戶
3、安裝mysql數據庫

tar xf mysql-boost-5.7.20.tar.gz –C /opt/       //解壓源碼包

cmake \     //檢查安裝配置環境
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1

make && make install        //編譯安裝

chown -R mysql.mysql /usr/local/mysql/  //將數據庫安裝目錄的使用權限給用戶mysql

vi /etc/my.cnf  //將主配置文件清空添加以下內容
[client]
port = 3306 
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

chown mysql:mysql /etc/my.cnf       //更改主配置文件的權限

echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile      //添加系統環境變量
source /etc/profile //刷新立即生效

cd /usr/local/mysql/        //進入安裝目錄初始化數據庫
bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/       //制作數據庫系統啟動命令
systemctl daemon-reload           //刷新識別啟動腳本
systemctl start mysqld      //啟動數據庫
netstat -anpt | grep 3306       //查看端口是否在監聽狀態

二、直接備份

停止數據庫，直接打包數據庫數據目錄/usr/local/mysql/data
tar cJvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/

三、完全備份

完全備份：每次對數據進行完整的備份。可以備份整個數據庫，包含用戶表、系統表、索引、視圖和存儲過程等所有數據庫對象。
優點：備份與恢復操作簡單方便
缺點：數據庫存在大量的重復，占用大量的備份空間，備份時間長。
1、使用mysqldump命令備份

# mysqldump -u root -p --all-databases > all-data-$(date +%F).sql //備份所有數據庫
# mysqldump -u root -p --databases auth mysql > auth-mysql.sql //備份多個數據庫
# mysqldump -u root -p auth > auth-$(date +%F).sql //備份單個auth數據庫
# mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql //備份mysql的庫中的user表

#mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql //備份mysql庫user表的結構。
2、還原備份的數據庫

source /tmp/auth.sql        //要先進入某個具體的數據庫再進行恢復，除非是恢復所有數據庫。
mysql -u root -p  < all-data-$(date +%F).sql    //恢復所有的數據庫
mysql -u root -p mysql < mysql-user-$(date +%F).sql     //恢復mysql的user表，需要指定庫名
mysql -uroot -p auth < /tmp/desc-mysql-user.sql     //將數據結構恢復到auth庫中，單庫備份的時候要指定還原到哪個數據庫中，需要建立空庫，庫名可以任意

3、案列演示

create database client; //創建client庫
use client; //進庫
create table user_info(××× int(18),姓名 varchar(20),性別 varchar(20),用戶ID號 int(20),資費 int(48));     //創建user_info表
desc user_info; //查看表結構

插入一些數據

insert into user_info values('000000001','孫空武','男','011','100');
insert into user_info values('000000002','藍凌','女','012','98');
insert into user_info values('000000003','姜紋','女','013','12');
insert into user_info values('000000004','關園','男','014','38');
insert into user_info values('000000005','羅中昆','男','015','39');

查看數據
select * from user_info;

a、備份整個client數據庫
mysqldump -uroot -p client > client-$(date +%F).sql

刪除原有的client庫重新建一個new_client空庫

drop database client;
create database new_client;

還原client庫
mysql -u root -p new_client< client-2018-12-10.sql
進入數據庫查看驗證

b、備份client庫的中的表
mysqldump -uroot -p client user_info > client_user_info-$(date +%F).sql

刪除client庫中的user_info表

drop tables client.user_info;
show tables in client;

還原client庫中的user_info表
mysql -uroot -p client < client_user_info-2018-12-10.sql
進入數據庫查看驗證

c、備份client庫中的表結構（無數據）
mysqldump -uroot -p -d client user_info > desc-client_user_info.sql

新建new_client空數據庫
create database new_client;
還原client庫中user_info表中的結構
mysql -uroot -p new_client < desc-client_user_info.sql
進入數據庫查看驗證

desc new_client.user_info;
select * from new_client.user_info;

四、增量備份

增量備份：只有那些在上次完全備份或增量備份后被修改的文件才會被備份。
優點：沒有重復數據，備份量不大，時間短。
缺點：需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且對所有增量備份進行逐個反推恢復，操作較為繁瑣。
為什么使用增量備份？
解決完全備份存在的問題，完全備份每次都把所有的數據備份，存在大量重復數據，備份與恢復的時間長。
增量備份的特點
（1）二進制日志保存了所有更新或者可能更新數據庫的操作
（2）二進制日志在啟動mysql服務器后開始記錄，并在文件達到max_binlog_size所設置的大小或者接收到flush logs命令后重新創建新的日志文件。
（3）只需要定時執行flush logs方法重新創建新的日志，生成二進制文件序列，并及時把這些日志保存到安全的地方就完成了一個時間段的增量備份。
1、開始二進制日志功能

vi /etc/my.cfg
在[mysqld]下添加
log_bin=/usr/local/mysql/data/mysql_bin

systemctl restart mysqld //重啟數據庫
2、查看生成的二進制文件
cd /usr/local/mysql/data/

3、案列演示
下面的數據庫中的操作會被記錄在mysql_bin.000001二進制日志中

create database client;
use client;
create table info(××× int(18),姓名 varchar(20),性別 varchar(20),用戶ID號 int(20),資費 int(48));
insert into info values('000000001','孫空武','男','011','100');
insert into info values('000000002','藍凌','女','012','98');
insert into info values('000000003','姜紋','女','013','12');
select * from info;

先做一次全量備份
mysqldump -u root -p client info > client_info-$(date +%F).sql

刷新日志文件
mysqladmin -uroot -p flush-logs

插入文件（該操作記錄在mysql_bin.000002二進制日志中）

insert into info values('000000004','關園','男','014','38');
insert into info values('000000005','羅中昆','男','015','39');

刷新日志文件
mysqladmin -uroot -p flush-logs

模擬刪除數據（該操作會被記錄在mysql_bin.000003二進制日志中）

delete from info where ×××=4;
delete from info where ×××=5;

通過二進制日志恢復刪除的文件

mysqlbinlog --no-defaults mysql_bin.000002 | mysql -u root -p       //實為恢復保存在mysql_bin.000002中的插入操作
進入數據庫驗證是否恢復成功
select * from client.info;

將恢復成功的表再做一次全量備份
mysqldump -u root -p client info > client_info-$(date +%F).sql
刷新日志
mysqladmin -uroot -p flush-logs

模擬數據庫操作（其中既有正確的操作又有錯誤的操作，并且記錄在mysql_bin.000004中）
正確操作：

insert into info values('000000006','藍凌林','女','012','98');
insert into info values('000000007','姜紋文','女','013','12');

錯誤操作：
delete from info where ×××=1;
正確操作：

insert into info values('000000008','凌林','女','012','98');
insert into info values('000000009','紋文','女','013','12');

斷點恢復（為了避開錯誤的操作）

進行最近一次全量備份還原
mysql -u root -p client < clientinfo-2018-12-05.sql
查看二進制日志內部詳細信息：
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000004
獲取第一個正確操作的位置變量


獲取第二個正確操作的位置變量


開始恢復

mysqlbinlog --no-defaults --start-position='293'  --stop-position='732' mysql_bin.000004 | mysql -uroot -p  //斷點恢復第一處
mysqlbinlog --no-defaults --start-position='1160'  --stop-position='1593' mysql_bin.000004 | mysql -uroot -p    //斷點恢復第二處

驗證端點恢復是否成功

說明：該操作成功避免了日志文件錯誤操作delete from info where ×××=1;對應的位置偏移量范圍，將正確的操作恢復。

附：關于位置變量的篩選方法和時間變量的篩選方法

基于位置：at
跳過錯誤的
mysqlbinlog --no-defaults --stop-position='871' mysql_bin.000005 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='1160' mysql_bin.000005 | mysql -uroot -p
篩選正確的
mysqlbinlog --no-defaults --start-position='871' --stop-position='1160' mysql_bin.000005 | mysql -uroot -p
基于時間點：time
mysqlbinlog --no-defaults --stop-datetime='2018-03-11 21:30:39' mysql_bin.000002 | mysql -u root -p
mysqlbinlog --no-defaults --start-datetime='2018-03-11 21:30:39' mysql_bin.000002 | mysql -u root -p
篩選一段時間
mysqlbinlog --no-defaults --start-datetime='2018-12-05 21:30:39' --stop-datetime='2018-12-05 22:01:20' mysql_bin.000002 | mysql -u root -p
關鍵記錄好時間點：
誤操作時間點：2018-12-05 22:01:13
之后新增正確：2018-12-05 22:01:20