MySQL多实例部署

MySQL多实例部署

mysql多实例部署

Mysql多实例就是一台服务器上同时开启多个不同的服务端口（如3306、3307）同时运行多个Mysql服务进程，这些服务进程通过不同socket监听不同的服务端口来提供服务

1.二进制安装

//下载mysql并解压
[root@student ~]# ls
anaconda-ks.cfg  kaike.log  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@student ~]# tar -xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@student ~]# cd /usr/local/
[root@student local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.38-linux-glibc2.12-x86_64  sbin  share  src
[root@student local]# mv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
//创建mysql用户，把mysql目录的属主与属组修改为mysql
[root@student local]# useradd -Mrs /sbin/nologin mysql
[root@student local]# id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)
[root@student local]# chown mysql.mysql /usr/local/mysql/
[root@student local]# ll -d ./mysql/
drwxr-xr-x 9 mysql mysql 129 Jul 30 18:59 ./mysql/
//配置环境变量
[root@student local]# cd mysql/
[root@student mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@student mysql]# cd bin/
[root@student bin]# pwd
/usr/local/mysql/bin
[root@student bin]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
[root@student bin]# source /etc/profile.d/mysql.sh
[root@student bin]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
[root@student bin]# which mysql
/usr/local/mysql/bin/mysql
//给头文件，库文件，man手册做映射
[root@student bin]# cd ..
[root@student mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@student mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@student mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@student mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib/
[root@student mysql]# vim /etc/man_db.conf
[root@192 mysql]# cat /etc/man_db.conf | grep mysql
MANDATORY_MANPATH                       /usr/local/mysql/man
//创建各实例数据存放的目录
[root@student mysql]# mkdir -p /opt/data/mysql-{3306,3307,3308}
[root@student mysql]# chown -R mysql.mysql /opt/data/
[root@student mysql]# ll /opt/data/
total 0
drwxr-xr-x 2 mysql mysql 6 Jul 30 19:07 mysql-3306
drwxr-xr-x 2 mysql mysql 6 Jul 30 19:07 mysql-3307
drwxr-xr-x 2 mysql mysql 6 Jul 30 19:07 mysql-3308

2.初始化各个实例

//初始化3306实例
[root@student ~]# mysqld --initialize --datadir=/opt/data/mysql-3306 --user=mysql
2022-07-30T11:49:59.995724Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:50:00.109608Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:50:00.131416Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:50:00.137247Z 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: bda79353-0ffd-11ed-8b87-000c299ee3c1.
2022-07-30T11:50:00.138125Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:50:00.317682Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:50:00.317723Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:50:00.318209Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:50:00.423077Z 1 [Note] A temporary password is generated for root@localhost: Cm0G_6uh.MD<

//初始化3307实例
[root@student ~]# mysqld --initialize --datadir=/opt/data/mysql-3307 --user=mysql
2022-07-30T11:51:17.735220Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:51:17.835904Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:51:17.866125Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:51:17.924348Z 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: ec04f36b-0ffd-11ed-8dbc-000c299ee3c1.
2022-07-30T11:51:17.925184Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:51:18.034460Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:51:18.034504Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:51:18.034920Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:51:18.061497Z 1 [Note] A temporary password is generated for root@localhost: 2m*4w/7roEds


//初始化3308实例
[root@student ~]# mysqld --initialize --datadir=/opt/data/mysql-3308 --user=mysql
2022-07-30T11:51:43.737491Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:51:43.844216Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:51:43.865418Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:51:43.870172Z 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: fb7bf85f-0ffd-11ed-b0e5-000c299ee3c1.
2022-07-30T11:51:43.871356Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:51:43.989160Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:51:43.989193Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:51:43.989675Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:51:44.124289Z 1 [Note] A temporary password is generated for root@localhost: ?dJiDUe>#6X9

3.安装perl

[root@student ~]# dnf -y install perl

4.配置/etc/my.cnf

[root@student ~]# vim /etc/my.cnf
[root@student ~]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/mysql-3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/mysql-3306/mysql_3306.pid
log-error = /var/log/mysql-3306.log

[mysqld3307]
datadir = /opt/data/mysql-3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/mysql-3307/mysql_3307.pid
log-error = /var/log/mysql-3307.log

[mysqld3308]
datadir = /opt/data/mysql-3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/mysql-3308/mysql_3308.pid
log-error = /var/log/mysql-3308.log

5.启动各实例并初始化密码

//启动各实例并查看端口是否启动
[root@student ~]# mysqld_multi start 3306
[root@student ~]# mysqld_multi start 3307
[root@student ~]# mysqld_multi start 3308
[root@student ~]# ss -anlt
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                             *:3308                        *:*
LISTEN       0            128                         [::]:22                       [::]:*
LISTEN       0            80                             *:3306                        *:*
LISTEN       0            80                             *:3307                        *:*

//初始化各实例的密码
[root@student ~]# mysql -uroot -p'Cm0G_6uh.MD<' -S /tmp/mysql3306.sock
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
//启动遇到报错信息，原因是没有‘libncurses.so.5’这个对象文件
[root@student ~]# dnf provides libncurses.so.5     //获取该对象文件属于哪个软件包
Last metadata expiration check: 0:46:23 ago on Sat 30 Jul 2022 07:46:41 PM CST.
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : base
Matched from:
Provide    : libncurses.so.5

[root@student ~]# dnf -y install ncurses-compat-libs     //下载该软件包
//再次登录3306实例修改密码
[root@student ~]# mysql -uroot -p'Cm0G_6uh.MD<' -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.38

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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
//登录3307实例修改密码
[root@student ~]# mysql -uroot -p'2m*4w/7roEds' -S /tmp/mysql3307.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.38

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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
//登录3308实例修改密码
[root@student ~]# mysql -uroot -p'?dJiDUe>#6X9' -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 2
Server version: 5.7.38

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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye