mysql的多实例部署和xtrabackup备份与恢复

1.mysql多实例部署

软件下载

[root@localhost src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

配置用户和组并解压二进制程序至/usr/local下

[root@localhost src]# groupadd -r mysql #创建组
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql #创建用户并不允许登录

##解压并创建软连接
[root@localhost src]# ls   
debug  kernels  mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# cd /usr/local/
[root@localhost local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.34-linux-glibc2.12-x86_64  share
[root@localhost local]# ln -sv mysql-5.7.34-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.34-linux-glibc2.12-x86_64/'

##修改目录的所属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll -d /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 8月  28 21:44 /usr/local/mysql -> mysql-5.7.34-linux-glibc2.12-x86_64/

##编辑环境变量
[root@localhost profile.d]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost profile.d]# . /etc/profile.d/mysql.sh 
[root@localhost profile.d]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

创建数据存放目录

[root@localhost opt]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost opt]# chown -R mysql.mysql /opt/data
[root@localhost opt]# ll /opt/data/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 8月  28 22:05 3306
drwxr-xr-x. 2 mysql mysql 6 8月  28 22:05 3307
drwxr-xr-x. 2 mysql mysql 6 8月  28 22:05 3308

初始化实列

#3306
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2021-08-29T02:07:55.590188Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T02:07:55.735327Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T02:07:55.765978Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T02:07:55.771323Z 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: ecba00e7-086d-11ec-a65b-000c29114d9c.
2021-08-29T02:07:55.772702Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T02:07:56.314763Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T02:07:56.450617Z 1 [Note] A temporary password is generated for root@localhost: G62,uB3+td/V
#3307
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2021-08-29T02:08:41.479023Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T02:08:41.622614Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T02:08:41.647048Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T02:08:41.712157Z 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: 081c0368-086e-11ec-aa0d-000c29114d9c.
2021-08-29T02:08:41.712871Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T02:08:42.036226Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T02:08:42.196217Z 1 [Note] A temporary password is generated for root@localhost: O,4>doY2gR.1
#3308
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2021-08-29T02:13:40.041901Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T02:13:40.179267Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T02:13:40.204397Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T02:13:40.260113Z 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: ba0ed253-086e-11ec-a111-000c29114d9c.
2021-08-29T02:13:40.261117Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T02:13:40.718606Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T02:13:40.795257Z 1 [Note] A temporary password is generated for root@localhost: 9*7eoP+kqQs7

安装perl

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

配置文件/etc/my.con

[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/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

启动各实列

[root@localhost ~]# mysqld_multi start 3306
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# mysqld_multi start 3307
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# mysqld_multi start 3308
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# ss -antl
State     Recv-Q    Send-Q       Local Address:Port        Peer Address:Port    Process    
LISTEN    0         128                0.0.0.0:22               0.0.0.0:*                  
LISTEN    0         128                   [::]:22                  [::]:*                  
LISTEN    0         80                       *:3306                   *:*                  
LISTEN    0         80                       *:3307                   *:*                  
LISTEN    0         80                       *:3308                   *:* 

初始化密码

[root@localhost ~]# mysql -uroot -p'G62,uB3+td/V' -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 2
Server version: 5.7.34

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

mysql> 

#3307
[root@localhost ~]# mysql -uroot -p'O,4>doY2gR.1' -S /tmp/mysql3307.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.34

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

#3308
[root@localhost ~]# mysql -uroot -p'9*7eoP+kqQs7' -S /tmp/mysql3308.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 2
Server version: 5.7.34

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

多实例设置开机自启

[root@localhost ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld.server
[root@localhost ~]# ls /etc/init.d/
functions  mysqld.server  README
[root@localhost ~]# vim /etc/init.d/mysqld.server 

[root@localhost ~]# vim /etc/init.d/mysqld.server 
[root@localhost ~]# cat /etc/init.d/mysqld.server 
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#

export PATH=/usr/local/mysql/bin/:$PATH  #添加该内容
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
······

[root@localhost ~]# chkconfig --add mysqld.server 

2.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优点

（1）备份速度快，物理备份可靠

（2）备份过程不会打断正在执行的事务（无需锁表）

（3）能够基于压缩等功能节约磁盘空间和流量

（4）自动备份校验

（5）还原速度快

（6）可以流传将备份传输到另外一台机器上

（7）在不增加服务器负载的情况备份数据

2.3 Xtrabackup备份原理

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子进程结束后退出。

2.4 Xtrabackup工具

Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
–host 指定主机
–user 指定用户名
–password 指定密码
–port 指定端口
–databases 指定数据库
–incremental 创建增量备份
–incremental-basedir 指定包含完全备份的目录
–incremental-dir 指定包含增量备份的目录
–apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
–redo-only 不回滚未提交事务
–copy-back 恢复备份目录

2.5 xtrabackup安装

[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

2.6 xtrabackup备份

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

mysql> create database luochuran;   #创建一个库验证后面是否备份成功
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| luochuran          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

##开始备份
[root@localhost ~]# innobackupex --defaults-files=/etc/my.cnf --password=123 --user=root -P3306 --host=127.0.0.1 /opt/backup
xtrabackup: recognized server arguments: 
xtrabackup: recognized client arguments: 
210828 23:07:03 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!".

210828 23:07:03  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=3306' as 'root'  (using password: YES).
210828 23:07:03  version_check Connected to MySQL server
210828 23:07:03  version_check Executing a version check against the server...

# A software update is available:
210828 23:07:06  version_check Done.
210828 23:07:06 Connecting to MySQL server host: 127.0.0.1, user: root, password: set, port: 3306, socket: not set
Using server version 5.7.34
······
210828 23:07:08 [00] Writing /opt/backup/2021-08-28_23-07-03/xtrabackup_info
210828 23:07:08 [00]        ...done
xtrabackup: Transaction log of lsn (2747353) to (2747362) was copied.
210828 23:07:08 completed OK!

##删除库和/opt/date的数据
mysql> drop database luochuran;
Query OK, 0 rows affected (0.01 sec)

[root@localhost ~]# rm -rf /opt/data/*
[root@localhost ~]# 

##停止mysqld
[root@localhost ~]# ss -antl
State                    Recv-Q                   Send-Q                                     Local Address:Port                                      Peer Address:Port                   Process                   
LISTEN                   0                        128                                              0.0.0.0:22                                             0.0.0.0:*                                                
LISTEN                   0                        128                                                 [::]:22                                                [::]:*                                                
LISTEN                   0                        80                                                     *:3306                                                 *:*                                                
LISTEN                   0                        80                                                     *:3307                                                 *:*                                                
LISTEN                   0                        80                                                     *:3308                                                 *:*                                                
[root@localhost ~]# pkill mysqld
[root@localhost ~]# ss -antl
State                    Recv-Q                   Send-Q                                     Local Address:Port                                      Peer Address:Port                   Process                   
LISTEN                   0                        128                                              0.0.0.0:22                                             0.0.0.0:*                                                
LISTEN                   0                        128                                                 [::]:22                                                [::]:*       

##恢复
[root@localhost ~]# innobackupex --copy-back /opt/backup/2021-08-28_23-07-03/
xtrabackup: recognized server arguments: --datadir=/opt/data/3308 
xtrabackup: recognized client arguments: 
210828 23:29:02 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.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39)
210828 23:29:02 [01] Copying ib_logfile0 to /opt/data/3308/ib_logfile0
210828 23:29:02 [01]        ...done
210828 23:29:02 [01] Copying ib_logfile1 to /opt/data/3308/ib_logfile1
210828 23:29:02 [01]        ...done
210828 23:29:02 [01] Copying ibdata1 to /opt/data/3308/ibdata1
210828 23:29:02 [01]        ...done
210828 23:29:02 [01] Copying ./mysql/plugin.ibd to /opt/data/3308/mysql/plugin.ibd
210828 23:29:02 [01]        ...done
210828 23:29:02 [01] Copying ./ibtmp1 to /opt/data/3308/ibtmp1
210828 23:29:02 [01]        ...done
210828 23:29:02 completed OK!

[root@localhost ~]# ls /opt/data/3308/ #查看已经恢复
ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  performance_schema  sys  xtrabackup_info  xtrabackup_master_key_id