一、mysqldump进行mysqll数据库的备份与还原
二、物理备份：基于LVM2快照功能实现，冷备份，几乎热备
三、使用xtrabackup对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命令；
实验及目的：
故障数据库：172.18.70.40
恢复至数据库：172.18.70.50
目的：故障数据库将备份恢复还原至新建数据库，并保证数据一致性
故障服务器进行的备份操作
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</pre>
新数据库进行还原操作：
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)</pre>
数据的一个前滚的操作：
为了保证数据的一致性，通过mysqldump进行备份时，一定要定期对二进制日志进行备份
故障数据库的操作：
1、根据位置精确解析binlog日志
~]# 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
#171111 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
#171111 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
#171111 15:44:08 server id 1 end_log_pos 344 Intvar
SET INSERT_ID=28/*!*/;
# at 344
#171111 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
#171111 15:44:08 server id 1 end_log_pos 519 Xid = 431
COMMIT/*!*/;
# at 519
#171111 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
#171111 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
#171111 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-11-11 确认备份中的时间点
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
#171111 15:38:32 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.52-MariaDB created 171111
15:38:32BINLOG '
+J3HWA8BAAAA8QAAAPUAAAAAAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAApbX/eg==
'/*!*/;
# at 245
#171111 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
#171111 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
#171111 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
#171111 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
#171111 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
#171111 15:50:39 server id 1 end_log_pos 688 Xid = 405
COMMIT/*!*/;
# at 688
#171111 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)</pre>
3、使用xtrabackup对MySQL进行备份和还原
完全备份
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-11-11
[root@localhost ~]# yum install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
3、使用命令对数据库进行备份操作
[root@localhost ~]# innobackupex --user=root /backups/
171111 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
171111 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
171111 16:45:34 [01] Copying ./ibdata1 to /backups/2017-11-11_16-45-33/ibdata1
171111 16:45:35 [01] ...done
171111 16:45:35 [01] Copying ./testdb/tb1.ibd to /backups/2017-11-11_16-45-33/testdb/tb1.ibd
......
171111 16:45:35 [00] Writing test/db.opt
171111 16:45:35 [00] ...done
171111 16:45:35 Finished backing up non-InnoDB tables and files
171111 16:45:35 [00] Writing xtrabackup_binlog_info
171111 16:45:35 [00] ...done
171111 16:45:35 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1651567'
xtrabackup: Stopping log copying thread.
171111 16:45:35 >> log scanned up to (1651567)
171111 16:45:35 Executing UNLOCK TABLES
171111 16:45:35 All tables unlocked
171111 16:45:35 Backup created in directory '/backups/2017-11-11_16-45-33/'
MySQL binlog position: filename 'mysql-bin.000003', position '523596'
171111 16:45:35 [00] Writing backup-my.cnf
171111 16:45:35 [00] ...done
17111116:45:35 [00] Writing xtrabackup_info
171111 16:45:35 [00] ...done
xtrabackup: Transaction log of lsn (1651567) to (1651567) was copied.
171111 16:45:35 completed OK!
~]# cat /backups/2017-11-11_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-11-11_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
171111 18:29:03 completed OK!
4、查看备份文件，备份文件会自动在一个自动创建的时间目录里面存放
备注：可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录
[root@localhost ~]# ls /backups/2017-11-11_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-11-11_16-45-33/ 172.18.70.50:root/
备库进行还原测试操作
备注：首先注意innodb_file_per_table=ON此参数的设置是否正确
1、备库也要先xtrabackup的程序，yum安装
注意：恢复不用启动MySQL
2、~]# innobackupex --copy-back /backups/2017-11-11_16-45-33/
171111 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)
171111 05:49:11 [01] Copying ib_logfile0 to /data/mysql/ib_logfile0
171111 05:49:11 [01] ...done
171111 05:49:11 [01] Copying ib_logfile1 to /data/mysql/ib_logfile1
......
171111 05:49:12 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/xtrabackup_binlog_pos_innodb
171111 05:49:12 [01] ...done
171111 05:49:12 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1
171111 05:49:13 [01] ...done
171111 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
hellodb
ibdata1
ib_logfile0
ib_logfile1
ibtmp1
mydb
mysql
performance_schema
test
testdb
xtrabackup_binlog_pos_innodb
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-11-11_17-40-01
3、查看数据目录
[root@localhost ~]# less /backups/2017-11-11_17-4
2017-11-11_17-40-01/ 2017-11-11_17-43-14/
[root@localhost ~]# less /backups/2017-11-11_17-43-14/
[root@localhost ~]# cat /backups/2017-11-11_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-11-11_17-40-01
innobackupex --apply-log --redo-only /backups/2017-11-11_17-40-01 --incremental-dir=/backups/2017-11-11_17-43-14
5、此时进行数据查看
cat /backups/2017-11-11_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-11-11_17-40-01/
7、查看还原后状态
root@localhost ~]# cd /data/mysql/
[root@localhost mysql]# ll -lh
总用量 19M
drwxr-x--- 2 root root 4.0K 11月 11 17:48 hellodb
-rw-r----- 1 root root 18M 11月 11 17:48 ibdata1
drwxr-x--- 2 root root 4.0K 11月 11 17:48 mydb
drwxr-x--- 2 root root 4.0K 11月 11 17:48 mysql
drwxr-x--- 2 root root 4.0K 11月 11 17:48 performance_schema
drwxr-x--- 2 root root 4.0K 11月 11 17:48 test
drwxr-x--- 2 root root 4.0K 11月 11 17:48 testdb
-rw-r----- 1 root root 38 11月 11 17:48 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 516 11月 11 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</pre>
网友评论