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)