多实例部署、开机自启、xtrabackup备份与恢复

一、mysql多实例部署

• 1.1 下载二进制格式的mysql软件包

[root@centos2 ~]# cd /usr/src/
wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

• 1.2 创建用户和组

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

• 1.3 解压软件至/usr/local/

[root@centos2~]# cd /usr/src/
[root@centos2 src]# tar -xvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@centos2 ~]# cd /usr/local/
[root@centos2 local]# ls
bin  etc  games  include  lib  lib64  libexec   mysql-5.7.35-linux-glibc2.12-x86_64  sbin  share  src

[root@centos2 local]# ln -s mysql-5.7.35-linux-glibc2.12-x86_64/  mysql

[root@centos2 local]# ll
总用量 0
drwxr-xr-x. 2 root root   6 8月  12 2018 bin
drwxr-xr-x. 2 root root   6 8月  12 2018 etc
drwxr-xr-x. 2 root root   6 8月  12 2018 games
drwxr-xr-x. 2 root root   6 8月  12 2018 include
drwxr-xr-x. 2 root root   6 8月  12 2018 lib
drwxr-xr-x. 2 root root   6 8月  12 2018 lib64
drwxr-xr-x. 2 root root   6 8月  12 2018 libexec
lrwxrwxrwx. 1 root root  36 8月  29 20:03 mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 8月  29 20:01 mysql-5.7.35-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 8月  12 2018 sbin
drwxr-xr-x. 5 root root  49 7月  13 14:27 share
drwxr-xr-x. 2 root root   6 8月  12 2018 src

• 1.4 修改目录/usr/local/mysql的属主属组

[root@centos2~]# chown -R mysql.mysql /usr/local/mysql
[root@centos2 ~]# ll /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 8月  29 20:03 /usr/local/mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/

• 1.5 配置环境变量

[root@centos2 ~]# echo  'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos2 ~]# . /etc/profile.d/mysql.sh
[root@centos2 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

• 1.6 创建各实例数据存放的目录

[root@centos2 ~]# mkdir -p /opt/data/{3306,3307,3308}

[root@centos2 ~]# chown -R mysql.mysql /opt/data/

[root@centos2 ~]# ll /opt/data/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 8月  29 20:07 3306
drwxr-xr-x. 2 mysql mysql 6 8月  29 20:07 3307
drwxr-xr-x. 2 mysql mysql 6 8月  29 20:07 3308


[root@centos2 ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308

3 directories, 0 files

• 1.7初始化各实例

//初始化3306实例
[root@centos2 ~]# mysqld --initialize --datadir=/opt/data/3306  --user=mysql
2021-08-29T12:11:53.510376Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:11:53.666927Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T12:11:53.706070Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T12:11:53.761882Z 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: 4c408b15-08c2-11ec-86fd-000c295fa2f2.
2021-08-29T12:11:53.762550Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T12:11:54.205295Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T12:11:54.205306Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T12:11:54.205688Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T12:11:54.386049Z 1 [Note] A temporary password is generated for root@centos2: HTQQcyu#:6xs
[root@centos2 ~]# echo 'HTQQcyu#:6xs' > 3306_pass



//初始化3307实例
[root@centos2 ~]# mysqld --initialize --datadir=/opt/data/3307  --user=mysql
2021-08-29T12:12:53.503406Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:12:53.664178Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T12:12:53.687291Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T12:12:53.743808Z 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: 70010f17-08c2-11ec-a1a2-000c295fa2f2.
2021-08-29T12:12:53.744996Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T12:12:54.528046Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T12:12:54.528061Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T12:12:54.528425Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T12:12:54.708440Z 1 [Note] A temporary password is generated for root@centos2: tjSZYUVWw1),
[root@centos2 ~]# echo 'tjSZYUVWw1),' > 3307_pass



//初始化3308实例
[root@centos2 ~]# mysqld --initialize --datadir=/opt/data/3308  --user=mysql
2021-08-29T12:13:42.955498Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:13:43.146589Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T12:13:43.183020Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T12:13:43.250835Z 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: 8d833b78-08c2-11ec-aac5-000c295fa2f2.
2021-08-29T12:13:43.251495Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T12:13:43.685506Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T12:13:43.685520Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T12:13:43.685863Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T12:13:44.107651Z 1 [Note] A temporary password is generated for root@centos2: 4j3:F99l%syh

[root@centos2 ~]# echo '4j3:F99l%syh' > 3308_pass

• 1.8 安装perl

[root@centos2 ~]# yum -y install perl

• 1.9 配置配置文件/etc/my.cnf

[root@centos2 ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

• 1.10 启动各实例

[root@centos2 ~]# mysqld_multi start 3306
[root@centos2 ~]# mysqld_multi start 3307
[root@centos2 ~]# mysqld_multi start 3308
[root@centos2 ~]# ss -antl
State      Recv-Q Send-Q     Local Address:Port                    Peer Address:Port
LISTEN     0      128                    *:22                                 *:*
LISTEN     0      100            127.0.0.1:25                                 *:*
LISTEN     0      80                    :::3307                              :::*
LISTEN     0      80                    :::3308                              :::*
LISTEN     0      128                   :::22                                :::*
LISTEN     0      100                  ::1:25                                :::*
LISTEN     0      80                    :::3306                              :::*

• 1.11 初始化密码

[root@centos2 ~]# ls
3306_pass  3307_pass  3308_pass  anaconda-ks.cfg
[root@centos2 ~]# cat 3306_pass
HTQQcyu#:6xs
[root@centos2 ~]# mysql -uroot -p'HTQQcyu#:6xs' -S /tmp/mysql3306.sock
mysql: [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.7.35

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> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> quit
Bye



[root@centos2 ~]# cat 3307_pass
tjSZYUVWw1),
[root@centos2 ~]# mysql -uroot -p'tjSZYUVWw1),' -S /tmp/mysql3307.sock -e 'set password = password("123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.


[root@centos2 ~]# cat 3308_pass
4j3:F99l%syh
[root@centos2 ~]# mysql -uroot -p'4j3:F99l%syh' -S /tmp/mysql3308.sock -e 'set password = password("123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.

• 1.12验证登录

[root@centos2 ~]# mysql -uroot -pgf123! -h127.0.0.1 -P3306
mysql: [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 15
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> 

• 1.13 自启动

首先要在你的安装目录里找到mysqld_multi.server文件。

[root@centos2 support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server

然后将它复制到/etc/init.d/目录下

[root@centos2 ~]# ls /etc/init.d/
functions  mysqld_multi  netconsole  network  README

在mysqld_multi中的配置添加一个环境变量

export PATH=/usr/local/mysql/bin:$PATH

重启reboot，重置环境,开机之后开启服务

[root@centos2 ~]# service mysqld_multi start
[root@centos2 ~]# ss - antl

State      Recv-Q Send-Q     Local Address:Port                    Peer Address:Port
LISTEN     0      128                    *:22                                 *:*
LISTEN     0      100            127.0.0.1:25                                 *:*
LISTEN     0      80                    :::3307                              :::*
LISTEN     0      80                    :::3308                              :::*
LISTEN     0      128                   :::22                                :::*
LISTEN     0      100                  ::1:25                                :::*
LISTEN     0      80                    :::3306                              :::*

二、xtrabackup备份与恢复

2.1 xtrabackup 介绍

MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。

Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

2.2 xtrabackup 优点

• 备份速度快，物理备份可靠
• 备份过程不会打断正在执行的事务（无需锁表）
• 能够基于压缩等功能节约磁盘空间和流量
• 自动备份校验
• 还原速度快
• 可以流传将备份传输到另外一台机器上
• 在不增加服务器负载的情况备份数据

2.3 xtrabackup备份原理

（1）innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；

（2）xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）

（4）xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）

（5）innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。

（6）当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；

（7）xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；

（8）innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；

（9）最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。

三、xtrabackup的安装部署以及备份恢复实现

3.1 xtrabackup的安装

[root@centos2 ~]# wget https://repo.percona.com/yum/release/7/RPMS/x86_64/percona-xtrabackup-2.3.10-1.el7.x86_64.rpm

[root@centos2 ~]# yum -y install percona-xtrabackup-2.3.10-1.el7.x86_64.rpm 
已加载插件：fastestmirror
正在检查 percona-xtrabackup-2.3.10-1.el7.x86_64.rpm: percona-xtrabackup-2.3.10-1.el7.x86_64
percona-xtrabackup-2.3.10-1.el7.x86_64.rpm 将被安装
正在解决依赖关系
--> 正在检查事务
.......
[root@centos2 ~]# rpm -qa |grep xtrabackup
percona-xtrabackup-2.3.10-1.el7.x86_64

3.2 Xtrabackup中主要包含的两个工具：

• xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
• innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。 常用选项:

选项	选项
–host	指定主机
–user	指定用户名
–password	指定密码
–port	指定端口
–databases	指定数据库
–incremental	创建增量备份
–incremental-basedir	指定包含完全备份的目录
–incremental-dir	指定包含增量备份的目录
–apply-log	对备份进行预处理操作

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

选项	注释
–redo-only	不回滚未提交事务
–copy-back	恢复备份目录

使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件，这些文件会被保存到一个以时间命名的目录当中。在备份的同时，innobackupex还会在备份目录中创建如下文件：

(1) 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息：

每个InnoDB页(通常为16k大小)
都会包含一个日志序列号，即LSN，LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info – mysql服务器当前正在使用的二进制日志文件及备份这一刻位置二进制日志时间的位置。

(3)xtrabackup_binlog_pos_innodb – 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary – 备份中用到的xtrabackup的可执行文件；

(5)backup-my.cnf – 备份命令用到的配置选项信息：

在使用innobackupex进行备份时，还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录：如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

mysql> CREATE USER 'bkpuser'@'centos2' IDENTIFIED BY '123456';　　#创建用户
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser';　　#回收此用户所有权限
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'centos2';　　#授权刷新、锁定表、用户查看服务器状态
mysql> FLUSH PRIVILEGES;　　#刷新授权表

注意：备份时需启动MySQL,恢复时需关闭MySQL,清空mysql数据目录且不能重新初始化,恢复数据后应该立即进行一次完全备份

3.3 实例

全量备份+增量备份

基本语法:innobackupex --user=root(用户) --password=密码 /path/to/BACKUP

• –defaults-file=/etc/my.cnf 指定mysql的配置文件my.cfg，如果指定则必须是第一个参数。
• /path/to/BACKUP-DIR/指定备份所存放的目标目录，备份过程会创建一个以当时备份时间命名的目录存放备份文件。

(1)创建备份目录，full为完全备份目录，inc为增量备份目录

[root@centos2 ~]# mkdir -p/opt/mysqlbackup/{full,inc}

(2)进行全量备份并把它放在/opt/mysqlbackup/full/下

[root@centos2 ~]# innobackupex --user=root--password=xu1 /opt/mysqlbackup/full/

MySQL binlog position:filename 'mysql-bin.000004', position '107'

210827 21:03:23 [00]Writing backup-my.cnf

210827  21:03:23 [00]        ...done

210827 21:03:23 [00]Writing xtrabackup_info

210827  21:03:23 [00]        ...done

xtrabackup: Transactionlog of lsn (1595675) to (1595675) was copied.

210827  21:03:24 completedOK!

(3)查看全量备份的文件

[root@centos2 ~]# ls /opt/mysqlbackup/full  

2021-08-27_21-03-06

[root@centos2 ~]# ls /opt/mysqlbackup/full/2021-08-27_21-03-06/

backup-my.cnf  mysql               xtrabackup_binlog_info  xtrabackup_info

ibdata1        performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

(4)备份数据库的用户需要具有相应权限.

mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 user 'bkpuser'@'centos2'identified by '123456';

Query OK, 0 rows affected(0.06 sec)

 

mysql> revoke all privileges,grantoption from 'bkpuser'@'centos2';

Query OK, 0 rows affected(0.00 sec)

 

mysql>  grant reload,lock tables,replication client,process on *.* to 'bkpuser'@'centos2';

Query OK, 0 rows affected(0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected(0.05 sec)

(5)进行增量之前的准备 查看日志位置

[root@centos2 ~]# cat/opt/mysqlbackup/full/2021-08-27_21-03-06/xtrabackup_binlog_info

mysql-bin.000004   107

(6)登录mysql数据库，进行操作

[root@centos2 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 benet;

Query OK, 1 row affected(0.00 sec)

 

mysql> use benet

Database changed

mysql> create table tb1 (id int,name char(16) not null);

Query OK, 0 rows affected(0.05 sec)

 

mysql> insert into tb1 values(1,'zhangsan');

Query OK, 1 row affected(0.02 sec)

 

mysql> insert into tb1 values(2,'lisi');

Query OK, 1 row affected(0.02 sec)

 

mysql> select * from tb1;

+------+----------+

| id   | name    |

+------+----------+

|    1 | zhangsan |

|    2 | lisi    |

+------+----------+

2 rows in set (0.00 sec)

mysql> quit

Bye

增量备份二进制文件

[root@centos2 ~]# mysqlbinlog --start-position=107 /usr/local/mysql/data/mysql-bin.000004> /opt/mysqlbackup/inc/`date +%F`.sql

[root@centos2 ~]# ls/opt/mysqlbackup/inc/

2021-08-27.sql

模拟数据库损坏

[root@centos2 ~]# mv /usr/local/mysql/data/*  /tmp/

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。

(7)innobakupex命令的–apply-log选项可用于实现上述功能。如下面的命令：

[root@centos2 ~]# innobackupex --apply-log/opt/mysqlbackup/full/2021-08-27_21-03-06/

.........

InnoDB: File './ibtmp1'size is now 12 MB.

InnoDB: 96 redo rollbacksegment(s) found. 1 redo rollback segment(s) are active.

InnoDB: 32 non-redorollback segment(s) are active.

InnoDB: 5.7.13 started;log sequence number 1595925

xtrabackup: startingshutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimizethread exiting.

InnoDB: Startingshutdown...

InnoDB: Shutdowncompleted; log sequence number 1595944

210827 21:33:32 completedOK!

(8)使用innobakupex命令的 --copy-back 进行拷贝

[root@centos2 ~]# innobackupex --copy-back/opt/mysqlbackup/full/2021-08-27_21-03-06/

...........

210827 21:36:07 [01]        ...done

210827 21:36:07 [01]Copying ./performance_schema/events_waits_current.frm to/usr/local/mysql/data/performance_schema/events_waits_current.frm

210827 21:36:07 [01]        ...done

210827 21:36:07 completedOK!

(9)查看结果

[root@centos2 ~]# ll /usr/local/mysql/data/

总用量 40972

-rw-r-----. 1 root root18874368 8月  27 21:36 ibdata1

-rw-r-----. 1 rootroot  5242880 8月  27 21:36 ib_logfile0

-rw-r-----. 1 rootroot  5242880 8月  2721:36 ib_logfile1

-rw-r-----. 1 root root12582912 8月  27 21:36 ibtmp1

drwxr-x---. 2 rootroot     4096 8月  27 21:36 mysql

drwxr-x---. 2 rootroot     4096 8月  27 21:36 performance_schema

-rw-r-----. 1 rootroot      478 8月  27 21:36 xtrabackup_info

(10) 修改属主属组

[root@centos2 ~]# chown -R mysql:mysql/usr/local/mysql/data/

(11)kill 掉mysql 的进程

[root@centos2 ~]# killall mysqld

(12)再重启

[root@centos2 ~]# service mysqld start

Starting MySQL..                                           [确定]

(13)登录查看

[root@centos2 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQLmonitor.  Commands end with ; or \g.

Your MySQL connection idis 1

Server version: 5.5.22-logSource distribution

 

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

 

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

 

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

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.01 sec)

我们可以看到在完全备份后创建的数据并没有恢复，这时就需要用增量备份来还原
为了避免产生大量的二进制日志，可以把二进制日志关掉

mysql> set sql_log_bin=0;

Query OK, 0 rows affected(0.00 sec)

读取增量备份文件

mysql> source/opt/mysqlbackup/inc/2021-08-27.sql

Query OK, 0 rows affected(0.00 sec)

 

Query OK, 0 rows affected(0.00 sec)

 

Query OK, 0 rows affected(0.00 sec)

 

Query OK, 0 rows affected (0.00sec)

mysql> set sql_log_bin=1;

Query OK, 0 rows affected(0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| benet              |

| mysql              |

| performance_schema |

+--------------------+

4 rows in set (0.00 sec)

 

mysql> use benet

Database changed

mysql> select * from tb1;

+------+----------+

| id   | name    |

+------+----------+

|    1 | zhangsan |

|    2 | lisi    |

+------+----------+

2 rows in set (0.01 sec)