Server support blog

There are plenty instruction on how to run multiple mysql instances on a single server on various blogs. Most of them are based on the original mysql rpm package produced by Oracle (nowdays). In my opinion this could be unnecessary overhead when you have OS provided mysql server package and all you need to configure another instance is to slightly modify bundled configuration and init.d script.

To separate new instance from default mysql server configuration we will use

• server port 3307
• pid file /var/run/mysqld/mysql2.pid
• server config file /etc/my2.cnf
• Data dir (for all database file storage) /home/mysql2
• Server log file /var/log/mysqld2.log
• Socket file /home/mysql2/mysql.sock

1. First we need to make /etc/my2.cnf –
   here is original minimal /etc/my.cnf
   Shell

   [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package).   [mysql.server] user=mysql basedir=/var/lib   [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

   And here modified /etc/my2.cnf

   /etc/my2.cnf

   Shell

   [mysqld] datadir=/home/mysql2 socket=/home/mysql2/mysql.sock port=3307 user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld2.log pid-file=/var/run/mysqld/mysqld2.pid

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   [mysqld] datadir=/home/mysql2 socket=/home/mysql2/mysql.sock port=3307 user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package).   [mysql.server] user=mysql basedir=/var/lib   [mysqld_safe] err-log=/var/log/mysqld2.log pid-file=/var/run/mysqld/mysqld2.pid

   Or as patch file

   /etc/my2.cnf patch

   Shell

   --- /etc/my2.cnf.old 2013-11-15 09:30:18.149241693 +0200 +++ /etc/my2.cnf 2013-11-15 09:30:51.767247315 +0200 @@ -1,6 +1,7 @@ [mysqld] -datadir=/var/lib/mysql -socket=/var/lib/mysql/mysql.sock +datadir=/home/mysql2 +socket=/home/mysql2/mysql.sock +port=3307 user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). @@ -13,7 +14,7 @@ basedir=/var/lib [mysqld_safe] -err-log=/var/log/mysqld.log -pid-file=/var/run/mysqld/mysqld.pid +err-log=/var/log/mysqld2.log +pid-file=/var/run/mysqld/mysqld2.pid

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   --- /etc/my2.cnf.old    2013-11-15 09:30:18.149241693 +0200 +++ /etc/my2.cnf        2013-11-15 09:30:51.767247315 +0200 @@ -1,6 +1,7 @@ [mysqld] -datadir=/var/lib/mysql -socket=/var/lib/mysql/mysql.sock +datadir=/home/mysql2 +socket=/home/mysql2/mysql.sock +port=3307 user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). @@ -13,7 +14,7 @@ basedir=/var/lib [mysqld_safe] -err-log=/var/log/mysqld.log -pid-file=/var/run/mysqld/mysqld.pid +err-log=/var/log/mysqld2.log +pid-file=/var/run/mysqld/mysqld2.pid

   You can download patch file and apply it

   Shell

   cd /etc cp my.cnf my2.cnf patch -b --verbose < ~/my2.cnf.patch

   1 2 3

   cd /etc cp my.cnf my2.cnf patch -b --verbose < ~/my2.cnf.patch

2. Next step – create modified startup script, that would read proper configuration file, extract port number from it (by default port number is not passed) and invoke mysqld process with set parameters. Here is the patch:
   mysqld2.patch

   Shell

   --- /etc/init.d/mysqld2.old 2013-11-15 12:59:31.561236256 +0200 +++ /etc/init.d/mysqld2 2013-11-15 13:54:08.047233073 +0200 @@ -5,9 +5,9 @@ # # chkconfig: - 64 36 # description: MySQL database server. -# processname: mysqld -# config: /etc/my.cnf -# pidfile: /var/run/mysqld/mysqld.pid +# processname: mysqld2 +# config: /etc/my2.cnf +# pidfile: /var/run/mysqld/mysqld2.pid # Source function library. . /etc/rc.d/init.d/functions @@ -15,9 +15,11 @@ # Source networking configuration. . /etc/sysconfig/network +mycnf="/etc/my2.cnf" + exec="/usr/bin/mysqld_safe" -prog="mysqld" +prog="mysqld2" # Set timeouts here so they can be overridden from /etc/sysconfig/mysqld STARTTIMEOUT=120 @@ -34,7 +36,7 @@ lockfile=/var/lock/subsys/$prog # We use my_print_defaults which prints all options from multiple files, # with the more specific ones later; hence take the last match. get_mysql_option(){ - result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1` + result=`/usr/bin/my_print_defaults -c $mycnf "$1" | sed -n "s/^--$2=//p" | tail -n 1` if [ -z "$result" ]; then # not found, use default result="$3" @@ -45,6 +47,8 @@ get_mysql_option mysqld datadir "/var/li datadir="$result" get_mysql_option mysqld socket "$datadir/mysql.sock" socketfile="$result" +get_mysql_option mysqld port "3306" +port="$result" get_mysql_option mysqld_safe log-error "/var/log/mysqld.log" errlogfile="$result" get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid" @@ -98,6 +102,7 @@ start(){ # alarms, per bug #547485 $exec --datadir="$datadir" --socket="$socketfile" \ --pid-file="$mypidfile" \ + --port="$port" \ --basedir=/usr --user=mysql >/dev/null 2>&1 & safe_pid=$! # Spin for a maximum of N seconds waiting for the server to come up;

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

   --- /etc/init.d/mysqld2.old     2013-11-15 12:59:31.561236256 +0200 +++ /etc/init.d/mysqld2 2013-11-15 13:54:08.047233073 +0200 @@ -5,9 +5,9 @@ # # chkconfig: - 64 36 # description: MySQL database server. -# processname: mysqld -# config: /etc/my.cnf -# pidfile: /var/run/mysqld/mysqld.pid +# processname: mysqld2 +# config: /etc/my2.cnf +# pidfile: /var/run/mysqld/mysqld2.pid # Source function library. . /etc/rc.d/init.d/functions @@ -15,9 +15,11 @@ # Source networking configuration. . /etc/sysconfig/network +mycnf="/etc/my2.cnf" + exec="/usr/bin/mysqld_safe" -prog="mysqld" +prog="mysqld2" # Set timeouts here so they can be overridden from /etc/sysconfig/mysqld STARTTIMEOUT=120 @@ -34,7 +36,7 @@ lockfile=/var/lock/subsys/$prog # We use my_print_defaults which prints all options from multiple files, # with the more specific ones later; hence take the last match. get_mysql_option(){ -       result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1` +       result=`/usr/bin/my_print_defaults -c $mycnf  "$1" | sed -n "s/^--$2=//p" | tail -n 1`         if [ -z "$result" ]; then             # not found, use default             result="$3" @@ -45,6 +47,8 @@ get_mysql_option mysqld datadir "/var/li datadir="$result" get_mysql_option mysqld socket "$datadir/mysql.sock" socketfile="$result" +get_mysql_option mysqld port "3306" +port="$result" get_mysql_option mysqld_safe log-error "/var/log/mysqld.log" errlogfile="$result" get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid" @@ -98,6 +102,7 @@ start(){         # alarms, per bug #547485         $exec   --datadir="$datadir" --socket="$socketfile" \                 --pid-file="$mypidfile" \ +               --port="$port" \                 --basedir=/usr --user=mysql >/dev/null 2>&1 &         safe_pid=$!         # Spin for a maximum of N seconds waiting for the server to come up;

   Download the patch to ~/mysqld2.patch and apply

   Shell

   cd /etc/init.d/ cp mysqld mysql2 patch --verbose < ~/mysqld2.patch

   1 2 3

   cd /etc/init.d/ cp mysqld mysql2 patch --verbose < ~/mysqld2.patch

3. Now after everything configured we can start new instance and let it configure new data dir with service databases and permissions
   Shell

   /etc/init.d/mysqld2 start Initializing MySQL database: Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h centos password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/bin/mysqlbug script! [ OK ] Starting mysqld: [ OK ]

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

   /etc/init.d/mysqld2 start Initializing MySQL database:   Installing MySQL system tables... OK Filling help tables... OK   To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system   PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:   /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h centos password 'new-password'   Alternatively you can run: /usr/bin/mysql_secure_installation   which will also give you the option of removing the test databases and anonymous user created by default.  This is strongly recommended for production servers.   See the manual for more instructions.   You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe &   You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl   Please report any problems with the /usr/bin/mysqlbug script!                                                              [  OK  ] Starting mysqld:                                           [  OK  ]

Now you have fully functional second instance of mysqld server running with the only overhead of modified configuration files. You can create as many instances as you need the same way.