9de7bb31d0644e7393039222babfed10
上次记录了软件包安装MySQL8, 本次演示怎么配置多实例.
有2种方式,第一种是多服务, 第二种是单服务。
在二进制安装的时候这两种方式都演示过了,本次只演示多服务的方式。
但不同的是,
二进制配置多实例演示的是systemd的多服务,
本次演示包含systemd多服务(适用于linux7及以上版本,含ubuntu )和sysvinit多服务(适用于linux6)。
个人习惯,不喜欢一个个编辑配置文件, 所以使用了脚本,复制粘贴就可以一次完成。
systemd多服务(centos7.9)
本次配置了4个实例,分别端口是3307,3308,4307,4308。
同样适用于oracle Linux7, CentOS7, Redhat7, Ubuntu等发行版
for i in {3307,3308,4307,4308} #必须使用4位数端口, mysqlx_port后面补0 admin_port后面补2
do
mkdir -p /u01/MySQL/${i}/{data,temp} #准备数据目录
[ -f /var/log/mysqld${i}.log ] || ( touch /var/log/mysqld${i}.log ; chown mysql:mysql /var/log/mysqld${i}.log ) #日志文件
cat > /etc/my${i}.cnf <<EOF #准备配置文件
[mysqld]
server_id=$(echo ${RANDOM}${RANDOM}${RANDOM}|awk '{print substr($1,1,9)}')
port=${i}
mysqlx_port=${i}0
datadir=/u01/MySQL/${i}/data
socket=/u01/MySQL/${i}/data/mysql.sock
mysqlx_socket=/run/mysqld/mysqlx${i}.sock
log_error=/var/log/mysqld${i}.log
pid-file=/var/run/mysqld/mysqld${i}.pid
log_bin=binlog
log_bin_index=binlog.index
lc-messages=en_US
lc-messages-dir=/usr/share/mysql-8.0
relay_log=relay-log
relay_log_index=relay-log.index
transaction-isolation=repeatable-read
user=mysql
gtid-mode=on
enforce_gtid_consistency=on
binlog_checksum=none
disabled_storage_engines="MyISAM,BLACKHOLE,ARCHIVE,MEMORY"
tmpdir=/u01/MySQL/${i}/temp
innodb_buffer_pool_size=4294967296
binlog_cache_size=1M
group_concat_max_len=4294967295
log-bin-trust-function-creators=on
skip_name_resolve=on
default_authentication_plugin=mysql_native_password
max_connections=1000
wait_timeout=1200
interactive_timeout=1200
log_timestamps=system
default_time_zone='+8:00'
federated=on
admin_address=127.0.0.1
admin_port=${i}2
event_scheduler=on
local_infile=on
innodb_print_all_deadlocks=on
EOF
chown -R mysql:mysql /u01/MySQL/${i} #权限
mysqld --initialize --user=mysql --datadir=/u01/MySQL/${i}/data #初始化数据库, 密码在 /var/log/mysqld.log中
cat > /usr/lib/systemd/system/mysqld${i}.service<<EOF #systemd配置文件
[Unit]
Description=MySQL Server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=notify
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/sbin/mysqld --defaults-file=/etc/my${i}.cnf
LimitNOFILE = 10000
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
EOF
systemctl --now enable mysqld${i}
done
#查看密码, 最后面的几个
grep password /var/log/mysqld.log
#检查
for i in {3307,3308,4307,4308}
do
systemctl status mysqld${i}
ps -ef|grep mysql|grep "my${i}.cnf"|grep -v "grep"
netstat -tunlp |grep mysqld |grep ":${i} "
echo
done
SysVinit多服务(oracle6.6)
本次演示了2个实例,端口分别是3307,3308。
适用于oracle linux6, CentOS6, Redhat6等。
for i in {3307,3308} #必须使用4位数端口, mysqlx_port后面补0
do
mkdir -p /u01/MySQL/${i}/{data,temp} #准备数据目录
[ -f /var/log/mysqld${i}.log ] || ( touch /var/log/mysqld${i}.log ; chown mysql:mysql /var/log/mysqld${i}.log ) #日志文件
cat > /etc/my${i}.cnf <<EOF #准备配置文件
[mysqld]
server_id=$(echo ${RANDOM}${RANDOM}${RANDOM}|awk '{print substr($1,1,9)}')
port=${i}
mysqlx_port=${i}0
datadir=/u01/MySQL/${i}/data
socket=/u01/MySQL/${i}/data/mysql.sock
mysqlx_socket=/var/run/mysqld/mysqlx${i}.sock
log_error=/var/log/mysqld${i}.log
pid-file=/var/run/mysqld/mysqld${i}.pid
log_bin=binlog
log_bin_index=binlog.index
lc-messages=en_US
lc-messages-dir=/usr/share/mysql-8.0
relay_log=relay-log
relay_log_index=relay-log.index
transaction-isolation=repeatable-read
user=mysql
gtid-mode=on
enforce_gtid_consistency=on
binlog_checksum=none
disabled_storage_engines="MyISAM,BLACKHOLE,ARCHIVE,MEMORY"
tmpdir=/u01/MySQL/${i}/temp
innodb_buffer_pool_size=4294967296
binlog_cache_size=1M
group_concat_max_len=4294967295
log-bin-trust-function-creators=on
skip_name_resolve=on
default_authentication_plugin=mysql_native_password
max_connections=1000
wait_timeout=1200
interactive_timeout=1200
log_timestamps=system
default_time_zone='+8:00'
federated=on
admin_address=127.0.0.1
admin_port=${i}2
event_scheduler=on
local_infile=on
innodb_print_all_deadlocks=on
EOF
chown -R mysql:mysql /u01/MySQL/${i} #权限
mysqld --initialize --user=mysql --datadir=/u01/MySQL/${i}/data #初始化数据库, 密码在 /var/log/mysqld.log中
cat > /etc/init.d/mysqld${i} <<EOF #SysVinit 配置文件
#!/bin/sh
# chkconfig: 345 64 36
. /etc/rc.d/init.d/functions
. /etc/sysconfig/network
start(){
if [ -e /var/run/mysqld/mysqld${i}.pid ]; then
action "Starting mysqld: [ OK ]" /bin/false
else
/usr/bin/mysqld_safe --defaults-file=/etc/my${i}.cnf &>/dev/null &
action "Starting mysqld: [ OK ]" /bin/true
fi
}
stop(){
if [ -e /var/run/mysqld/mysqld${i}.pid ]; then
/bin/su - mysql -s /bin/bash -c "/bin/kill \$(cat "/var/run/mysqld/mysqld${i}.pid")" >/dev/null 2>&1
action "Stopping mysqld: [ OK ]" /bin/true
else
action "Stopping mysqld: [ OK ]" /bin/false
fi
}
status(){
if [ -e /var/run/mysqld/mysqld${i}.pid ]; then
action "MySQL is running"
else
action "MySQL is stopped"
fi
}
restart(){
stop
sleep 3
start
}
case "\$1" in
start)
start
;;
stop)
stop
;;
status)
status
;;
restart)
restart
;;
*)
echo \$"Usage: \$0 {start|stop|status|restart}"
exit 2
esac
EOF
chmod a+rx /etc/init.d/mysqld${i}
service mysqld${i} start
chkconfig mysqld${i} on
done
#查看密码, 最后面的几个
grep password /var/log/mysqld.log
#检查
sleep 5
for i in {3307,3308}
do
service mysqld${i} status
ps -ef|grep mysql|grep "my${i}.cnf"|grep -v "grep"
netstat -tunlp |grep mysqld |grep ":${i} "
echo
done
修改密码
以上多服务配置启动后,密码根据初始化的顺序记录在日志文件中,找出来就可以修改管理员密码了, 注意顺序,以下命令是个参考。
mysql -S /u01/MySQL/3307/data/mysql.sock -u root -p #使用 mysqld.log 中的密码, 按先后顺序
set password='********';
至此,多实例配置就完成了。谢谢,关注防走失
9de7bb31d0644e7393039222babfed10