mysql_multi多实例管理

继上篇文章继续

MySQL编译安装-CSDN博客

配置/etc/my.cnf文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
[mysqld_multi]

mysqld /usr/local/mysql8.0.30/bin/mysqld_safe
mysqladmin /usr/local/mysql8.0.30/bin/mysqladmin
log /home/mysql8/data30/mysqld_multi.log

[mysqld]
user mysql8
port 3306
server_id 3306
basedir /usr/local/mysql8.0.30/
datadir /home/mysql8/data30/
socket /home/mysql8/data30/mysql.sock
character-set-server UTF8MB4
lower_case_table_names 1
log_error /var/log/mysqld.log

[mysqld3307]

user mysql8
datadir /home/mysql8/3307/data
port 3307
server_id 3307
socket /home/mysql8/3307/data/mysql_3307.sock
log-bin /home/mysql8/3307/binlog/mysql3307_bin
log_error /home/mysql8/3307/data/mysqld.log

[mysqld3308]

user mysql8
datadir /home/mysql8/3308/data
port 3308
server_id 3308
socket /home/mysql8/3308/data/mysql_3308.sock
log-bin /home/mysql8/3308/binlog/mysql3308_bin
log_error /home/mysql8/3308/data/mysqld.log

[client]
socket /home/mysql8/data30/mysql.sock

创建数据目录和日志目录

1
2
3
4
5
6
7
su - mysql8
mkdir 3307 3308
mkdir 3307/binlog
mkdir 3307/data
mkdir 3308/binlog
mkdir 3308/data
exit

初始化数据库

1
2
3
4
5
6
7
8
9
10
11
mysqld --user mysql8 \
--basedir /usr/local/mysql8.0.30/ \
--datadir /home/mysql8/3307/data \
--lower-case-table-names 1 \
--initialize

mysqld --user mysql8 \
--basedir /usr/local/mysql8.0.30/ \
--datadir /home/mysql8/3308/data \
--lower-case-table-names 1 \
--initialize

查看全部实例的状态

1
2
3
4
5
6
7
8
9
10
11
12
13
su - mysql8
mysqld_multi report

#这里可能需要配置下mysql8的环境变量
su - mysql8
vim .bash_profile /添加下述内容

export MYSQL_HOME /usr/local/mysql8.0.30
export PATH $MYSQL_HOME/bin:$PATH

#设置mysqld_multi.log文件的属主和属组
su - root
chown mysql8.mysql8 /home/mysql8/data30/mysqld_multi.log

启动单个实例

1
mysqld_multi start 3307

关闭单个实例

1
mysqld_multi stop 3307

关闭失败了,查看mysqld_multi.log日志,发现’root’ ‘localhost’ 连接被拒绝了

那么就在关闭的时候指定用户和密码,但是要重置实例密码,不然密码是过期状态

1
mysqld_multi --user root --password '123456' stop 3307

重置密码

1
2
mysqladmin -S 3307/data/mysql_3307.sock -P 3307 -uroot -p'M>_2k3fXO&g7' password '123456'
mysqladmin -S 3308/data/mysql_3308.sock -P 3308 -uroot -p'CriDygysI3*H' password '123456'

最后再尝试关闭,已经OK

1
mysqld_multi --user root --password '123456' stop 3308

启动多个实例

1
mysqld_multi start

关闭多个实例

1
2
3
4
5
6
7
8
9
10
11
12
13
mysqld_multi stop

su - root
vim /etc/my.cnf

#[mysqld_multi]模块中添加
user root
pass 123456

#如果每个MySQL服务器的密码不一样,只需要在[mysqld_multi]中添加多个密码即可
user root
pass 123456
pass 123457