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
原创文章，作者：N23-苏州-void，如若转载，请注明出处：http://www.178linux.com/71952
评论列表（1条）
总结的非常好，操作过程清晰，加油！！！