mysql多实例怎么验证_MySQL多实例部署

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

解压安装包

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四个实例

for i in `seq 3306 3309`;do echo $i; sh /mysql/bin/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