mysql多实例部署

王酒懵子

已于 2022-07-03 20:13:55 修改

阅读量156

点赞数

文章标签：服务器网络 linux

于 2022-07-03 18:29:13 首次发布

本文链接：https://blog.csdn.net/whl0102222/article/details/125587587

版权

mysql多实例部署

文章目录

mysql多实例部署
- 安装mysql
- 配置多实例部署

安装mysql

[root@node1 ~]# useradd -r -M -s /sbin/nologin mysql //创建用户
[root@node1 ~]# id mysql
uid=993(mysql) gid=990(mysql) groups=990(mysql)
[root@node1 ~]# tar -xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ 
//解压二进制包
[root@node1 ~]# cd /usr/local/
[root@node1 local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.37-linux-glibc2.12-x86_64  share
[root@node1 ~]# cd /usr/local/
[root@node1 local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.37-linux-glibc2.12-x86_64  share
[root@node1 local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
[root@node1 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@node1 local]# chown -R mysql.mysql mysql  //修改属主属组
[root@node1 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@node1 local]# ll
total 0
drwxr-xr-x. 2 root  root    6 May 19  2020 bin
drwxr-xr-x. 2 root  root    6 May 19  2020 etc
drwxr-xr-x. 2 root  root    6 May 19  2020 games
drwxr-xr-x. 2 root  root    6 May 19  2020 include
drwxr-xr-x. 2 root  root    6 May 19  2020 lib
drwxr-xr-x. 3 root  root   17 May 25 20:11 lib64
drwxr-xr-x. 2 root  root    6 May 19  2020 libexec
drwxr-xr-x. 9 mysql mysql 129 Jul  3 18:01 mysql
drwxr-xr-x. 2 root  root    6 May 19  2020 sbin
drwxr-xr-x. 5 root  root   49 May 25 20:11 share
drwxr-xr-x. 2 root  root    6 May 19  2020 src
//配置环境变量
[root@node1 local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh 
[root@node1 local]# source /etc/profile.d/mysql.sh 
[root@node1 local]# which mysqld
/usr/local/mysql/bin/mysqld
[root@node1 local]# ln -s /usr/local/mysql/include/ /usr/include/mysql //配置include软连接
[root@node1 local]# vi /etc/ld.so.conf.d/mysql.conf  //配置lib库
/usr/local/mysql/lib
[root@node1 local]# vi /etc/man_db.conf   //添加man文档
MANDATORY_MANPATH                       /usr/man
MANDATORY_MANPATH                       /usr/share/man
MANDATORY_MANPATH                       /usr/local/share/man
MANDATORY_MANPATH                       /usr/local/mysql/man
//创建数据库目录
[root@node1 ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@node1 ~]# chown -R mysql.mysql /opt/data/
[root@node1 ~]# cd /opt/data/
[root@node1 data]# ll
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul  3 18:05 3306
drwxr-xr-x. 2 mysql mysql 6 Jul  3 18:05 3307
drwxr-xr-x. 2 mysql mysql 6 Jul  3 18:05 3308

配置多实例部署

?/3306初始化
[root@node1 ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3306
2022-07-03T10:06:45.058000Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T10:06:45.340869Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T10:06:45.395426Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T10:06:45.464966Z 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: d8305a9b-fab7-11ec-b9ad-000c29a35b7b.
2022-07-03T10:06:45.465772Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T10:06:46.231220Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T10:06:46.231244Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T10:06:46.231764Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T10:06:46.260210Z 1 [Note] A temporary password is generated for root@localhost: OoyeDagaT2#E
[root@node1 ~]# echo 'OoyeDagaT2#E' > 3306  //保存3306密码
//3307初始化
[root@node1 ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3307
2022-07-03T10:08:08.236296Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T10:08:08.506428Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T10:08:08.549880Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T10:08:08.609432Z 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: 09bf3192-fab8-11ec-bd5e-000c29a35b7b.
2022-07-03T10:08:08.610372Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T10:08:09.495753Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T10:08:09.495775Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T10:08:09.496215Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T10:08:09.706175Z 1 [Note] A temporary password is generated for root@localhost: etQ#NejBm7eZ
[root@node1 ~]# echo 'etQ#NejBm7eZ' > 3307
//3308初始化
[root@node1 ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3308
2022-07-03T10:08:26.807290Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T10:08:27.073616Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T10:08:27.109999Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T10:08:27.169278Z 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: 14cf338b-fab8-11ec-bf24-000c29a35b7b.
2022-07-03T10:08:27.170187Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T10:08:28.625314Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T10:08:28.625341Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T10:08:28.626010Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T10:08:29.012047Z 1 [Note] A temporary password is generated for root@localhost: .%RiGigf9+r(
[root@node1 ~]# echo '.%RiGigf9+r(' > 3308

//安装软件包perl
[root@node1 ~]# yum -y install perl
[root@node1 ~]# yum install -y ncurses-compat-libs
[root@node1 ~]# file /usr/local/mysql/bin/mysqld_multi //因为此脚本要用perl来执行
/usr/local/mysql/bin/mysqld_multi: Perl script text executable

//配置my.cnf文件
[root@node1 ~]# vi /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@node1 ~]# mysqld_multi start 3306
[root@node1 ~]# mysqld_multi start 3307
[root@node1 ~]# mysqld_multi start 3308
[root@node1 ~]# 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          80                         *:3306                    *:*                   
LISTEN     0          80                         *:3307                    *:*                   
LISTEN     0          80                         *:3308                    *:*                   
LISTEN     0          128                     [::]:22                   [::]:*

//修改密码
[root@node1 ~]# mysql -uroot -p'OoyeDagaT2#E' -S /tmp/mysql3306.sock 
mysql> set password = password('test123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@node1 ~]# mysql -uroot -p'etQ#NejBm7eZ' -S /tmp/mysql3307.sock 
mysql> set password = password('test123');
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@node1 ~]# mysql -uroot -p'.%RiGigf9+r(' -S /tmp/mysql3308.sock 
mysql>  set password = password('test123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

//设置开机自启
[root@node1 ~]# cd /usr/lib/systemd/system/
[root@node1 system]# cp sshd.service 3306.service
[root@node1 system]# vi 3306.service 
Documentation=man:sshd(8) man:sshd_config(5)
EnvironmentFile=-/etc/crypto-policies/back-ends/opensshserver.config
EnvironmentFile=-/etc/sysconfig/sshd
KillMode=process
Restart=on-failure
RestartSec=42s
[Unit]
Description=3306 server daemon
After=network.target sshd-keygen.target
Wants=sshd-keygen.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306
ExecStop=ps -ef | grep 3306 | grep -v grep|awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target
[root@node1 ~]# which my_print_defaults //启动mysql时找不到my_print_defaults 配置软连接
/usr/local/mysql/bin/my_print_defaults   
[root@node1 ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/  
[root@node1 ~]# systemctl daemon-reload 
[root@node1 ~]# systemctl start 3306.service
[root@node1 ~]# systemctl enable --now 3306.service 
Created symlink /etc/systemd/system/multi-user.target.wants/3306.service → /usr/lib/systemd/system/3306.service.
[root@node1 ~]# systemctl status 3306.service 
● 3306.service - 3306 server daemon
   Loaded: loaded (/usr/lib/systemd/system/3306.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Sun 2022-07-03 18:20:05 CST; 4s ago
  Process: 15183 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 (code=exited, status=0/S>
 Main PID: 15188 (code=exited, status=1/FAILURE)

Jul 03 18:20:04 node1 systemd[1]: Starting 3306 server daemon...
Jul 03 18:20:04 node1 systemd[1]: Started 3306 server daemon.
Jul 03 18:20:05 node1 systemd[1]: 3306.service: Main process exited, code=exited, status=1/FAILU>
Jul 03 18:20:05 node1 systemd[1]: 3306.service: Failed with result 'exit-code'.
//以相同方式配置3307、308
[root@node1 system]# cp 3306.service 3307.service 
[root@node1 system]# cp 3306.service 3308.service 
[root@node1 system]# vi 3307.service 
[Unit]
Description=3307 server daemon
After=network.target sshd-keygen.target
Wants=sshd-keygen.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3307
ExecStop=ps -ef | grep 3307 | grep -v grep|awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target
[root@node1 system]# vi 3308.service 
[Unit]
Description=3308 server daemon
After=network.target sshd-keygen.target
Wants=sshd-keygen.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3308
ExecStop=ps -ef | grep 3308 | grep -v grep|awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target
[root@node1 system]# systemctl enable --now 3307.service 
Created symlink /etc/systemd/system/multi-user.target.wants/3307.service → /usr/lib/systemd/system/3307.service.
[root@node1 system]# systemctl enable --now 3308.service 
Created symlink /etc/systemd/system/multi-user.target.wants/3308.service → /usr/lib/systemd/system/3308.service.

//多实例部署应用
[root@node1 ~]# mysql -uroot -p'test123' -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 5
Server version: 5.7.37 MySQL Community Server (GPL)

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> create database school;
Query OK, 1 row affected (0.00 sec)

mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
[root@node1 ~]# mysql -uroot -p'test123' -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.37 MySQL Community Server (GPL)

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