Xtrabackup实现MySQL备份

Mysql配置

1.安装

[root@localhost ~]# tar xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz -C /usr/src/
[root@localhost ~]# ln -s /usr/src/mysql-5.6.40-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@localhost ~]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
[root@localhost ~]# cp /usr/src/mysql-5.6.40-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld			#复制启动文件
[root@localhost ~]# chmod +x /etc/init.d/mysqld
[root@localhost ~]# useradd -M -s /sbin/nologin mysql
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/
[root@localhost ~]# cp /usr/src/mysql-5.6.40-linux-glibc2.12-x86_64/support-files/my-default.cnf /etc/my.cnf			#拷贝配置文件
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql		#初始化
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
.. SUCCESS! 
[root@localhost ~]# mysqladmin -uroot password '123456'		#更改root密码
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

mysql> create database temp;
Query OK, 1 row affected (0.00 sec)

mysql> use temp;
Database changedmysql> create table xt (name varchar(12) not null,pwd varchar(32) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into xt values('zhangsan','1');
Query OK, 1 row affected (0.02 sec)

mysql> insert into xt values('lisi','2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into xt values('chengwu','3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from xt;
+----------+-----+
| name     | pwd |
+----------+-----+
| zhangsan | 1   |
| lisi     | 2   |
| chengwu  | 3   |
+----------+-----+
3 rows in set (0.00 sec)

mysql> quit
Bye

Xtrabackup配置

1.安装

[root@localhost ~]# yum -y install perl-DBD-MySQL perl-Digest-MD5
[root@localhost ~]# wget http://ftp.tu-chemnitz.de/pub/linux/dag/redhat/el6/en/x86_64/rpmforge/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
[root@localhost ~]# wget https://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/p/percona-xtrabackup-2.3.6-1.el7.x86_64.rpm
[root@localhost ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm percona-xtrabackup-2.3.6-1.el7.x86_64.rpm 

2.完整备份及恢复

[root@localhost ~]# innobackupex --user=root --password=123456 /backup/mysqlbackup --no-timestamp		#完整备份
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@localhost ~]# rm -rf /usr/local/mysql/data/*		#模拟数据丢失
[root@localhost ~]# innobackupex --user=root --password=123456 --copy-back /backup/mysqlbackup
180712 20:41:54 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.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
Error: datadir must be specified.
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir = /usr/local/mysql/data		#指定数据库路径
[root@localhost ~]# innobackupex --user=root --password=123456 --copy-back /backup/mysqlbackup		#完整备份恢复
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
 ERROR! The server quit without updating PID file (/usr/local/mysql/data/localhost.localdomain.pid).
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/data/
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use temp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from xt;
+----------+-----+
| name     | pwd |
+----------+-----+
| zhangsan | 1   |
| lisi     | 2   |
| chengwu  | 3   |
+----------+-----+
3 rows in set (0.01 sec)

mysql> quit
Bye

3.增量备份

[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use temp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into xt values('cs1','4');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xt values('cs2','5');
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# innobackupex --user=root --password=123456 --incremental /backup/temp --incremental-basedir=/backup/mysqlbackup --no-timestamp			#增量备份

准备恢复完整备份(校验)

[root@localhost ~]# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/mysqlbackup/		#合并之前校验数据

准备恢复增量备份(校验)

[root@localhost ~]# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/mysqlbackup/ --incremental-dir=/backup/temp		#合并之前校验数据
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@localhost ~]# innobackupex --user=root --password=123456 --copy-back /backup/mysqlbackup/
180712 20:49:56 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.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
Original data directory /usr/local/mysql/data is not empty!
[root@localhost ~]# rm -rf /usr/local/mysql/data/*
[root@localhost ~]# innobackupex --user=root --password=123456 --copy-back /backup/mysqlbackup/
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/data
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use temp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from xt;
+----------+-----+
| name     | pwd |
+----------+-----+
| zhangsan | 1   |
| lisi     | 2   |
| chengwu  | 3   |
| cs1      | 4   |
| cs2      | 5   |
+----------+-----+
5 rows in set (0.00 sec)