系统配置：CentOS7
xtrabackup:
回顾：
mysqldump:
--databases db1 db2 ...
--all-databases
--lock-all-tables
--single-transaction
--master-data={0|1|2}
--flush-logs
常用工具：mysqldump, lvm-snapshot，xtrabackup(percona)
innobackupex：需要MySQL服务处于运行状态
1、先安装percona-toolkit-2.2.12-1.noarch.rpm和percona-xtrabackup-2.2.11-1.el7.x86_64.rpm两个软件
yum install percona-toolkit-2.2.12-1.noarch.rpm和percona-xtrabackup-2.2.11-1.el7.x86_64.rpm
2、先删除数据目录下的二进制文件
[root@localhost mysql]# ls
aria_log.00000001 hellodb ib_logfile0 localhost.log mysql-bin.000001 mysql-bin.000003 mysql.sock test
aria_log_control ibdata1 ib_logfile1 mysql mysql-bin.000002 mysql-bin.index performance_schema
[root@localhost mysql]# rm mysql-bin.* -f
[root@localhost mysql]# ls
aria_log.00000001 aria_log_control hellodb ibdata1 ib_logfile0 ib_logfile1 localhost.log mysql mysql.sock performance_schema test
3、创建个数据备份目录
[root@localhost mysql]# mkdir /mybackups
4、使用innobackupex备份，备份到数据目录/mybackups/里
[root@localhost ~]# innobackupex --user=root /mybackups/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
..................................................
innobackupex: Backup created in directory '/mybackups/2017-03-26_18-15-32'
innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 316
170326 18:15:37 innobackupex: Connection to database server closed
170326 18:15:37 innobackupex: completed OK!
5、进入到数据文件备份目录，查看一些文件
[root@localhost mybackups]# ls
2017-03-26_18-15-32
[root@localhost mybackups]# cd 2017-03-26_18-15-32/
[root@localhost 2017-03-26_18-15-32]# ls
backup-my.cnf hellodb ibdata1 mysql performance_schema test xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile
（1）、保存备份时my.cnf文件的配置信息
[root@localhost 2017-03-26_18-15-32]# cat backup-my.cnf
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5242880
innodb_fast_checksum=OFF
innodb_page_size=16384
innodb_log_block_size=512
（2）、备份时的二进制日志的时间点
[root@localhost 2017-03-26_18-15-32]# cat xtrabackup_binlog_info
mysql-bin.000003 316
（3）、检查点
[root@localhost 2017-03-26_18-15-32]# cat xtrabackup_checkpoints
backup_type = full-backuped#完全备份
from_lsn = 0#从哪个逻辑单元号
to_lsn = 1602106#到哪个逻辑单元号结束
last_lsn = 1602106#最后的lsn号
compact = 0#打包机制
6、演示完整导入过程
（1）、不记录二进制日志
MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.02 sec)
（2）、导入hellodb.sql
MariaDB [(none)]> source /tmp/hellodb.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
（3）、查看数据库已经导入
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
（4）、再打开二进制文件
MariaDB [hellodb]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
（5）、再hellodb的数据库中创建一张表
MariaDB [hellodb]> CREATE TABLE t1(Name char(20));
Query OK, 0 rows affected (0.05 sec)
（6）、删除原先备份的目录
[root@localhost mybackups]# rm -rf 2017-03-26_18-15-32/
（7）、重新使用xtrabackup备份下数据文件，完全备份
[root@localhost ~]# innobackupex --user=root /mybackups/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
170326 19:27:29 innobackupex: Connection to database server closed
170326 19:27:29 innobackupex: completed OK!
（8）、查看数据库表的类型
MariaDB [hellodb]> show table status from hellodb;
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| classes | InnoDB | 10 | Compact | 9 | 1820 | 16384 | 0 | 0 | 10485760 | NULL | 2017-03-26 19:20:22 | NULL | NULL | latin1_swedish_ci | NULL | | |
| t1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 10485760 | NULL | 2017-03-26 19:23:17 | NULL | NULL | latin1_swedish_ci | NULL | | |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)
7、加入不小心把数据目录删除，做一次完整的备份还原
（1）、先停用MariaDB数据库
[root@localhost ~]# systemctl stop mariadb.service
（2）、进入数据目录，删除当前目录下的所有文件
[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# rm -rf ./*
（3）、使用apply-log对数据备份目录做数据整理
[root@localhost mysql]# innobackupex --apply-log /mybackups/2017-03-26_19-27-24/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
InnoDB: Shutdown completed; log sequence number 1610262
170326 19:53:18 innobackupex: completed OK!
（4）、执行数据备份目录的还原
[root@localhost mysql]# innobackupex --copy-back /mybackups/2017-03-26_19-27-24/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
170326 19:58:24 innobackupex: completed OK!
（5）、进入到mysql的数据目录，并查看数据是否已经还原，从下面看数据已经还原
[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ls
hellodb ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test xtrabackup_info
（6）、验证目录的属主和属组,看到mysql的数据库的属主和属组时root用户
[root@localhost mysql]# ll
总用量 28684
drwxr-xr-x. 2 root root 50 3月 26 19:58 hellodb
-rw-r--r--. 1 root root 18874368 3月 26 19:58 ibdata1
-rw-r--r--. 1 root root 5242880 3月 26 19:58 ib_logfile0
-rw-r--r--. 1 root root 5242880 3月 26 19:58 ib_logfile1
drwxr-xr-x. 2 root root 4096 3月 26 19:58 mysql
drwxr-xr-x. 2 root root 4096 3月 26 19:58 performance_schema
drwxr-xr-x. 2 root root 6 3月 26 19:58 test
-rw-r--r--. 1 root root 548 3月 26 19:58 xtrabackup_info
（7）、把mysql数据库数据目录下的所有文件的属主和属组都改为mysql
[root@localhost mysql]# chown -R mysql.mysql ./*
[root@localhost mysql]# ll
总用量 28684
drwxr-xr-x. 2 mysql mysql 50 3月 26 19:58 hellodb
-rw-r--r--. 1 mysql mysql 18874368 3月 26 19:58 ibdata1
-rw-r--r--. 1 mysql mysql 5242880 3月 26 19:58 ib_logfile0
-rw-r--r--. 1 mysql mysql 5242880 3月 26 19:58 ib_logfile1
drwxr-xr-x. 2 mysql mysql 4096 3月 26 19:58 mysql
drwxr-xr-x. 2 mysql mysql 4096 3月 26 19:58 performance_schema
drwxr-xr-x. 2 mysql mysql 6 3月 26 19:58 test
-rw-r--r--. 1 mysql mysql 548 3月 26 19:58 xtrabackup_info
（8）、启动mysql服务，当然这里是mariadb
[root@localhost mysql]# systemctl start mariadb.service
（9）、连上mysql,查看我们备份时的所有数据都在
[root@localhost mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.41-MariaDB-log MariaDB Server
Copyright (c) 2000, 2014, 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 |
| t1 |
+-------------------+
2 rows in set (0.03 sec)
MariaDB [hellodb]>
8、对上一次的备份，做一次增量备份
（1）、先再执行一次备份，完全备份
[root@localhost ~]# innobackupex --user=root /mybackups/
170326 21:07:22 innobackupex: completed OK!
（2）、新的备份又创建了一个目录文件
[root@localhost mybackups]# ls
2017-03-26_19-27-24 2017-03-26_21-07-17
（3）、连接上数据库，在数据库hellodb里创建名为t2的表，并向表里插入两个值
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
MariaDB [hellodb]> CREATE TABLE t2(id int);
Query OK, 0 rows affected (0.02 sec)
MariaDB [hellodb]> insert into t2 values (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
（4）、创建增量备份
[root@localhost mybackups]# innobackupex --incremental /mybackups/ --incremental-basedir=/mybackups/2017-03-26_21-07-17/
（5）、到备份目录里发现增加一个增量备份的文件
[root@localhost mybackups]# ls
2017-03-26_19-27-24 2017-03-26_21-07-17 2017-03-26_21-24-44
阅读(1246) | 评论(0) | 转发(0) |
