MySQL multi instance

第一步先将本文末尾的配置内容复制到my.cnf配置文档

 

到官网下载安装包并解压 

tar -xvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz

mkdir /mysql

mv mysql-8.0.22-linux-glibc2.12-x86_64/*   /mysql

 创建用户

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

mkdir /data/p{3306..3309}
chown -R mysql:mysql /data

初始化3306至3309四个实例

cd /mysql/bin
for i in `seq 3306 3309`;do echo $i;./mysqld --datadir=/data/p${i} --port=${i} --initialize --user=mysql ; done;

添加环境变量
echo 'PATH=$PATH:/mysql/bin' >> /etc/profile

source /etc/profile

启动实例

mysqld_multi start 3306-3309

统一修改密码

for i in `seq 3306 3309`;do echo -e "\n$i"; mysql -S /data/p${i}/mysql${i}.sock -e 'flush privileges;alter user 'root'@'localhost' identified by "Password";' ; done;

检验

for i in `seq 3306 3309`;do echo -e "\n$i"; mysql -S /data/p${i}/mysql${i}.sock -uroot -pPassword -e 'show databases' ; done;

设置开机自启

echo 'export PATH=$PATH:/mysql/bin;mysqld_multi start 3306-3309'>>/etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local

 

 

 

my.cnf配置

为了方便统一修改密码配置文件中添加skip-grant-tables直接免密登录，修改密码之后再注释该配置。

[client]
user=root
password=Password
socket=/data/p3306/mysql3306.sock
 
[mysqld]
# basic settings #
user = mysql
basedir = /mysql
character_set_server = utf8
collation_server = utf8_general_ci
lower_case_table_names = 1
max_connections = 2000
autocommit = 1
skip-grant-tables
 
# innodb settings #
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 16
innodb_page_size = 16384
innodb_flush_log_at_trx_commit = 1
default-storage-engine = INNODB
log-bin-trust-function-creators = 1
 
# log settings #
expire_logs_days = 7
#binlog_expire_logs_seconds = 604800
max_binlog_size = 100M
 
# replication settings #
log-bin = mysql-bin
gtid_mode = on
enforce_gtid_consistency = on
binlog_checksum = none
log_slave_updates = ON
binlog_format = ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
 
[mysqld_multi]
log = /data/mysqld_multi.err
mysqld = /mysql/bin/mysqld_safe
mysqladmin = /mysql/bin/mysqladmin
 
[mysqld3306]
server-id = 1026
port = 3306
mysqlx_port = 33060
basedir = /mysql
datadir = /data/p3306
pid-file = /data/p3306/mysql3306.pid
log-error = /data/p3306/mysql3306.err
socket = /data/p3306/mysql3306.sock
mysqlx_socket = /data/p3306/mysqlx3306.sock
 
[mysqld3307]
server-id = 1027
port = 3307
mysqlx_port = 33070
basedir = /mysql
datadir = /data/p3307
socket = /data/p3307/mysql3307.sock
pid-file = /data/p3307/mysql3307.pid
log-error = /data/p3307/mysql3307.err
mysqlx_socket = /data/p3307/mysqlx3307.sock
 
[mysqld3308]
server-id = 1028
port = 3308
mysqlx_port = 33080
basedir = /mysql
datadir = /data/p3308
socket = /data/p3308/mysql3308.sock
pid-file = /data/p3308/mysql3308.pid
log-error = /data/p3308/mysql3308.err
mysqlx_socket = /data/p3308/mysqlx3308.sock
 
[mysqld3309]
server-id = 1029
port = 3309
mysqlx_port = 33090
basedir = /mysql
datadir = /data/p3309
socket = /data/p3309/mysql3309.sock
pid-file = /data/p3309/mysql3309.pid
log-error = /data/p3309/mysql3309.err
mysqlx_socket = /data/p3309/mysqlx3309.sock