基于mysqld_multi实现MySQL 5.7.24多实例多进程配置

 wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
 tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
 ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql
 useradd -r -s /sbin/nologin mysql
 mkdir -p /data/mysql/{3306,3307,3308}
 chown mysql:mysql /data/mysql/{3306,3307,3308}

 

[root@localhost ~]# vim /data/mysql/3308.cnf
[mysqld]
port=3308
datadir=/data/mysql/3308/
socket=/tmp/mysql3308.sock
symbolic-links=0

[mysqld_safe]
log-error=/data/mysql/3308.log
pid-file=/usr/local/mysql/data/3308.pid

[client]
port=3308
socket=/tmp/mysql3308.sock

[root@localhost ~]# mysqld --defaults-file=/data/mysql/3308.cnf --initialize-insecure --user=mysql

初始化完成之后,后续的配置不需要3308.cnf文件,实例的参数会在my.cnf中集中配置。 
注意,data目录在初始化前,必须为空,不然初始化是会报错

重复上面两个步骤,把3307、3306目录初始化好。

[root@localhost ~]# cat /data/mysql/3306.cnf
[mysqld]
port=3306
datadir=/data/mysql/3306/
socket=/tmp/mysql3306.sock
symbolic-links=0

[mysqld_safe]
log-error=/data/mysql/3306.log
pid-file=/usr/local/mysql/data/3306.pid

[client]
port=3306
socket=/tmp/mysql3306.sock
[root@localhost ~]# cat /data/mysql/3307.cnf
[mysqld]
port=3307
datadir=/data/mysql/3307/
socket=/tmp/mysql3307.sock
symbolic-links=0

[mysqld_safe]
log-error=/data/mysql/3307.log
pid-file=/usr/local/mysql/data/3307.pid

[client]
port=3307
socket=/tmp/mysql3307.sock
[root@localhost ~]# mysqld --defaults-file=/data/mysql/3307.cnf --initialize-insecure --user=mysql
[root@localhost ~]# mysqld --defaults-file=/data/mysql/3306.cnf --initialize-insecure --user=mysql

二、配置my.cnf文件集中管理多个实例

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

[mysqld1]
port=3306
socket=/tmp/mysql3306.sock
datadir=/data/mysql/3306/
skip-external-locking
log-bin=/data/mysql/3306/mysql-bin
server-id=3306
user=mysql

[mysqld2]
port=3307
socket=/tmp/mysql3307.sock
datadir=/data/mysql/3307/
skip-external-locking
log-bin=/data/mysql/3307/mysql-bin
server-id=3307
user=mysql

[mysqld3]
port=3308
socket=/tmp/mysql3308.sock
datadir=/data/mysql/3308/
skip-external-locking
log-bin=/data/mysql/3308/mysql-bin
server-id=3308
user=mysql

[mysql]
no-auto-rehash

mysqld_multi的配置文件和一般MySQL配置不同,没有[mysqld]段,取而代之的是[mysqld1]、[mysqld2]等配置段,每个配置段代表一个MySQL实例。

三、管理多个MySQL实例
1,启动多个MySQL实例
启动多个实例时需要一个启动脚本,这个脚本一般在/usr/local/mysql/support-files目录下的mysqld_multi.server文件

[root@localhost ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

启动多个MySQL实例

[root@localhost ~]# /etc/init.d/mysqld_multi start 1-3

启动三个MySQL实例,注意这里的数字和my.cnf中的[mysqldN]对应,1-3就是启动[mysqld1]、[mysqld2]、[mysqld3]配置段的MySQL实例。

[root@localhost ~]# netstat -lnpt | grep -E "3306|3307|3308"
tcp6       0      0 :::3306                 :::*                    LISTEN      2744/mysqld         
tcp6       0      0 :::3307                 :::*                    LISTEN      65502/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      65499/mysqld

查看端口,看MySQL有没有正常启动,如果没有启动或报错,一般报错详细日志存在各个实例data目录下的主机名.err文件中,打开此文件查找错误原因,逐步排错就可以了。

2,关闭多个MySQL实例

[root@localhost ~]# /etc/init.d/mysqld_multi stop 1-3
[root@localhost ~]# netstat -lnpt | grep -E "3306|3307|3308"

也可以使用以下命令:
killall -u mysql 或者 kill -9 the-mysql-pid

四、root账户管理
前面初始化数据库时用的是--initialize-insecure参数,所以我们初始化的数据库,root账户是没有密码的,要先改密码为123456:

mysqladmin -uroot password '123456' -S /tmp/mysql3306.sock
mysqladmin -uroot password '123456' -S /tmp/mysql3307.sock
mysqladmin -uroot password '123456' -S /tmp/mysql3308.sock

或者:

mysqladmin -uroot password '123456' -P3306 -h127.0.0.1
mysqladmin -uroot password '123456' -P3307 -h127.0.0.1
mysqladmin -uroot password '123456' -P3308 -h127.0.0.1

五、连接mysql多实例:

[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3306.sock
[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3307.sock
[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3308.sock
posted @ 2019-09-05 14:36  agang_19  阅读(424)  评论(0编辑  收藏  举报