http://naveensnayak.wordpress.com/2013/11/10/mysql-multiple-instances-on-ubuntu/
MySQL multiple instances on UbuntuAssumptions
OS: Ubuntu 12.04 LTS server edition – up to date
Already has MySQL installed that comes default with 12.04 – you can easily install LAMP with the command tasksel
MySQL Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu)
You have OS root privileges
Default MySQL is running on port 3306
What will we do
Set up 2 more MySQL instances on ports 3307 and 3308
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 = 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 data 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/mysql3307sudo 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/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.cnfsudo 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
after modifying, restart apparmor
/etc/apparmor.d/usr.sbin.mysqld
# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
######### mysqld1 on port 3307 ###############
/var/lib/mysql3307/ r,
/var/lib/mysql3307/** rwk,
/var/run/mysqld/mysqld.pid3307 rw,
/var/run/mysqld/mysqld.sock3307 w,
/run/mysqld/mysqld.pid3307 rw,
/run/mysqld/mysqld.sock3307 w,
######### mysqld2 on port 3308 ###############
/var/lib/mysql3308/ r,
/var/lib/mysql3308/** rwk,
/var/run/mysqld/mysqld.pid3308 rw,
/var/run/mysqld/mysqld.sock3308 w,
/run/mysqld/mysqld.pid3308 rw,
/run/mysqld/mysqld.sock3308 w,
$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
如果是centos系统，这样启动：/usr/bin/mysqld_safe --defaults-file= /etc/mysql/my3307.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
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
补充：
防火墙添加3307端口，重启防火墙，
# vi /etc/sysconfig/iptables #编辑防火墙配置文件
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3307 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3308 -j ACCEPT
重启防火墙
# /etc/init.d/iptables restart #最后重启防火墙使配置生效登录多实例的mysql要指定.sock文件
mysql -uroot -p -S /var/lib/mysql3307/mysql3307.sock
mysql -uroot -prx5dbt2c -S /var/lib/mysql3307/mysql3307.sock
创建密码：
mysqladmin -u root -S /var/lib/mysql3307/mysql3307.sock password 'rx5dbt2c'
授权远程登录
mysql>grant all privileges on *.* to 'root'@'%' identified by 'rx5dbt2c' with grant option;
mysql> flush privileges;
