一、準備數(shù)據(jù)庫環(huán)境

1、搭建安裝環(huán)境

成都創(chuàng)新互聯(lián)是網(wǎng)站建設專家,致力于互聯(lián)網(wǎng)品牌建設與網(wǎng)絡營銷，專業(yè)領域包括成都網(wǎng)站建設、網(wǎng)站建設、電商網(wǎng)站制作開發(fā)、微信小程序開發(fā)、微信營銷、系統(tǒng)平臺開發(fā)，與其他網(wǎng)站設計及系統(tǒng)開發(fā)公司不同，我們的整合解決方案結合了恒基網(wǎng)絡品牌建設經(jīng)驗和互聯(lián)網(wǎng)整合營銷的理念，并將策略和執(zhí)行緊密結合，且不斷評估并優(yōu)化我們的方案，為客戶提供全方位的互聯(lián)網(wǎng)品牌整合方案！

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

2、添加數(shù)據(jù)庫用戶
useradd -s /sbin/nologin MySQL //添加數(shù)據(jù)庫用戶
3、安裝mysql數(shù)據(jù)庫

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

cmake \     //檢查安裝配置環(huán)境
-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/  //將數(shù)據(jù)庫安裝目錄的使用權限給用戶mysql

vi /etc/my.cnf  //將主配置文件清空添加以下內(nèi)容
[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      //添加系統(tǒng)環(huán)境變量
source /etc/profile //刷新立即生效

cd /usr/local/mysql/        //進入安裝目錄初始化數(shù)據(jù)庫
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/       //制作數(shù)據(jù)庫系統(tǒng)啟動命令
systemctl daemon-reload           //刷新識別啟動腳本
systemctl start mysqld      //啟動數(shù)據(jù)庫
netstat -anpt | grep 3306       //查看端口是否在監(jiān)聽狀態(tài)

二、直接備份

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

三、完全備份

完全備份：每次對數(shù)據(jù)進行完整的備份?？梢詡浞菡麄€數(shù)據(jù)庫，包含用戶表、系統(tǒng)表、索引、視圖和存儲過程等所有數(shù)據(jù)庫對象。
優(yōu)點：備份與恢復操作簡單方便
缺點：數(shù)據(jù)庫存在大量的重復，占用大量的備份空間，備份時間長。
1、使用mysqldump命令備份

# mysqldump -u root -p --all-databases > all-data-$(date +%F).sql //備份所有數(shù)據(jù)庫
# mysqldump -u root -p --databases auth mysql > auth-mysql.sql //備份多個數(shù)據(jù)庫
# mysqldump -u root -p auth > auth-$(date +%F).sql //備份單個auth數(shù)據(jù)庫
# 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、還原備份的數(shù)據(jù)庫

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

3、案列演示

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

插入一些數(shù)據(jù)

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');

查看數(shù)據(jù)
select * from user_info;

a、備份整個client數(shù)據(jù)庫
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
進入數(shù)據(jù)庫查看驗證

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
進入數(shù)據(jù)庫查看驗證

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

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

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

四、增量備份

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

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

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

3、案列演示
下面的數(shù)據(jù)庫中的操作會被記錄在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

模擬刪除數(shù)據(jù)（該操作會被記錄在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中的插入操作
進入數(shù)據(jù)庫驗證是否恢復成功
select * from client.info;

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

模擬數(shù)據(jù)庫操作（其中既有正確的操作又有錯誤的操作，并且記錄在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
查看二進制日志內(nèi)部詳細信息：
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