MySQL多实例部署

迎安
已于 2023-11-13 18:05:08 修改
阅读量53
点赞数
文章标签： mysql
于 2023-09-06 00:36:27 首次发布
本文链接：https://blog.csdn.net/qq_63150485/article/details/132703239
版权
这里操作的前提是已经部署好MySQL数据库，如果没有，请参照前面文章部署数据库
# 前提，关闭mysql数据库服务，删除原先数据库数据
[root@zmq ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@zmq ~]# rm -rf /opt/data/*


1.# 创建各实例数据存放的目录
[root@zmq ~]# mkdir /opt/data/{3306,3307,3308}
[root@zmq ~]# chown -R mysql.mysql /opt/data
[root@zmq ~]# ll /opt/data/
total 123840
drwxr-xr-x. 2 mysql mysql        6 Sep  4 04:51 3306
drwxr-xr-x. 2 mysql mysql        6 Sep  4 04:51 3307
drwxr-xr-x. 2 mysql mysql        6 Sep  4 04:51 3308
[root@zmq ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
├── 3308

2.# 初始化实例3306
[root@zmq ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2023-09-04T08:57:21.243025Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-09-04T08:57:21.368655Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-09-04T08:57:21.388600Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-09-04T08:57:21.393938Z 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: 0f028a85-4b01-11ee-b354-000c2978662d.
2023-09-04T08:57:21.394327Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-09-04T08:57:21.666986Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-09-04T08:57:21.667016Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-09-04T08:57:21.667291Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-04T08:57:21.693538Z 1 [Note] A temporary password is generated for root@localhost: <.NbiyF9_#Si

3.# 初始化实例3307
[root@zmq ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2023-09-04T08:57:59.195022Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-09-04T08:57:59.311166Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-09-04T08:57:59.326195Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-09-04T08:57:59.382534Z 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: 25a72407-4b01-11ee-b882-000c2978662d.
2023-09-04T08:57:59.382988Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-09-04T08:57:59.486129Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-09-04T08:57:59.486152Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-09-04T08:57:59.486425Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-04T08:57:59.566156Z 1 [Note] A temporary password is generated for root@localhost: VrP_C+;pg8to

4.# 初始化实例3308
[root@zmq ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2023-09-04T08:58:12.535871Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-09-04T08:58:12.664523Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-09-04T08:58:12.684736Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-09-04T08:58:12.689831Z 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: 2d95ac69-4b01-11ee-b9ba-000c2978662d.
2023-09-04T08:58:12.690402Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-09-04T08:58:12.783238Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-09-04T08:58:12.783261Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-09-04T08:58:12.783515Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-04T08:58:12.839737Z 1 [Note] A temporary password is generated for root@localhost: 93f*IB3>?*He

5.# 将三个实例的初始化密码写入文件中
[root@zmq ~]# echo '93f*IB3>?*He' > 3308_pass
[root@zmq ~]# echo 'VrP_C+;pg8to' > 3307_pass
[root@zmq ~]# echo '<.NbiyF9_#Si' > 3306_pass
[root@zmq ~]# ls
3306_pass  3307_pass  
3308_pass  anaconda-ks.cfg

6.# 安装perl
Last metadata expiration check: 1:16:34 ago on Mon 04 Sep 2023 03:42:32 AM EDT.
Dependencies resolved.
......省略

 
7.# 配置/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


8.# 启动各实例
[root@zmq ~]# mysqld_multi start 3306
[root@zmq ~]# mysqld_multi start 3307
[root@zmq ~]# mysqld_multi start 3308
[root@zmq ~]# 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              *:*             


9.# 使用初始化密码登录数据库，并设置新的数据库密码
[root@zmq ~]# mysql -uroot -p'<.NbiyF9_#Si' -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 3
Server version: 5.7.42

Copyright (c) 2000, 2023, 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('2664218545Z');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@zmq ~]# mysql -uroot -p'VrP_C+;pg8to' -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 2
Server version: 5.7.42

Copyright (c) 2000, 2023, 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('2664218545Z')
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@zmq ~]# mysql -uroot -p'93f*IB3>?*He' -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.42

Copyright (c) 2000, 2023, 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('2664218545Z');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

10.# 多实例部署完成