MySQL multi-instance configuration solution

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

You may also be interested in:
  • In-depth analysis based on MySQL multi-instance installation
  • MySQL database introduction: detailed explanation of multi-instance configuration method
  • MySQL tutorial on how to deploy multiple instances on a single machine using mysqld_multi
  • MySQL multi-instance installation boot auto-start service configuration process

<<:  How to add a paging navigation bar to the page through Element UI

>>:  Detailed tutorial on installing Protobuf 3 on Ubuntu

Recommend

How to solve the phantom read problem in MySQL

Table of contents Preface 1. What is phantom read...

MySQL online deadlock analysis practice

Preface I believe that everyone has had a simple ...

Docker Detailed Illustrations

1. Introduction to Docker 1.1 Virtualization 1.1....

How to automatically back up the mysql database regularly

We all know that data is priceless. If we don’t b...

CSS to achieve compatible text alignment in different browsers

In the front-end layout of the form, we often nee...

Detailed explanation of browser negotiation cache process based on nginx

This article mainly introduces the detailed proce...

How to install Docker CE on Ubuntu 18.04 (Community Edition)

Uninstall old versions If you have installed an o...

Analysis of the process of deploying pure HTML files in Tomcat and WebLogic

1. First, the pure HTML file must have an entry i...

MySQL configuration master-slave server (one master and multiple slaves)

Table of contents Ideas Host Configuration Modify...

Docker's flexible implementation of building a PHP environment

Use Docker to build a flexible online PHP environ...