MariaDB 10 - multiinstance

Creating and using multiple mysqld instances by mysqld_multi.

Creating multi instance environment

1) create appropriate my.cnf (see https://tiki.uvt.cz/tiki-index.php?page=MariaDB+10+example+my.cnf) in standard way, delete content of /var/lib/mysql and recreate new mysql data by invoking "mysql_install_db" This recreate mysql tables and files regarding new my.cnf (ibdata,..)

2) Run mysql_secure_installation to create some password and so on, and add multiadmin user with shutdown privilege:

mysql> CREATE USER 'multiadmin'@'localhost' IDENTIFIED BY 'multipass';
mysql> GRANT SHUTDOWN ON *.* TO 'multiadmin'@'localhost';

3) shutdown mysql and prepare environment for multi instance mysql, ie. create separate directories (/var/lib/mysql/<instance id>)., create appropriate tmp directories, change owners to mysql and so on. Copy original mysql data to every instance directory and keep original as a template.

4) create mysqld_multi compatible cnf file (see attachment) and create /etc/my.cnf symlink to this file

5) start all instances invoking "mysqld_multi start"

Working with instances

• starting/shuting down all instances: "mysqld_multi start/stop"
• starting/shuting down selected instance: "mysqld_multi start/stop 5905"