MySQL multi-instance configuration solution

1.1 What is MySQL multi-instance?

Simply put, MySQL multi-instance is to open multiple different service ports (such as: 3306/3307/3308) on a server at the same time and run multiple MySQL servers at the same time. These service processes listen to different service ports through different sockets to provide services.

These Mysql instances share a set of Mysql installation programs, use different my.cnf configuration files, startup programs, and data files. When providing services, the multiple Mysql instances are logically independent of each other. They obtain the corresponding resources of the server according to the corresponding settings in the configuration files.

1.2 Multi-instance configuration ideas:

1. Multiple independent directories
2. Each instance has independent data (initialization data)
3. Multiple Ports
4. Multiple sockets
5. Multiple startup programs
6. Multiple Log Files

1.3 Multi-instance configuration process:

1.3.1 Create a separate directory:

mkdir -p /data/{3307,3308}
chown –R mysql.mysql /data

1.3.2 Write the configuration file for each instance:

[root@db01 ~]# cat /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307
socket=/data/3307/mysql.sock
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip_name_resolve=1
server_id=3307
port=3307

1.3.3 Initialization data:

./mysql_install_db --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307 --user=mysql

1.3.4 Start the instance:

sh mysqld_safe --defaults-file=/data/3307/my.cnf --pid-file=/data/3307/3307.pid &

Shell script to manage multi-instance services:

#!/bin/bash
 
. /etc/init.d/functions
./etc/profile
 
Start='/application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf --pid-file=/data/3307/3307.pid'
Stop='mysqladmin -uroot -S /data/3307/mysql.sock shutdown'
Port=`ss -tunlp|grep 3307|wc -l`
 
function START(){
 if [ $Port -ne 1 ];then
  $Start >/dev/null 2>&1 &
  sleep 3
  if [ $? -eq 0 ];then
   action 'MySQL 3307 Starting' /bin/true
  fi
 else
  action 'MySQL 3307 Already Exists' /bin/true
 fi
}
function STOP(){
 if [ $Port -ne 0 ];then
  $Stop
  if [ $? -eq 0 ];then
   action 'MySQL Stopping Successfully' /bin/true
  fi
 else
  action 'MySQL already Stopped' /bin/true
 fi
}
function RESTART(){
 STOP
 sleep 1
 START
}
case $1 in
start)
 START
 ;;
stop)
 STOP
 ;;
restart)
 RESTART
 ;;
*)
 echo "Usage: $0 {start|stop|restart}"
 ;;
esac