MySQL多实例部署和xtrabackup简单使用

MySQL多实例部署和xtrabackup简单使用

1. MySQL多实例部署

软件包下载

[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug  kernels  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

配置用户和组

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

解压二进制程序至/usr/local下

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

创建软连接

[root@localhost local]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64/'
[root@localhost 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 19:11 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 7161 31415 129 6月   2 2020 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root    6 8月  12 2018 sbin
drwxr-xr-x. 5 root root   49 4月   6 16:22 share
drwxr-xr-x. 2 root root    6 8月  12 2018 src

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

[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 8月  29 19:11 /usr/local/mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/

配置环境变量

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

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

[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/data/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 8月  29 19:15 3306
drwxr-xr-x. 2 mysql mysql 6 8月  29 19:15 3307
drwxr-xr-x. 2 mysql mysql 6 8月  29 19:15 3308
[root@localhost ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308

3 directories, 0 files

初始化各实例

//初始化3306实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2021-08-29T11:17:11.068658Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T11:17:11.263073Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T11:17:11.296422Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T11:17:11.351315Z 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: a7c8706f-08ba-11ec-8c04-000c297af8de.
2021-08-29T11:17:11.351877Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T11:17:12.609190Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T11:17:12.738577Z 1 [Note] A temporary password is generated for root@localhost: nQ>kpgmRo9nk
[root@localhost ~]# echo 'nQ>kpgmRo9nk' > 3306_pass

//初始化3307实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2021-08-29T11:19:16.476573Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T11:19:16.671948Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T11:19:16.703116Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T11:19:16.761796Z 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: f2888eb5-08ba-11ec-b3d5-000c297af8de.
2021-08-29T11:19:16.762313Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T11:19:17.349114Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T11:19:17.633404Z 1 [Note] A temporary password is generated for root@localhost: o1JWA<wGr02o
[root@localhost ~]# echo 'o1JWA<wGr02o' > 3307_pass

//初始化3308实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2021-08-29T11:21:00.603743Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T11:21:00.744450Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T11:21:00.777711Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T11:21:00.833044Z 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: 3090928b-08bb-11ec-b492-000c297af8de.
2021-08-29T11:21:00.833540Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T11:21:01.796482Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T11:21:02.172091Z 1 [Note] A temporary password is generated for root@localhost: 8xd9zjsY;*!u
[root@localhost ~]# echo '8xd9zjsY;*!u' > 3308_pass

安装perl

[root@localhost ~]# yum -y install perl
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:00:14 前，执行于 2021年08月29日 星期日 19时27分44秒。
依赖关系解决。
===========================================================================================
 软件包                           架构       版本                      仓库           大小
===========================================================================================
安装:
 perl                             x86_64     4:5.26.3-416.el8          AppStream      72 k
安装依赖关系:
 dwz                              x86_64     0.12-9.el8                AppStream     109 k
 efi-srpm-macros                  noarch     3-2.el8                   AppStream      22 k

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

[root@localhost ~]# 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

启动各实例

[root@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi start 3308
[root@localhost ~]# 
[root@localhost ~]# 
[root@localhost ~]# 
[root@localhost ~]# ss -anlt
State      Recv-Q      Send-Q           Local Address:Port           Peer Address:Port     
LISTEN     0           128                    0.0.0.0:111                 0.0.0.0:*        
LISTEN     0           32               192.168.122.1:53                  0.0.0.0:*        
LISTEN     0           128                    0.0.0.0:22                  0.0.0.0:*        
LISTEN     0           5                    127.0.0.1:631                 0.0.0.0:*        
LISTEN     0           80                           *:3307                      *:*        
LISTEN     0           80                           *:3308                      *:*        
LISTEN     0           128                       [::]:111                    [::]:*        
LISTEN     0           128                       [::]:22                     [::]:*        
LISTEN     0           5                        [::1]:631                    [::]:*        
LISTEN     0           80                           *:3306                      *:*        

初始化密码

// 修改3306密码
[root@localhost ~]# mysql -uroot -p'nQ>kpgmRo9nk' -P3306 -h127.0.0.1
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.31

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('redhat');         # 修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

// 修改3307密码
[root@localhost ~]# mysql -uroot -p'o1JWA<wGr02o' -P3307 -h127.0.0.1
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.31

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('redhat');        # 修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

// 修改3308密码
[root@localhost ~]# mysql -uroot -p'8xd9zjsY;*!u' -P3308 -h127.0.0.1
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.31

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('redhat');      # 修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

多实例设置服务:

停止MySQL服务用 pkill mysqld , 启动服务用 /usr/local/mysql/support-files/mysqld_multi.server start

1.设置开机自启 ：把 /usr/local/mysql/bin/support.files/mysqld_multi.server 文件复制到 /etc/init.d/mysqld.server

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

2. 在/etc/init.d/mysqld.server 文件中增加环境变量设置 export PATH=/usr/local/mysql/bin: $PATH

[root@localhost init.d]# cat 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
......

3. chkconfig --add mysqld.server

[root@localhost init.d]# chkconfig --add mysqld.server 
[root@localhost init.d]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld.server   0:off   1:off   2:on    3:on    4:on    5:on    6:off

2. xtrabackup简单使用

XtraBackup(PXB)工具是Percona公司用perl语言开发的一个用于 MySQL数据库物理热备的备份工具，能够非常快速地备份与恢复mysql数据库，且支持在线热备份（备份时不影响数据读写）。

Xtrabackup中包含两个工具：

xtrabackup ：用于热备份innodb,xtradb引擎表的工具，不能备份其他表。
innobackupex ：提供了用于myisam(会锁表)和innodb引擎，及混合使用引擎备份的能力。
Xtrabackup的优点：

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

语法

xtrabackup | innobackupex  [--defaults-file=#] [--backup | --prepare | --copy-back | --move-back] [OPTIONS]

下载地址

https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.22-15.1.el8.x86_64.rpm

安装

[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

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

完全备份

[root@localhost backup]# innobackupex --defaults-files=/etc/my.cnf --password=syb123 --user=root -P3306 --host=127.0.0.1 /opt/backup/
xtrabackup: recognized server arguments: --datadir=/opt/data/3306 
xtrabackup: recognized client arguments: 
210827 19:54:46 innobackupex: Starting the backup operation
......
210827 19:54:48 [00]        ...done
xtrabackup: Transaction log of lsn (2755443) to (2755452) was copied.
210829 19:54:50 completed OK!

删除库

[root@localhost backup]# mysql -uroot -psyb123 -P3306 -h127.0.0.1 -e "drop database cool;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost backup]# mysql -uroot -psyb123 -P3306 -h127.0.0.1 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

删除/opt/data数据准备恢复数据

[root@localhost ~]# rm -rf /opt/data/*
[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            128           [::]:22                                     [::]:*                   
LISTEN              0            80              *:3306                                    *:*                   
[root@localhost ~]# pkill 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              128               [::]:22                                   [::]:*

恢复数据

[root@localhost ~]# innobackupex --copy-back /opt/backup/2021-08-27_08-29-26/
xtrabackup: recognized server arguments: --datadir=/opt/data/3306 
xtrabackup: recognized client arguments: 
210827 19:56:34 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
......
210827 19:56:36 [01] Copying ./xtrabackup_info to /opt/data/3306/xtrabackup_info
210827 19:56:38 [01]        ...done
210827 19:56:40 completed OK!

[root@localhost ~]# ls /opt/data/
3306
[root@localhost ~]# ls /opt/data/3306
cool  ib_buffer_pool  ibdata1  mysql  performance_schema  sys  xtrabackup_info

查看数据恢复

[root@localhost ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.. SUCCESS! 
[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                                           [::]:*                   

[root@localhost ~]# mysql -uroot -psyb123 -P3306 -h127.0.0.1
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.31 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cool               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)