Multiple MySQL instances on EC2

Overview:

I am keen to see what other people’s thoughts of MySQL and using MySQL on EC2 are in general. So I read any blogs which discuss them with interest.

In the latter part of last year I read an interesting article on proposing to run multiple MySQL instances on a single EC2.

Running multiple MySQL instances on one box is reasonably straightforward. Most of the exposure I have seen is either running multiple replication slaves on one box or as a cascaded slave for backups.
For example two separate machines are master instances and both their slaves are on a single box. This is not to provide failover, rather a location to run backups from.
In the second option, the standard replication pair run on separate machines and the slave machine has two slave instances, one “live” the other a cascaded slave of that instance so it can be used as a backup without stopping or affecting either the master or “live” slave.

So the first step down the path of the Apokalyptik Multiple Instance Master-Master (MIMM) replicated pairs configuration is to get two instances running on one EC2 AMI.

Resources:

MySQL Documentation has plenty of useful references:

http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html
http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html
http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html

Steps:

  1. Install MySQL
  2. Create instance
  3. Edit /etc/my.cnf to include the necessary options.
  4. Create directories required for each instance
  5. Move the original instance into both new data directories.
  6. Startup using mysqld_multi

Summary:

  • Whilst the configuration file looks complex, it is not really. You add a new group for mysqld_multi and you can cut and paste your existing mysqld for each mysqldN group.
  • Make sure each instance is not going to consume more than 40% of available memory including if each instance is at max connections.
  • Make sure the data directory for each instance has to correct permissions.

My Example:



Sample file running mysql_multi and two mysqld instances.

[root@ip-10-251-46-8 mysql]# cat /etc/my.cnf.tmp

[client]
port = 3306
socket = /tmp/mysql1.sock

[mysqld_multi]
mysqld = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe
mysqladmin = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqladmin
user = multi_root
password = multi_pass

[mysqld1]
port = 3306
socket = /tmp/mysql1.sock
pid-file = /usr/local/mysql/data1/mysql1.pid
datadir = /usr/local/mysql/data1
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql1-bin

server-id = 1


[mysqld2]
port = 3307
socket = /tmp/mysql2.sock
pid-file = /usr/local/mysql/data2/mysql2.pid
datadir = /usr/local/mysql/data2
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql2-bin

server-id = 2




[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


Checking that mysqld_multi is working correctly and reading /etc/my.cnf

[root@ip-10-251-46-8 mysql]# mysqld_multi report 1
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
[root@ip-10-251-46-8 mysql]# mysqld_multi report 2
Reporting MySQL servers
MySQL server from group: mysqld2 is not running
[root@ip-10-251-46-8 mysql]# mysqld_multi report 3
Reporting MySQL servers
No groups to be reported (check your GNRs)

Note: Make sure the directory permissions for datadirs and other directories are correct.

Starting mysqld individually

[root@ip-10-251-46-8 mysql]# mysqld_multi start 1 --verbose --password=...
[root@ip-10-251-46-8 mysql]# mysqld_multi report 1
Reporting MySQL servers
MySQL server from group: mysqld1 is running
[root@ip-10-251-46-8 mysql]# mysqld_multi start 2 --verbose --password=...
[root@ip-10-251-46-8 mysql]# mysqld_multi report 2
Reporting MySQL servers
MySQL server from group: mysqld2 is running

Checking that two mysqld_safe processes were spawned

[root@ip-10-251-46-8 mysql]# ps -ef|grep mysqld_safe
root 3539 1 0 06:33 ttyp0 00:00:00 /bin/sh /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3306 --socket=/tmp/mysql1.sock --pid-file=/usr/local/mysql/data1/mysql1.pid --datadir=/usr/local/mysql/data1 --skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --log-bin=mysql1-bin --server-id=1
root 3681 1 0 06:34 ttyp0 00:00:00 /bin/sh /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3307 --socket=/tmp/mysql2.sock --pid-file=/usr/local/mysql/data2/mysql2.pid --datadir=/usr/local/mysql/data2 --skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --log-bin=mysql2-bin --server-id=2
root 3827 2795 0 06:35 ttyp0 00:00:00 grep mysqld_safe

Checking client connectivity

[root@ip-10-251-46-8 mysql]# mysql -u root -p -S /tmp/mysql1.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%dir%';
+----------------------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+-----------------------------------------------------------------------+
| basedir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/ |
| character_sets_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/share/mysql/charsets/ |
| datadir | /usr/local/mysql/data1/ |
| innodb_data_home_dir | |
| innodb_log_arch_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| plugin_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/mysql |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+----------------------------+-----------------------------------------------------------------------+

[root@ip-10-251-46-8 mysql]# mysql -u root -p -S /tmp/mysql2.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%dir%';
+----------------------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+-----------------------------------------------------------------------+
| basedir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/ |
| character_sets_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/share/mysql/charsets/ |
| datadir | /usr/local/mysql/data2/ |
| innodb_data_home_dir | |
| innodb_log_arch_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| plugin_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/mysql |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+----------------------------+-----------------------------------------------------------------------+
10 rows in set (0.00 sec)

3 thoughts on “Multiple MySQL instances on EC2

  1. Alternatively, you could use Giuseppe’s excellent < HREF="http://mysql-sandbox.sourceforge.net/" REL="nofollow">MySQL Sandbox<> for running multiple instances of a MySQL Server without much hassle🙂

  2. following your commands I successfully manage to let it run on Fedora Core 4 (mysql 4 binaries), but cannot manage to let it run on Fedora 6, 7 or 8 ;-(Always the same errorStarting mysqld daemon with databases from /var/lib/mysql2STOPPING server from pid file /var/run/mysqld/mysqld.pid2080508 19:17:27 mysqld ended

Comments are closed.