Running two or more MySQL instances on a single Linux server

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

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

I think this approach is not entirely correct, if only because it is not always possible to correctly complete such a running mysql server. You can, of course, still use mysqld_multi , but personally, I prefer to completely "isolate" the instances from each other. That is why I suggest that you use the initialization system in your publication to help you.

Given: OS Linux CentOS 6.5. I won’t tell you how to install a MySQL server or its derivatives (for example, Percona), I will get right to the point. Delivered, launched.

[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 started, everything works. All options are by default, unless otherwise indicated in my.cnf .

Now let's start the second instance. Create a place for the 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

Let's take a separate directory in / var / run:

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

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

Edit /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

Well, finally, prepare the init script to run the second server. Here the fun 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 palm off mysqld_safe default values:

#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 necessary 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 the logs to a separate folder. To do this, we bring the following line to approximately this 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 &

Save. We start by executing /etc/init.d/mysql2 start. Stop: /etc/init.d/mysql2 stop. Verification:

[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 we see that everything worked out for us.

Thanks for attention.

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