MySQL多实例部署
安装mysql
下载mysql的安装包
安装包: https://downloads.mysql.com/archives/community/
[root@Soap ~]# useradd -r -M -s /sbin/nologin mysql //创建用户
[root@Soap ~]# cd /usr/src/
[root@Soap src]# tar -xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ //解压软件包、
[root@Soap src]# cd /usr/local/
[root@Soap local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
[root@Soap local]# chown -R mysql.mysql mysql/ //修改权限
[root@Soap local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@Soap local]# source /etc/profile.d/mysql.sh
[root@Soap local]# which mysql
/usr/local/mysql/bin/mysql //配置环境变量
[root@Soap mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql //配置include软连接
[root@Soap mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@Soap mysql]# cat /etc/ld.so.conf.d/mysql.conf //配置lib库
/usr/local/mysql/lib
[root@Soap mysql]# vim /etc/man_db.conf //添加man文档
MANDATORY_MANPATH /usr/local/mysql/man
#创建数据库目录
[root@Soap ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@Soap ~]# chown -R mysql.mysql /opt/data/
[root@Soap ~]# cd /opt/data/
[root@Soap data]# ll
总用量 0
drwxr-xr-x. 2 mysql mysql 6 7月 2 11:37 3306
drwxr-xr-x. 2 mysql mysql 6 7月 2 11:37 3307
drwxr-xr-x. 2 mysql mysql 6 7月 2 11:37 3308
配置多实例部署
3306初始化
[root@Soap ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3306
2022-07-02T03:39:07.431121Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-02T03:39:07.703358Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-02T03:39:07.831924Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-02T03:39:07.952119Z 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: 8737c75c-f9b8-11ec-a29d-000c29d84f4a.
2022-07-02T03:39:07.952946Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-02T03:39:08.607273Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:39:08.607289Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:39:08.607957Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-02T03:39:08.937337Z 1 [Note] A temporary password is generated for root@Soap: q/m?Crf5iM#o
[root@Soap ~]# echo 'q/m?Crf5iM#o' > 3306 //保存3306的临时密码
初始化3307
[root@Soap ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3307
2022-07-02T03:41:30.616464Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-02T03:41:30.834151Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-02T03:41:30.860689Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-02T03:41:30.918286Z 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: dc6eae26-f9b8-11ec-9d03-000c29d84f4a.
2022-07-02T03:41:30.919190Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-02T03:41:31.278832Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:41:31.278849Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:41:31.280297Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-02T03:41:31.505911Z 1 [Note] A temporary password is generated for root@Soap: iOvQergS.2iC
[root@Soap ~]# echo 'iOvQergS.2iC' > 3307
初始化3308
[root@Soap ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3308
2022-07-02T03:42:11.391605Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-02T03:42:11.644992Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-02T03:42:11.677601Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-02T03:42:11.733139Z 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: f4c28963-f9b8-11ec-b8e5-000c29d84f4a.
2022-07-02T03:42:11.734222Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-02T03:42:12.174186Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:42:12.174202Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:42:12.174635Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-02T03:42:12.290170Z 1 [Note] A temporary password is generated for root@Soap: rN>I?tXZ:3gN
[root@Soap ~]# echo 'rN>I?tXZ:3gN' > 3308
安装软件包perl
[root@Soap ~]# yum -y install perl
[root@Soap ~]# yum install ncurses-compat-libs //连接mysql需要
[root@Soap ~]# file /usr/local/mysql/bin/mysqld_multi //因为此脚本要用perl来执行
/usr/local/mysql/bin/mysqld_multi: Perl script text executable
配置my.cnf文件
[root@Soap ~]# 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@Soap ~]# mysqld_multi start 3306
[root@Soap ~]# mysqld_multi start 3307
[root@Soap ~]# mysqld_multi start 3308
[root@Soap ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:111 [::]:*
修改密码
#3306修改密码
[root@Soap ~]# mysql -uroot -p'q/m?Crf5iM#o' -S /tmp/mysql3306.sock
mysql> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.01 sec)
#3307修改密码
[root@Soap ~]# mysql -uroot -p'iOvQergS.2iC' -S /tmp/mysql3307.sock
mysql> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
#3308修改密码
[root@Soap ~]# mysql -uroot -p'rN>I?tXZ:3gN' -S /tmp/mysql3308.sock
mysql> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.01 sec)
设置开机自启
[root@Soap ~]# cd /usr/lib/systemd/system
[root@Soap system]# cp sshd.service 3306.service
[root@Soap system]# cat 3306.service
[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
配置软连接
#启动mysql时找不到my_print_defaults
[root@Soap ~]# which my_print_defaults
/usr/local/mysql/bin/my_print_defaults
[root@Soap ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/
1234
#可以正常启动和关闭
[root@Soap ~]# systemctl daemon-reload
[root@Soap ~]# systemctl start 3306.service
[root@Soap ~]# systemctl enable --now 3306.service
Created symlink /etc/systemd/system/multi-user.target.wants/3306.service → /usr/lib/systemd/system/3306.service.
[root@Soap ~]# systemctl status 3306.service
● 3306.service - 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/3306.service; enabled; vendor preset: d>
Active: active (running) since Sat 2022-07-02 12:26:01 CST; 23s ago
Main PID: 251609 (mysqld_safe)
Tasks: 28 (limit: 4596)
Memory: 155.9M
CGroup: /system.slice/3306.service
├─251609 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3>
└─251743 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datad>
#关闭
[root@Soap ~]# systemctl stop 3306.service
[root@Soap ~]# systemctl status 3306.service
● 3306.service - 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/3306.service; enabled; vendor preset: d>
Active: failed (Result: exit-code) since Sat 2022-07-02 12:27:06 CST; 1s ago
Process: 257522 ExecStop=/usr/bin/ps -ef | grep 3306 | grep -v grep|awk {print $>
Main PID: 251609 (code=exited, status=0/SUCCESS)
以同样的方法配置3307和3308的开机自启服务
[root@Soap system]# cp 3306.service 3308.service
[root@Soap system]# cp 3306.service 3307.service
#3307配置
[root@Soap system]# cat 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
#3308配置
[root@Soap system]# cat 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@Soap system]# systemctl enable --now 3307.service
Created symlink /etc/systemd/system/multi-user.target.wants/3307.service → /usr/lib/systemd/system/3307.service.
[root@Soap system]# systemctl enable --now 3308.service
Created symlink /etc/systemd/system/multi-user.target.wants/3308.service → /usr/lib/systemd/system/3308.service.
多实例部署可以在同一台主机配置多个mysql让资源不会被浪费 多个mysql库的数据时不同的
[root@Soap ~]# mysql -uroot -p'runtime123!' -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.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@Soap ~]# mysql -uroot -p'runtime123!' -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 4
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)
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· AI与.NET技术实操系列（六）：基于图像分类模型对图像进行分类