MySQL multiple instances on Ubuntu

Production systems must take care of security and tweaking no covered by this post

The post looks long, but you can get another instance of MySQL running in less than 5 mins

Assumptions

1. OS: Ubuntu 12.04 LTS Server Edition –up to date

2. Already have MySQL installed that comes default with 12.04–you can easily install LAMP with the command Tasksel

3. MySQL Server version:5.5.34-0ubuntu0.12.04.1 (Ubuntu)

4. You have OS root privileges

5. Default MySQL is running on port 3306

What would we do

1. Set up 2 + MySQL instances on ports 3307 and 3308

2. Each instance would have their own config files, data directories and log directories

Stopping default MySQL instance

sudo service mysql Stopsudo ps-a | grep MySQL

Creating Data Directories

• MySQL cannot share data directories, so we need to set up new ones

• Default basedir =/usr, this can be shared across instances

• Default instance port = 3306 and data dir =/var/lib/mysql

• new instance port = 3307 and data dir =/var/lib/mysql3307

• new instance port = 3308 and data dir =/var/lib/mysql3308

• MySQL must own data dirs

• we need to set rules in AppArmor MySQL access the new dirs

sudo mkdir/var/lib/mysql3307sudo mkdir/var/lib/mysql3308sudo chown-r mysql/var/lib/mysql3307sudo chown-r mysql/var/l ib/mysql3308

Creating Log Directories

• Create separate log dirs for new MySQL instances

• Default log dir = /var/log/mysql

• New log dir for 3307 = /var/log/mysql/mysql3307

• New log dir for 3308 = /var/log/mysql/mysql3308

• Log dirs must be owned by MySQL

• Note That/var/log/mysql already have AppArmor permissions for MySQL, so any dir under it also have access

sudo mkdir/var/log/mysql/mysql3307sudo mkdir/var/log/mysql/mysql3308sudo chown-r mysql/var/log/mysql/mysql3307sudo Chown-r mysql/var/log/mysql/mysql3308

Creating Config files

• Create the config files for new instances by copying default file

• Default Config file = /etc/mysql/my.cnf

• Config file for 3307 = /etc/mysql/my3307.cnf

• Config file for 3308 = /etc/mysql/my3308.cnf

• See Config files on GitHub

• /etc/mysql/my3307.cnf

• /etc/mysql/my3308.cnf

• Special care have to is taken so, these values are different

• DataDir

• Server-id

• All Port Entries

• All socket Entries

• All Pid-file Entries

• All logs file entries, general, error, binary etc

sudo cp/etc/mysql/my.cnf/etc/mysql/my3307.cnfsudo cp/etc/mysql/my.cnf/etc/mysql/my3308.cnf

Apparmor settings (skip if you dont has this)

• AppArmor is a application Firewall–comes installed default with Ubuntu server

• Command Aa-status 'll show you if it's loaded

• Default AppArmor profile for MySQL = /etc/apparmor.d/usr.sbin.mysqld

• Put additional rules in /etc/apparmor.d/local/usr.sbin.mysqld

• Specify the correct data dirs, PID and socket files for each instance–see file on GitHub

• /etc/apparmor.d/local/usr.sbin.mysqld

• After modifying, restart AppArmor

sudo service AppArmor Reload

Installing new MySQL Instances

• Install MySQL files into the new data dirs for Port 3307 and Port 3308

• After this, under all new Data dir, you'll see the MySQL, Performance_schema and test dirs

• This would install MySQL with default settings, no root password

• In the below commands, you can use the –-verbose flag to see more details

sudo mysql_install_db--user=mysql--basedir=/usr--datadir=/var/lib/mysql3307--defaults-file=/etc/mysql/ My3307.cnfsudo mysql_install_db--user=mysql--basedir=/usr--datadir=/var/lib/mysql3308--defaults-file=/etc/mysql /my3308.cnf

Starting the MySQL instances

• Start the default instance on 3306

• Start instances on 3307 and 3308 in the background

sudo service MySQL startsudo-b mysqld_safe--defaults-file=/etc/mysql/my3307.cnf--user=mysqlsudo-b Mysqld_safe-- DEFAULTS-FILE=/ETC/MYSQL/MY3308.CNF--user=mysql

Accessing the new instances

• Note the new instances on 3307 and 3308 won't have a root password

• It is important to specify host and host=127.0.0.1

• If host is omitted, or localhost was given, then default instance is assumed (on 3306)

• Remember to explicitly specify host and port for all commands

Mysql-h 127.0.0.1--port=3307-u rootmysql-h 127.0.0.1--port=3308-u Root

Shutting down the MySQL instances

• We'll use Mysqladmin to cleanly shutdown

• It is important to specify host and and port

• No password for now

Mysqladmin-h 127.0.0.1--port=3307-u root shutdownmysqladmin-h 127.0.0.1--port=3308-u root shutdown

Post installation set up (for each instance)

• Update Root password

• drop All anonymous Users–check for users with empty username

• Drop DATABASE Test

• Flush Privileges

Update Mysql.user set Password=password (' Myrootpassword ') where user= ' root ';d ROP database test;drop user ' @ ' localhost ' ;d ROP user ' @ '% ';d ROP user ' @ ' ubuntu '; flush privileges;

Starting new instances on boot and reboot

• Put commands in the file /etc/rc.local to start new instances on boot

• The rc.local file would look like this

Sudo-b mysqld_safe--defaults-file=/etc/mysql/my3307.cnf--user=mysqlsudo-b mysqld_safe--defaults-file=/etc/mysql/ MY3308.CNF--user=mysqlexit 0

Troubleshooting

• Most of the time, the problem are due to incorrect permissions, or incorrect config files or apparmor

• Check error logs in/var/log/mysql for each instance

• Make sure, each MySQL config have different values for variables

• Make sure that directory permissions is correct, MySQL must own data and log dirs

• Remember to specify host and port explicitly when connecting

• If connecting from a remote host, check the bind-address config variable with the config file for the instance

• If connecting from remote host, make sure that ports 3307 and 3308 is open and no other applications is using them

• Make sure this all dirs has the AppArmor permissions and you have reloaded apparmor.

• Can see enties like the below in /var/log/syslog if AppArmor is blocking MySQL

Nov 7 11:51:16 ubuntu kernel: [1080.756609] type=1400 Audit (1383843076.476:32): apparmor= "DENIED" operation= "Mknod" parent=2749 profile= "/usr/sbin/mysqld" name= "/var/lib/mysql1/ibdata1" pid=3559 comm= "mysqld" requested_mask= "C" Denied_mask= "C" fsuid=102 ouid=102

Finally–bash Aliases If you need

• To make commands simpler to type, can set up bash aliases

• Put the below aliases or WHA Tever want, in a file called. Bash_aliases in your home dir

• If The file was not found, create it IN&N Bsp ~/.bash_aliases

• Remember to open a new shell for these commands to take effect

########### MySQL @ 3307 ############################# #alias mysql3307-start= ' Sudo-b mysqld_safe--defaults-file=/etc /mysql/my3307.cnf--user=mysql ' Alias mysql3307-stop= ' mysqladmin-h 127.0.0.1--port=3307-u root shutdown-p ' Alias mysql3307-root= ' mysql-h 127.0.0.1--port=3307-u root-p ' ########### mysql @ 3308 ############################# #alias my Sql3308-start= ' Sudo-b mysqld_safe--defaults-file=/etc/mysql/my3308.cnf--user=mysql ' Alias mysql3308-stop= ' Mysqladmin-h 127.0.0.1--port=3308-u Root shutdown-p ' Alias mysql3308-root= ' mysql-h 127.0.0.1--port=3308-u root-p '

MySQL multiple instances on Ubuntu