Running two or more MySQL instances on the same Linux server

I want to share another way to solve this problem, like running more than one mysql-server on one linux server. I think some of you have already tried to do this by running with your hands, for example, like this:

mysqld_safe --defaults-file=...my2.cnf... & 

I think this approach is not entirely correct, if only because correctly launching such a running mysql server will not always work. You can still, of course, use mysqld_multi , but personally I prefer to completely “isolate” instances from each other. That is why I propose to you in your publication to use the initialization system to help.

Given: OS Linux CentOS 6.5. How to put a MySQL server or its derivatives (for example, Percona), I will not tell, I will go straight to the point. Set, run.

 [root@localhost /]# ps aux | grep sql 

 root 8455 0.0 0.2 5064 1344 pts/0 S 01:36 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql mysql 8566 0.0 3.0 135476 15328 pts/0 Sl 01:36 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306 

The server has started, everything works. All parameters are defaulted, unless otherwise specified in my.cnf .
')
Now let's start the second instance. Create a place for second base:

 mkdir /var/lib/mysql2 && chown -R mysql:mysql /var/lib/mysql2 

To store logs:

 mkdir /var/log/mysql2 && chown -R mysql:mysql /var/log/mysql2 

Copy the default configuration file for the second instance:

 cp /etc/my.cnf /etc/my2.cnf 

Take a separate directory in / var / run:

 mkdir /var/run/mysqld2 && chown -R mysql:mysql /var/run/mysqld2 

It remains to correct my2.cnf and add the init script to /etc/init.d/ to correctly manage the new instance .

Rule /etc/my2.cnf:

 [mysqld] bind-address=127.0.0.1 port=3307 datadir=/var/lib/mysql2 socket=/var/lib/mysql2/mysql2.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysql2/mysqld.log pid-file=/var/run/mysqld2/mysqld.pid 

What is the difference between configuration files:

 [root@localhost mysqld2]# diff /etc/my.cnf /etc/my2.cnf 3,5c3,5 < port=3306 < datadir=/var/lib/mysql < socket=/var/lib/mysql/mysql.sock --- > port=3307 > datadir=/var/lib/mysql2 > socket=/var/lib/mysql2/mysql2.sock 11,12c11,12 < log-error=/var/log/mysqld.log < pid-file=/var/run/mysqld/mysqld.pid --- > log-error=/var/log/mysql2/mysqld.log > pid-file=/var/run/mysqld2/mysqld.pid 

And finally, we will prepare an init-script to start the second server. Here the most interesting begins:

 cp /etc/init.d/mysqld /etc/init.d/mysqld2 

Open and edit individual lines in the /etc/init.d/mysqld2 file:
1) First, we need to comment out (or delete) the get_mysql_option () function so that it does not slip the default values ​​for mysqld_safe:

 #get_mysql_option(){ # result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1` # if [ -z "$result" ]; then # # not found, use default # result="$3" # fi #} 

2) We also comment on its use in the future and write unique paths and directories for the new instance into the required variables:

 #get_mysql_option mysqld datadir "/var/lib/mysql2" datadir="/var/lib/mysql2" #get_mysql_option mysqld socket "$datadir/mysql2.sock" socketfile="$datadir/mysql2.sock" #get_mysql_option mysqld_safe log-error "/var/log/mysql2/mysqld.log" errlogfile="/var/log/mysql2/mysqld.log" #get_mysql_option mysqld_safe pid-file "/var/run/mysqld2/mysqld.pid" mypidfile="/var/run/mysqld2/mysqld.pid" 

3) The final touch: in the start () function, we explicitly tell mysqld_safe to use our separate configuration file and write logs to a separate folder. To do this, we bring the following line to approximately the following form:

 $exec --defaults-file=/etc/my2.cnf --datadir="$datadir" --socket="$socketfile" \ --pid-file="$mypidfile" --log-error=/var/log/mysql2/mysqld.log \ --basedir=/usr --user=mysql >/dev/null 2>&1 & 

We save. Run by executing /etc/init.d/mysql2 start. Stop: /etc/init.d/mysql2 stop. Check:

 [root@localhost run]# ps aux | grep sql2 root 9375 0.0 0.2 5064 1348 pts/1 S 02:44 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my2.cnf --datadir=/var/lib/mysql2 --socket=/var/lib/mysql2/mysql2.sock --pid-file=/var/run/mysqld2/mysqld.pid --log-error=/var/log/mysql2/mysqld.log --basedir=/usr --user=mysql mysql 9489 0.1 3.0 135476 15328 pts/1 Sl 02:44 0:00 /usr/libexec/mysqld --defaults-file=/etc/my2.cnf --basedir=/usr --datadir=/var/lib/mysql2 --user=mysql --log-error=/var/log/mysql2/mysqld.log --pid-file=/var/run/mysqld2/mysqld.pid --socket=/var/lib/mysql2/mysql2.sock --port=3307 root 9510 0.0 0.1 4356 732 pts/1 S+ 02:44 0:00 grep sql2 

Well, finally, we connect using the client to our second MySQL server by explicitly specifying the socket file:

 mysql -S /var/lib/mysql2/mysql2.sock 

... and see that we have succeeded.

Thanks for attention.

PS In the second base directory, you can first use either the default one by copying it from / var / lib / mysql , or you can re-create it using mysql_install_db , or already existing (for example, a backup created by Percona XtraBackup ). The most important thing is the correct user-owner ( mysql ) on the base files.