mysql 多实例部署、xtrabackup下载与安装_ncurses-compat-libs下载-CSDN博客

本文链接：https://blog.csdn.net/Xiaoajie55/article/details/119985444

mysql 多实例部署

一、二进制安装mysql

软件下载

下载网络源

curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-8.repo

下载mysql二级制包

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

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

[root@mariadb ~]# ls
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@mariadb ~]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/
[root@mariadb ~]# cd /usr/local/
[root@mariadb local]# ls
apr       bin  games  include  lib64    mysql-5.7.34-linux-glibc2.12-x86_64  share
apr-util  etc  httpd  lib      libexec  sbin                                 src
[root@mariadb local]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@mariadb local]# ll mysql
lrwxrwxrwx 1 root root 47 8月  29 13:13 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
[root@mariadb local]# useradd -r -M -s /sbin/nologin mysql
[root@mariadb local]# chown -R mysql.mysql /usr/local/mysql*
[root@mariadb local]# ll /usr/local/mysql
lrwxrwxrwx 1 mysql mysql 47 8月  29 13:13 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
[root@mariadb local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@mariadb local]# source /etc/profile.d/mysql.sh
[root@mariadb local]# echo $PATH
/usr/local/mysql/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

二、实例部署

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

[root@mariadb ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@mariadb ~]# chown -R mysql.mysql /opt/data/
[root@mariadb ~]# ll /opt/data/
总用量 110680
drwxr-xr-x 2 mysql mysql        6 8月  29 13:18 3306
drwxr-xr-x 2 mysql mysql        6 8月  29 13:18 3307
drwxr-xr-x 2 mysql mysql        6 8月  29 13:18 3308
-rw-rw---- 1 mysql mysql    16384 8月  24 15:38 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 8月  24 15:38 aria_log_control
-rw-r----- 1 mysql mysql       56 8月  10 17:03 auto.cnf
-rw------- 1 mysql mysql     1676 8月  10 17:03 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 8月  10 17:03 ca.pem
-rw-r--r-- 1 mysql mysql     1112 8月  10 17:03 client-cert.pem
-rw------- 1 mysql mysql     1680 8月  10 17:03 client-key.pem
-rw-rw---- 1 mysql mysql     1486 8月  24 15:38 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  24 15:38 ibdata1
-rw-rw---- 1 mysql mysql 50331648 8月  24 15:38 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 8月  10 17:39 ib_logfile1
-rw-rw---- 1 mysql mysql        0 8月  10 17:39 multi-master.info
drwxr-x--- 2 mysql mysql     4096 8月  10 17:03 mysql
drwxr-x--- 2 mysql mysql     8192 8月  10 17:03 performance_schema
-rw------- 1 mysql mysql     1680 8月  10 17:03 private_key.pem
-rw-r--r-- 1 mysql mysql      452 8月  10 17:03 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 8月  10 17:03 server-cert.pem
-rw------- 1 mysql mysql     1676 8月  10 17:03 server-key.pem
drwxr-x--- 2 mysql mysql     8192 8月  10 17:03 sys
[root@mariadb ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
├── 3308

初始化各实例

[root@mariadb ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2021-08-29T13:20:20.373096Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T13:20:20.483071Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T13:20:20.503876Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T13:20:20.557846Z 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: dc17e096-08cb-11ec-b3f4-000c29dfcde8.
2021-08-29T13:20:20.558730Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T13:20:21.895207Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T13:20:22.090131Z 1 [Note] A temporary password is generated for root@localhost: UB>A(p>5(w34
[root@mariadb ~]# echo 'UB>A(p>5(w34' > 3306_pass
其他两个端口同理
密码分别为：
Installing new database in /opt/data/3307

2021-08-29T13:26:01.829149Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T13:26:01.913933Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T13:26:01.933676Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T13:26:01.989315Z 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: a79a2f64-08cc-11ec-bf55-000c29dfcde8.
2021-08-29T13:26:01.989990Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T13:26:02.850369Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T13:26:03.167494Z 1 [Note] A temporary password is generated for root@localhost: <l!pL,0ad;%e
[root@mariadb ~]# echo '<l!pL,0ad;%e' > 3307_pass

Installing new database in /opt/data/3308

2021-08-29T13:26:04.788409Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T13:26:04.875391Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T13:26:04.910447Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T13:26:04.965507Z 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: a96050e6-08cc-11ec-8cb9-000c29dfcde8.
2021-08-29T13:26:04.966128Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T13:26:05.870725Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T13:26:06.034786Z 1 [Note] A temporary password is generated for root@localhost: R(Q;:xd,w0L,
[root@mariadb ~]# echo 'R(Q;:xd,w0L,' > 3308_pass

安装perl、ncuress-compat-libs依赖包

[root@mariadb ~]# yum -y install perl 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.
Repository AppStream is listed more than once in the configuration
上次元数据过期检查：0:22:54 前，执行于 2021年08月29日 星期日 13时00分11秒。
软件包 perl-4:5.26.3-419.el8_4.1.x86_64 已安装。
软件包 ncurses-compat-libs-6.1-7.20180224.el8.x86_64 已安装。
依赖关系解决。
无需任何处理。
完毕！

配置文件/etc/my.cnf

[root@mariadb ~]# vim /etc/my.cnf
[root@mariadb ~]# 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.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

启动各实例

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

[root@mariadb ~]# mysql -uroot -p'UB>A(p>5(w34' -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> exit
Bye

[root@mariadb ~]# mysql -uroot -p<l!pL,0ad;%e -h127.0.0.1 -P3307
-bash: !pL,0ad: event not found
[root@mariadb ~]# mysql -uroot -p'<l!pL,0ad;%e' -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 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> exit
Bye
[root@mariadb ~]# mysql -uroot -p'R(Q;:xd,w0L,'  -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.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> exit
Bye

三、设置开机自启

[root@mariadb ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi.server
[root@mariadb ~]# vim /etc/init.d/mysqld_multi.server
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
export PATH=/usr/local/mysql/bin:$PATH
[root@mariadb ~]# chkconfig mysqld_multi.server on
[root@mariadb ~]# 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:关

xtrabackup

一、xtrabackup下载与安装

[root@mariadb ~]# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
--2021-08-27 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-27 20:32:18 (40.8 KB/s) - 已保存 “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm” [8060704/8060704])

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

安装

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

完毕！

二、备份

删除
删除配置文件中/etc/my.cnf中3307、3308

[root@mariadb ~]# vim /etc/my.cnf
[root@mariadb ~]# cat /etc/my.cnf
[3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql.pid
log-error=/var/log/mysql_3306.log


//配置文件
[root@mariadb ~]# cd /usr/local/mysql/support-files
[root@mariadb support-files]# vim mysql.server
basedir=/usr/local/mysql
datadir=/opt/data/3306
[root@mariadb support-files]# ./mysql.server start
 SUCCESS! 
 
[root@mariadb support-files]# 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           128                       [::]:111                    [::]:*        
LISTEN     0           128                       [::]:22                     [::]:*        
LISTEN     0           5                        [::1]:631                    [::]:*

将密码和用户写入配置文件

[root@mariadb ~]# vim ~/.my.cnf
[root@mariadb ~]# cat ~/.my.cnf 
[client]
user=root
password=redhat123

创建一个存放备份文件的目录方便等下放置备份文件

[root@mariadb ~]# mkdir /backup

创建表

[root@mariadb ~]# 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 |
| mariadb                |
| 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@mariadb ~]# 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-27_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@mariadb ~]# cd /backup/
[root@mariadb backup]# ls
2021-08-27_21-30-57

误删除数据

[root@mariadb ~]# 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 |
| mariadb                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database mariadb;
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@mariadb ~]# innobackupex --apply-log /backup/2021-08-27_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@mariadb ~]# rm -rf /opt/data/3306/*

[root@mariadb ~]# innobackupex  --defaults-file=/etc/my.cnf --copy-back -uroot -predhat123 --host=127.0.0.1 /backup1/2021-08-27_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 |
| mariadb                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)