mysql多实例部署

mysql多实例部署

目录

mysql多实例部署

MySQL多实例部署

---

## MySQL多实例概念

多实例就是在一台服务器上同时开启多个不同的数据库服务端口（例如3306、3307、3308），同时运行多个MYSQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。

 优点：MySQL多实例，可以通过多个端口向用户提供服务，充分利用一台服务器的闲置资源
 ​
 缺点：无论是多少个端口提供服务，但始终使用的是一台服务器的资源，所以当访问量过大时，依然存在高并发的问题
 ​

MySQL多实例部署：

 [root@localhost ~]# ls
 anaconda-ks.cfg  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  to.sh
 #解压安装包
 [root@localhost ~]# tar -xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz 
 #移动并重命名
 [root@localhost ~]# mv mysql-5.7.38-linux-glibc2.12-x86_64 /usr/local/mysql
 #进入mysql目录
 [root@localhost ~]# cd /usr/local/mysql/
 #创建data
 [root@localhost local]# chown -R mysql.mysql /usr/local/mysql
 [root@localhost local]# 
 #配置环境变量
 [root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
 [root@localhost local]# . /etc/profile.d/mysql.sh
 [root@localhost local]# echo $PATH
 /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
 [root@localhost local]# 
 ​
 ​

为三个实例创建主配置文件my.cnf

 ​

在vi或vim编辑器中执行命令:%s/被替换的目录名/要替换的目录名/g

注意：要在每个实例的主目录中为每个实例都创建主配置文件，且目录名要和实例端口号对应

启动实例

 [root@localhost 3308]# cd /usr/local/mysql/bin/
 [root@localhost bin]# ./mysqld_safe --defaults-file=/usr/local/mysql/data/3306/my.cnf &
 [1] 76183
 [root@localhost bin]# Logging to '/usr/local/mysql/data/3306/error.log'.
 2022-07-29T07:54:23.237926Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/3306
 ​
 [root@localhost bin]# ./mysqld_safe --defaults-file=/usr/local/mysql/data/3307/my.cnf &
 [2] 77117
 [root@localhost bin]# Logging to '/usr/local/mysql/data/3307/error.log'.
 2022-07-29T07:54:42.801032Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/3307
 ​
 [root@localhost bin]# ./mysqld_safe --defaults-file=/usr/local/mysql/data/3308/my.cnf &
 [3] 77813
 [root@localhost bin]# Logging to '/usr/local/mysql/data/3308/error.log'.
 2022-07-29T07:54:55.405095Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/3308
 ​
 [root@localhost bin]# 
 [root@localhost ~]# mkdir -p /opt/data/{3306..3308}
 [root@localhost ~]# chown -R mysql.mysql /opt/data/
 [root@localhost ~]#  ll /opt/data/
 total 0
 drwxr-xr-x. 2 mysql mysql 6 Jul 29 16:24 3306
 drwxr-xr-x. 2 mysql mysql 6 Jul 29 16:24 3307
 drwxr-xr-x. 2 mysql mysql 6 Jul 29 16:24 3308
 [root@localhost ~]# 
 ​
 ​

初始化实例：

 [root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
 2022-07-29T08:25:24.016889Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 2022-07-29T08:25:24.191527Z 0 [Warning] InnoDB: New log files created, LSN=45790
 2022-07-29T08:25:24.219888Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 2022-07-29T08:25:24.277141Z 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: fe423b39-0f17-11ed-9536-000c297f82fa.
 2022-07-29T08:25:24.278200Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 2022-07-29T08:25:24.410020Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
 2022-07-29T08:25:24.410049Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
 2022-07-29T08:25:24.410549Z 0 [Warning] CA certificate ca.pem is self signed.
 2022-07-29T08:25:24.481970Z 1 [Note] A temporary password is generated for root@localhost: A<6-o_O:(jB_
 [root@localhost ~]# echo ' A<6-o_O:(jB_'> 3306pass
 [root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
 2022-07-29T08:26:08.711742Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 2022-07-29T08:26:08.872916Z 0 [Warning] InnoDB: New log files created, LSN=45790
 2022-07-29T08:26:08.898282Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 2022-07-29T08:26:08.903257Z 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: 18dba33d-0f18-11ed-a920-000c297f82fa.
 2022-07-29T08:26:08.903765Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 2022-07-29T08:26:09.069925Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
 2022-07-29T08:26:09.069986Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
 2022-07-29T08:26:09.070459Z 0 [Warning] CA certificate ca.pem is self signed.
 2022-07-29T08:26:09.114901Z 1 [Note] A temporary password is generated for root@localhost: D*Z.Qr7!u?XC
 [root@localhost ~]# echo ' D*Z.Qr7!u?XC' > 3307passw
 [root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
 2022-07-29T08:26:36.324371Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 2022-07-29T08:26:36.486345Z 0 [Warning] InnoDB: New log files created, LSN=45790
 2022-07-29T08:26:36.514910Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 2022-07-29T08:26:36.571518Z 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: 29597a92-0f18-11ed-9c65-000c297f82fa.
 2022-07-29T08:26:36.572685Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 2022-07-29T08:26:36.680480Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
 2022-07-29T08:26:36.680545Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
 2022-07-29T08:26:36.680993Z 0 [Warning] CA certificate ca.pem is self signed.
 2022-07-29T08:26:36.746319Z 1 [Note] A temporary password is generated for root@localhost: It*wlm)aL9kn
 [root@localhost ~]# echo 'It*wlm)aL9kn' > 3308passwd
 ​

安装perl

 [root@localhost ~]# yum -y install perl

配置/etc/my.cnf

 [root@localhost ~]# vim /etc/my.cnf
 [root@localhost ~]# 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_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
 perl: warning: Setting locale failed.
 perl: warning: Please check that your locale settings:
         LANGUAGE = (unset),
         LC_ALL = (unset),
         LANG = "zh_CN.UTF-8"
     are supported and installed on your system.
 perl: warning: Falling back to the standard locale ("C").
 [root@localhost ~]# mysqld_multi start 3307
 perl: warning: Setting locale failed.
 perl: warning: Please check that your locale settings:
         LANGUAGE = (unset),
         LC_ALL = (unset),
         LANG = "zh_CN.UTF-8"
     are supported and installed on your system.
 perl: warning: Falling back to the standard locale ("C").
 [root@localhost ~]# mysqld_multi start 3308
 perl: warning: Setting locale failed.
 perl: warning: Please check that your locale settings:
         LANGUAGE = (unset),
         LC_ALL = (unset),
         LANG = "zh_CN.UTF-8"
     are supported and installed on your system.
 perl: warning: Falling back to the standard locale ("C").
 [root@localhost ~]# 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                    *:*                   
 [root@localhost ~]# 

修改密码

[root@localhost ~]# mysql -uroot -p'A<6-o_O:(jB_' -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.38

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

mysql> exit
Bye
[root@localhost ~]# cat  3307* 3308*
 D*Z.Qr7!u?XC
It*wlm)aL9kn
[root@localhost ~]# mysql -uroot -p'D*Z.Qr7!u?XC' -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 3
Server version: 5.7.38

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

mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p'It*wlm)aL9kn' -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.38

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

mysql> exit
Bye