To manage multiple MySQL database instances, use mysqld_multi!

The following articles mainly introduce the actual operation process of using mysqld_multi to manage multiple MySQL database instances. MySQL databases can be backed up in real time from multiple master nodes, in google, you can use mysqld_multi to manage multiple MySQL instances.

I have previously thought about running multiple MySQL instances on one server to implement real-time backup in the master-slave mode with multiple masters, today, we can see that mysqld-multi is used to test the configuration process as follows,

1. Modify the mysql configuration file my. cnf to configure the mysqld_multi option.

 
 

  
  
[mysqld_multi]  
  
  
mysqld = /usr/local/mysql/bin/mysqld_safe  
  
  
mysqladmin = /usr/local/mysql/bin/mysqladmin  
  
  
user = multi_admin 
  
  
password = multipass 
  
  
log = /usr/local/mysql/etc/mysqld_multi.log 
 
 

2. Create config-file for mysqld_multi

 
 

  
  
cat /usr/local/mysql/etc/mysqld_multi.cnf   
  
  
[mysqld1]  
  
  
socket = /tmp/mysql.sock1  
  
  
port = 3306 
  
  
pid-file = /usr/local/mysql/var1/localhost.pid  
  
  
datadir = /usr/local/mysql/var1  
  
  
user = mysql 
  
  
[mysqld2]  
  
  
socket = /tmp/mysql.sock2  
  
  
port = 3307 
  
  
pid-file = /usr/local/mysql/var2/localhost.pid  
  
  
datadir = /usr/local/mysql/var2  
  
  
user = mysql 
  
  
[mysqld3]  
  
  
socket = /tmp/mysql.sock3  
  
  
port = 3308 
  
  
pid-file = /usr/local/mysql/var3/localhost.pid  
  
  
datadir = /usr/local/mysql/var3  
  
  
user = mysql 
 
 

3. Copy the mysqld_multi.server script to/usr/sbin in support-files in the source code.

 
 

  
  
cp /mysql_source/support-files/mysqld_multi.server /usr/sbin 
 
 

4. Edit/usr/sbin/mysqld_multi.server (add the-config-file =/usr/local/mysql/etc/mysqld_multi.cnf option)

 
 

  
  
'start' )  
  
  
"$mysqld_multi" start $2  
  
  
;;  
  
  
'stop' )  
  
  
"$mysqld_multi" --config-file=/usr/local/mysql/etc/mysqld_multi.cnf stop $2  
  
  
;;  
  
  
'report' )  
  
  
"$mysqld_multi" --config-file=/usr/local/mysql/etc/mysqld_multi.cnf report $2 
 
 

5. Start Multiple MySQL database instances

 
 

  
  
mysqld_multi.server start 
 
 

6. the user and password set in my. cnf are used to disable the database instance. The shutdown permission is granted to the user according to the user name and password in the configuration.

 
 

  
  
mysql -u root -S /tmp/mysql.sock1 -p  
  
  
mysql> grant shutdown on *.* to multi_admin@'localhost' identified by 'multipass'; 
 
 

7. Close all database instances

 
 

  
  
mysqld_multi.server stop 
 
 

8. Start and close a single database

Syntax: mysqld_multi [options] {start | stop | report} [GNR [, GNR]…]

For example, you can use mysqld_multi to start or disable mysqld1.

Start: mysqd_multi start 1

Close: mysqd_multi stop 1

You can also start and close multiple MySQL database instances at the same time.

Start: mysqd_multi start 1-3

Close: mysqd_multi stop 1-3