mysql多实例部署_mysql 麒麟 wide character-CSDN博客

本文链接：https://blog.csdn.net/m0_62377363/article/details/125590209

系列文章目录

文章目录

系列文章目录
前言
一、什么是MySQL多实例
- MySQL多实例优劣势
- 二.实操步骤分析
总结

前言

一、什么是MySQL多实例

MySQL的多实例就是在一台机器上开启多个不同的服务端口，运行多个MySQL服务进程，使用不同的socket来监听这多个不同的端口以此提供服务，这一点和Oracle的多实例类似。这些MySQL的实例共用相同的MySQL但是使用的参数文件是不一样的，相应的数据文件也不同。提供服务的时候从逻辑上看各自独立，各自获取的硬件资源可以灵活设定？

MySQL多实例优劣势

有效的利用服务器资源。当单个服务器的资源有剩余的时候可以将多余的资源有效的利用起来，而还实现了资源的逻辑隔离。
节约经济消耗。例如需要多个数据库来搭建主从，但是又只有一台服务器。
当单个数据库并发很高或计算资源需求很高时。整个实例会消耗大量系统的CPU，IO等资源。这样其他实例的可利用资源就会变少产生问题。无法实现实例资源的绝对隔离。

如何部署MySQL多实例
部署的方式有两种：

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@bogon ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2022-07-03T13:46:03.940623Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T13:46:04.124834Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T13:46:04.156136Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T13:46:04.215109Z 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: 7b6a47dc-fad6-11ec-9cf1-000c29be3284.
2022-07-03T13:46:04.218230Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T13:46:04.900806Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T13:46:04.900824Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T13:46:04.901307Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T13:46:04.931677Z 1 [Note] A temporary password is generated for root@localhost: TGT!c?coE3NW
[root@bogon ~]# echo 'TGT!c?coE3NW' > 3306_pass
[root@bogon ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2022-07-03T13:46:49.936613Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T13:46:50.122880Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T13:46:50.157795Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T13:46:50.216546Z 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: 96d58b84-fad6-11ec-962a-000c29be3284.
2022-07-03T13:46:50.217203Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T13:46:50.616925Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T13:46:50.616942Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T13:46:50.617406Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T13:46:50.806811Z 1 [Note] A temporary password is generated for root@localhost: 8ouj&Tm/%24V
[root@bogon ~]#  echo '8ouj&Tm/%24V' > 3307_pass
[root@bogon ~]#  mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2022-07-03T13:47:20.251591Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T13:47:20.439197Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T13:47:20.475581Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T13:47:20.533090Z 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: a8e77b6d-fad6-11ec-a3b9-000c29be3284.
2022-07-03T13:47:20.533836Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T13:47:20.738145Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T13:47:20.738162Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T13:47:20.738489Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T13:47:20.798418Z 1 [Note] A temporary password is generated for root@localhost: wGosjzzjl3.Z

配置文件/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@bogon ~]# mysqld_multi start 3306
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@bogon ~]#  mysqld_multi start 3307
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@bogon ~]#  mysqld_multi start 3308
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@bogon ~]# 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@bogon ~]#

初始化密码

[root@localhost ~]# cat 3306_pass
TGT!c?coE3NW
[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
8ouj&Tm/%24V
[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
wGosjzzjl3.Z
[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.