From MySQL GUI Tools to MySQL Workbench

I bet that some of you still work with phpMyAdmin and there are plenty of good reasons for that. Perhaps the main reason would be its mobility. Being run by a web server, phpMyAdmin is accessible from anywhere, without installing any extra software, even on the mobiles phones. And the second reason is of course security – most web hosting providers restrict external access to MySQL servers. But there’s absolutely no reason to get used to phpMyAdmin, as there’s software far better than that.

Drop that phpMyAdmin Stuff, it's for Kids! ;)

Most of you probably used the MySQL GUI Tools bundle, which is very similar to what phpMyAdmin is – database tweaking, user management, structure design, query builder, etc. Browsing up to the usual MySQL GUI Tools place a few days ago, I encountered the following message:

Users of the MySQL GUI Tools Bundle should plan to upgrade to MySQL Workbench. MySQL Workbench provides DBAs and developers an integrated tools environment for:

• Database Design & Modeling
• SQL Development (replacing MySQL Query Browser)
• Database Administration (replacing MySQL Administrator)

So we’re now forced to upgrade to MySQL Workbench. Honestly, for a moment there I though that it’s just a naming issue, and that MySQL GUI Tools has gone so far, that the guys from Sun Microsystems decided to ship the bundle as a single product – MySQL Workbench, but I was wrong. Browsing the MySQL Workbench website I noticed the archive, which dates back to Novermber 8th, 2007! So it seems that they’ve just merged the two pieces.

MySQL Workbench is far more professional then the GUI Tools, but may be complicated at first sight. And the most funny thing about it is that the MySQL GUI Tools leads to a page to download the 5.1 community version of MySQL Workbench, which unfortunatelly does not have all the promised features (SQL development, data modelling, server administration), and there’s no home screen at all, which is shown on each and every screenshot related to the move!

I thought something was wrong, so I went back to the Workbench homepage, browsed the blogs, forums and downloads. It turned out that 5.2 is the one we were supposed to move to, but it’s still in beta, thus hidden in the downloads. After installing MySQL Workbench 5.2 I finally managed to get to the promising home screen, a little bit fancier than 5.1, and very similar to what we used to see in MySQL GUI Tools, plus the improvements.

Here are a few screenshots:

 

So I hope to see the 5.2 release very very soon, and, oh come on, drop the phpMyAdmin stuff, that’s for kids ;)

Cloud Tips: Automatic Backups to S3

In a previous post about backing up EC2 MySQL to an Amazon S3 bucket we covered dumping MySQL datasets, compressing them and uploading to S3. After a few weeks test-driving the shell script, I came up with a new version that checks, fixes and optimizes all tables before generating the dump. This is pretty important as mysqldump will fail on whatever step would cause an error (data corruption, crashed tables, etc), thus your uploaded to S3 archive would be kind of corrupt. Here’s the script:

Working in the Amazon Cloud

1
2
3
4
5
6
7
8
9
10

filename=mysql.`date +%Y-%m-%d`.sql.gz
echo Checking, Fixing and Optimizing all tables
mysqlcheck -u username -p password --auto-repair --check --optimize --all-databases
echo Generating MySQL Dump: ${filename}
mysqldump -u username -p password --all-databases | gzip -c9 > /tmp/${filename}
echo Uploading ${filename} to S3 bucket
php /ebs/data/s3-php/upload.php ${filename}
echo Removing local ${filename}
rm -f /tmp/${filename}
echo Complete

There you go. If you remember my previous example I stored the temporary backup file on Amazon EBS (Elastic Block Storage) which is quite not appropriate. Amazon charges for EBS storage, reads and writes, so why the extra cost? Dump everything into your temp folder on EC2 and remove afterwards. Don’t forget to make changes in your upload.php script ($local_dir settings). Also, just as a personal not and to people who didn’t figure out how to upload archives with data to S3, here’s another version of the script which takes your public_html (www, htdocs, etc) directory, archives it, compresses and uploads to an Amazon S3 bucket:

1
2
3
4
5
6
7
8

filename=data.`date +%Y-%m-%d`.sql.gz
echo Collecting data
tar -czf /tmp/${filename} /ebs/home/yourusername/www
echo Uploading ${filename} to S3 bucket
php /ebs/data/s3-php/upload.php ${filename}
echo Removing local ${filename}
rm -f /tmp/${filename}
echo Complete

Oh and have you noticed? Amazon has changed the design a little bit, and woah! They’ve finally changed the way they show the Access Secret without a trailing space character! Congrats Amazon, it took you only a few months.

Working With Amazon EC2: Tips & Tricks

It’s been a while now since I’ve been hosting on Amazon Web Services and I’d just like to point out some issues I had and quick ways of solving them. We’re gonna talk about setting up a server that would serve not only you, but your clients too, cause $100/mo is quite expensive, isn’t it? So let’s begin and keep this as straightforward as possible. If you don’t understand something, it’s probably because you haven’t read the official EC2 docs and haven’t searched the forums. This is not a tutorial, it’s just a set of rules you may want to follow to make things right.

Cloud Computing with Amazon Web Services

Once you start a new instance from an Amazon predefined AMI (Fedora Core 8 for example) I suggest you start building your structure right straight away. Attach an EBS volume to you instance (I mount it to /ebs) and start creating your users with their home directories in /ebs/home/kovshenin not the regular /home/kovshenin. Also point your MySQL server to keep your database files in /ebs/mysql. There are plenty tutorials out there on how to do that.

Now, edit your httpd.conf, add your vhosts, point them to the right users dirs, install an ftp server and make sure you chroot the users to their home directories. That way they won’t be able to mess up with eachothers files and folders, peek passwords etc. You might want to change the root user’s home directory to / instead of /root in case you’ll want to use ftp via your root user (which is quite dangerous).

Now comes the fun part. The HTTP server runs under the apache user by default in FC8 and I recommend you don’t touch this. Damn it took me quite some time to figure out how the heck can the apache user execute and write to files not belonging to apache. I messed up big time with the groups, adding apache to all my client’s users groups, but thank god I found mod_suphp in the end. Install that one and make sure you use it and there’s no need to change the users umasks anymore.

Note: There’s a little issue with the mod_suphp in Fedora as far as I know, which doesn’t let you use the suPHP_UserGroup directive in the httpd.conf yelling that it does not exist. Most of the man pages on the net say you have to use that directive, but I’m good without it. It seems that suphp can figure out what user to run on its own, look closely at the config files, and also make sure you’re running php-cgi, not the CLI version. By the way, this is the part where WordPress stops asking you your FTP credentials on plugins/themes update, install, remove and core upgrade too. Speeds up the whole process ;)

I used the following code to test how mod_suphp works (or doesnt):

<?php echo system("id"); ?>

Which should output what’s the current user. Make sure you check everything works before going public, and do not set your min_uid and min_gid in suphp lower than 50. It’s safer to chown -R files and folders than to let suphp run your scripts via root or some other powerful user.

Backing up your EC2 and EBS

This is very important. Once you have everything set up and running, DO backup. Backing up the EBS is quite simple, just create a snapshot from the Amazon EC2 Management Console. Backing up the running AMI (instance) is a little bit mroe complex. You have to use the ec2 command line tools to bundle a new volume, upload it to an Amazon S3 bucket and register the AMI. There are plenty tutorials on the net on how to do that. Shouldn’t take you more than half an hour to figure it out.

Just make sure you have copies of all the major config files (httpd.conf, crontab, fstab, ..) backed up on your /ebs/config for instance. You might need them in the future (when you loose everything, haha ;) Restoring a backed up AMI instance is simple. Launch a new instance using the AMI you generated, attach the Amazon Elastic IP address to it and voila. Way too simple.

About the EBS, there are quite a few things you should be able to do with it before continuing. Restoring a backed up Snapshot: Create Volume from Snapshot, umount /ebs, deattach old volume, attach new volume, mount /ebs. Cool? Be careful when you’re resizing your EBS. The xfs filesystem automatically grows as far as I know, but in my case I use the ext3 filesystem. So if you need to grow your ext3 EBS you’ll go:

1. Create a Snapshot
2. Create a new EBS Volume from that Snapshot you created (say 10 GB if you were running 5 GB)
3. Attach it to your Instance, say /dev/sdg
4. Use the resize2fs command to resize the partition to 10GB
5. Mount it to /ebs2 or whatever
6. Check to see if everything’s in place
7. Unmount /ebs2, deattach /ebs2, unmount /ebs, deattach /ebs
8. Attach the 10GB volume to where /ebs was attached (/dev/sdf)
9. Mount /ebs and start your services

There you go, back to work, server. By the way, when working with Amazon AWS, note that you should be working in the same region where your AMI is (us, eu, east, 1c, …) otherwise some of the options (when attaching, etc) might just not come up. Beware of that.

Well, I guess those are pretty much all the basics. Don’t forget to read the Amazon S3 tutorials and API, pretty sweet stuff! Good luck.

Foller.me: MySQL Tweaking & Optimization

As I mentioned in the interview with @enked on his website Chidimar.com, I had serious problems with MySQL database optimization on the Foller.me project. The current public stable version (beta-1) is using the MyISAM engine and it’s not holding much data – profiles, locations, geo points for the followers geography.

Because Performance Really Matters

In the new version (currently dev-1 and hopefully beta-2 in a few days) I changed most of the old tables and added new ones, and using InnoDB this time. You see, it’s not very easy to scan through ~1,000,000 relations for the @mashable account ;) and I bumped into a ~10 second delay before the @mashable profile showed up at Foller.me. Slow query showed that one of the simplest queries caused that slow-mo – it took 6 seconds to execute! The guys at Stack Overflow helped me optimize the query and the two tables I was having problems with, so I came down to ~2 seconds for that query, neat!

Digging further I managed to tune the MySQL server up a little bit (caching, all sorts of buffers, etc – you should definitely take a look at MySQLTuner, it’s a perl script that helps you tune pretty much all the MySQL config) which decreased the query execution time to 1 s. The peeps at Stack Overflow said it’s pretty okay for that kind of query to execute for 3 seconds on over 2 million rows, so I thought that 1 second is final. Phew! :)

Now, think about the MySQL query cache. It doesn’t work in my situation, simply because I shoot UPDATEs and INSERTs at the relations table every five minutes or so (via a cron job), so there actually is a way to perform even higher. Thought of temporary tables, views and triggers (and even stored procedures). Nah.. Simply caching that query would be good, right? I mean if I cache the whole profile for an hour, why wouldn’t I cache the relations result set? Cache the query.. Aha, but I thought slightly further. Why not cache the whole page with memcached? I’ll keep you updated with the results.

Three Linux Commands You Can’t Live Without

Okay, we’re not going to talk about the shutdown, yum, etc. commands, though THEY are probably the ones that nobody can live without. I’m talking about the web here, remember? And we all know that not everybody owns a VPS, a VDS or a dedicated server. Virtual hosting plans are quite cheap today, and most of them are tuned to provide you with SSH access and basic privileges (although I DO highly recommend you get yourself a VPS, it’s not much more expensive than virtual hosting and I’m sure it’s worthed). These linux commands are extremely helpful when doing backup or moving from one server to another.

Please Memorize These Linux Commands

1. Create a compressed archive of the current directory:

tar -cvzf backup.tar.gz ./*

2. Create a compressed archive of a MySQL database dump:

mysqldump -u username -p password -h host -P port databasename | gzip -c > mysql.sql.gz

3. Get directory contents from a remote FTP server:

wget -r ftp://username:password@domain.com/directory/*

Hope that helped ;)