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:
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.
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:
To store logs:
Copy the default configuration file for the second instance:
Let's take a separate directory in / var / run:
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:
What is the difference between configuration files:
Well, finally, prepare the init script to run the second server. Here the fun begins:
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:
2) We also comment on its use in the future and write unique paths and directories for the new instance into the necessary variables:
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:
Save. We start by executing /etc/init.d/mysql2 start. Stop: /etc/init.d/mysql2 stop. Verification:
Well, finally, we connect using the client to our second MySQL server by explicitly specifying the socket file:
... 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.
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.