MySQL multiple instances on Ubuntu

Assumptions
  1. OS: Ubuntu 16.x edition – up to date
  2. Already has MySQL installed that comes default – you can easily install LAMP
  3. MySQL Server version: > 5.5
  4. You have OS root privileges
  5. Default MySQL is running on port 3306
What will we do
  1. Set up 1 more MySQL instances on ports 3407
  2. Each instance will have their own config files, data directories and log directories
Stopping default MySQL instance
sudo service mysql stop
sudo 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 = 3408 and data dir = /var/lib/mysql3408
  • MySQL must own data dirs
  • we need to set rules in apparmor to let MySQL access the new dirs
sudo mkdir /var/lib/mysql3408
sudo chown -R mysql /var/lib/mysql3408
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/mysql3408
  • log dirs must be owned by MySQL
  • note that /var/log/mysql already has apparmor permissions for MySQL, so any dir under it also has access
sudo mkdir /var/log/mysql/mysql3408
sudo chown -R mysql /var/log/mysql/mysql3408
Creating config files
  • create the config files for new instances by copying default file
  • default config file directory = /etc/mysql
  • config directory for 3408 = /etc/mysql3408
  • copy config files from the default server  
  • cp -r  /etc/mysql/* /etc/mysql3408
  • special care has to be taken so that these values are different
  • datadir
  • server-id
  • all port entries
  • all socket entries
  • all pid-file entries
  • all log file entries, general, error, binary etc
sudo cp -r  /etc/mysql/* /etc/mysql3408
Apparmor settings ( skip if you dont have this )
  • apparmor is like an application firewall – comes installed default with Ubuntu server
  • command aa-status will show you if it is 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
  • /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 each new data dir, you will see the mysql, performance_schema and test dirs
  • this will 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.cnf
sudo 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 start
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql
Accessing the new instances
  • Note that the new instances on 3307 and 3308 will not have a root password
  • it is important to specify host and host=127.0.0.1
  • if host is omitted, or localhost is 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 root
mysql -h 127.0.0.1 --port=3308 -u root
Shutting down the MySQL instances
  • We will 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 shutdown
mysqladmin -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';
drop database test;
drop user ''@'localhost';
drop user ''@'%';
drop 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 will look like this
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql
exit 0
Troubleshooting
  • Most of the time, the problem is due to incorrect permissions, or incorrect config files or apparmor
  • Check error logs in /var/log/mysql for each instance
  • Make sure that each mysql config has different values for variables
  • Make sure that directory permissions are 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 in the config file for the instance
  • if connecting from remote host, make sure that ports 3307 and 3308 are open and no other applications are using them
  • Make sure that all dirs have the apparmor permissions and you have reloaded apparmor.
  • You 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, you can set up bash aliases
  • Put the below aliases or whatever you want, in a file called .bash_aliases in your home dir
  • If the file is not found, create it in ~/.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 mysql3308-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'
Stopping default MySQL instance
sudo service mysql stop
sudo 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 to let MySQL access the new dirs
sudo mkdir /var/lib/mysql3307
sudo mkdir /var/lib/mysql3308
sudo chown -R mysql /var/lib/mysql3307
sudo chown -R mysql /var/lib/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 has apparmor permissions for MySQL, so any dir under it also has access
sudo mkdir /var/log/mysql/mysql3307
sudo mkdir /var/log/mysql/mysql3308
sudo chown -R mysql /var/log/mysql/mysql3307
sudo 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 has to be taken so that these values are different
  • datadir
  • server-id
  • all port entries
  • all socket entries
  • all pid-file entries
  • all log file entries, general, error, binary etc
sudo cp /etc/mysql/my.cnf /etc/mysql/my3307.cnf
sudo cp /etc/mysql/my.cnf /etc/mysql/my3308.cnf
Apparmor settings ( skip if you dont have this )
  • apparmor is like an application firewall – comes installed default with Ubuntu server
  • command aa-status will show you if it is 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 each new data dir, you will see the mysql, performance_schema and test dirs
  • this will 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.cnf
sudo 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 start
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql
Accessing the new instances
  • Note that the new instances on 3307 and 3308 will not have a root password
  • it is important to specify host and host=127.0.0.1
  • if host is omitted, or localhost is 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 root
mysql -h 127.0.0.1 --port=3308 -u root
Shutting down the MySQL instances
  • We will 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 shutdown
mysqladmin -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';
drop database test;
drop user ''@'localhost';
drop user ''@'%';
drop 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 will look like this
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql
exit 0
Troubleshooting
  • Most of the time, the problem is due to incorrect permissions, or incorrect config files or apparmor
  • Check error logs in /var/log/mysql for each instance
  • Make sure that each mysql config has different values for variables
  • Make sure that directory permissions are 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 in the config file for the instance
  • if connecting from remote host, make sure that ports 3307 and 3308 are open and no other applications are using them
  • Make sure that all dirs have the apparmor permissions and you have reloaded apparmor.
  • You 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, you can set up bash aliases
  • Put the below aliases or whatever you want, in a file called .bash_aliases in your home dir
  • If the file is not found, create it in ~/.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 mysql3308-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'

Comments

Popular posts from this blog

Setting Up Network RAID1 With DRBD On Ubuntu 12.04

HOWTO remove all dangling commits from your git repository