There are various method to setup multiple of mysql instances on a single linux ubuntu server. In this scope of this page, i will show you the most simple one.
You can follow the steps below to get it done:
1. Create separate data and log directories
We need to create new directories for our datadir and log folder (if used). Also we need to assign proper permissions on those folders:
mkdir /var/lib/mysql2
chown -R mysql.mysql /var/lib/mysql2/
mkdir /var/log/mysql2
chown -R mysql.mysql /var/log/mysql2
2. Create a new mysql configuration file
Next we need a separate configuration file. We can start by copying the existing one and changing the needed values. This example was taken on a debian machine that holds the mysql configurations under /etc/mysql/my.cnf. We just copy this folder and modify it from there:
cp -R /etc/mysql/ /etc/mysql2
if you use a redhat variant package then your configuration file is under /etc/my.cnf by default and you can just copy it directly:
cp /etc/my.cnf /etc/my2.cnf
(or change the path appropriately for your configuration file is in a different place).
Next, we need to edit our new configuration file and at least update the mysql port (default to 3306), the pid and socket to be different than the default ones, and also point the data andlog folders to the ones created before. On my debian machine this can be done with something like this:
cd /etc/mysql2/
sed -i 's/3306/3307/g' my.cnf
sed -i 's/mysqld.sock/mysqld2.sock/g' my.cnf
sed -i 's/mysqld.pid/mysqld2.pid/g' my.cnf
sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf
sed -i 's/var\/log\/mysql/var\/log\/mysql2/g' my.cnf
3. Initializing and starting
Finally we need to initialize the default dbs:
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/
Alternatively we can copy the existing /var/lib/mysql if this is needed (shut down mysql prior to do this).
Finally we can start our new mysql instance with:
mysqld_safe --defaults-file=/etc/mysql2/my.cnf &
We can connect to our new instance using:
mysql -S /var/run/mysqld/mysqld2.sockor
mysql -h 127.0.0.1 -P 3307
and if we no longer need it, stop it with:
mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown
Problems you might have:
- At step 3 , if you can’t not create the database , it maybe because apparmor is running, this security application prohibiting mysql user to work on other folders , to let appamor allows mysql to access some folders we have to modify this file . The error looks like this: john@pc:~/$ mysql_install_db –user=mysql –datadir=/var/lib/mysql2/ Installing MySQL system tables… 120307 22:36:58 [Warning] Can’t create test file /var/lib/mysql2/pc.lower-test 120307 22:36:58 [Warning] Can’t create test file /var/lib/mysql2/pc.lower-test Installation of system tables failed! Examine the logs in
To Fix it , edit this file: /etc/apparmor.d/usr.sbin.mysqld :
nano /etc/apparmor.d/usr.sbin.mysqld
Here is a sample file that i created
/var/lib/mysql/ r, /var/lib/mysql/* rwk, /var/log/mysql/ r, /var/log/mysql/ rw, /var/run/mysqld/mysqld.pid w, /var/run/mysqld/mysqld.sock w,
#new section /var/lib/mysql[1-9]/* rwk, /var/log/mysql[1-9]/ rw, /etc/mysql2/* r, /var/run/mysqld/mysqld[1-9].pid w, /var/run/mysqld/mysqld[1-9].sock w,
After that , you need to restart apparmor /etc/init.d/apparmor restart
After this, re-install the db.