多实例部署MySQL

最新推荐文章于 2024-10-02 06:14:43 发布

公子欧夏

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量227

点赞数 3

文章标签： mysql adb 数据库

本文链接：https://blog.csdn.net/m0_70780888/article/details/136511322

版权

本文详细描述了如何在Linux系统中安装、配置和管理多个MySQL实例，包括创建用户、数据目录、配置环境变量、编写my.cnf文件以及设置开机自启和开机状态监控。

摘要由CSDN通过智能技术生成

可以在同一台设备上跑多个mysql
环境

系统	服务
Rocky-9.3	mysql

软件包下载地址: https://downloads.apache.org/httpd/httpd-2.4.58.tar.bz2
解压压缩包

[root@localhost ~]# tar xf mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local
[root@localhost ~]# cd /usr/local
[root@localhost local]# mv mysql-8.0.35-linux-glibc2.28-x86_64/ mysql
[root@localhost local]# ls
bin etc games include lib lib64 libexec mysql sbin share src

创建mysql用户并修改所属用户和所属组

[root@localhost ~]# useradd -r -s /sbin/nologin -M mysql
[root@localhost local]# chown -R mysql.mysql mysql
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 May 16 2022 bin
drwxr-xr-x. 2 root root 6 May 16 2022 etc
drwxr-xr-x. 2 root root 6 May 16 2022 games
drwxr-xr-x. 2 root root 6 May 16 2022 include
drwxr-xr-x. 2 root root 6 May 16 2022 lib
drwxr-xr-x. 3 root root 17 Dec 10 11:37 lib64
drwxr-xr-x. 2 root root 6 May 16 2022 libexec
drwxr-xr-x. 9 mysql mysql 129 Dec 10 12:47 mysql
drwxr-xr-x. 2 root root 6 May 16 2022 sbin
drwxr-xr-x. 5 root root 49 Dec 10 11:37 share
drwxr-xr-x. 2 root root 6 May 16 2022 src

配置环境变量

[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# source /etc/profile.d/mysql.sh

创建多实例数据存放目录并初始化

[root@localhost opt]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost opt]# chown -R mysql.mysql /opt/data
[root@localhost opt]# ll
total 0
drwxr-xr-x. 5 mysql mysql 42 Dec 10 12:49 data
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
root@localhost: 9%1P?(OsQCPg
[root@localhost ~]# echo '9%1P?(OsQCPg' > 3306mima
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
root@localhost: }t={C<JKnCY<
[root@localhost ~]# echo '}t={C<JKnCY<' > 3307mima
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
root@localhost: !/gzjjcine:j
[root@localhost ~]# echo '!/gzjjcine:j' > 3308mima

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

编写配置文件/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
[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                    70
*:33060                                       *:*
LISTEN              0                    151
*:3306                                        *:*
[root@localhost ~]# mysqld_multi start 3307
[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                    70
*:33060                                       *:*
LISTEN              0                    151
*:3307                                        *:*
LISTEN              0                    151
*:3306                                        *:*
[root@localhost ~]# mysqld_multi start 3308
[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                    70
*:33060                                       *:*
LISTEN              0                    151
*:3307                                        *:*
LISTEN              0                    151
*:3306                                        *:*
LISTEN              0                    151
*:3308                                        *:*

修改数据库密码

[root@localhost ~]# mysql -uroot -ph'9%1P?(OsQCPg' -P3306 -S /tmp/mysql3306.sock
 
mysql> alter user root@'localhost' identified with mysql_native_password by 'myp0sswd@_';
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'}t={C<JKnCY<' -P3306 -S /tmp/mysql3307.sock

mysql> alter user root@'localhost' identified with mysql_native_password by 'myp0sswd@_';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'!/gzjjcine:j' -P3306 -S /tmp/mysql3308.sock


mysql> alter user root@'localhost' identified with mysql_native_password by 'myp0sswd@_';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

配置mysql文件

[root@localhost ~]# cd /usr/lib/systemd/system
[root@localhost system]# cp sshd.service mysqld3306.service
[root@localhost system]# vi mysqld3306.service
[Unit]
Description=mysql 3306 server daemon
After=network.target

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

[Install]
WantedBy=multi-user.target
[root@localhost system]# cp mysqld3306.service mysql3307.service
[root@localhost system]# cp mysqld3306.service mysql3308.service
[root@localhost system]# vi mysql3307.service
[root@localhost system]# vi mysql3308.service

关闭多实例进程

[root@localhost ~]# kill -9 $(ps -ef|grep -v 'grep' | grep 3306 | awk '{print $2}')
[root@localhost ~]# kill -9 $(ps -ef|grep -v 'grep' | grep 3307 | awk '{print $2}')
[root@localhost ~]# kill -9 $(ps -ef|grep -v 'grep' | grep 3308 | awk '{print $2}')

设置配置文件的软连接,让系统找到配置文件

[root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/

设置开机自启

[root@localhost ~]# systemctl enable mysql3306
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3306.service →
/usr/lib/systemd/system/mysql3306.service.
[root@localhost ~]# systemctl enable mysql3307
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3307.service →
/usr/lib/systemd/system/mysql3307.service.
[root@localhost ~]# systemctl enable mysql3308
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3308.service →
/usr/lib/systemd/system/mysql3308.service.

重启并查看mysql

[root@localhost ~]# reboot
[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                    151
*:3308                                        *:*
LISTEN              0                    151
*:3307                                        *:*
LISTEN              0                    151
*:3306                                        *:*
LISTEN              0                    128
[::]:22                                       [::]:*
LISTEN              0                    70
*:33060                                       *:*