MySQL多实例部署

1、软件下载和安装

---

//配置网络源
[root@wjm ~]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-8.repo
//下载二进制格式的mysql软件包
[root@wjm ~]# cd /usr/src/
[root@wjm src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz /usr/src
……
mysql-5.7.34-linux-gli 100%[===========================>] 634.56M  14.3MB/s  用时 61s     
2021-08-28 19:55:22 (10.5 MB/s) - 已保存 “mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz” [665389778/665389778])

//配置用户并压缩二进制程序
[root@wjm src]# cd /usr/local/
[root@wjm local]# ls
bin  etc  games  include  lib  lib64  libexec  sbin  share  src

//解压软件至/usr/local/
[root@wjm src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@wjm src]# cd /usr/local/
[root@wjm local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.34-linux-glibc2.12-x86_64  share

//创建软连接
[root@wjm local]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@wjm local]# ll mysql
lrwxrwxrwx. 1 root root 47 8月  28 17:02 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/

//创建用户
[root@wjm local]# useradd -r -M -s /sbin/nologin mysql
[root@wjm local]# chown -R mysql.mysql /usr/local/mysql*
[root@wjm local]# ll /usr/local/mysql 
lrwxrwxrwx. 1 mysql mysql 47 8月  28 19:56 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/

//添加环境变量
[root@wjm ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@wjm ~]# source /etc/profile.d/mysql.sh
[root@wjm ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//初始化各实例
//初始化3306实例
[root@wjm ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2021-08-28T09:11:30.359416Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T09:11:30.517732Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:11:30.542228Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:11:30.546668Z 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: c44976d0-0716-11ec-b400-000c292a28a3.
2021-08-28T09:11:30.547669Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:11:31.627301Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:11:31.753843Z 1 [Note] A temporary password is generated for root@localhost: L8vg_klv9A%/
[root@wjm ~]# echo 'L8vg_klv9A%/' > 3306_pass

//初始化3307实例
[root@wjm ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2021-08-28T09:12:31.985800Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T09:12:32.141828Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:12:32.177101Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:12:32.235015Z 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: e90e5c02-0716-11ec-92d9-000c292a28a3.
2021-08-28T09:12:32.236748Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:12:32.604836Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:12:32.831085Z 1 [Note] A temporary password is generated for root@localhost: l_OqkMUC2aur
[root@wjm ~]# echo 'l_OqkMUC2aur' > 3307_pass

//初始化3308实例
[root@wjm ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2021-08-28T09:13:00.709454Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T09:13:00.861253Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:13:00.888880Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:13:00.893827Z 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: fa2358c8-0716-11ec-be60-000c292a28a3.
2021-08-28T09:13:00.894394Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:13:01.490271Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:13:01.704002Z 1 [Note] A temporary password is generated for root@localhost: H1WZks(R9oiC
[root@wjm ~]# echo 'H1WZks(R9oiC' > 3308_pass

[root@wjm ~]# ls  33*
3306_pass  3307_pass  3308_pass
 
//安装Perl，ncuress-compat-libs
[root@wjm ~]# 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.
……
  rust-srpm-macros-5-2.el8.noarch                                                          
  systemtap-sdt-devel-4.4-11.el8_4.x86_64                                                  

完毕！

//查看某一个程序的依赖包
ldd /usr/local/mysql/bin/mysql
yum whatprovides libncurses.so.5

[root@wjm ~]# yum -y install ncurses-compat-libs
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.
……
已安装:
  ncurses-compat-libs-6.1-7.20180224.el8.x86_64                                            

完毕！

//配置文件/etc/my.cnf
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.pid
log-error=/var/log/mysql_3306.log

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

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

2、配置开机自启

[root@wjm ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi.server

[root@wjm ~]# vim /etc/init.d/mysqld_multi.server 
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
export PATH=/usr/local/mysql/bin:$PATH

[root@wjm ~]# ss -antl
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                           *:3306                      *:*        
LISTEN     0           80                           *:3307                      *:*        
LISTEN     0           80                           *:3308                      *:*        
LISTEN     0           128                       [::]:111                    [::]:*        
LISTEN     0           128                       [::]:22                     [::]:*        
LISTEN     0           5                        [::1]:631                    [::]:*

[root@wjm ~]#  chkconfig mysqld_multi.server on
[root@wjm ~]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld_multi.server     0:关    1:关    2:开    3:开    4:开    5:开    6:关



//测试
[root@wjm ~]# pkill mysqld
[root@wjm ~]# ss -antl
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           128                       [::]:111                    [::]:*        
LISTEN     0           128                       [::]:22                     [::]:*        
LISTEN     0           5                        [::1]:631                    [::]:*  

[root@wjm ~]# reboot
[root@wjm ~]# ss -antl
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                           *:3306                      *:*        
LISTEN     0           80                           *:3307                      *:*        
LISTEN     0           80                           *:3308                      *:*        
LISTEN     0           128                       [::]:111                    [::]:*        
LISTEN     0           128                       [::]:22                     [::]:*        
LISTEN     0           5                        [::1]:631                    [::]:* 

3、xtrabackup

Xtrabackup介绍

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

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

Xtrabackup优点

• 备份速度快，物理备份可靠
• 备份过程不会打断正在运行的事物
• 能够基于压缩等功能节约磁盘空间和流量
• 自动备份效验
• 在不增加服务器负载的情况备份数据

3.1 xtrabackup下载、安装、备份、恢复

[root@wjm ~]# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
--2021-08-28 20:29:02--  https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
正在解析主机 repo.percona.com (repo.percona.com)... 167.71.118.3, 157.245.119.64, 167.99.233.229
正在连接 repo.percona.com (repo.percona.com)|167.71.118.3|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：8060704 (7.7M) [application/x-redhat-package-manager]
正在保存至: “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm”

percona-xtrabackup-24- 100%[===========================>]   7.69M  32.4KB/s  用时 3m 13s  

2021-08-28 20:32:18 (40.8 KB/s) - 已保存 “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm” [8060704/8060704])

[root@wjm ~]# ls pe*
percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

//安装
[root@wjm ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
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.
……
  mariadb-connector-c-config-3.1.11-2.el8_3.noarch                                         
  percona-xtrabackup-24-2.4.23-1.el8.x86_64                                                
  perl-DBD-MySQL-4.046-3.module_el8.1.0+203+e45423dc.x86_64                                

完毕！

//创建表
[root@wjm ~]# mysql -uroot -predhat123 
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 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 |
| wjm1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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

mysql> insert student (name,age) values('tom',20),('jerry',20),('wnagermazi',15),('zhangsan',19),('lisi',25);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
5 rows in set (0.00 sec)

//备份
[root@wjm ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=redhat123 --host=127.0.0.1 /backup
xtrabackup: recognized server arguments: 
xtrabackup: recognized client arguments: 
……
210827 21:31:01 [00] Writing /backup/2021-08-28_21-30-57/xtrabackup_info
210827 21:31:01 [00]        ...done
xtrabackup: Transaction log of lsn (2755296) to (2755305) was copied.
210827 21:31:01 completed OK!

[root@wjm ~]# cd /backup/
[root@wjm backup]# ls
2021-08-28_21-30-57

//误删数据
[root@wjm ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.34 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 |
| wjm1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database wjm1;
Query OK, 1 row affected (0.01 sec)

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


//恢复
[root@wjm ~]# innobackupex --apply-log /backup/2021-08-28_21-30-57/		//检查日志
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=
……
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2755624
210827 21:37:41 completed OK!

//删除
[root@wjm ~]# rm -rf /opt/data/3306/*

[root@wjm ~]# innobackupex  --defaults-file=/etc/my.cnf --copy-back -uroot -predhat123 --host=127.0.0.1 /backup1/2021-08-28_22-22-59/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --
……
InnoDB: Shutdown completed; log sequence number 2755624
210827 21:50:10 completed OK!


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