MySQL多实例部署
文章目录
1.多实例的概述
MySQL 多实例部署指的是在同一台服务器上同时运行多个独立的 MySQL 实例，每个实例有自己的配置、数据目录和端口。这种部署方式通常用于在单个物理服务器上托管多个数据库，提高资源利用率，降低成本，并且可以隔离不同应用或服务的数据。
以下是 MySQL 多实例部署的概述：
- 独立性：每个 MySQL 实例是相互独立的，拥有独立的配置文件、日志文件、数据目录和端口。这意味着它们可以完全隔离，互不干扰，即使其中一个实例出现问题也不会影响其他实例的正常运行。
- 资源隔离：多实例部署可以更有效地利用服务器资源。不同的实例可以配置不同的缓冲池大小、线程数等参数，根据各自的需求进行优化，避免资源争用。
- 灵活性：通过多实例部署，可以为不同的应用或服务提供定制化的 MySQL 环境。每个实例可以针对特定的应用进行优化，例如设置不同的字符集、排序规则等。
- 管理和维护：尽管部署多个实例会增加管理和维护的复杂性，但可以通过统一的管理工具或脚本来简化操作。例如，可以编写脚本批量备份、恢复、监控和升级多个实例。
- 端口和访问控制：每个实例都有自己的端口，可以通过不同的端口访问不同的实例。这也使得可以为每个实例设置独立的访问控制策略，提高安全性。
- 应用场景：多实例部署适用于有多个应用或服务需要访问不同数据库的情况。例如，在开发环境中可以为每个开发团队提供独立的实例；在测试环境中可以为不同的测试用例提供独立的数据库。
总的来说，MySQL 多实例部署可以提供灵活性、资源隔离和独立性，但需要注意管理和维护的复杂性。在设计和部署时，需要考虑服务器资源、访问控制、备份策略等因素，以确保整体系统的稳定性和安全性。
2.多实例的部署
2.1配置用户和组并解压二进制程序至/usr/local下
//安装依赖包
[root@localhost ~]# yum -y install ncurses-devel openssl-devel openssl cmake
[root@localhost ~]# yum -y install https://mirror.stream.centos.org/9-stream/CRB/x86_64/os/Packages/mariadb-devel-10.5.16-2.el9.x86_64.rpm
//上传或下载的MySQL软件包（官网上面下：mysql.com）
[root@localhost ~]# ls
anaconda-ks.cfg mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz.xz
//创建系统用户
[root@localhost ~]# useradd -M -s /sbin/nologin -r mysql
[root@localhost ~]# id mysql
uid=991(mysql) gid=991(mysql) groups=991(mysql)
//解压软件包到指定位置
[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]# ls
bin games lib libexec sbin src
etc include lib64 mysql-8.0.35-linux-glibc2.28-x86_64 share
[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
//修改目录所有者和所属组
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]# ll -d /usr/local/mysql
drwxr-xr-x. 9 mysql mysql 129 Dec 8 16:54 /usr/local/mysql
//配置环境变量
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# source /etc/profile.d/mysql.sh
[root@localhost ~]# which mysql
/usr/local/mysql/bin/mysql
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
2.2创建各实例数据存放目录
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# ls /opt/data
3306 3307 3308
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/data
total 0
drwxr-xr-x. 2 mysql mysql 6 Dec 8 17:02 3306
drwxr-xr-x. 2 mysql mysql 6 Dec 8 17:02 3307
drwxr-xr-x. 2 mysql mysql 6 Dec 8 17:02 3308
2.3初始化各实例
//初始化3306实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2023-12-08T09:05:51.745292Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 66999
2023-12-08T09:05:51.753681Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-08T09:05:52.075136Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-08T09:05:53.505592Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: j5rpQnlWip-s
[root@localhost ~]# echo 'j5rpQnlWip-s' > 3306
//初始化3307实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2023-12-08T09:06:08.512212Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 67710
2023-12-08T09:06:08.519192Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-08T09:06:08.885525Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-08T09:06:10.709674Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: AZH!UhAj)8to
[root@localhost ~]# echo 'AZH!UhAj)8to' > 3307
//初始化3308实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2023-12-08T09:06:22.773313Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 68325
2023-12-08T09:06:22.779948Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-08T09:06:23.058296Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-08T09:06:24.366370Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: h_XM_BG=Q6e=
[root@localhost ~]# echo 'h_XM_BG=Q6e=' > 3308
2.4安装perl
[root@localhost ~]# yum -y install perl
2.5配置配置文件/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
2.6启动各实例
[root@localhost ~]# which mysqld_multi
/usr/local/mysql/bin/mysqld_multi
[root@localhost ~]# file /usr/local/mysql/bin/mysqld_multi
/usr/local/mysql/bin/mysqld_multi: Perl script text executable
[root@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[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 151 *:3307 *:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 151 *:3308 *:*
LISTEN 0 70 *:33060 *:*
2.7修改密码
//3306
[root@localhost ~]# ls
3306 3307 3308 anaconda-ks.cfg mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz
[root@localhost ~]# cat 3306
j5rpQnlWip-s
[root@localhost ~]# mysql -uroot -p'j5rpQnlWip-s' -S /tmp/mysql3306.sock -P3306
或
[root@localhost ~]# mysql -uroot -p'j5rpQnlWip-s' -h127.0.0.1 -P3306
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 9
Server version: 8.0.35
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> alter user root@'localhost' identified with mysql_native_password by '1'
-> ;
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -uroot -p1 -P3306 -h127.0.0.1
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 10
Server version: 8.0.35 MySQL Community Server - GPL
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>
//3307
[root@localhost ~]# ls
3306 3307 3308 anaconda-ks.cfg mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz
[root@localhost ~]# cat 3307
AZH!UhAj)8to
[root@localhost ~]# mysql -uroot -p'AZH!UhAj)8to' -P3307 -h127.0.0.1
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 8
Server version: 8.0.35
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>
mysql> alter user root@'localhost' identified with mysql_native_password by '2';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p2 -P 3307 -h127.0.0.1
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 9
Server version: 8.0.35 MySQL Community Server - GPL
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>
//3308
[root@localhost ~]# ls
3306 3307 3308 anaconda-ks.cfg mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz
[root@localhost ~]# cat 3308
h_XM_BG=Q6e=
[root@localhost ~]# mysql -uroot -p'h_XM_BG=Q6e=' -P3308 -h127.0.0.1
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 8
Server version: 8.0.35
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> alter user root@'localhost' identified with mysql_native_password by '3';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p3 -P3308 -h127.0.0.1
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 9
Server version: 8.0.35 MySQL Community Server - GPL
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>
2.8测试(三个数据库服务同时在一台主机上连接，也就是说开多个窗口用同一个ip，连接上了之后互不影响)
//3308
[root@localhost ~]# mysql -uroot -p3 -P3308 -h127.0.0.1
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 10
Server version: 8.0.35 MySQL Community Server - GPL
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>
//3306
[root@localhost ~]# mysql -uroot -p1 -P3306 -h127.0.0.1
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 11
Server version: 8.0.35 MySQL Community Server - GPL
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>
//3307
[root@localhost ~]# mysql -uroot -p2 -P3307 -h127.0.0.1
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 10
Server version: 8.0.35 MySQL Community Server - GPL
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>
2.9设置开机自启
[root@localhost ~]# cd /usr/lib/systemd/system
[root@localhost system]# cp sshd.service mysql3306.service
[root@localhost system]# vim mysql3306.service
[root@localhost system]# cat mysql3306.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 mysql3306.service mysql3307.service
[root@localhost system]# vim mysql3307.service
[root@localhost system]# cat mysql3307.service
[Unit]
Description=mysql 3307 server daemon
After=network.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3307
ExecStop=kill -9 $(ps -ef | grep -v 'grep'| grep 3307| awk '{print $2}')
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@localhost system]# cp mysql3306.service mysql3308.service
[root@localhost system]# vim mysql3308.service
[root@localhost system]# cat mysql3308.service
[Unit]
Description=mysql 3308 server daemon
After=network.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3308
ExecStop=kill -9 $(ps -ef | grep -v 'grep'| grep 3308| awk '{print $2}')
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@localhost system]# systemctl daemon-reload
//查看端口号是否再运行，为了更好的演示结果，假如端口号是开着的先停掉端口号
[root@localhost system]# 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 151 *:3307 *:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 151 *:3308 *:*
LISTEN 0 70 *:33060 *:*
[root@localhost system]# kill -9 $(ps -ef | grep -v 'grep'|grep 3306|awk '{print $2}')
[root@localhost system]# kill -9 $(ps -ef | grep -v 'grep'|grep 3307|awk '{print $2}')
[root@localhost system]# kill -9 $(ps -ef | grep -v 'grep'|grep 3308|awk '{print $2}')
[root@localhost system]# 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 [::]:*
//查看状态
[root@localhost system]# systemctl status mysql3306
○ mysql3306.service - mysql 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/mysql3306.service; disabled; preset: disabl>
Active: inactive (dead)
[root@localhost system]# systemctl status mysql3307
○ mysql3307.service - mysql 3307 server daemon
Loaded: loaded (/usr/lib/systemd/system/mysql3307.service; disabled; preset: disabl>
Active: inactive (dead)
[root@localhost system]# systemctl status mysql3308
○ mysql3308.service - mysql 3308 server daemon
Loaded: loaded (/usr/lib/systemd/system/mysql3308.service; disabled; preset: disabl>
Active: inactive (dead)
//开启mysql3306
[root@localhost system]# systemctl start mysql3306
Job for mysql3306.service failed because the control process exited with error code.
See "systemctl status mysql3306.service" and "journalctl -xeu mysql3306.service" for details.
//解决方案
[root@localhost ~]# ls /usr/local/mysql/bin
ibd2sdi mysql mysqld_multi mysqlshow
innochecksum mysqladmin mysqld_safe mysqlslap
lz4_decompress mysqlbinlog mysqldump mysql_ssl_rsa_setup
myisamchk mysqlcheck mysqldumpslow mysql_tzinfo_to_sql
myisam_ftdump mysql_config mysqlimport mysql_upgrade
myisamlog mysql_config_editor mysql_migrate_keyring perror
myisampack mysqld mysqlpump zlib_decompress
my_print_defaults mysqld-debug mysql_secure_installation
[root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin
[root@localhost ~]# which my_print_defaults
/usr/local/mysql/bin/my_print_defaults
[root@localhost ~]# ll /usr/bin |grep my_print_defaults
lrwxrwxrwx. 1 root root 38 Dec 8 21:19 my_print_defaults -> /usr/local/mysql/bin/my_print_defaults
//再次开启
[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# systemctl start mysql3306
[root@localhost ~]# systemctl start mysql3307
[root@localhost ~]# systemctl start mysql3308
[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.
[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 151 *:3307 *:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 151 *:3308 *:*
LISTEN 0 70 *:33060 *:*
//停止mysql3308
[root@localhost ~]# systemctl stop mysql3308
[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 151 *:3307 *:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 70 *:33060 *:*
//查看状态
[root@localhost ~]# systemctl status mysql3306
● mysql3306.service - mysql 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/mysql3306.service; enabled; preset: disable>
Active: active (running) since Fri 2023-12-08 21:22:24 CST; 3min 49s ago
Main PID: 679179 (mysqld_safe)
Tasks: 38 (limit: 10820)
Memory: 371.9M
CPU: 1.926s
CGroup: /system.slice/mysql3306.service
├─679179 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3306 >
└─679318 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/>
Dec 08 21:22:24 localhost.localdomain systemd[1]: Starting mysql 3306 server daemon...
Dec 08 21:22:24 localhost.localdomain systemd[1]: Started mysql 3306 server daemon.
//重启测试
[root@localhost ~]# reboot
[root@localhost ~]#
连接断开
连接主机...
连接主机成功
Last login: Fri Dec 8 20:54:59 2023 from 192.168.116.1
[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 70 *:33060 *:*
LISTEN 0 151 *:3307 *:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 151 *:3308 *:*
LISTEN 0 128 [::]:22 [::]:*
703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
