mysql多实例部署
- mysql多实例部署
- 创建各实例数据存放的目录
- 登录测试
- 多实例的登录连接方法
- 创建个表，看看俩个实例是否隔离
- 设置开机自启
mysql多实例部署
[root@hm1 src]# useradd -r -M -s /sbin/nologin mysql
[root@hm1 src]# id mysql
uid=975(myaql) gid=973(myaql) 组=973(myaql)
[root@hm1 src]# ls /usr/local/
bin etc games include lib lib64 libexec sbin share src
[root@hm1 src]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@hm1 src]# cd /usr/local/
[root@hm1 local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.37-linux-glibc2.12-x86_64 share
[root@hm1 local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
[root@hm1 local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
//修改目录账户属主属组
[root@hm1 local]# chown -R mysql.mysql mysql
[root@hm1 local]# ll -d mysql/
drwxr-xr-x. 9 mysql mysql 129 6月 28 15:41 mysql/
//添加环境变量，叫他在那个目录找MySQL
[root@hm1 local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
//读取
[root@hm1 local]# source /etc/profile.d/mysql.sh
[root@hm1 local]# which mysql
/usr/local/mysql/bin/mysql
//设置软链接，里面有多个文件，以后好直接删除目录
[root@hm1 mysql]# cd /usr/local/mysql
[root@hm1 mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
//告诉他头文件库在那个地方
[root@hm1 mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@hm1 mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@hm1 mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
//读取配置文件
[root@hm1 mysql]# ldconfig
//告诉他man文档在那个地方
[root@hm1 mysql]# vim /etc/man_db.conf
#MANDATORY_MANPATH /usr/src/pvm3/man
#
MANDATORY_MANPATH /usr/man
MANDATORY_MANPATH /usr/share/man
MANDATORY_MANPATH /usr/local/share/man
//加上这一行
MANDATORY_MANPATH /usr/local/mysql/man
#---------------------------------------------------------
# set up PATH to MANPATH mapping
# ie. what man tree holds man pages for what binary directory.
创建各实例数据存放的目录
[root@hm1 mysql]# mkdir -p /opt/data/{3306,3307,3308}
[root@hm1 ~]# chown -R mysql.mysql /opt/data/
[root@hm1 mysql]# mysqld --initialize --user mysql --datadir /opt/data/3306
2022-07-03T07:02:10.749725Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T07:02:11.229348Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T07:02:11.285959Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T07:02:11.346698Z 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: 0f7ffe60-fa9e-11ec-9d15-000c29d7902e.
2022-07-03T07:02:11.347819Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T07:02:12.832795Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T07:02:12.832883Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T07:02:12.834048Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T07:02:13.478082Z 1 [Note] A temporary password is generated for root@localhost: UVPv?oei;33M
[root@hm1 mysql]# cd
[root@hm1 ~]# echo "UVPv?oei;33M" > 3306
[root@hm1 ~]# ls
3306 模板 图片 下载 桌面 fhdsfds index.html
公共 视频 文档 音乐 anaconda-ks.cfg httpd initial-setup-ks.cfg
[root@hm1 ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2022-07-03T07:03:05.136074Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T07:03:05.479039Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T07:03:05.537366Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T07:03:05.550677Z 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: 2fcedf48-fa9e-11ec-b389-000c29d7902e.
2022-07-03T07:03:05.551605Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T07:03:06.263539Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T07:03:06.263563Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T07:03:06.264250Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T07:03:06.603429Z 1 [Note] A temporary password is generated for root@localhost: yvVD!%2oPlhL
[root@hm1 ~]# echo "yvVD!%2oPlhL" > 3307
echo "yvVD2oPlhL" > 3307
[root@hm1 ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2022-07-03T07:04:03.544646Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T07:04:03.923139Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T07:04:03.968824Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T07:04:04.026827Z 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: 52a9a14d-fa9e-11ec-9010-000c29d7902e.
2022-07-03T07:04:04.028095Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T07:04:04.994282Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T07:04:04.994322Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T07:04:04.995127Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T07:04:05.269095Z 1 [Note] A temporary password is generated for root@localhost: ej9qWst./dZQ
[root@hm1 ~]# echo "ej9qWst./dZQ" > 3308
// 配置配置文件/etc/my.cnf
[root@hm1 ~]# vim /etc/my.cnf
// 添加下面这些
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@hm1 ~]# dnf -y install perl //安装perl
// /usr/local/mysql/bin/下有一个 mysqld_mults的脚本要用perl
// [root@hm1 bin]# file mysqld_multi
// mysqld_multi: Perl script text executable
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:04:25 前，执行于 2022年07月03日 星期日 15时02分14秒。
依赖关系解决。
// 启动各实例
[root@hm1 ~]# mysqld_multi start 3306
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@hm1 ~]# mysqld_multi start 3307
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@hm1 ~]# mysqld_multi start 3308
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@hm1 ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
[root@hm1 ~]# dnf provides libncurses.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:17:35 前，执行于 2022年07月03日 星期日 15时02分14秒。
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
仓库 ：base
匹配来源：
提供 : libncurses.so.5
[root@hm1 ~]# dnf -y install ncurses-compat-libs
Updating Subscription Management repositories.
Unable to read consumer identity
// 初始化密码
[root@hm1 ~]# cat 3306
UVPv?oei;33M
[root@hm1 ~]# mysql -uroot -p'UVPv?oei;33M' -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.37
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('runtime123');
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> quit
Bye // 第一个实例设置好了
[root@hm1 ~]# cat 3307
yvVD!%2oPlhL
[root@hm1 ~]# mysql -uroot -p'yvVD!%2oPlhL' -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 4
Server version: 5.7.37
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('runtime123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye // 第二个实例设置好了
[root@hm1 ~]#
[root@hm1 ~]# cat 3308
ej9qWst./dZQ
[root@hm1 ~]# mysql -uroot -p'ej9qWst./dZQ' -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.37
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('runtime123');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> quit
Bye // 第三个实例设置好了
登录测试
[root@hm1 ~]# mysql -uroot -pruntime123 -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 5
Server version: 5.7.37 MySQL Community Server (GPL)
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> quit
Bye
[root@hm1 ~]# mysql -uroot -pruntime123 -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 5
Server version: 5.7.37 MySQL Community Server (GPL)
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> quit
Bye
[root@hm1 ~]# mysql -uroot -pruntime123 -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 3
Server version: 5.7.37 MySQL Community Server (GPL)
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> quit
Bye
[root@hm1 ~]#
多实例的登录连接方法
[root@hm1 ~]# mysql -uroot -pruntime123 -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 6
Server version: 5.7.37 MySQL Community Server (GPL)
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> create database school;
Query OK, 1 row affected (0.02 sec)
mysql> use school;
Database changed
mysql> create table tb_students_info(id int not null primary key auto_increment,name varchar(30),age tinyint,sex varchar(6),height int,course_id int);
Query OK, 0 rows affected (0.02 sec)
mysql> Query OK, 0 rows affected (0.04 sec)
mysql> insert tb_students_info(name,age,sex,height,course_id) values('Dany',25,'M',160,1),('Green',23,'M',158,2),('Henry',23,'F',185,1),('jane',22,'M',162,3),('Jim',24,'F',175,2),('John',21,'F',172,4),('Lily',22,'M',165,4),('Susan',23,'M',170,5),('Thomas',22,'F',178,5),('Tom',23,'F',165,5);
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
[root@hm1 ~]# mysql -uroot -pruntime123 -h127.0.0.1 -P3306
mysql> select * from tb_students_info
-> ;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | M | 160 | 1 |
| 2 | Green | 23 | M | 158 | 2 |
| 3 | Henry | 23 | F | 185 | 1 |
| 4 | jane | 22 | M | 162 | 3 |
| 5 | Jim | 24 | F | 175 | 2 |
| 6 | John | 21 | F | 172 | 4 |
| 7 | Lily | 22 | M | 165 | 4 |
| 8 | Susan | 23 | M | 170 | 5 |
| 9 | Thomas | 22 | F | 178 | 5 |
| 10 | Tom | 23 | F | 165 | 5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
mysql> select * from school
-> ;
ERROR 1146 (42S02): Table 'school.school' doesn't exist
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
[root@hm1 ~]# mysql -uroot -pruntime123 -h127.0.0.1 -P3307
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
// 隔离成功
设置开机自启
[root@hm1 ~]# cp /usr/lib/systemd/system/sshd.service /usr/lib/systemd/system/my3306.service
[root@hm1 ~]# vim /usr/lib/systemd/system/my3306.service
#启动mysql时找不到my_print_defaults
[root@localhost ~]# which my_print_defaults
/usr/local/mysql/bin/my_print_defaults
// 配置软连接
[root@hm1 ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/
[root@hm1 ~]# systemctl daemon-reload
[root@hm1 ~]# systemctl status my3306.service
● my3306.service - 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/my3306.service; disabled; vendor >
Active: inactive (dead)
[root@hm1 ~]# vim /etc/selinux/config
[root@hm1 ~]# setenforce 0
[root@hm1 ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@hm1 ~]# systemctl start my3306
[root@hm1 ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@hm1 ~]# systemctl status my3306
● my3306.service - 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/my3306.service; disabled; vendor >
Active: active (running) since Sun 2022-07-03 16:39:08 CST; 28s ago
Process: 613025 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 (co>
Main PID: 613030 (mysqld_safe)
Tasks: 28 (limit: 10746)
Memory: 217.5M
CGroup: /system.slice/my3306.service
├─613030 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/>
└─613169 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql ->
7月 03 16:39:08 hm1 systemd[1]: Starting 3306 server daemon...
7月 03 16:39:08 hm1 mysqld_multi[613025]: Wide character in print at /usr/lo>
7月 03 16:39:08 hm1 systemd[1]: Started 3306 server daemon.
[root@hm1 ~]# systemctl stop my3306
[root@hm1 ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@hm1 ~]# cd /usr/lib/systemd/system
[root@hm1 system]# cp my3306.service my3307.service
[root@hm1 system]# cp my3306.service my3308.service
[root@hm1 system]# vim my3307.service
// 将配置文件里面的3306改成3307
[root@hm1 system]# vim my3308.service
// 将配置文件里面的3306改成3308
[root@hm1 system]# systemctl start my3307
[root@hm1 system]# systemctl start my3308
[root@hm1 system]# systemctl start my3306
[root@hm1 system]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@hm1 system]# systemctl enable --now my3306
Created symlink /etc/systemd/system/multi-user.target.wants/my3306.service → /usr/lib/systemd/system/my3306.service.
[root@hm1 system]#
// 可以完成开机自启，开启，关闭
