ubuntu mysql_multi_mysql multi设置

MySQL multiple instances on Ubuntu

AssumptionsOS: 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 doSet 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

Iconsudo 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

Iconsudo mkdir/var/lib/mysql3307

sudo mkdir/var/lib/mysql3308sudo chown-R mysql/var/lib/mysql3307sudo chown-R mysql/var/lib/mysql3308

Creating data directoriescreate 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

Iconsudo 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 filescreate 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

Iconsudo 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

Icon

# 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 instancesinstall 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

Iconsudo 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 instancesstart the default instance on 3306

start instances on 3307 and 3308 in the background

Iconsudo 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 instancesNote 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

Iconmysql -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

Iconmysqladmin -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 rebootPut commands in the file/etc/rc.local to start new instances on boot

the rc.local file will look like this

Iconsudo -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;