Running multiple mysql instances on one machine

Installation instructions:

A mysql program file, a configuration file, running multiple mysql instances (each with its own distinct data directory).For the convenience of the test, only two examples are used in this experiment.

MySQL program installation directory: /usr/local/mysql_5.6.38
Data directories for MySQL instances: /data/mysql/data1 and/data/mysql/data2, corresponding to data directories for port 3306 and 3307 instances, respectively

setup script

Instantiate Data Catalog

By running $MYSQL_HOME/scripts/mysql_install_db script instantiates MySQL data directory

[root@host3 mysql_5.6.38]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql_5.6.38/ --datadir=/data/mysql/data1/
[root@host3 mysql_5.6.38]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql_5.6.38/ --datadir=/data/mysql/data2/

Modify Profile

This configuration does not require a socket parameter when the client connects to 3306 instances, but must add a socket parameter when connecting 3307 instances, otherwise the login will not be possible

The configuration information is as follows

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[client]
port=3306
socket=/data/mysql/data1/mysql.sock
default-character-set=utf8

[mysqld_multi]
mysqld=/usr/local/mysql_5.6.38/bin/mysqld_safe
mysqladmin=/usr/local/mysql_5.6.38/bin/mysqladmin
#user=mysql
#password=123456

[mysqld1]
port=3306
socket=/data/mysql/data1/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M
basedir=/usr/local/mysql_5.6.38
datadir=/data/mysql/data1
#binary logging
log-bin=mysql-bin
binlog-format=Row
character-set-server=utf8
default-storage-engine=INNODB
log-error=/data/mysql/data1/mysqld.log
pid-file=/data/mysql/data1/mysqld.pid

[mysqld2]
port=3307
socket=/data/mysql/data2/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M
basedir=/usr/local/mysql_5.6.38
datadir=/data/mysql/data2
#binary logging
log-bin=mysql-bin
binlog-format=Row
character-set-server=utf8
default-storage-engine=INNODB
log-error=/data/mysql/data2/mysqld.log
pid-file=/data/mysql/data2/mysqld.pid
[mysqldump]
quick

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

Run mysqld_safe (run this way when first installed)

[root@host3 mysql_5.6.38]# ./bin/mysqld_safe --user=mysql --basedir=/usr/local/mysql_5.6.38/ --datadir=/data/mysql/data1/ --port=3306 --socket=/data/mysql/data1/mysql.sock &
[root@host3 mysql_5.6.38]# ./bin/mysqld_safe --user=mysql --basedir=/usr/local/mysql_5.6.38/ --datadir=/data/mysql/data2/ --port=3307 --socket=/data/mysql/data2/mysql.sock &

Stop mysql service

[root@host3 mysql_5.6.38]# ./support-files/mysqld_multi.server stop 1,2

perhaps

[root@host3 mysql_5.6.38]# ./bin/mysqladmin -u root -S /data/mysql/data1/mysql.sock -P 3306 -p123456 shutdown
[root@host3 mysql_5.6.38]# ./bin/mysqladmin -u root -S /data/mysql/data2/mysql.sock -P 3307 -p123456 shutdown

Follow-up Startup Service

Execute mysqld_Multi.serverBefore scripting, modify the values of basedir and bindir based on the actual path installed

[root@host3 mysql_5.6.38]# ./support-files/mysqld_multi.server start 1,2

Client login

[root@host3 mysql_5.6.38]# ./bin/mysql --user=root --socket=/data/mysql/data1/mysql.sock --port=3306 -p

Reference Documents

Manage multiple mysql instances

Run mysql multiple instances

Connecting mysql multiple instances with client program

Tags: MySQL socket mysqladmin mysqldump

Posted on Thu, 16 Jul 2020 12:08:54 -0400 by ashutosh.titan