Guide to run multiplie MySQL instances on same Ubuntu 14.04 Server.
Based mainly on http://java.dzone.com/articles/mysqldmulti-how-run-multiple.
We have an instance, that will "rename" to 'mysql0', and we'll add another one: 'mysql3307'.
/etc/init.d/mysql stop
sudo mv /etc/init.d/mysql /etc/init.d/mysql_mono.server (backup)
sudo cp /usr/share/mysql/mysqld_multi.server /etc/init.d/mysql
edit /etc/init.d/mysql, then find the lines:
and change to:
sudo cp -p /etc/mysql/my.cnf /etc/my.cnf.mono (backup)
edit /etc/mysql/my.cnf to rename [mysqld] to [mysqld0] and to add [mysqld3307] (changes pid-file, socket, port and datadir; also logdir, serverid...)
Also add this to /etc/mysql/my.cnf:
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = multipass
put additional rules to deal with 3307 file/dirs in the application firewall /etc/apparmor.d/local/usr.sbin.mysqld (https://github.com/naveensnayak/mysql-multi/blob/master/usr.sbin.mysqld) or /etc/apparmor.d/usr.sbin.mysqld?
sudo service apparmor reload
sudo mysql_install_db -verbose --user=mysql --datadir=/var/lib/mysql3307
sudo mkdir /var/log/mysql3307
sudo chown mysql:mysql /var/log/mysql3307
Better rename log files in /var/log/mysql instead of create a new dir with same file names?
sudo /etc/init.d/mysql start 0 (mysqladmin -h 127.0.0.1 --port=3306 -u root start)
mysql -h 127.0.0.1 --port=3306 -u root -p
repeat GRANT for mysql3307 connection [mysql -h 127.0.0.1 --port=3307 -u root -p (empty password)]
and give root a password:
mysql> update mysql.user set password=PASSWORD('myRootPassword') where User='root'; flush privileges;
Manage instances:
This is to make client access easier:
create /usr/sbin/mysql3307, and put the following lines on it:
#!/bin/bash
mysql --socket=/var/run/mysqld3307.sock $1 $2 $3 $4 $5 $6 $7 $8
(maybe better with: mysql -h 127.0.0.1 --port=3307 $1 $2 $3 $4 $5 $6 $7 $8)
sudo chmod a+x /usr/sbin/mysql3307
# mysql3307
mysql> select @@server_id;
Finally make sure instances are loaded automatically at startup.
--------------
If you want the new instance to be a slave of a master in another server, you can follow this: