mysql備份與恢復

1、mysqldump進行mysqll數據庫的備份與還原

命令說明： Schema和數據存儲一起、巨大的SQL語句、單個巨大的備份文件

mysqldump: 客戶端，通過mysql協議連接至mysqld；

        mysqldump [options] [db_name [tbl_name ...]]

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

        -A, --all-databases

        MyISAM, InnoDB: 溫備
             -x, --lock-all-tables：鎖定所有庫的所有表；
             -l, --lock-tables：對每個單獨的數據庫，在啟動備份之前鎖定其所有表；

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

        -B, --databases db_name1 db_name2 ...：備份指定的數據庫

        -C, --compress：壓縮傳輸；

    命令的語法格式：
        mysqldump [OPTIONS] database [tables]：備份單個庫，或庫指定的一個或多個表
        mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：備份一個或多個庫
        mysqldump [OPTIONS] --all-databases [OPTIONS]：備份所有庫


    其它選項：

        -E, --events：備份指定庫的事件調度器event scheuler；
        -R, --routines：備份存儲過程和存儲函數；
        --triggers：備份觸發器


        --master-data[=#]：
            1：記錄CHANGE MASTER TO語句；此語句未被注釋；
            2：記錄為注釋語句；

        --flush-logs, -F：鎖定表之后執行flush logs命令；

實驗及目的：

故障數據庫：192.168.150.138
恢復至數據庫：192.168.150.137
目的：故障數據庫將備份恢復還原至新建數據庫，并保證數據一致性

故障服務器進行的備份操作

1、為保證數據的一致性，故障服務器需先開啟二進制日志功能

MariaDB [(none)]> SHOW GLOBAL VARIABLES like '%log% 查看二進制日志功能是否開啟
| log_bin                                   | OFF  

修改配置文件進行全局修改
可以修改的地方：
/etc/my.conf
/etc/my.cnf.d/
此次修改為/etc/my.cnf.d/server.cnf
[server]
log_bin=mysql-bin   #mysql-bin即為二進制日志的名稱

修改配置文件需對數據庫進行重啟操作
 ~]# systemctl restart mariadb.service

再次確認二進制日志功能是否已經開啟：
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';
| log_bin                                   | ON

2、進行mysqldump操作
~]# mysqldump -uroot --all-databases --lock-all-tables --master-data=2 > /root/all.sql
說明--lock-all-tables表示鎖表操作，保證備份期間無數據變動
--master-data=2將備份時的二進制日志進行表示為注釋語句添加至all.sql中
具體內容為：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;

3、為了模擬備份點之后還出現了數據修改，對原數據庫進行數據變更操作
~]# mysql
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> DESC students;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)         | NO   | MUL | NULL    |                |
| Age       | tinyint(3) unsigned | NO   | MUL | NULL    |                |
| Gender    | enum('F','M')       | NO   |     | NULL    |                |
| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                |
| TeacherID | int(10) unsigned    | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES('void',11,'M',3,6);
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> select * from studnets;
ERROR 1146 (42S02): Table 'hellodb.studnets' doesn't exist
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | JinJiao King  | 100 | M      |    NULL |         1 |
|    27 | YinJiao King  |  98 | M      |    NULL |         2 |
|    28 | void          |  11 | M      |       3 |         6 |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (0.00 sec)

MariaDB [hellodb]> DELETE FROM students WHERE StuID=3;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> quit
Bye

新數據庫進行還原操作：

1、將備份的.sql文件拷貝至新數據庫
2、注意點：在數據庫進行還原操作是，為了減少IO負擔，可以將二進制日志文件在session層面進行暫時的關閉

SET sql_log_bin=OFF;

3、首先我查看新數據，并無任何hellodb的數據
root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.54-MariaDB Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myda               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

4、進行還原操作：需要注意mysql用戶對all.sql有讀取權限
source /tmp/all.sql:

5、數據確認：此時的數據僅僅為我備份點時的數據，顧還需要進行數據的一個前滾操作
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| myda               |
| mydb               |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | JinJiao King  | 100 | M      |    NULL |         1 |
|    27 | YinJiao King  |  98 | M      |    NULL |         2 |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

數據的一個前滾的操作：

為了保證數據的一致性，通過mysqldump進行備份時，一定要定期對二進制日志進行備份
故障數據庫的操作：
1、~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000001
可以查看到在備份點之后我做過的所有sql操作

/*!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
#170119 15:39:40 server id 1  end_log_pos 245   Start: binlog v 4, server v 5.5.52-MariaDB created 170119
 15:39:40 at startup# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
PG2AWA8BAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA8bYBYEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAW+9+9w==
'/*!*/;
# at 245
#170119 15:44:08 server id 1  end_log_pos 316   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1484811848/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.au
tocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;
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=8/*!*/
;SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 316
#170119 15:44:08 server id 1  end_log_pos 344   Intvar
SET INSERT_ID=28/*!*/;
# at 344
#170119 15:44:08 server id 1  end_log_pos 492   Query   thread_id=4 exec_time=0 error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1484811848/*!*/;
INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES('void',11,'M',3,6)
/*!*/;
# at 492
#170119 15:44:08 server id 1  end_log_pos 519   Xid = 431
COMMIT/*!*/;
# at 519
#170119 15:45:05 server id 1  end_log_pos 590   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1484811905/*!*/;
BEGIN
/*!*/;
# at 590
#170119 15:45:05 server id 1  end_log_pos 690   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1484811905/*!*/;
DELETE FROM students WHERE StuID=3
/*!*/;
# at 690
#170119 15:45:05 server id 1  end_log_pos 717   Xid = 434
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2、將這些sql操作導出至增量文件，并拷貝至新數據庫
 ~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000001 >incre.sql

3、新數據庫進行前滾操作：
MariaDB [hellodb]> source /tmp/incre.sql

4、數據確認：此時我備份點之后的數據修改也全部還原
    MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | JinJiao King  | 100 | M      |    NULL |         1 |
|    27 | YinJiao King  |  98 | M      |    NULL |         2 |
|    28 | void          |  11 | M      |       3 |         6 |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

2、物理備份：基于LVM2快照功能實現，冷備份，幾乎熱備

1、創建LVM2邏輯卷，將mariadb的數據文件目錄和二進制日志目錄放置在LVM2邏輯卷中
 ~]# fdisk /dev/sdb
歡迎使用 fdisk (util-linux 2.23.2)。

更改將停留在內存中，直到您決定將更改寫入磁盤。
使用寫入命令前請三思。


命令(輸入 m 獲取幫助)：n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
分區號 (1-4，默認 1)：
起始 扇區 (2048-41943039，默認為 2048)：
將使用默認值 2048
Last 扇區, +扇區 or +size{K,M,G} (2048-41943039，默認為 41943039)：+10G
分區 1 已設置為 Linux 類型，大小設為 10 GiB

命令(輸入 m 獲取幫助)：w
The partition table has been altered!

Calling ioctl() to re-read partition table.
正在同步磁盤。
[root@localhost ~]# partx -a /dev/sdb
partx: /dev/sdb: error adding partition 1
[root@localhost ~]# partx -a /dev/sdb
partx: /dev/sdb: error adding partition 1
[root@localhost ~]# pvcreate /dev/sdb1
Physical volume "/dev/sdb1" successfully created
[root@localhost ~]# vgcreate myvg /dev/sdb1
  Physical volume "/dev/sdb1" successfully created
  Volume group "myvg" successfully created
[root@localhost ~]# lvcreate -L +5G -n mydata myvg
  Logical volume "mydata" created.                                  
[root@localhost ~]# mke2fs -t ext4 /dev/myvg/mydata 
mke2fs 1.42.9 (28-Dec-2013)
文件系統標簽=
OS type: Linux
塊大小=4096 (log=2)
分塊大小=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
327680 inodes, 1310720 blocks
65536 blocks (5.00%) reserved for the super user
第一個數據塊=0
Maximum filesystem blocks=1342177280
40 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
    32768, 98304, 163840, 229376, 294912, 819200, 884736

Allocating group tables: 完成                            
正在寫入inode表: 完成                            
Creating journal (32768 blocks): 完成
Writing superblocks and filesystem accounting information: 完成 

[root@localhost ~]# fdisk /dev/sdc
歡迎使用 fdisk (util-linux 2.23.2)。

更改將停留在內存中，直到您決定將更改寫入磁盤。
使用寫入命令前請三思。

Device does not contain a recognized partition table
使用磁盤標識符 0x8d8aa980 創建新的 DOS 磁盤標簽。

命令(輸入 m 獲取幫助)：n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
分區號 (1-4，默認 1)：
起始 扇區 (2048-41943039，默認為 2048)：
將使用默認值 2048
Last 扇區, +扇區 or +size{K,M,G} (2048-41943039，默認為 41943039)：+10G
分區 1 已設置為 Linux 類型，大小設為 10 GiB

命令(輸入 m 獲取幫助)：w
The partition table has been altered!

Calling ioctl() to re-read partition table.
正在同步磁盤。
[root@localhost ~]# partx -a /dev/sdc
partx: /dev/sdc: error adding partition 1
[root@localhost ~]# partx -a /dev/sdc
partx: /dev/sdc: error adding partition 1
[root@localhost ~]# pvcreate /dev/sdc1
  Physical volume "/dev/sdc1" successfully created
[root@localhost ~]# vgcreate myvg2 /dev/sdc1
  Volume group "myvg2" successfully created
[root@localhost ~]# lvcreate -L +5G -n mybinlogs myvg2
  Logical volume "mybinlogs" created.
[root@localhost ~]# mke2fs -t ext4 /dev/myvg2/mybinlogs 
mke2fs 1.42.9 (28-Dec-2013)
文件系統標簽=
OS type: Linux
塊大小=4096 (log=2)
分塊大小=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
327680 inodes, 1310720 blocks
65536 blocks (5.00%) reserved for the super user
第一個數據塊=0
Maximum filesystem blocks=1342177280
40 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
    32768, 98304, 163840, 229376, 294912, 819200, 884736

Allocating group tables: 完成                            
正在寫入inode表: 完成                            
Creating journal (32768 blocks): 完成
Writing superblocks and filesystem accounting information: 完成 

[root@localhost ~]# lvs
  LV        VG     Attr       LSize  Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  root      centos -wi-ao---- 17.47g                                                    
  swap      centos -wi-ao----  2.00g                                                    
  mydata    myvg   -wi-a-----  5.00g                                                    
  mybinlogs myvg2  -wi-a-----  5.00g                              


[root@localhost ~]# mkdir -pv /data/{mysql,binlogs}
mkdir: 已創建目錄 "/data"
mkdir: 已創建目錄 "/data/mysql"
mkdir: 已創建目錄 "/data/binlogs"
[root@localhost ~]# mount /dev/myvg/mydata /data/mysql
[root@localhost ~]# mount /dev/myvg2/mybinlogs /data/binlogs
[root@localhost ~]# chown -R mysql.mysql /data/*

2、修改mariadb配置文件，指定文件目錄并開啟
[root@localhost ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d   

[root@localhost ~]# vim /etc/my.cnf.d/server.cnf 

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]
log_bin=/data/binlogs/mysql-bin

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]


3、先開啟數據庫并進行一次數據寫入操作進行測試
[root@localhost ~]# systemctl start mariadb.service
[root@localhost ~]# ls /data/mysql/
aria_log.00000001  ibdata1      ib_logfile1  mysql               test
aria_log_control   ib_logfile0  lost+found   performance_schema
[root@localhost ~]# ls /data/binlogs/
lost+found  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index

關閉二進制日志進行數據寫入
[root@localhost ~]# cp all.sql /tmp/
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET sql_log_bin=0;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> source /tmp/all.sql;

MariaDB [testdb]> SHOW DATABASES;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| hellodb             |
| #mysql50#lost+found |
| mydb                |
| mysql               |
| performance_schema  |
| test                |
| testdb              |
+---------------------+
8 rows in set (0.00 sec)

MariaDB [testdb]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> SET sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)


4、請求鎖定所有表,FLUSH TBALES為將所有內存中的數據寫入磁盤中
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

5、記錄二進制文件及事件位置
[root@localhost ~]# mysql -e 'FLUSH LOGS;'
[root@localhost ~]# mysql -e 'SHOW MASTER STATUS' >/root/pos-`date +%F`
[root@localhost ~]# cat pos-
pos-            pos-2017-03-14  
[root@localhost ~]# cat pos-2017-03-14 
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000004    245 

6、創建數據文件的快照
[root@localhost ~]# lvcreate -L 2G -n mydata-snap -s -p r /dev/myvg/mydata 
Logical volume "mydata-snap" created.

7、釋放鎖
MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

8、掛載快照進程拷貝操作，cp -a進行文件屬性保留
[root@localhost ~]# mount -r /dev/myvg/mydata-snap /mnt/
          testdb/             
[root@localhost ~]# cp -a /mnt/ /tmp/mysql
[root@localhost ~]# ls /tmp/mysql/
aria_log.00000001  hellodb  ib_logfile0  mnt   mysql               test
aria_log_control   ibdata1  ib_logfile1  mydb  performance_schema  testdb

9、備份完成后刪除快照
[root@localhost ~]# umount /mnt/
[root@localhost ~]# lvremove /dev/myvg/mydata-snap 
Do you really want to remove active logical volume mydata-snap? [y/n]: y
Logical volume "mydata-snap" successfully removed

10、快照備份后再對數據庫進行部分數據修改操作，后面實驗可以進行數據一致性驗證
MariaDB [hellodb]> DELETE FROM students WHERE StuID=15;
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | JinJiao King  | 100 | M      |    NULL |         1 |
|    27 | YinJiao King  |  98 | M      |    NULL |         2 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

11、模擬數據庫宕機操作
~]# systemctl stop mariadb.service
[root@localhost ~]# rm -rf /data/mysql/*

12、進行還原操作，開啟數據庫，數據認證操作，此時的還原點為做快照是的還原點，所以之前StuID=15的已刪除資料還在
[root@localhost ~]# cp -a /tmp/mysql/* /data/mysql/
[root@localhost ~]# systemctl start mariadb.service
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mnt                |
| mydb               |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | JinJiao King  | 100 | M      |    NULL |         1 |
|    27 | YinJiao King  |  98 | M      |    NULL |         2 |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

13、利用原來的二進制日志進行回滾操作，保證一致性
[root@localhost ~]# cat pos-2017-03-14  確認備份中的時間點
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000004    245     
[root@localhost ~]# mysqlbinlog --start-position=245 /data/binlogs/mysql-bin.000004 >incre.sql
[root@localhost ~]# cat incre.sql 
/*!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
#170314 15:38:32 server id 1  end_log_pos 245   Start: binlog v 4, server v 5.5.52-MariaDB created 170314
 15:38:32BINLOG '
+J3HWA8BAAAA8QAAAPUAAAAAAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAApbX/eg==
'/*!*/;
# at 245
#170314 15:50:16 server id 1  end_log_pos 316   Query   thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1489477816/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.au
tocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;
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=8/*!*/
;SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 316
#170314 15:50:16 server id 1  end_log_pos 417   Query   thread_id=9 exec_time=0 error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1489477816/*!*/;
DELETE FROM students WHERE StuID=30
/*!*/;
# at 417
#170314 15:50:16 server id 1  end_log_pos 489   Query   thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1489477816/*!*/;
COMMIT
/*!*/;
# at 489
#170314 15:50:39 server id 1  end_log_pos 560   Query   thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1489477839/*!*/;
BEGIN
/*!*/;
# at 560
#170314 15:50:39 server id 1  end_log_pos 661   Query   thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1489477839/*!*/;
DELETE FROM students WHERE StuID=15
/*!*/;
# at 661
#170314 15:50:39 server id 1  end_log_pos 688   Xid = 405
COMMIT/*!*/;
# at 688
#170314 15:51:30 server id 1  end_log_pos 707   Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

14、進行前滾操作，并進行數據驗證，操作時在session層面關閉二進制日志(沒必要開啟，減少IO)
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET sql_bin_log=0;
ERROR 1193 (HY000): Unknown system variable 'sql_bin_log'
MariaDB [(none)]> SET sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> source /tmp/incre.sql

MariaDB [hellodb]> SET sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM students WHERE StuID=15;
Empty set (0.00 sec)

MariaDB [hellodb]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3、使用xtrabackup對MySQL進行備份和還原

完全備份

1、進入http://www.percona.com/software/percona-xtrabackup/官網進行rpm包下載
2、yum進行本地包安裝，xtrabackup所依賴的包會包括epel源中的包，事先配置好倉庫
[root@localhost ~]# ls
all.sql          hellodb_mydb.sql  percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
anaconda-ks.cfg  hellodb.sql       pos-
hellodb          incre.sql         pos-2017-03-14
[root@localhost ~]# yum install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm 
3、使用命令對數據庫進行備份操作
[root@localhost ~]# innobackupex --user=root /backups/
170314 16:45:33 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
 socket: not set
Using server version 5.5.52-MariaDB
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
170314 16:45:34 >> log scanned up to (1651567)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 18 for testdb/tb1, old maximum was 0
170314 16:45:34 [01] Copying ./ibdata1 to /backups/2017-03-14_16-45-33/ibdata1
170314 16:45:35 [01]        ...done
170314 16:45:35 [01] Copying ./testdb/tb1.ibd to /backups/2017-03-14_16-45-33/testdb/tb1.ibd
......
170314 16:45:35 [00] Writing test/db.opt
170314 16:45:35 [00]        ...done
170314 16:45:35 Finished backing up non-InnoDB tables and files
170314 16:45:35 [00] Writing xtrabackup_binlog_info
170314 16:45:35 [00]        ...done
170314 16:45:35 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1651567'
xtrabackup: Stopping log copying thread.
.170314 16:45:35 >> log scanned up to (1651567)

170314 16:45:35 Executing UNLOCK TABLES
170314 16:45:35 All tables unlocked
170314 16:45:35 Backup created in directory '/backups/2017-03-14_16-45-33/'
MySQL binlog position: filename 'mysql-bin.000003', position '523596'
170314 16:45:35 [00] Writing backup-my.cnf
170314 16:45:35 [00]        ...done
170314 16:45:35 [00] Writing xtrabackup_info
170314 16:45:35 [00]        ...done
xtrabackup: Transaction log of lsn (1651567) to (1651567) was copied.
170314 16:45:35 completed OK!

~]# cat /backups/2017-03-14_17-40-01/xtrabackup_checkpoints 此文件可以查看備份具體內容
backup_type = full-backuped
from_lsn = 0
to_lsn = 1657383
last_lsn = 1657383
compact = 0
recover_binlog_info = 0

為保證一致性，備份完成后還要有一個prepare操作
一般情況下，在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。
[root@localhost backups]# innobackupex --apply-log  /backups/2017-03-14_17-40-01/
InnoDB: 5.7.13 started; log sequence number 1662001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1662020
170314 18:29:03 completed OK!
4、查看備份文件，備份文件會自動在一個自動創建的時間目錄里面存放
備注：可以使用--no-timestamp選項來阻止命令自動創建一個以時間命名的目錄
[root@localhost ~]# ls /backups/2017-03-14_16-45-33/
backup-my.cnf  ibdata1  mysql               test    xtrabackup_binlog_info  xtrabackup_info
hellodb        mydb     performance_schema  testdb  xtrabackup_checkpoints  xtrabackup_logfile

注意：innodb_file_per_table此參數建議開啟，表示每個表單獨使用一個文件
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
innodb_file_per_table=ON

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

5、將備份拷貝至備庫進行還原測試
~]# scp -r /backups/2017-03-14_16-45-33/ 192.168.150.137:root/

備庫進行還原測試操作
備注：首先注意innodb_file_per_table=ON此參數的設置是否正確
1、備庫也要先xtrabackup的程序，yum安裝
注意：恢復不用啟動MySQL
2、~]# innobackupex --copy-back /backups/2017-03-14_16-45-33/
170313 05:49:11 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
170313 05:49:11 [01] Copying ib_logfile0 to /data/mysql/ib_logfile0
170313 05:49:11 [01]        ...done
170313 05:49:11 [01] Copying ib_logfile1 to /data/mysql/ib_logfile1
......
170313 05:49:12 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/xtrabackup_binlog_pos_innodb
170313 05:49:12 [01]        ...done
170313 05:49:12 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1
170313 05:49:13 [01]        ...done
170313 05:49:13 completed OK!
3、查看目錄中的文件
[root@localhost ~]# ls /data/mysql/     文件已恢復
hellodb  ib_logfile0  ibtmp1  mysql               test    xtrabackup_binlog_pos_innodb
ibdata1  ib_logfile1  mydb    performance_schema  testdb  xtrabackup_info
[root@localhost ~]# cd /data/mysql/
[root@localhost mysql]# ll
total 40980
drwxr-x--- 2 root root     4096 Mar 13 05:49 hellodb
-rw-r----- 1 root root 18874368 Mar 13 05:49 ibdata1
-rw-r----- 1 root root  5242880 Mar 13 05:49 ib_logfile0
-rw-r----- 1 root root  5242880 Mar 13 05:49 ib_logfile1
-rw-r----- 1 root root 12582912 Mar 13 05:49 ibtmp1
drwxr-x--- 2 root root       45 Mar 13 05:49 mydb
drwxr-x--- 2 root root     4096 Mar 13 05:49 mysql
drwxr-x--- 2 root root     4096 Mar 13 05:49 performance_schema
drwxr-x--- 2 root root       19 Mar 13 05:49 test
drwxr-x--- 2 root root       47 Mar 13 05:49 testdb
-rw-r----- 1 root root       38 Mar 13 05:49 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      457 Mar 13 05:49 xtrabackup_info
[root@localhost mysql]# chown -R mysql.mysql ./*        由于我使用root用戶進行的操作，所以權限會變成root.root，需要進行手動修改，正式環境中建議適用于mysql用戶執行操作
4、數據確認OK
root@localhost mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.54-MariaDB Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mydb               |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | JinJiao King  | 100 | M      |    NULL |         1 |
|    27 | YinJiao King  |  98 | M      |    NULL |         2 |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

MariaDB [hellodb]> exit
Bye

增量備份
1、首先在上次全備之后，進行數據修改：刪除部分表、創建部分表
root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]use hellodb;
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

MariaDB [hellodb]SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| courses           |
| scores            |
| students          |
| teachers          |
| testtb            |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]DROP TABLE toc
    -;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]SHWO TABELS;
DB server version for the right syntax to use near 'SHWO TABELS' at line 1
MariaDB [hellodb]SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| courses           |
| scores            |
| students          |
| teachers          |
| testtb            |
+-------------------+
6 rows in set (0.00 sec)

MariaDB [hellodb]INSERT INTO testtb VALUES (22),(222);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hellodb]exit
Bye

2、對數據庫進行增量備份
innobackupex --incremental /backups/ --incremental-basedir=/backups/2017-03-14_17-40-01
3、查看數據目錄
[root@localhost ~]# less /backups/2017-03-14_17-4
2017-03-14_17-40-01/ 2017-03-14_17-43-14/ 
[root@localhost ~]# less /backups/2017-03-14_17-43-14/
[root@localhost ~]# cat /backups/2017-03-14_17-43-14/xtrabackup_checkpoints 
backup_type = incremental   #此時的備份類型為增量備份
from_lsn = 1657383
to_lsn = 1661632
last_lsn = 1661632
compact = 0
recover_binlog_info = 0
4、增量備份還原的準備工作
“準備”(prepare)增量備份與整理完全備份有著一些不同，尤其要注意的是：
(1)需要在每個備份(包括完全和各個增量備份)上，將已經提交的事務進行“重放”。“重放”之后，所有的備份數據將合并到完全備份上。
(2)基于所有的備份將未提交的事務進行“回滾”。
innobackupex --apply-log --redo-only /backups/2017-03-14_17-40-01
innobackupex --apply-log --redo-only /backups/2017-03-14_17-40-01 --incremental-dir=/backups/2017-03-14_17-43-14
5、此時進行數據查看
cat /backups/2017-03-14_17-40-01/xtrabackup_checkpoints 
backup_type = log-applied   此備份已經為合并后應用完redolog的備份
from_lsn = 0
to_lsn = 1661632
last_lsn = 1661632
compact = 0
recover_binlog_info = 0
6、關閉數據庫并進行刪除數據庫數據，進行還原
rm -rf /data/mysql/*
rm -rf /data/binlogs/*
innobackupex --copy-back /backups/2017-03-14_17-40-01/
7、查看還原后狀態
root@localhost ~]# cd /data/mysql/
[root@localhost mysql]# ll -lh
總用量 19M
drwxr-x--- 2 root root 4.0K 3月  14 17:48 hellodb
-rw-r----- 1 root root  18M 3月  14 17:48 ibdata1
drwxr-x--- 2 root root 4.0K 3月  14 17:48 mydb
drwxr-x--- 2 root root 4.0K 3月  14 17:48 mysql
drwxr-x--- 2 root root 4.0K 3月  14 17:48 performance_schema
drwxr-x--- 2 root root 4.0K 3月  14 17:48 test
drwxr-x--- 2 root root 4.0K 3月  14 17:48 testdb
-rw-r----- 1 root root   38 3月  14 17:48 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root  516 3月  14 17:48 xtrabackup_info
[root@localhost mysql]# chown -R mysql.mysql ./*
8、還原后的數據驗證操作
[root@localhost mysql]# systemctl start mariadb.service
[root@localhost mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]USE hellodb
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
MariaDB [hellodb]SELECT * FROM testtb;
+------+
| id   |
+------+
|    1 |
|   11 |
|   22 |
|  222 |
+------+
4 rows in set (0.00 sec)

MariaDB [hellodb]exit
Bye