MySQL多实例部署

目录

1配置文件my.cnf

2数据库软件安装

2.1解压安装包 

3多实例部署

3.1初始化3306至3309四个实例

3.2启动实例

3.3统一修改密码

3.4检验

4设置开机自启

---

1配置文件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

2数据库软件安装

2.1解压安装包 

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

 2.2创建用户和目录

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

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

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

source /etc/profile

3多实例部署

3.1初始化3306至3309四个实例

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

3.2启动实例

mysqld_multi start 3306-3309        #启动

mysqld_multi stop 3306-3309        #关闭

mysqld_multi report 3306-3309      #查看状态

3.3统一修改密码

因为前面配置已经跳过密码验证登陆，因此这里我们可以直接免密批量修改密码。修改完之后记得取消免密登陆设置并重启所有数据库实例。

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;

3.4检验

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;

4设置开机自启

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