• Blog
  • Get Help Now!
  • Customer Portal

Blue Gecko

  • Why you need us
    • Why act now
    • Why are we unique
  • What we do
    • Products Supported 24/7
      • MySQL
      • Postgres
      • SQL Server
      • Open Source
      • OS & Storage
  • How we do it
    • Consulting
    • System Monitoring
    • Security
  • Support
    • Blog
      • Oracle
      • MySQL
      • News & Events
      • Oracle Applications
      • Amazon Web Services
      • Jeremiah Wilton’s Oradeblog
      • OurSQL Community Podcast
      • Remote DBA
    • Team viewer
    • Whitepapers
  • About Us
    • Philosophy
    • The Blue Gecko Teams
    • Employment Opportunities
    • Partners
    • News
    • Press
  • Contact Us

April 21 by mhamrick

Introducing the Bluegecko MySQL Training AMI

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:

  1. OurSQL Episode 41: Zmanda [backup series #6]
  2. OurSQL Episode 38: Xtrabackup (Backup Series #5)
  3. Using Checksums to Ensure Table Consistency in MySQL

Filed Under: Amazon Web Services, Education, MySQL

Categories

  • Amazon Web Services
  • Configuration Management
  • downtime
  • Drizzle
  • ebs
  • Education
  • elastic block store
  • elastic compute cloud
  • hosting
  • hot backup
  • ignorance
  • Infrastructure
  • IOUG
  • Jeremiah Wilton's Oradeblog
  • misconception
  • misconceptions
  • Monocle
  • MySQL
  • MySQL Council
  • News & Events
  • Oracle
  • Oracle Applications
  • Oracle Data Guard
  • Oracle Database
  • Oracle Database Appliance
  • Our People
  • OurSQL Community Podcast
  • outage
  • parallel
  • performance
  • podcast
  • proof
  • Remote DBA
  • replication
  • S3
  • Security
  • SQL Server
  • System monitoring
  • Uncategorized

Contact Blue Gecko

  • This field is for validation purposes and should be left unchanged.

Get Our Newsletter

  • This field is for validation purposes and should be left unchanged.

The Fine Print

  • Privacy Policy
  • Terms of Use
  • When is remote DBA the right solution
  • What are remote DBA services
  • Get Help

Search

Blog Categories

  • 24/7 support
  • Blue Gecko 360° Services
  • Products Supported 24/7

Contact Blue Gecko

Blue Gecko A/S
info@bluegecko.com
Phone: +45 70 60 52 10

Networks

  • Email
  • Facebook
  • Twitter

© Blue Gecko Group