HostsVault BlogHostsVault Blog

One of our users demanded 2 mysql instances on his dedicated server as he had his  mysql hacked before on his previous host so he wanted to separate his own databases in a completely different directory and even using another MySQL process to assure physical separation I will show you here how to accomplish this

First create another mysql directory :

mkdir /var/lib/mysql2
chown mysql.mysql /var/lib/mysql2/

Then make a copy of your current my.cnf :

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

Next edit/add this inside your my2.cnf :

[mysqld]
pid-file = /var/lib//mysql2/mysql2.pid
socket = /var/lib/mysql2/mysql2.sock
port = 3300 # (or any other random port of your choice)
datadir = /var/lib/mysql2
log = /var/log/mysql2.log

Then you will have to initialize MySQL on your new datadir :

mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

Now we are ready to start this instance

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

The reason of adding & in the end is to make this command run in the background and bring  you back to your normal bash shell.

But hey now how can I connect to this new instance ,its easy just use this command :

mysql -h localhost --port=3300

If you need to use mysqldump use this syntax :

mysqldump -s=/var/lib/mysql2/mysql2.sock dbase_name > db.sql

for an added layer of security you can disable remote MySQL access by adding this under [mysqld] section in your my2.cnf :

skip-networking