多实例部署、开机自启、xtrabackup备份与恢复
一、MySQL多实例部署
1.下载二进制包
wget http://https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# ls
anaconda-ks.cfg mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
2.创建用户和组并解压二进制包至/usr/local/下
[root@localhost ~]# tar -xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin etc games include lib lib64 libexec mysql-5.7.35-linux-glibc2.12-x86_64 sbin share src
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.35-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.35-linux-glibc2.12-x86_64/"
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 4月 11 2018 bin
drwxr-xr-x. 2 root root 6 4月 11 2018 etc
drwxr-xr-x. 2 root root 6 4月 11 2018 games
drwxr-xr-x. 2 root root 6 4月 11 2018 include
drwxr-xr-x. 2 root root 6 4月 11 2018 lib
drwxr-xr-x. 2 root root 6 4月 11 2018 lib64
drwxr-xr-x. 2 root root 6 4月 11 2018 libexec
lrwxrwxrwx. 1 root root 36 8月 27 12:42 mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 8月 27 12:41 mysql-5.7.35-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 4月 11 2018 sbin
drwxr-xr-x. 5 root root 49 4月 21 19:36 share
drwxr-xr-x. 2 root root 6 4月 11 2018 src
3.修改目录/usr/local/mysql的属主属组
[root@localhost local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 8月 27 12:42 /usr/local/mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/
4.配置环境变量
[root@localhost local]# cat /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost local]# source /etc/profile.d/mysql.sh
[root@localhost local]#
[root@localhost local]# which mysql
/usr/local/mysql/bin/mysql
5.创建各实例数据存放的目录
[root@localhost opt]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost opt]# ls /opt/data/
3306 3307 3308
6.初始化各实例
初始化3306
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
2021-08-27T12:09:43.957362Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-27T12:09:45.070984Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-27T12:09:45.259869Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-27T12:09:45.368131Z 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: aae5b407-072f-11ec-8cb9-000c2978b180.
2021-08-27T12:09:45.371548Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-27T12:09:46.251041Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-27T12:09:46.251061Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-27T12:09:46.252075Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-27T12:09:47.105510Z 1 [Note] A temporary password is generated for root@localhost: tdX-*ey)Y1_a
[root@localhost ~]# echo 'tdX-*ey)Y1_a' > 3306_pass
[root@localhost ~]# ls
3306_pass anaconda-ks.cfg mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# cat 3306_pass
tdX-*ey)Y1_a
初始化3307
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2021-08-27T12:10:22.121228Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-27T12:10:23.371094Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-27T12:10:23.551015Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-27T12:10:23.988171Z 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: c1eaa7e8-072f-11ec-8eee-000c2978b180.
2021-08-27T12:10:23.991268Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-27T12:10:25.006022Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-27T12:10:25.006044Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-27T12:10:25.014096Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-27T12:10:25.139118Z 1 [Note] A temporary password is generated for root@localhost: OpCs&?ILf0z)
[root@localhost ~]# echo 'OpCs&?ILf0z)' > 3307_pass
初始化3308
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2021-08-27T12:11:08.680649Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-27T12:11:10.064525Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-27T12:11:10.267492Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-27T12:11:10.356165Z 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: dd8ddb31-072f-11ec-9185-000c2978b180.
2021-08-27T12:11:10.357696Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-27T12:11:11.694578Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-27T12:11:11.694596Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-27T12:11:11.695690Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-27T12:11:12.272690Z 1 [Note] A temporary password is generated for root@localhost: nGpdhZUu;48q
^[[A^[[A^[[A[root@loecho 'nGpdhZUu;48q' > 3308_pass
查看保存的密码
[root@localhost ~]# ls
3306_pass 3308_pass mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
3307_pass anaconda-ks.cfg
7.安装perl 并检查是否还有依赖库没有安装
[root@localhost ~]# yum -y install install perl
已加载插件:fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.cn99.com
* extras: mirrors.tuna.tsinghua.edu.cn
* updates: mirrors.cn99.com
[root@localhost ~]# ldd /usr/local/mysql/bin/mysql
linux-vdso.so.1 => (0x00007ffecdbfb000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f543b00b000)
librt.so.1 => /lib64/librt.so.1 (0x00007f543ae03000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f543abff000)
libncurses.so.5 => /lib64/libncurses.so.5 (0x00007f543a9d8000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f543a6d0000)
libm.so.6 => /lib64/libm.so.6 (0x00007f543a3ce000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f543a1b8000)
libc.so.6 => /lib64/libc.so.6 (0x00007f5439deb000)
libtinfo.so.5 => /lib64/libtinfo.so.5 (0x00007f5439bc1000)
/lib64/ld-linux-x86-64.so.2 (0x00007f543b227000
8.配置配置文件/etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /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/3306.sock
pid-file = /opt/date/3306/mysql.pid
log-error = /var/log/mysql_3306.log
[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/3307.sock
pid-file = /opt/date/3307/mysql.pid
log-error = /var/log/mysql_3307.log
[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/3308.sock
pid-file = /opt/date/3308/mysql.pid
log-error = /var/log/mysql_3308.log
9.启动各实例
[root@localhost ~]# mysqld_multi start 3336
[root@localhost ~]# ss -anlt
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 :::*
10.修改密码
[root@localhost ~]# ls
3306_pass 3307_pass 3308_pass anaconda-ks.cfg
[root@localhost ~]# cat 3306_pass
gKRe:h._B2nj
[root@localhost ~]# mysql -uroot -p'gKRe:h._B2nj' -h127.0.0.1 -h3306
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.22
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('xu1');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> quit
Bye
[root@localhost ~]# cat 3307_pass
hSf,.Mou.7WP
[root@localhost ~]# mysql -uroot -phSf,.Mou.7WP -h127.0.0.1 -p3307
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.22
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('xu1');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> quit
Bye
[root@localhost ~]# cat 3308_pass
rXumdcis(3;B
[root@localhost ~]# mysql -uroot -p'rXumdcis(3;B' -h127.0.0.1 -P3308
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 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> set password = password('xu1');
Query OK, 0 rows affected, 1 warning (0.00 sec)
11.验证
[root@localhost ~]# mysql -uroot -pxu1 -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>
二、自启动
//将服务文件拷贝到init.d下,并重命名为mysql
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
//
[root@localhost ~]# ECHO export PATH=/usr/local/mysql/bin:$PATH > /etc/init.d/mysqld
//赋予可执行权限
[root@localhost ~]# chmod +x /etc/init.d/mysqld
//添加服务
[root@localhost ~]# chkconfig --add mysqld
//启动
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
三、xtrabackup备份与恢复
1.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数据表。
1.2 xtrabackup 优点
- 备份速度快,物理备份可靠
- 备份过程不会打断正在执行的事务(无需锁表)
- 能够基于压缩等功能节约磁盘空间和流量
- 自动备份校验
- 还原速度快
- 可以流传将备份传输到另外一台机器上
- 在不增加服务器负载的情况备份数据
1.3 xtrabackup备份原理
- innobackupex启动后,会先fork一个进程,用于启动xtrabackup,然后等待xtrabackup备份ibd数据文件;
- xtrabackup在备份innoDB数据是,有2种线程:redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后,会启动一个redo拷贝的线程,用于从最新的checkpoint点开始顺序拷贝redo.log;再启动ibd数据拷贝线程,进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段,innobackupex进行处于等待状态(等待文件被创建)
- xtrabackup拷贝完成ibd数据文件后,会通知innobackupex(通过创建文件),同时xtrabackup进入等待状态(redo线程依旧在拷贝redo.log)
- innobackupex收到xtrabackup通知后哦,执行FLUSH TABLES WITH READ LOCK(FTWRL),取得一致性位点,然后开始备份非InnoDB文件(如frm、MYD、MYI、CSV、opt、par等格式的文件),在拷贝非InnoDB文件的过程当中,数据库处于全局只读状态。
- 当innobackup拷贝完所有的非InnoDB文件后,会通知xtrabackup,通知完成后,进入等待状态;
- xtrabackup收到innobackupex备份完成的通知后,会停止redo拷贝线程,然后通知innobackupex,redo.log文件拷贝完成;
- innobackupex收到redo.log备份完成后,就进行解锁操作,执行:UNLOCK TABLES;
- 最后innbackupex和xtrabackup进程各自释放资源,写备份元数据信息等,innobackupex等xtrabackup子进程结束后退出。
四、xtrabackup的安装部署以及备份恢复实现
1.xtrabackup的安装
[root@localhost ~]# wget https://repo.percona.com/yum/release/7/RPMS/x86_64/percona-xtrabackup-2.3.10-1.el7.x86_64.rpm
[root@localhost ~]# 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@localhost ~]# rpm -qa |grep xtrabackup
percona-xtrabackup-2.3.10-1.el7.x86_64
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还会在备份目录中创建如下文件:
- 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息:每个InnoDB页(通常为16k大小),都会包含一个日志序列号,即LSN,LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的。
- xtrabackup_binlog_info – mysql服务器当前正在使用的二进制日志文件及备份这一刻位置二进制日志时间的位置
- xtrabackup_binlog_pos_innodb – 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position
- xtrabackup_binary – 备份中用到的xtrabackup的可执行文件;
- backup-my.cnf – 备份命令用到的配置选项信息:
在使用innobackupex进行备份时,还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录:如此一来,innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。
如果要使用一个最小权限的用户进行备份,则可基于如下命令创建此类用户:如果要使用一个最小权限的用户进行备份,则可基于如下命令创建此类用户:
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456'; #创建用户
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser'; #回收此用户所有权限
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; #授权刷新、锁定表、用户查看服务器状态
mysql> FLUSH PRIVILEGES; #刷新授权表
注意:备份时需启动MySQL,恢复时需关闭MySQL,清空mysql数据目录且不能重新初始化,恢复数据后应该立即进行一次完全备份
3. 实例
全量备份+增量备份
基本语法:innobackupex --user=root(用户) --password=密码 /path/to/BACKUP
- –defaults-file=/etc/my.cnf 指定mysql的配置文件my.cfg,如果指定则必须是第一个参数。
- –defaults-file=/etc/my.cnf 指定mysql的配置文件my.cfg,如果指定则必须是第一个参数。
(1)创建备份目录,full为完全备份目录,inc为增量备份目录
[root@localhost ~]# mkdir -p/opt/mysqlbackup/{full,inc}
(2)进行全量备份并把它放在/opt/mysqlbackup/full/下
[root@localhost ~]# 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@localhost ~]# ls /opt/mysqlbackup/full
2021-08-27_21-03-06
[root@localhost ~]# 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'@'localhost'identified by '123456';
Query OK, 0 rows affected(0.06 sec)
mysql> revoke all privileges,grantoption from 'bkpuser'@'localhost';
Query OK, 0 rows affected(0.00 sec)
mysql> grant reload,lock tables,replication client,process on *.* to 'bkpuser'@'localhost';
Query OK, 0 rows affected(0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected(0.05 sec)
(5)进行增量之前的准备 查看日志位置
[root@localhost ~]# cat/opt/mysqlbackup/full/2021-08-27_21-03-06/xtrabackup_binlog_info
mysql-bin.000004 107
(6)登录mysql数据库,进行操作
[root@localhost ~]# 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 xx;
Query OK, 1 row affected(0.00 sec)
mysql> use xx;
Database changed
mysql> create table xk (id int,name char(16) not null);
Query OK, 0 rows affected(0.05 sec)
mysql> insert into xk values(1,'rng');
Query OK, 1 row affected(0.02 sec)
mysql> insert into xk values(2,'fpx');
Query OK, 1 row affected(0.02 sec)
mysql> select * from xk;
+------+----------+
| id | name |
+------+----------+
| 1 | rng |
| 2 | fpx |
+------+----------+
2 rows in set (0.00 sec)
mysql> quit
Bye
增量备份二进制文件
[root@localhost ~]# mysqlbinlog --start-position=107 /usr/local/mysql/data/mysql-bin.000004> /opt/mysqlbackup/inc/`date +%F`.sql
[root@localhost ~]# ls/opt/mysqlbackup/inc/
2021-08-27.sql
模拟数据库损坏
[root@localhost ~]# mv /usr/local/mysql/data/* /tmp/
一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
在准备(prepare)过程结束后,InnoDB表数据已经前滚到整个备份结束的点,而不是回滚到xtrabackup刚开始时的点。
(7)innobakupex命令的–apply-log选项可用于实现上述功能。如下面的命令:
[root@localhost ~]# 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@localhost ~]# 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@localhost ~]# 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@localhost ~]# chown -R mysql:mysql/usr/local/mysql/data/
(11)kill 掉mysql 的进程
[root@localhost ~]# killall mysqld
(12)再重启
[root@localhost ~]# service mysqld start
Starting MySQL.. [确定]
(13)登录查看
[root@localhost ~]# 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;
+--------------------+![请添加图片描述](https://img-blog.csdnimg.cn/f31a8416f0fd421aab52e5022a4bcbb7.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5rGf5Z-O6ICBSw==,size_20,color_FFFFFF,t_70,g_se,x_16)
| Database |
+--------------------+
| information_schema |
| xx |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use benet
Database changed
mysql> select * from xx;
+------+----------+
| id | name |
+------+----------+
| 1 | rng |
| 2 | fpx |
+------+----------+
2 rows in set (0.01 sec)