杰霸霸博客

主题

多实例安装 ​

1. 多实例安装场景 ​

服务器配置很高，单个数据库实例不能充分利用硬件资源。降低服务器成本。

2. 配置多实例 ​

可以使用mysqld_multi多实例管理工具来管理多实例。

  1. 配置mysql环境变量,编辑/etc/profile.d/my_env.sh：
sh
# 添加下面内容
export PATH=$PATH:/usr/local/mysql/bin
# 配置生效
[jack@hadoop104 bin]# source /etc/profile
  1. 编辑my.cnf文件：
ini
## 多实例的信息
[mysqld1]
port=3307
datadir=/opt/module/mysql-8.0.39/data1
socket=/tmp/mysql.sock1
pid-file=/opt/module/mysql-8.0.39/data1/mysql.pid
log_error=/opt/module/mysql-8.0.39/logs1/mysql.log
## 配置mysqld_multi
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/usr/local/mysql/mysqld_multi.log

提示

[mysqld]节点将作为公共属性节点，不再参与MySQL服务器启动。
[mysqld_multi]也一定要配置，其中mysqld_safe配置用来启动多实例节点，mysqladmin用来关闭多实例节点。

3. 初始化多实例 ​

sh
## 创建/usr/local/mysql的软连接
[jack@hadoop104 local]$ sudo ln -s /opt/module/mysql-8.0.39 /usr/local/mysql
[jack@hadoop104 bin]$ sudo mysqld --initialize --datadir=/opt/module/mysql-8.0.39/data1
[root@hadoop104 mysql-8.0.39]# cat logs/mysql.log 
2024-12-02T07:39:57.511635Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.39).
2024-12-02T07:39:59.516350Z 0 [Warning] [MY-010909] [Server] /opt/module/mysql-8.0.39/bin/mysqld: Forcing close of thread 10  user: 'root'.
2024-12-02T07:39:59.516742Z 0 [Warning] [MY-010909] [Server] /opt/module/mysql-8.0.39/bin/mysqld: Forcing close of thread 11  user: 'root'.
2024-12-02T07:39:59.517087Z 0 [Warning] [MY-010909] [Server] /opt/module/mysql-8.0.39/bin/mysqld: Forcing close of thread 12  user: 'root'.
2024-12-02T07:39:59.517322Z 0 [Warning] [MY-010909] [Server] /opt/module/mysql-8.0.39/bin/mysqld: Forcing close of thread 13  user: 'root'.
2024-12-02T07:40:00.287270Z 0 [System] [MY-010910] [Server] /opt/module/mysql-8.0.39/bin/mysqld: Shutdown complete (mysqld 8.0.39)  MySQL Community Server - GPL.
2024-12-02T09:38:37.949416Z 0 [System] [MY-013169] [Server] /opt/module/mysql-8.0.39/bin/mysqld (mysqld 8.0.39) initializing of server in progress as process 3670
2024-12-02T09:38:37.958653Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-12-02T09:38:38.267308Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-12-02T09:38:40.592720Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: h2GRud_+eKYc
记录下当前密码,用于后面登录：        
[jack@hadoop104 mysql-8.0.39]$ sudo ls data1
auto.cnf    client-cert.pem    #ib_16384_1.dblwr  #innodb_redo  mysql.ibd           public_key.pem   sys
ca-key.pem  client-key.pem     ib_buffer_pool     #innodb_temp  performance_schema  server-cert.pem  undo_001
ca.pem      #ib_16384_0.dblwr  ibdata1            mysql         private_key.pem     server-key.pem   undo_002
## 设置文件归属为mysql用户
[jack@hadoop104 mysql-8.0.39]$ chown -R mysql:mysql data1

4. 启动多实例 ​

  1. 查询多实例状态：
sh
[root@hadoop104 mysql-8.0.39]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
  1. 启动多实例
sh
[root@hadoop104 mysql-8.0.39]# mysqld_multi start 1
[root@hadoop104 mysql-8.0.39]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running

5. 客户端连接 ​

sh
[root@hadoop104 mysql-8.0.39]# mysql -uroot -S/tmp/mysql.sock1 -p
## 输入刚才日志打印的密码
mysql> alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.01 sec)
## 设置远程访问
mysql> update mysql.user set host = '%' where user ='root';
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

现在多实例mysql3307可以远程连接了。

6. 多实例关停 ​

  1. 关闭指定多实例
sh
[root@hadoop104 mysql-8.0.39]# mysqld_multi stop 1
[root@hadoop104 mysql-8.0.39]# ps -ef |grep mysql
root       4834    973  0 20:39 pts/0    00:00:00 grep --color=auto mysql
  1. 关闭所有多实例
sh
[root@hadoop104 mysql-8.0.39]# mysqld_multi stop

如果遇到mysqld_multi stop命令执行该命令无效，mysql依旧正常运行问题，查看日志报错：

sh
Stopping MySQL servers

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
mysqld_multi log file version 2.16; run: 一 12月  2 20:35:00 2024

通过看上面的报错信息，可以看出，肯定是mysqladmin无法登录的问题,原因是在my.cnf文件的中没有配置user和pass导致的，加上之后就可以了:

ini
[client]
user=root
password=root

7. 配置不同版本多实例 ​

在一台机器上面，MySQL也支持不同版本的实例同时运行。下面在MySQL5.7和8.0实例为例展示。

sh
[root@hadoop104 module]# ll
总用量 4
drwxr-xr-x.  9 root root  129 12月  3 06:44 mysql-5.7.44
drwxr-xr-x. 13 root root  203 12月  2 17:38 mysql-8.0.39

7.1 编辑my.cnf文件 ​

ini
[mysqld2]
server-id=22
port=3308
innodb_buffer_pool_size=64M
## 使用basedir参数覆盖[mysqld]中的公共参数
basedir=/opt/module/mysql-5.7.44/
## 覆盖[mysqld]中的datadir参数
datadir=/opt/module/mysql-5.7.44/data
socket=/tmp/mysql.sock2
## 覆盖[mysqld]中的pid-file参数
pid-file=/opt/module/mysql-5.7.44/data/mysql.pid
## 覆盖[mysqld]中的log_error参数
log_error=/opt/module/mysql-5.7.44/logs/mysql.log
character-set-server=utf8
collation-server=utf8_general_ci
## 设置时区为东八区
default-time-zone='+08:00'

需要手动创建目录data和logs以及/opt/module/mysql-5.7.44/logs/mysql.log文件，并设置归属为mysql用户。

7.2 初始化mysql5.7的data目录 ​

sh
[root@hadoop104 mysql-5.7.44]# bin/mysqld --initialize --basedir=/opt/module/mysql-5.7.44 --datadir=/opt/module/mysql-5.7.44/data
[root@hadoop104 mysql-5.7.44]# ll data
总用量 110660
-rw-r-----. 1 mysql mysql       56 12月  3 08:00 auto.cnf
-rw-------. 1 mysql mysql     1676 12月  3 08:00 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 12月  3 08:00 ca.pem
-rw-r--r--. 1 mysql mysql     1112 12月  3 08:00 client-cert.pem
-rw-------. 1 mysql mysql     1680 12月  3 08:00 client-key.pem
-rw-r-----. 1 mysql mysql      436 12月  3 08:00 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月  3 08:00 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月  3 08:00 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月  3 08:00 ib_logfile1
drwxr-x---. 2 mysql mysql     4096 12月  3 08:00 mysql
drwxr-x---. 2 mysql mysql     8192 12月  3 08:00 performance_schema
-rw-------. 1 mysql mysql     1680 12月  3 08:00 private_key.pem
-rw-r--r--. 1 mysql mysql      452 12月  3 08:00 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 12月  3 08:00 server-cert.pem
-rw-------. 1 mysql mysql     1680 12月  3 08:00 server-key.pem
drwxr-x---. 2 mysql mysql     8192 12月  3 08:00 sys

7.3 启动多实例mysql5.7 ​

sh
[root@hadoop104 mysql-5.7.44]# ./bin/mysqld_multi start 2
[root@hadoop104 mysql-5.7.44]# ./bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is running
[root@hadoop104 mysql-5.7.44]# netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
tcp6       0      0 :::3308                 :::*                    LISTEN

查看mysql进程，发现守护进程还是使用的mysql8.0中的mysqld_safe, 但不是数据进程没有影响：

sh
[root@hadoop104 mysql-8.0.39]# ps -ef | grep mysql
root       4489      1  0 08:04 pts/1    00:00:00 /bin/sh /opt/module/mysql-8.0.39/bin/mysqld_safe --server-id=22 --port=3308 --innodb_buffer_pool_size=64M --basedir=/opt/module/mysql-5.7.44/ --datadir=/opt/module/mysql-5.7.44/data --socket=/tmp/mysql.sock2 --pid-file=/opt/module/mysql-5.7.44/data/mysql.pid --log_error=/opt/module/mysql-5.7.44/logs/mysql.log --character-set-server=utf8 --collation-server=utf8_general_ci
mysql      4932   4489  0 08:04 pts/1    00:00:00 /opt/module/mysql-5.7.44/bin/mysqld --basedir=/opt/module/mysql-5.7.44/ --datadir=/opt/module/mysql-5.7.44/data --plugin-dir=/opt/module/mysql-5.7.44//lib/plugin --user=mysql --server-id=22 --innodb-buffer-pool-size=64M --character-set-server=utf8 --collation-server=utf8_general_ci --log-error=/opt/module/mysql-5.7.44/logs/mysql.log --pid-file=/opt/module/mysql-5.7.44/data/mysql.pid --socket=/tmpmysql.sock2 --port=3308
root       4971    944  0 08:08 pts/0    00:00:00 grep --color=auto mysql

查看mysql5.7初始化数据库密码，由于初始化命令并没有指定日志文件目录，日志默认会往[mysql]节点的日志目录输出：

sh
[root@hadoop104 mysql-8.0.39]# tail -n 20 /opt/module/mysql-8.0.39/logs/mysql.log 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2024-12-02T23:58:44.561282Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-12-02T23:58:44.684449Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-12-02T23:58:44.710755Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-12-02T23:58:44.767290Z 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: 5d2aa589-b109-11ef-9ff8-000c29b9fd34.
2024-12-02T23:58:44.768226Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-12-02T23:58:46.612764Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-12-02T23:58:46.612785Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-12-02T23:58:46.616577Z 0 [Warning] CA certificate ca.pem is self signed.
2024-12-02T23:58:46.965554Z 1 [Note] A temporary password is generated for root@localhost: Yj0BTtz!qbye
2024-12-03T00:00:21.097044Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-12-03T00:00:21.225551Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-12-03T00:00:21.254202Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-12-03T00:00:21.310196Z 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: 96b5ecd6-b109-11ef-a406-000c29b9fd34.
2024-12-03T00:00:21.311014Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-12-03T00:00:22.840318Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-12-03T00:00:22.840338Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-12-03T00:00:22.844195Z 0 [Warning] CA certificate ca.pem is self signed.
2024-12-03T00:00:23.182074Z 1 [Note] A temporary password is generated for root@localhost: CB++XVa>c9_o

7.4 连接mysql5.7 ​

sh
[root@hadoop104 mysql-5.7.44]# mysql -uroot -S/tmp/mysql.sock2 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.44-log

Copyright (c) 2000, 2024, 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>alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'root'@'%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)