- mysql多实例部署
- 什么是MySQL多实例
- MySQL多实例优劣势
- 如何部署MySQL多实例
MySQL的多实例就是在一台机器上开启多个不同的服务端口，运行多个MySQL服务进程，使用不同的socket来监听这多个不同的端口以此提供服务，这一点和Oracle的多实例类似。这些MySQL的实例共用相同的MySQL但是使用的参数文件是不一样的，相应的数据文件也不同。提供服务的时候从逻辑上看各自独立，各自获取的硬件资源可以灵活设定。
MySQL多实例优劣势- 有效的利用服务器资源。当单个服务器的资源有剩余的时候可以将多余的资源有效的利用起来，而还实现了资源的逻辑隔离。
- 节约经济消耗。例如需要多个数据库来搭建主从，但是又只有一台服务器。
- 当单个数据库并发很高或计算资源需求很高时。整个实例会消耗大量系统的CPU，IO等资源。这样其他实例的可利用资源就会变少产生问题。无法实现实例资源的绝对隔离。
部署的方式有两种：
1.使用mysqld_multi工具，用单独的配置文件实现多实例配置复杂但是管理方便。
2.设置多个配置文件启动，这样启动不同进程实现多实例。原理简单，但是不易管理。
软件下载
//下载二进制格式的mysql软件包 [root@localhost ~]# cd /usr/src/ [root@localhost src]# ls debug kernels mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
配置用户和组并解压二进制程序至/usr/local下
//创建用户和组 [root@localhost src]# useradd -r -M -s /sbin/nologin mysql [root@localhost src]# id mysql uid=993(mysql) gid=990(mysql) 组=990(mysql) //解压软件至/usr/local/ [root@localhost src]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@localhost src]# cd /usr/local/ [root@localhost local]# ls bin etc games include lib lib64 libexec mysql-5.7.37-linux-glibc2.12-x86_64 sbin share src [root@localhost local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql [root@localhost local]# ls bin etc games include lib lib64 libexec mysql sbin share src [root@localhost local]# ll 总用量 0 drwxr-xr-x. 2 root root 6 8月 12 2018 bin drwxr-xr-x. 2 root root 6 8月 12 2018 etc drwxr-xr-x. 2 root root 6 8月 12 2018 games drwxr-xr-x. 2 root root 6 8月 12 2018 include drwxr-xr-x. 2 root root 6 8月 12 2018 lib drwxr-xr-x. 2 root root 6 8月 12 2018 lib64 drwxr-xr-x. 2 root root 6 8月 12 2018 libexec drwxr-xr-x. 9 root root 129 7月 2 22:35 mysql drwxr-xr-x. 2 root root 6 8月 12 2018 sbin drwxr-xr-x. 5 root root 49 5月 12 04:27 share drwxr-xr-x. 2 root root 6 8月 12 2018 src //修改目录/usr/local/mysql的属主属组 [root@localhost local]# chown -R mysql.mysql mysql/ [root@localhost local]# ll mysql/ -d drwxr-xr-x. 9 mysql mysql 129 7月 2 22:35 mysql/ [root@localhost local]# ls mysql/ bin docs include lib LICENSE man README share support-files //配置环境变量 [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 local]# which mysql /usr/local/mysql/bin/mysql [root@localhost local]# cd mysql/ [root@localhost mysql]# ls bin docs include lib LICENSE man README share support-files [root@localhost mysql]# ln -s /usr/local/include /usr/include/mysql [root@localhost mysql]# vi /etc/ld.so.conf.d/mysql.conf [root@localhost mysql]# cat /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib [root@localhost mysql]# ldconfig [root@localhost mysql]# vi /etc/man_db.conf 添加“MANDATORY_MANPATH /usr/local/mysql/man”
创建各实例数据存放的目录
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ls /opt/data/
3306 3307 3308
[root@localhost ~]# ll /opt/data/
总用量 4
drwxr-xr-x. 5 mysql mysql 4096 7月 2 23:08 3306
drwxr-xr-x. 2 mysql mysql 6 7月 2 23:07 3307
drwxr-xr-x. 2 mysql mysql 6 7月 2 23:07 3308
初始化各实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql 2022-07-03T03:08:42.163073Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2022-07-03T03:08:42.417632Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-03T03:08:42.535535Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2022-07-03T03:08:42.596030Z 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: 71a24b2c-fa7d-11ec-a0be-000c29c2fcc6. 2022-07-03T03:08:42.600451Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-03T03:08:43.322797Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-03T03:08:43.322848Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-03T03:08:43.324439Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-03T03:08:43.994866Z 1 [Note] A temporary password is generated for root@localhost: r-9g4e3rqbML [root@localhost ~]# echo 'r-9g4e3rqbML' > 3306_pass [root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql 2022-07-03T03:10:49.488135Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2022-07-03T03:10:49.936504Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-03T03:10:50.023829Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2022-07-03T03:10:50.083752Z 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: bd9f6045-fa7d-11ec-840f-000c29c2fcc6. 2022-07-03T03:10:50.085093Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-03T03:10:50.682679Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-03T03:10:50.682713Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-03T03:10:50.683435Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-03T03:10:50.924170Z 1 [Note] A temporary password is generated for root@localhost: ZlpcT3.h(tDe [root@localhost ~]# echo 'ZlpcT3.h(tDe' > 3307_pass [root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql 2022-07-03T03:11:23.445160Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2022-07-03T03:11:23.958692Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-03T03:11:24.057814Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2022-07-03T03:11:24.117228Z 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: d1e87890-fa7d-11ec-af7c-000c29c2fcc6. 2022-07-03T03:11:24.118931Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-03T03:11:25.402357Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-03T03:11:25.402381Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-03T03:11:25.403608Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-03T03:11:25.957044Z 1 [Note] A temporary password is generated for root@localhost: 6qX8UA8Vp4#R [root@localhost ~]# echo '6qX8UA8Vp4#R' > 3308_pass
配置文件/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 ~]# yum -y install perl [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 Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678. [root@localhost ~]# mysqld_multi start 3307 Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678. [root@localhost ~]# mysqld_multi start 3308 Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678. [root@localhost ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port 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@localhost ~]# cat 3306_pass
r-9g4e3rqbML
[root@localhost ~]# mysql -uroot -p'r-9g4e3rqbML' -S /tmp/mysql3306.sock -e 'set password = password("123.com!");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# cat 3307_pass
ZlpcT3.h(tDe
[root@localhost ~]# mysql -uroot -p'ZlpcT3.h(tDe' -S /tmp/mysql3307.sock -e 'set password = password("123.com!");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# cat 3308_pass
6qX8UA8Vp4#R
[root@localhost ~]# mysql -uroot -p'6qX8UA8Vp4#R' -S /tmp/mysql3308.sock -e 'set password = password("123.com!");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
设置开机自启
//3306开机自启
[root@localhost ~]# cp /usr/lib/systemd/system/sshd.service /usr/lib/systemd/system/my3306.service
[root@localhost ~]# vim /usr/lib/systemd/system/my3306.service
[root@localhost ~]# cat /usr/lib/systemd/system/my3306.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
[root@localhost ~]# which my_print_defaults
/usr/local/mysql/bin/my_print_defaults
[root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin
[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# systemctl start my3306.service
[root@localhost ~]# systemctl enable --now my3306.service
Created symlink /etc/systemd/system/multi-user.target.wants/my3306.service → /usr/lib/systemd/system/my3306.service.
[root@localhost ~]# systemctl status my3306.service
//3307、3308开机自启
[root@localhost ~]# cp /usr/lib/systemd/system/my3306.service /usr/lib/systemd/system/my3307.service
[root@localhost ~]# cp /usr/lib/systemd/system/my3306.service /usr/lib/systemd/system/my3308.service
[root@localhost ~]# vim /usr/lib/systemd/system/my3307.service
[root@localhost ~]# cat /usr/lib/systemd/system/my3307.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@localhost ~]# vim /usr/lib/systemd/system/my3308.service
[root@localhost ~]# cat /usr/lib/systemd/system/my3308.service
[Unit]
Description=3308 server daemon
After=network.target 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@localhost system]# systemctl enable --now my3307.service
Created symlink /etc/systemd/system/multi-user.target.wants/my3307.service → /usr/lib/systemd/system/my3307.service.
[root@localhost system]# systemctl enable --now my3308.service
Created symlink /etc/systemd/system/multi-user.target.wants/my3308.service → /usr/lib/systemd/system/my3308.service.
