mysql多实例部署
MySQL多实例部署
配置用户和组并解压二进制程序至/usr/local下
#创建mysql用户
[root@zzd139 ~]# useradd -rMs /sbin/nologin mysql
[root@zzd139 ~]# id mysql
uid=994(mysql) gid=991(mysql) groups=991(mysql)
#解压mysql安装包到/usr/local/下
[root@zzd139 ~]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@zzd139 ~]# cd /usr/local/
[root@zzd139 local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@zzd139 local]# ll
total 0
drwxr-xr-x. 2 root root 6 May 19 2020 bin
drwxr-xr-x. 2 root root 6 May 19 2020 etc
drwxr-xr-x. 2 root root 6 May 19 2020 games
drwxr-xr-x. 2 root root 6 May 19 2020 include
drwxr-xr-x. 2 root root 6 May 19 2020 lib
drwxr-xr-x. 3 root root 17 Jun 28 09:25 lib64
drwxr-xr-x. 2 root root 6 May 19 2020 libexec
lrwxrwxrwx. 1 root root 36 Jul 31 12:37 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 31 12:36 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 May 19 2020 sbin
drwxr-xr-x. 5 root root 49 Jun 28 09:25 share
drwxr-xr-x. 2 root root 6 May 19 2020 src
#修改mysql安装目录的属主和属组
[root@zzd139 local]# chown -R mysql.mysql mysql-5.7.38-linux-glibc2.12-x86_64/
[root@zzd139 local]# chown -R mysql.mysql mysql
#做mysql全局环境变量
[root@zzd139 mysql]# echo "export PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh
[root@zzd139 mysql]# source /etc/profile.d/mysql.sh
#给mysql头部文件做映射，让系统默认可以识别
[root@zzd139 mysql]# ln -sv /usr/local/mysql/include/ /usr/include/mysql
'/usr/include/mysql' -> '/usr/local/mysql/include/'
[root@zzd139 mysql]# ll -d /usr/include/mysql
lrwxrwxrwx. 1 root root 25 Jul 31 12:43 /usr/include/mysql -> /usr/local/mysql/include/
#让系统可以识别mysql的lib库文件
[root@zzd139 mysql]# echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
[root@zzd139 mysql]# ldconfig
#添加mysql man文档
[root@zzd139 mysql]# vim /etc/man_db.conf
MANDATORY_MANPATH /usr/local/mysql/man
创建各实例数据存放的目录
[root@zzd139 ~]# mkdir -p /opt/mysql/data/{3306,3307,3308}
[root@zzd139 ~]# chown -R mysql.mysql /opt/mysql/
[root@zzd139 ~]# ll -d /opt/mysql/
drwxr-xr-x. 3 mysql mysql 18 Jul 31 12:49 /opt/mysql/
[root@zzd139 ~]# tree /opt/mysql/
/opt/mysql/
└── data
├── 3306
├── 3307
└── 3308
4 directories, 0 files
初始化各实例
#初始化3306
[root@zzd139 ~]# mysqld --initialize --datadir /opt/mysql/data/3306 --user mysql
2022-07-31T04:57:58.055583Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T04:57:58.199632Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T04:57:58.222224Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T04:57:58.277486Z 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: 58b0e5f9-108d-11ed-ad61-000c29f177ce.
2022-07-31T04:57:58.278037Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T04:57:58.466754Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T04:57:58.466766Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T04:57:58.467085Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T04:57:58.504976Z 1 [Note] A temporary password is generated for root@localhost: Zijiul!dD9ah
#初始化3307
[root@zzd139 ~]# mysqld --initialize --datadir /opt/mysql/data/3307 --user mysql
2022-07-31T04:58:47.248209Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T04:58:47.390566Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T04:58:47.411008Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T04:58:47.466127Z 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: 76027d4f-108d-11ed-b06a-000c29f177ce.
2022-07-31T04:58:47.466606Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T04:58:47.678872Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T04:58:47.678885Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T04:58:47.679220Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T04:58:47.693800Z 1 [Note] A temporary password is generated for root@localhost: sH,POrtex18%
#初始化3308
[root@zzd139 ~]# mysqld --initialize --datadir /opt/mysql/data/3308 --user mysql
2022-07-31T04:59:07.895747Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T04:59:08.045977Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T04:59:08.073626Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T04:59:08.129126Z 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: 825369bd-108d-11ed-b0c0-000c29f177ce.
2022-07-31T04:59:08.129730Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T04:59:08.289922Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T04:59:08.289936Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T04:59:08.290281Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T04:59:08.355936Z 1 [Note] A temporary password is generated for root@localhost: ujU(rICNy1qi
#将初始化得到的密码保存起来
[root@zzd139 ~]# echo 'Zijiul!dD9ah' > 3306_pass
[root@zzd139 ~]# echo 'sH,POrtex18%' > 3307_pass
[root@zzd139 ~]# echo 'ujU(rICNy1qi' > 3308_pass
配置配置文件/etc/my.cnf
[root@zzd139 ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
[mysqld3306]
datadir = /opt/mysql/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/mysql/data/3306/mysql_3306.pid
log-error=/var/log/3306.log
[mysqld3307]
datadir = /opt/mysql/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/mysql/data/3307/mysql_3307.pid
log-error=/var/log/3307.log
[mysqld3308]
datadir = /opt/mysql/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/mysql/data/3308/mysql_3308.pid
log-error=/var/log/3308.log
[root@zzd139 ~]# chown -R mysql.mysql /etc/my.cnf
启动实例
[root@zzd139 ~]# dnf -y install perl
[root@zzd139 ~]# mysqld_multi start 3306
[root@zzd139 ~]# mysqld_multi start 3307
[root@zzd139 ~]# mysqld_multi start 3308
[root@zzd139 ~]# 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 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:22 [::]:*
登录到mysql修改密码
[root@zzd139 ~]# dnf -y install ncurses-compat-libs
#3306
[root@zzd139 ~]# mysql -uroot -p'Zijiul!dD9ah' -S/tmp/mysql3306.sock
mysql> set password = password('xxxxxx');
Query OK, 0 rows affected, 1 warning (0.00 sec)
#3307
[root@zzd139 ~]# mysql -uroot -p'sH,POrtex18%' -S/tmp/mysql3307.sock
mysql> set password = password('xxxxxx');
Query OK, 0 rows affected, 1 warning (0.00 sec)
#3308
[root@zzd139 ~]# mysql -uroot -p'ujU(rICNy1qi' -S/tmp/mysql3308.sock
mysql> set password = password('xxxxxx');
Query OK, 0 rows affected, 1 warning (0.00 sec)
停止实例
[root@zzd139 ~]# ps -elf | grep 3306 | grep -v grep | awk -F ' ' '{print $4}' | xargs kill -9
[root@zzd139 ~]# ps -elf | grep 3307 | grep -v grep | awk -F ' ' '{print $4}' | xargs kill -9
[root@zzd139 ~]# ps -elf | grep 3308 | grep -v grep | awk -F ' ' '{print $4}' | xargs kill -9
[root@zzd139 ~]# 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 [::]:*
使用systemd风格控制实例
[root@zzd139 system]# cp sshd.service mysql3306.service
[root@zzd139 system]# cp sshd.service mysql3307.service
[root@zzd139 system]# cp sshd.service mysql3308.service
#修改service文件
[root@zzd139 system]# vim mysql3306.service
[Unit]
Description=3306 server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306
ExecStop=ps -elf | grep 3306 | grep -v grep | awk -F ' ' '{print $4}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@zzd139 system]# vim mysql3307.service
[Unit]
Description=3307 server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3307
ExecStop=ps -elf | grep 3307 | grep -v grep | awk -F ' ' '{print $4}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@zzd139 system]# vim mysql3308.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 -elf | grep 3308 | grep -v grep | awk -F ' ' '{print $4}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@zzd139 system]# systemctl daemon-reload
[root@zzd139 system]# ln -sv /usr/local/mysql/bin/my_print_defaults /usr/bin/my_print_defaults
'/usr/bin/my_print_defaults' -> '/usr/local/mysql/bin/my_print_defaults'
[root@zzd139 system]# systemctl start mysql3306
[root@zzd139 system]# systemctl start mysql3307
[root@zzd139 system]# systemctl start mysql3308
[root@zzd139 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 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:22 [::]:*
报错信息
#在启动实例的时候报以下错误
bash: /usr/local/mysql/bin/mysqld_multi: /usr/bin/perl: bad interpreter: No such file or directory
#解决方法
[root@zzd139 ~]# dnf -y install perl
#在登录mysql的时候报以下错误
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
#解决方法
[root@zzd139 ~]# dnf -y install ncurses-compat-libs
#在使用systemd风格启动mysql时
Job for mysql3306.service failed because the control process exited with error code.
See "systemctl status mysql3306.service" and "journalctl -xe" for details.
#解决方法
[root@zzd139 system]# journalctl -xe
$(找是否有这一行)
Jul 31 14:17:54 zzd139 mysqld_multi[17148]: WARNING: my_print_defaults command not found.
$(如果有)
[root@zzd139 system]# ln -sv /usr/local/mysql/bin/my_print_defaults /usr/bin/my_print_defaults
'/usr/bin/my_print_defaults' -> '/usr/local/mysql/bin/my_print_defaults'
