I created the Bluegecko MySQL training AMI as a MySQL sandbox that folks could use to learn things about MySQL. I wanted the AMI to have MySQL 5.0, and a large collection of tools — for both tinkering and visualizing what is happening inside MySQL and on the system in general.
I chose to host the AMI on a small instance since they’re cheap to operate, which means you can fire up 3-4 of them to try out a replication scheme without feeling guilty. It runs CentOS 5.4 and MySQL 5.0.77 from the CentOS repository.
One of the goals I had for the training AMI was for it to be easy to visually inspect what is going on inside of MySQL and the system at large. To that end I’ve installed Cacti, a graphing and visualization tool, and equipped it with templates that allow you to visualize i/o statistics and detailed MySQL statistics. I’ve also installed a recent version of Innotop — a very handy top-like tool that lets you see a list of queries happening in real-time. It’s also good for visualizing locking behavior.
Another goal I had for the AMI was that it’d be useful for learning how to backup/clone MySQL databases. On startup I made certain that the ephemeral data store was converted into a physical volume, and then manged with the logical volume manager. A 64GB logical volume is reserved for MySQL and is mounted in /var/lib/mysql. I also installed xtrabackup 1.0 so one could experiment both with backups using LVM snapshots as well as ‘hot’ backups with xtrabackup.
Naturally the AMI includes the Maatkit tool suite, which I didn’t install from the CentOS repository because Maatkit changes so frequently — I wanted the most up-to-date and bug free version possible. The mk-table-checksum and mk-table-sync tools alone make it a valuable addition to any MySQL DBA’s toolkit. In later blog posts we’ll examine using these tools to verify the consistency of replication slaves.
I also wanted to use the AMI for benchmarking various MySQL configurations as well as being able to simulate concurrent read/write load. I built and installed sysbench 0.5 from SVN. The various LUA scripts that drive sysbench’s MySQL testing behavior can be found in /root/sysbench.
Lastly, I wanted the AMI to be useful for learning about SQL, so I made sure some interesting sample datbases were available. In the /root/datasets directory you’ll find SQL for the ‘world‘, ‘sakila‘, and ‘employee‘ test databases.
The password for just about everything (system root, mysql root, cacti admin, etc) is ‘mysqltrain’. If you want to see the Cacti graphs from your web browser you’ll want to open up port 80 on your EC2 Security Group to your home machine’s IP, the same goes for port 3306 if you want to use a local MySQL client such as SQLyog.
The AMI is named “Bluegecko/mysql-training.manifest.xml”. Enjoy!
Related posts: