在同一台linux服务器上安装2+个mysql服务

在这里插入图片描述

1. 制作第二个mysql配置文件my.13306.cnf

如下面的配置。请注意：下面的端口，和路径相关的参数，需要和第一个mysql的配置重合，除了basedir参数，该参数是mysql安装的根路径。

[mysqld]
group_concat_max_len = 102400
user=mysql
basedir=/usr/local/mysql
datadir=/var/lib/mysql13306
port=13306
socket    = /var/lib/mysql13306/mysqld13306.sock
pid-file  = /var/lib/mysql13306/mysqld13306.pid
log-error = /var/lib/mysql13306/mysqld13306.logserver_id=1
symbolic-links=0
lower_case_table_names = 1
character_set_server = utf8mb4
max_connections = 1000
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"gtid_mode=on
log_bin=mysql-bin
enforce_gtid_consistency=1
skip-host-cache
skip-name-resolvelong_query_time = 10
slow_query_log = 1
slow_query_log_file=/var/lib/mysql13306/slow13306.log#skip-grant-tables[mysql]
socket    =  /var/lib/mysql13306/mysqld13306.sock
default-character-set = utf8mb4[client]
#客户端设置
port    = 13306
socket    =  /var/lib/mysql13306/mysqld13306.sock
default-character-set = utf8mb4
max_allowed_packet = 256M

2. 初始化数据库，得到初始化密码

/usr/local/mysql/bin/mysqld --initialize --datadir=/var/lib/mysql13306  --explicit_defaults_for_timestamp

控制台会打印初始密码：
2024-07-26T02:58:14.221211Z 1 [Note] A temporary password is generated for root@localhost: jNQRKrEkf0/P

上面的初始密是：jNQRKrEkf0/P

3. 启动数据库

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.13306.cnf &

4. 登录数据库

这里需要在当前数据库所在服务器的控制台进行登录

/usr/local/mysql/bin/mysql --defaults-file=/usr/local/mysql/my.13306.cnf  -uroot -pjNQRKrEkf0/P

注意：上面需要制定--defaults-file，否则会默认去找第一个mysql实例的配置文件。

如果密码搞忘了，则需要停止mysql服务后，通过下面的方式重启数据库

/usr/local/mysql/bin/mysqld_safe  --skip-grant-tables  --defaults-file=/usr/local/mysql/my.13306.cnf

这种方式登录数据库后，通过下面的alert进行密码的修改。修改完成密码后，在停止mysql服务，然后通过第三步 3. 启动数据库的方式在次启动数据库。

5. 修改数据库密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'zsc2z2se!4r';

6. 创建新账号

注意：必选重置初始化密码后，才能进行新用户的创建等操作

下面的账号创建和授权仅供参考，不要随意使用到生产环境。

CREATE USER 'root'@'%' IDENTIFIED BY 's8d$f7al0sf)d';
GRANT Create user ON *.* TO 'root'@'%';
GRANT Event ON *.* TO 'root'@'%';
GRANT File ON *.* TO 'root'@'%';
GRANT Process ON *.* TO 'root'@'%';
GRANT Reload ON *.* TO 'root'@'%';
GRANT Replication client ON *.* TO 'root'@'%';
GRANT Replication slave ON *.* TO 'root'@'%';
GRANT Show databases ON *.* TO 'root'@'%';
GRANT Shutdown ON *.* TO 'root'@'%';
GRANT Super ON *.* TO 'root'@'%';
GRANT Create tablespace ON *.* TO 'root'@'%';
GRANT Usage ON *.* TO 'root'@'%';
GRANT Grant option ON *.* TO 'root'@'%';
GRANT Alter ON *.* TO 'root'@'%';
GRANT Create ON *.* TO 'root'@'%';
GRANT Create view ON *.* TO 'root'@'%';
GRANT Delete ON *.* TO 'root'@'%';
GRANT Drop ON *.* TO 'root'@'%';
GRANT Grant option ON *.* TO 'root'@'%';
GRANT Index ON *.* TO 'root'@'%';
GRANT Insert ON *.* TO 'root'@'%';
GRANT References ON *.* TO 'root'@'%';
GRANT Select ON *.* TO 'root'@'%';
GRANT Show view ON *.* TO 'root'@'%';
GRANT Trigger ON *.* TO 'root'@'%';
GRANT Update ON *.* TO 'root'@'%';
GRANT Alter routine ON *.* TO 'root'@'%';
GRANT Create routine ON *.* TO 'root'@'%';
GRANT Create temporary tables ON *.* TO 'root'@'%';
GRANT Execute ON *.* TO 'root'@'%';
GRANT Lock tables ON *.* TO 'root'@'%';
GRANT Grant option ON *.* TO 'root'@'%';

上面的操作的mysql版本是：

# /mysql --version
./mysql  Ver 14.14 Distrib 5.7.36, for linux-glibc2.12 (x86_64) using  EditLine wrapper

其它

停止mysql服务

由于是通过脚本的方式启动的第二个mysql服务，因此停用mmysql可以通过kill 命令操作。如下：

# ps aux | grep mysql
root      4646  0.0  0.0 112812   980 pts/2    S+   11:33   0:00 grep --color=auto mysql
root     25159  1.5  4.9 4758144 1208120 ?     Ssl  May28 1346:01 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf
root     26827  0.0  0.0 113416  1640 pts/3    S    11:10   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.13306.cnf
root     27162  0.1  0.7 1315836 185444 pts/3  Sl   11:10   0:01 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.13306.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql13306 --plugin-dir=/usr/local/mysql/lib/plugin --user=root --log-error=/var/lib/mysql13306/mysqld13306.log --pid-file=/var/lib/mysql13306/mysqld13306.pid --socket=/var/lib/mysql13306/mysqld13306.sock --port=13306