MySql进阶管理备份操作和Xtrabackup使用

mysql进阶

1.二进制格式mysql安装:

 

 

下载软件包：
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
创建用户和组：

[root@chouyu]#cd /usr/src

[root@chouyu src]# groupadd -r mysql
[root@chouyu src]# useradd -M -s /sbin/nologin -g mysql mysql

解压软件到/usr/local
[root@chouyu src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz.2 -C /usr/local/

创建软连接：
[root@chouyu local]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64/'

修改属组属主：
[root@chouyu local]# chown mysql:mysql /usr/local/mysql

配置环境变量：
[root@chouyu local]# ls /usr/local/mysql
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@chouyu local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@chouyu local]# . /etc/profile.d/mysql.sh 
[root@chouyu local]# echo $PATH
/usr/local/mysql/bin:/usr/local/httpd/bin:/usr/local/httpd-2.4.46/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@chouyu local]# 

创建数据存放目录：
[root@chouyu local]# mkdir /opt/data
[root@chouyu local]# chown mysql:mysql /opt/data/
[root@chouyu local]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 12月 28 05:35 data
[root@chouyu local]# 

启动数据库：
[root@localhost ~]# service mysqld start

初始化数据库：

[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2020-12-28T11:13:43.830403Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-28T11:13:44.093744Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-28T11:13:44.121143Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-28T11:13:44.127449Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bf799599-48fd-11eb-95e7-000c295fced8.
2020-12-28T11:13:44.128099Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-12-28T11:13:44.629165Z 0 [Warning] CA certificate ca.pem is self signed.
2020-12-28T11:13:44.822939Z 1 [Note] A temporary password is generated for root@localhost: D8Gd%cXojuv3
[root@localhost ~]#

生成配置文件：

　　[root@localhost ~]# vim /etc/my.cnf
　　[root@localhost ~]# cat /etc/my.cnf
　　[mysql]
　　basedir = /usr/local/mysql
　　datadir = /opt/data
　　socket = /tmp/mysql.sock
　　port = 3306
　　pid-file = /opt/data/mysql.pid
　　user = mysql
　　skip-name-resolve

配置服务启动脚本：
[root@localhost init.d]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost init.d]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost init.d]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld


重置密码：
[root@localhost ~]# mysql -uroot -p
mysql> set password =password ('123456')
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>

2.MySQL配置文件

• 存放路径/etc/my.cnf

• 关于.my.cnf:存放于家目录，多应用于脚本，不会输出登录步骤

[root@localhost ~]# vim .my.cnf

[client]
user=root
password=123456

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

 

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

 

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析.使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求

 

数据库破解密码

写入跳过授权：
[root@localhost ~]# vim /etc/my.cnf 
...
skip-grant-tables

重启数据库服务：
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

进入数据库：

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 
修改数据库登录密码为：123321123

　　mysql> use mysql
　　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 user\G //查看user表
　　*************************** 1. row ***************************
　　Host: localhost
　　User: root
　　Select_priv: Y
　　Insert_priv: Y
　　Update_priv: Y

....

     mysql>update user set authentication_string=password('123321123')where User='root' and Host='localhost';
    Query OK, 1 row affected, 1 warning (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 1
    mysql> quit
    Bye
    [root@localhost ~]# vim /etc/my.cnf    //删除skip-grant-tables
    [root@localhost ~]# service mysqld restart
    Shutting down MySQL.... SUCCESS! 
    Starting MySQL. SUCCESS! 
    [root@localhost ~]# mysql -uroot -p123321123

3.MYSQL备份

备份方案

 

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

这里一般我们只会使用全量备份加差异备份

常用备份工具

mysqldump

• -u:指定用户
• -h指定主机
• -p指定密码

数据库全量备份：
[root@localhost ~]# mysqldump -uroot -p123321123 --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

恢复数据使用:
[root@localhost ~]# mysql -uroot -p123321123 < all.sql

 全量备份到文件all.sql

//进入数据库
[root@localhost ~]# mysql -uroot -p123321123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> create database chouyu;
Query OK, 1 row affected (0.00 sec)

mysql> use chouyu;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.36 sec)

mysql> insert student(name,age)values('tom',12),('wangwu',25),('liliu',19);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> quit
Bye

//全量备份数据库到all.sql
[root@localhost ~]# mysqldump -uroot -p123321123 --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all.sql  anaconda-ks.cfg  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# file all.sql 
all.sql: UTF-8 Unicode text, with very long lines
[root@localhost ~]# mysql -uroot -p123321123 -e 'drop database chouyu;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123321123 < all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123321123 -e 'select * from chouyu.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.

//查看恢复的数据
[root@localhost ~]# mysql -uroot -p123321123 -e 'select * from chouyu.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   12 |
|  2 | wangwu |   25 |
|  3 | liliu  |   19 |
+----+--------+------+
[root@localhost ~]#

 在chouyu数据库中创建表student1,student2，向student1插入数据，备份表student1，备份文件为table_student1.sql

语法：

mysqldump [OPTIONS] database [tables ...] mysqldump [OPTIONS] --all-databases [OPTIONS] mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

常用的OPTIONS：

-uUSERNAME //指定数据库用户名

-hHOST //指定服务器主机，请使用ip地址

-pPASSWORD //指定数据库用户的密码

-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

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

mysql> use chouyu;
Database changed

mysql> create table student1(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
Query OK, 0 rows affected (0.36 sec)

mysql> insert student1(name,age) values('zhangsan',18),('tom',15),('lisi',19),('xiaoming',12);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table student2(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_chouyu |
+------------------+
| student1         |
| student2         |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from student1;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   18 |
|  2 | tom      |   15 |
|  3 | lisi     |   19 |
|  4 | xiaoming |   12 |
+----+----------+------+
4 rows in set (0.00 sec)

//备份chouyu中所有的表到文件table_school.sql
[root@localhost ~]# mysqldump -uroot chouyu student1 > table_student1.sql

//创建库information，在里面创建表teacher并插入数据，给数据库chouyu和information全备份到文件database.sql
mysql> create database information;
Query OK, 1 row affected (0.00 sec)

mysql> use information;
Database changed
mysql> create table teacher(id int not null primary key auto_increment,name varchar(100)not null,age tinyint(4));
Query OK, 0 rows affected (0.37 sec)

mysql> insert teacher(name,age) values('chouyu',31),('xixi',43),('azhe',28);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> exit
Bye

[root@localhost ~]# mysqldump -uroot --databases chouyu information > database.sql

删除库school，使用备份文件table_student1.sql恢复

mysql> drop database chouyu;
Query OK, 2 rows affected (0.24 sec)

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

mysql> source table_student1.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
....

mysql> show tables;
+------------------+
| Tables_in_chouyu |
+------------------+
| student1 |
+------------------+
1 row in set (0.00 sec)

 

删除库chouyu和information，使用备份文件database.sql恢复两个库的数据。

mysql> drop database chouyu;
Query OK, 1 row affected (0.35 sec)

mysql> drop database information;
Query OK, 1 row affected (0.00 sec)

[root@localhost ~]#  mysql -uroot -p123 < database.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

差异备份

先完全备份现在状态的所有数据库，往表student2里插入数据，再更改其中一项数据，再删除库chouyu

 

[root@localhost ~]# vi /etc/my.cnf
....
log-bin=mysql_bin  //开启二进制日志功能
server-id=1    //设置服务器标识符
 
[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

 [root@localhost ~]# rm -rf *.sql

[root@localhost ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202012310225.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# 

mysql> use chouyu;
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 student2(name,age) values('tom',18),('jerry',20),('lisi',21);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update student2 set age=25 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> drop database chouyu;
Query OK, 2 rows affected (0.00 sec)

我们要恢复先前库chouyu里的所有数据（包括之前新增的数据），先找到日志存放路径，之后立刻刷新日志(刷新日志方便我们寻找要恢复的时间点)，然后恢复完全备份，最后查找删除库命令位置并恢复差异备份。

[root@localhost ~]# ls /opt/data
auto.cnf         ib_buffer_pool  information                mysql.pid           server-key.pem
ca-key.pem       ibdata1         localhost.localdomain.err  performance_schema  sys
ca.pem           ib_logfile0     mysql                      private_key.pem
client-cert.pem  ib_logfile1     mysql_bin.000003           public_key.pem
client-key.pem   ibtmp1          mysql_bin.index            server-cert.pem
[root@localhost ~]# mysqladmin -uroot flush-logs
[root@localhost ~]# ls /opt/data
auto.cnf         ib_buffer_pool  information                mysql_bin.index     server-cert.pem
ca-key.pem       ibdata1         localhost.localdomain.err  mysql.pid           server-key.pem
ca.pem           ib_logfile0     mysql                      performance_schema  sys
client-cert.pem  ib_logfile1     mysql_bin.000003           private_key.pem
client-key.pem   ibtmp1          mysql_bin.000004           public_key.pem
[root@localhost ~]# mysql -uroot -p123 < all-202012310225.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql

检查误删数据库的位置在什么地方

mysql> show binlog events in 'mysql_bin.000003'\G

*************************** 
14. row ***************************
   Log_name: mysql_bin.000003
        Pos: 799
 Event_type: Query
  Server_id: 1
End_log_pos: 897
       Info: drop database chouyu
 //使用mysqlbinlog恢复差异备份

[root@localhost ~]# mysqlbinlog --stop-position=799 /opt/data/mysql_bin.000003|mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# 

mysql> use chouyu;
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 student2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisi  |   25 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> 

增量备份

下载Xtrabackup包，准备环境。

 

XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar

使用innobackupex命令全量备份

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar

[root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar 

yum -y install percona*

innobackupex --user=root --password=123 --host=192.168.122.134 /backups/ 进行全量备份

#语法解释说明：
#--user=root 指定备份用户
#--password=123456  指定备份用户密码
#--host　　指定主机
#/backups　　指定备份目录

[root@localhost backups]# ll
总用量 0
drwxr-x---. 2 root root 6 12月 30 14:10 2020-12-30_14-10-47

总结全库备份与恢复三步曲：

a.　innobackupex全量备份，并指定备份目录路径；

b.　在恢复前，需要使用--apply-log参数先进行合并数据文件，确保数据的一致性要求；

c.　恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。

 

删除数据库所有文件再恢复

[root@localhost backups]# /etc/init.d/mysqld stop     //停止服务
Shutting down MySQL... SUCCESS! 


innobackupex --apply-log /backups/2020-12-29_21-41-12/   //合并数据，使数据文件处于一致性的状态

[root@localhost backups]#  innobackupex --copy-back /backups/2020-12-30_14-10-47/
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1 
xtrabackup: recognized client arguments: 
201230 14:16:00 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!".

//这里显示ok就是可以了

[root@localhost backups]# ll /opt/data/
总用量 111528
-rw-r-----. 1 mysql mysql       56 12月 30 05:53 auto.cnf
-rw-------. 1 mysql mysql     1680 12月 30 05:53 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 12月 30 05:53 ca.pem
drwxr-x---. 2 mysql mysql      100 12月 30 13:31 chouyu
-rw-r--r--. 1 mysql mysql     1112 12月 30 05:53 client-cert.pem
-rw-------. 1 mysql mysql     1680 12月 30 05:53 client-key.pem
-rw-r-----. 1 mysql mysql      814 12月 30 14:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月 30 14:14 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 30 14:14 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 30 05:53 ib_logfile1
drwxr-x---. 2 mysql mysql       58 12月 30 13:31 information
-rw-r-----. 1 mysql mysql    31176 12月 30 14:14 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 12月 30 13:31 mysql
-rw-r-----. 1 mysql mysql      944 12月 30 13:30 mysql_bin.000003
-rw-r-----. 1 mysql mysql   845454 12月 30 14:14 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 12月 30 13:30 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 12月 30 05:53 performance_schema
-rw-------. 1 mysql mysql     1680 12月 30 05:53 private_key.pem
-rw-r--r--. 1 mysql mysql      452 12月 30 05:53 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 12月 30 05:53 server-cert.pem
-rw-------. 1 mysql mysql     1676 12月 30 05:53 server-key.pem
drwxr-x---. 2 mysql mysql     8192 12月 30 05:53 sys
[root@localhost backups]# 

//这里可以看到东西都已经恢复了

[root@localhost backups]# chown -R mysql.mysql /opt/data/   //修改属组属主
[root@localhost backups]# /etc/init.d/mysqld start   //启动服务
Starting MySQL. SUCCESS! 
[root@localhost backups]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chouyu             |
| information        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost backups]# 

增量备份

[root@localhost backups]# innobackupex --user=root --password=123 --host=192.168.122.134 /backups/   //全量备份
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1 
xtrabackup: recognized client arguments: 
201230 14:18: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!".


[root@localhost backups]# ll /backups/
总用量 0
drwxr-x---. 2 root root 6 12月 30 14:18 2020-12-30_14-18-33


[root@localhost backups]# innobackupex --user=root --password=123 --host=192.168.122.134 --incremental /backups/ --incremental-basedir=/backups/2020-12-30_14-18-33/  //进行增量备份
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1 
xtrabackup: recognized client arguments: 
201230 14:20:52 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!".

[root@localhost backups]# ll
总用量 0
drwxr-x---. 2 root root 6 12月 30 14:18 2020-12-30_14-18-33  //全量备份数据
drwxr-x---. 2 root root 6 12月 30 14:20 2020-12-30_14-20-52  //增量备份数据

 

使用增量数据恢复数据

[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@localhost ~]# rm -rf /opt/data/
//合并全备数据目录，确保数据的一致性
[root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-30_14-
2020-12-30_14-18-33/ 2020-12-30_14-20-52/ 
//将增量备份数据合并到全备数据目录当中
[root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-30_14-18-33/
xtrabackup: recognized server arguments: 
xtrabackup: recognized client arguments: 
201230 14:25:25 innobackupex: Starting the apply-log operation

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

//恢复数据
[root@localhost ~]# innobackupex --copy-back /backups/2020-12-30_14-18-33/
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1 
xtrabackup: recognized client arguments: 
201230 14:27:49 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!".
//查看恢复后的数据
[root@localhost backups]# ll /opt/data/
-rw-r-----. 1 mysql mysql       56 12月 30 05:53 auto.cnf
-rw-------. 1 mysql mysql     1680 12月 30 05:53 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 12月 30 05:53 ca.pem
drwxr-x---. 2 mysql mysql      100 12月 30 13:31 chouyu
-rw-r--r--. 1 mysql mysql     1112 12月 30 05:53 client-cert.pem
-rw-------. 1 mysql mysql     1680 12月 30 05:53 client-key.pem
-rw-r-----. 1 mysql mysql      814 12月 30 14:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月 30 14:14 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 30 14:14 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 30 05:53 ib_logfile1
drwxr-x---. 2 mysql mysql       58 12月 30 13:31 information
-rw-r-----. 1 mysql mysql    31176 12月 30 14:14 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 12月 30 13:31 mysql
-rw-r-----. 1 mysql mysql      944 12月 30 13:30 mysql_bin.000003
-rw-r-----. 1 mysql mysql   845454 12月 30 14:14 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 12月 30 13:30 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 12月 30 05:53 performance_schema
-rw-------. 1 mysql mysql     1680 12月 30 05:53 private_key.pem
-rw-r--r--. 1 mysql mysql      452 12月 30 05:53 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 12月 30 05:53 server-cert.pem
-rw-------. 1 mysql mysql     1676 12月 30 05:53 server-key.pem
drwxr-x---. 2 mysql mysql     8192 12月 30 05:53 sys

//修改属主属组
[root@localhost ~]# chown -R mysql.mysql /opt/data/
//启动服务
[root@localhost ~]# /etc/init.d/mysqld start
//查看恢复后的数据
mysql> use chouyu;
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 student2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisi  |   25 |
+----+-------+------+
3 rows in set (0.00 sec)