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 implement nginx smooth restart

1. Background During the server development proce...

Steps to repair grub.cfg file corruption in Linux system

Table of contents 1. Introduction to grub.cfg fil...

Steps for IDEA to integrate Docker to achieve remote deployment

1. Enable remote access to the docker server Log ...

CSS position fixed left and right double positioning implementation code

CSS Position The position attribute specifies the...

A detailed introduction to JavaScript primitive values ​​and wrapper objects

Table of contents Preface text Primitive types Pr...

Differences between proxy_pass in two modules in nginx

1. The proxy_pass directive of the 1.ngx_stream_p...

Vue3 Vue Event Handling Guide

Table of contents 1. Basic event handling 2. Send...

How to create https using nginx and Tencent Cloud free certificate

I have been studying how to get https. Recently I...

Understanding v-bind in vue

Table of contents 1. Analysis of key source code ...

Detailed explanation of Vue save automatic formatting line break

I searched for many ways to change it online but ...

Tutorial on installing MySQL on Alibaba Cloud Centos 7.5

It seems that the mysql-sever file for installing...

Velocity.js implements page scrolling switching effect

Today I will introduce a small Javascript animati...

Detailed explanation of how to dynamically set the browser title in Vue

Table of contents nonsense text The first router/...

How to determine if the Linux system is installed on VMware

How to determine whether the current Linux system...