MultiMySQL

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:

• basedir=/usr/local/mysql
• bindir=/usr/local/mysql/bin

and change to:

• basedir=/usr
• bindir=/usr/bin

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

• mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
• mysql> FLUSH PRIVILEGES;

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:

• sudo mysqld_multi report
• sudo mysqld_multi start 3307
• sudo mysqld_multi stop 0,3307

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:
http://java.dzone.com/articles/mysqldmulti-how-run-multiple (this related: http://dba.stackexchange.com/questions/41050/is-it-safe-to-delete-mysql-bin-files)