Skip to main content

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

Post a Comment

Pascal Fares and Open Source Lebanese Movement >

Popular posts from this blog

Setting Up Network RAID1 With DRBD On Ubuntu 12.04

A Network software raid1Setting Up Network RAID1 With DRBD On Ubuntu 12.04 This tutorial shows how to set up network RAID1 with the help of DRBD on two Ubuntu 12.04 systems. DRBD stands for Distributed Replicated Block Device and allows you to mirror block devices over a network (like raid1 with 2 disks). This is useful for high-availability setups because if one node fails, all data is still available from the other node.
1 Preliminary Note We use two servers (both running the same version of linux in our case Ubuntu 12.04): server1.cofares.net (IP address 192.168.10.100)server2.cofares.net (IP address: 192.168.10.101) Both nodes have an unpartitioned second drive (/dev/sdb) with identical size (X GB) that I want to mirror over the network (network RAID1) with the help of DRBD. It is important that both nodes can resolve each other, either through DNS or through /etc/hosts, /etc/hosts would be a better choice (our 2 servers are on the same switch and local. You can modify /etc/hosts on…

HOWTO remove all dangling commits from your git repository

A good explanation of the dangling (fr: ballants) commits source tells you how they get created.

git fsck --fullChecking object directories: 100% (300/300), done. Checking objects: 100% (10658/10658), done. dangling commit x.... dangling blob y.... dangling commit z.... dangling blob w.... dangling blob a.... dangling commit b....
How to quickly remove those?
git reflog expire --expire=now --all git gc --prune=now