How to run multiple MySQL instances in one server
4 minute read
Env
- System: Ubuntu 14.04
- Mysql-server: 5.5+
Solutions
Use separate my.cnf
Steps
- copy the original
my.cnf
as my2.cnf
;
- Update
pid-file
, socket
, port
and datadir
in my2.cnf
;
- Run
sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql2
;
- start the second mysql server with
mysqld_safe --defaults-file=/etc/mysql/my2.cnf
, then you will have two server running.
Pros and Cons
- Easy, use less effort to achieve;
- Not good to control, need write a new service command to handle start/stop/etc.
Use mysqld_multi
Steps
- Run
mysqld_multi --example
to show all codes we will use;
- Copy and Update
mysqld_multi
section in my.cnf
;
- Update group name
mysqld
to mysqld1
(make original instance controlled by mysqld_multi
) in my.cnf
;
- Add new group
mysqld2
in my.cnf
with user
, pid-file
, socket
, port
and datadir
.
- Run
sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql2
;
- Use command
mysqld_multi
to manage(start/stop ) all instances.
Pros and Cons
- Easy, a little more than the first one;
- Easy to control, command
mysqld_multi
build-in with mysql.
Traps
- In MySQL 5.6, when you run
mysql_install_db
, you might get an error that says FATAL ERROR: Could not find my-default.cnf
. Fix it by sudo cp /etc/mysql/my.cnf /usr/share/mysql/my-default.cnf
.
- You may see
[Warning] Can't create test file /var/lib/mysql2/ip-xxx.lower-test
, this is because Ubuntu’s security check, Fix it by disable MySQL security check:
sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld
Refs