（2）centos7--MySQL-5.6.36 backup

MySQL備份與恢復
數據備份的類型
1、物理備份
冷備份：在數據庫關閉狀態下進行的備份操作。
熱備份：在數據庫處于運行狀態時進行備份操作，該方法依賴于數據庫的日志文件。
溫備份：數據庫鎖定表格（不可寫入但可讀）的狀態下進行的備份操作。
2、邏輯備份
對數據庫邏輯組件（如表等數據庫對象）的備份，表示為邏輯數據庫結構（create database，create table語句）和
內容（insert語句或分割文本文件）的信息，這種類型的備份適用于可以編輯數據值或表結構較小的數據量，或者在不
同的機器體系結構上重新創建數據
3、從數據庫備份策略角度，數據庫的備份可分為完整備份、差異備份和增量備份。
完整備份：每次對數據庫進行完整的備份，即對整個數據庫的備份，數據庫結構和文件結構的整體備份，是差異備份和增量備份的基礎，
差異備份：備份從上次完整備份之后被修改的所有文件，備份的時間點是上次完整備份起，備份數據量會越來越大，恢復數據時，只需
恢復上次的完整備份與最近的一次差異備份
增量備份：只有在那些上次完整備份或增量備份后被修改的文件才會被備份，以上次完整備份或增量備份的時間為時間點，僅備份這之
間的數據變化因而備份的數據量小，占用的空間小，備份速度快，但恢復時需要將上一次完整備份到最后一次增量備份一次
恢復
數據庫常見備份方法
物理冷備份
tar命令
專用備份工具
mysqldump
Mysqlhotcopy
二進制日志
第三方工具備份
Percona XtraBackup
Xtrabackup

一、數據庫完全備份操作
1、物理冷備份與恢復；使用tar命令直接打包數據庫文件夾，在備份之前需要使用停止mysql服務

備份數據庫；創建一個/backup目錄作為備份數據庫路徑，使用tar創建備份文件。整個數據庫文件夾備份屬于完全備份
[root@localhost ~]# systemctl stop mysqld.service
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@localhost ~]# ls -l /backup/
總用量 736
-rw-r--r-- 1 root root 751542 8月  15 08:40 mysql_all-2018-08-15.tar.gz  

恢復數據庫；執行下面操作將數據庫文件/usr/local/mysql/data/轉移至bak目錄下，模擬故障。
[root@localhost ~]# mkdir /bak
[root@localhost ~]# mv /usr/local/mysql/data/ /bak/
執行下面操作可以從備份文件恢復數據
[root@localhost ~]# mkdir restore/
[root@localhost ~]# tar zxf /backup/mysql_all-2018-08-15.tar.gz -C restore/
[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/  
[root@localhost ~]# systemctl start mysqld.service

2、mysqldump備份與恢復
備份數據庫；使用mysqldump命令導出數據時，默認會直接在終端顯示，若要保存到文件，還需要結合shell的‘>’重定向
輸出操作。命令格式如下。
格式1；備份指定數據庫中的部分表
mysqldump [選項] 庫名 [表名1] [表名2] ... > /備份路徑/備份文件名
格式2；備份一個或多個完整的庫（包含其中所有的表）
mysqldump [選項] --databases 庫名1 [庫名2] ... > /備份路徑/備份文件名
格式3；備份mysql數據庫中的所有庫
mysqldump [選項] --all-databases > /備份路徑/備份文件名
其中，常用的選項包括“-u”“-p”，分別用于指定數據庫的用戶名，密碼。
例如，以下操作分別使用格式1，格式2，將mysql庫中的user表導出為mysql-user.sql，將整個benet庫導出
為benet.sql文件，所有操作均以root用戶身份驗證
[root@localhost ~]# mysqldump -u root -p mysql user >mysql-user.sql
Enter password: ##mysql中root密碼

[root@localhost ~]# mysqldump -u root -p --databases benet > benet.sql
Enter password:                                                                     ##mysql中root密碼

若需要備份整個mysql服務器中的所有庫，應使用格式3。當導出的數據量較大時，可以添加“--opt”選項以優化執行速度。
例如；執行以下操作將創建備份文件all-data.sql，其中包含MySQL服務器中的所有庫。
[root@localhost ~]# mysqldump -u root -p --opt --all-databases > all-databases.sql
Enter password:                                                                     ##mysql中root密碼

查看備份文件
通過mysqldump工具導出的SQL腳本文本文件，其中“/*...*/”部分或以--開頭的行表示注釋信息。使用grep、less、cat等文本工
具都可以查看文件內容。
例如；執行以下操作可以過濾出benet.sql腳本中的數據庫操作語句
[root@localhost ~]# grep -v "^--" benet.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `benet` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `benet`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`user_name` char(15) NOT NULL,
`user_passwd` char(100) DEFAULT '',
PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('lisi','123123'),('zhangsan','*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1');
UNLOCK TABLES;

恢復數據庫；使用mysqldump命令導出的SQL備份腳本，在需要恢復時可以通過mysql命令對其進行導入操作，
命令格式如下。
mysql [選項] [庫名] [表名] < /備份路徑/備份文件名
當備份文件中只包含表的備份，而不包含創建庫的語句時，執行導入操作時必須制定庫名，且目標庫必須存在。
例如；以下操作可以從備份文件mysql-user.sql中將表導入test庫。其中“-e”選項是用于指定連接MySQL執行后的命令，
命令執行后自動退出。
[root@localhost ~]# mysql -u root -p test < mysql-user.sql 
Enter password:                                                                     ##mysql中root密碼                          

[root@localhost ~]# mysql -u root -p -e ' show tables from test;'
Enter password:                                                                     ##mysql中root密碼
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+  

若備份文件中已經包含完整的數據庫信息，則執行導入操作時無需指定庫名。
例如指定一下操作可以從備份文件benet.sql恢復到benet庫。
[root@localhost ~]# mysql -u root -p -e ' drop database benet;'                     ##刪除benet庫，模擬數據庫故障
Enter password: 
[root@localhost ~]# mysql -u root -p -e ' show databases;'                          ##查看到benet庫已經不存在了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@localhost ~]# mysql -u root -p < ~/benet.sql
Enter password: 
[root@localhost ~]# mysql -u root -p -e ' show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| benet              |
| mysql              |
| performance_schema |
| test               |
+--------------------+  

二、增量備份
特點；與完整備份不同，增量備份沒有重復數據，備份量不大，時間短，但恢復麻煩，需要上次完整備份及完整備份之
后的增量備份才能恢復，而且要對所有增量備份進行逐個反推恢復，mysql中沒有提供直接增量備份的方法，可以通過
MySQL提供的我二進制日志（binary logs）間接實現增量備份。

二進制日志
要進行MySQL的增量備份，首先要開啟二進制功能，開啟MySQL的二進制日志功能的實現方法有很多種，最常用的是在MySQL
配置文件的mysqld項下加入“log-bin=/文件路徑/文件名”前綴，如；log-bin=/usr/local/mysql/mysql-bin,然后重啟mysqld.service
服務，就可以看到指定路徑下的二進制文件文件了。默認情況下，二進制日志文件的擴展名是一個六位的數字，如；mysql-bin.000001

[root@localhost ~]# vim /etc/my.cnf                                         ##修改配置文件，啟用二進制功能
log-bin=/usr/local/mysql/mysql-bin
:wq

[root@localhost ~]# systemctl restart mysqld.service 
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 120 8月  15 10:35 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  34 8月  15 10:35 /usr/local/mysql/mysql-bin.index

MySQL增量恢復
常用的增量恢復方法有三種；一般恢復，基于位置的恢復，基于時間點的恢復

一般恢復；將所有備份的二進制日志內容全部恢復，格式命令如下
mysqlbinlog [--no-defaults] 增量備份文件 | mysql -u root -p

基于位置的恢復；數據庫管理員在操作數據庫時可能在同一時間點既有錯誤的操作也有正確的操作，通過基于位置進行恢復可以更加精準，
命令格式如下所示。
格式一；恢復數據到指定位置
mysqlbinlog --stop-position='459' 二進制日志 | mysql -u root -p ******
格式二；從指定的位置開始恢復數據
mysqlbinlog --start-position='459' 二進制日志 | mysql -u root -p ******

基于時間點的恢復；跳過某個發生錯誤的時間點實現數據恢復，而基于時間點的恢復可以分成三種情況。
格式1；從日志開頭截止到某個時間點恢復
mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u 用戶 -p 密碼
格式2；從某個時間點到日志結尾恢復
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u 用戶 -p 密碼
格式3；從某個時間點到某個時間點恢復
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' --stop-datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u 用戶 -p 密碼

下面我們通過一個企業級的備份案例講解增量備份與恢復的具體操作。

三、企業備份案例
需求描述；北京移動公司的用戶信息數據庫為client，用戶資費數據表為user_info，每周需要進行完整備份，每天需要進行增量備份，
新增加的用戶信息表如下

-----------------------------------------------------------------------
    ×××         姓名         性別            用戶ID號     資費
-----------------------------------------------------------------------
    000006          張三          男               016         10
    000007          李四          女               017         19
    000008          王五          女               018         23
    000009          趙六          男               019         37
    000010          孫七          男               020         36
-----------------------------------------------------------------------

一般恢復
1）添加數據庫，表，錄入信息
[root@localhost ~]# mysql -u root -p
Enter password:
mysql> create database client;
Query OK, 1 row affected (0.00 sec
mysql> use client;
Database changed
mysql> create table user_info(××× char(20) not null,姓名 char(20) not null,性別 char(4),用戶ID號 char(10) not null,資費 int(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user_info values ('000006','張三','男','016','10');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info values ('000007','李四','女','017','91');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_info values ('000008','王五','女','018','23');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| ×××    | 姓名   | 性別   | 用戶ID號    | 資費   |
+-----------+--------+--------+-------------+--------+
| 000006    | 張三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

2）先進行一次完整備份
為方便驗證二進制日志增量的恢復能力，在插入三行數據的client.user_info表進行一次完全備份，然后在Linux系統命令下
執行"mysqladmin -uroot -p123123 flush-logs"命令或在"mysql>"命令提示符下執行"flush logs"生成新的二進制日志。

[root@localhost ~]# mysqldump -uroot -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql
Enter password:
[root@localhost ~]# ls /mysql_bak/
client_userinfo-2018-08-15.sql
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1252 8月  15 11:50 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  120 8月  15 11:50 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql   68 8月  15 11:50 /usr/local/mysql/mysql-bin.index

3）繼續錄入新的數據并進行增量備份
新增兩行數據，并執行"mysqladmin -uroot -p flush-logs"命令刷新二進制日志進行增量備份，
如此；二進制日志文件mysql-bin.000003中就會保留插入兩個用戶數據的操作
mysql> use client;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into user_info values('000009','趙六','男','019','37');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info values('000010','孫七','男','020','36');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| ×××    | 姓名   | 性別   | 用戶ID號    | 資費   |
+-----------+--------+--------+-------------+--------+
| 000006    | 張三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 趙六   | 男     | 019         |     37 |
| 000010    | 孫七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)

[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1252 8月  15 11:50 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  679 8月  15 12:02 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql  120 8月  15 12:02 /usr/local/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql  102 8月  15 12:02 /usr/local/mysql/mysql-bin.index

4）模擬誤操作刪除user_info表
[root@localhost ~]# mysql -uroot -p -e 'drop table client.user_info;'
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

5）恢復操作
在執行恢復操作時，需要先執行完整恢復，在進行增量恢復。
[root@localhost ~]# mysql -u root -p client < /backup_bak/client_userinfo-2018-08-18.sql
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-----------+--------+--------+-------------+--------+
一般恢復
[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 趙六 | 男 | 019 | 37 |
| 000010 | 孫七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+

基于位置恢復
[root@localhost ~]# mysqlbinlog --no -defaults /usr/local/mysql/mysql-bin.000002                ##查看二進制文件中的內容以便恢復

mysqlbinlog: unknown option '--no'

[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000002
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
# at 4
#180818 14:16:21 server id 1 end_log_pos 120 CRC32 0xf20e69c2 Start: binlog v 4, server v 5.6.36-log created 180818 14:16:21
BINLOG '
tbl3Ww8BAAAAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcJp
DvI=
'/!/;
# at 120
#180818 14:17:53 server id 1 end_log_pos 203 CRC32 0x122cbd0b Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1534573073/!/;
SET @@session.pseudo_thread_id=4/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
SET @@session.sql_mode=1075838976/!/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;
/!\C utf8 //!/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
SET @@session.lc_time_names=0/!/;
SET @@session.collation_database=DEFAULT/!/;
BEGIN
/!/;
# at 203
#180818 14:17:53 server id 1 end_log_pos 345 CRC32 0xa4c70118 Query thread_id=4 exec_time=0 error_code=0
use client/!/;
SET TIMESTAMP=1534573073/!/;
insert into user_info values('000009','趙六','男','019','37')
/!/;
# at 345
#180818 14:17:53 server id 1 end_log_pos 376 CRC32 0x7599a729 Xid = 46
COMMIT/!/;
# at 376
#180818 14:18:04 server id 1 end_log_pos 459 CRC32 0x1ff3f3f2 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1534573084/!/;
BEGIN
/!/;
# at 459
#180818 14:18:04 server id 1 end_log_pos 601 CRC32 0xea02a0b2 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1534573084/!/;
insert into user_info values('000010','孫七','男','020','36')
/!/;
# at 601
#180818 14:18:04 server id 1 end_log_pos 632 CRC32 0xc9b66df7 Xid = 47
COMMIT/!/;
# at 632
#180818 14:19:23 server id 1 end_log_pos 679 CRC32 0x2bf94bf2 Rotate to mysql-bin.000003 pos: 4
DELIMITER ;
# End of log file
ROLLBACK / added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
基于位置恢復：方法一
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='459' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 趙六 | 男 | 019 | 37 |
+-----------+--------+--------+-------------+--------+

基于位置恢復：方法二
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password:
[root@localhost ~]# mysql -u root -p client < /backup_bak/client_userinfo-2018-08-18.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='459' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000010 | 孫七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+

基于時間點
方法一：從日志開頭截止到某個時間點恢復
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password:
[root@localhost ~]# mysql -u root -p client < /backup_bak/client_userinfo-2018-08-18.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='2018-08-18 14:18:04' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 趙六 | 男 | 019 | 37 |
+-----------+--------+--------+-------------+--------+

方法三：時間點到時間點
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password: 
[root@localhost ~]# mysql -u root -p client < /backup_bak/client_userinfo-2018-08-18.sql
Enter password: 
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='2018-08-18 14:18:04'  --stop-datetime='2018-08-18 14:19:23' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| ×××    | 姓名   | 性別   | 用戶ID號    | 資費   |
+-----------+--------+--------+-------------+--------+
| 000006    | 張三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000010    | 孫七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+