Colin Charles Agenda

Something that has excited me for a long time with upcoming features in the MySQL Server, is online backup. Since seeing it first being demonstrated by Chuck Bell at the Heidelberg Developers Conference in 2007, I’ve been enthralled. Now you too, can try online backup.

If you’ve not read the Forge Wiki page about it yet, please head over to Online Backup on the Wiki. You can grab the latest source from mysql-6.0-backup from mysql.bkbits.net. If you’ve never built MySQL from source before, go ahead and read Building MySQL from source. And you naturally need to test it once built, so I suggest making use of MySQL Sandbox.

NOTE: mysql-6.0-backup is the MySQL Backup Team Tree, and frequently changes and can break sometimes. This is not for production use. It can eat babies.

So, you’ve got BitKeeper (bkf) built, you’ve checked out the code, you’ve built it, and you have a binary distribution.

Place the built version in a location that sanbox likes (/opt/mysql in my case). Now, run ./express-install.pl /opt/mysql/mysql-6.0.6-alpha-darwin9.2.1-i386.tar.gz. Once the install is completed, head over to ~/msb_6_0_6 and run ./use.

Backing up…

I now loaded the sakila sample database. Then, I proceeded to backup the database.

BACKUP DATABASE sakila TO 'sakila-backup.sql';
+-----------+
| backup_id |
+-----------+
| 1         | 
+-----------+
1 row in set (0.37 sec)

sakila-backup.sql is saved in your MySQL “data” directory, and in the case of the sandbox, its kept in your home directory.

du -sh ~/msb_6_0_6/data/sakila-backup.sql
1.9M	/Users/ccharles/msb_6_0_6/data/sakila-backup.sql

Out of curiosity, I ran file on the backup, and it was reported to be data (not ASCII English text, with very long lines):

file ~/msb_6_0_6/data/sakila-backup.sql
/Users/ccharles/msb_6_0_6/data/sakila-backup.sql: data

Once you’ve done the backup, you might want to check the state:

SELECT * FROM mysql.online_backup WHERE backup_id = 1 \G
*************************** 1. row ***************************
          backup_id: 1
         process_id: 0
         binlog_pos: 0
        binlog_file: NULL
       backup_state: complete
          operation: backup
          error_num: 0
        num_objects: 16
        total_bytes: 1654492
validity_point_time: 2008-05-03 18:55:19
         start_time: 2008-05-03 18:55:18
          stop_time: 2008-05-03 18:55:19
host_or_server_name: localhost
           username: msandbox
        backup_file: sakila-backup.sql
       user_comment:
            command: BACKUP DATABASE sakila TO 'sakila-backup.sql'
            engines: Default
1 row in set (0.00 sec)

online_backup provides statistics and metadata about a backup or restore. There is another table in the mysql database, that allows you to find progress information, and its called online_backup_progress.

If you run SELECT * FROM mysql.online_backup_progress WHERE backup_id = 1 \G, you’ll see notes changing from starting, running, validity point, running to complete.

Restoring…

Now, its time to restore. Note that the restore is what is known as a destructive restore (i.e. it will replace the current version of the database).

RESTORE FROM 'sakila-backup.sql';
+-----------+
| backup_id |
+-----------+
| 2         |
+-----------+
1 row in set (3.04 sec)

That’s it! You’ve restored your database. For posterity, here’s some statistics on the restore:

SELECT * FROM mysql.online_backup WHERE backup_id = 2 \G
*************************** 1. row ***************************
          backup_id: 2
         process_id: 0
         binlog_pos: 0
        binlog_file: NULL
       backup_state: complete
          operation: restore
          error_num: 0
        num_objects: 16
        total_bytes: 1654492
validity_point_time: NULL
         start_time: 2008-05-03 19:01:25
          stop_time: 2008-05-03 19:01:28
host_or_server_name: localhost
           username: msandbox
        backup_file: sakila-backup.sql
       user_comment:
            command: RESTORE FROM 'sakila-backup.sql'
            engines: Default
1 row in set (0.00 sec)

There you have it, MySQL 6.0’s Backup and Restore functionality. Still in its early stages of development, but very, very cool! All these features will also be available in MySQL 6.0.5, when this gets released…

I couldn’t resist downloading NetBeans 6.1, with GlassFish and the MySQL bundle, the moment I found out it was released. Pulling it down while at the Star Alliance Gold lounge in Singapore was easy enough, and it only took me an hour (its a pretty big bundle – 164MB, as opposed to 6.0.1 where it only weighed in at 143MB for the OS X bundle).

Instead of the 4 packages in 6.0.1, I now only see 2 files – MySQL.prefPane (because we only fixed this in 5.0.51b, which probably didn’t make the release cut-off – the included version is 5.0.51a-community), and NetBeans IDE with MySQL.mpkg. The install is slated to take 526MB and takes a good two minutes.

I noticed that it doesn’t uninstall NetBeans 6.0.1, so my Applications folder now has two versions, including two versions of Glassfish (UR1 from 6.0.1 versus UR2 in 6.1). Upon starting 6.1, it does ask if I’d like to import settings from a previous version of NetBeans; of course I would.

You are then asked to register. I never looked at registering software as important, but I’m starting to think a lot more about it. One has to provide value, for a user to register – and we’ll explore this in another blog post. I’m writing this while on a plane, so it doesn’t make sense to register, anyway.

The first thing I do? Jump to the Services tab, make a connection to MySQL (look under Drivers -> MySQL (Connector/J driver)), and play with the sakila sample database.

Design a query, using a GUI

Drill down into tables, find a table name (say, store if using the sakila sample database), drill down into a row, like store_id, right-click, and select Design Query. Now, feel free to drag tables and drop them, only to find that it now helps you create an ER diagram of your tables, while helping you design an appropriate query!

ER diagrams for free

Play with the database tool a lot more. It looks like its got plenty of potential. It also looks like NetBeans is a great IDE for all your web development needs (PHP included). I’m certainly going to use it a lot more, even though I’m notably a vim guy.

The MySQL User Guide is worth looking at. Its not the reference manual (which is excellent – kudos to our Documentation Team). Its target audience are users that are new to databases or users that are new to MySQL in general.

What’s really interesting about the MySQL User Guide is that you can help shape it. You, the community, can participate in writing it!

I for one, know that this is the easiest way you can start contributing to any open source project. Documenting it. Soon, you will realise that you’ve become an expert (writing documentation, or giving training, will always keep you sharp). Some move on to then delving in coding, some go on being consultants, and some end up being hired by the company that sponsors the project ;)

The URL again: http://userguide.forge.mysql.com/

Happy writing!

Technorati Tags: mysql, community, documentation, forge, user guide, contributing, open source, advocacy, mysql user guide

MySQL will be in Beijing, China, this week. Well, not MySQL per se, but I will be there. As will Kaj Arno. I arrive on 21 April 2008 and depart on 24 April 2008. I expect to be busy during the days, but my nights should be free. Drop me email at colinATmysqlDOTcom if you’d like to catch up.

There’s an event, on the 23rd of April as well. Its part of the Sun-MySQL World Tour. You definitely should be there, I’m sure there will be some nice schwag involved as well :)

Sitting in San Francisco, getting a little bored before my flight to Beijing. And Twitter is down, for 1.5 hours, for what they seem to call database maintenance. Wonder why. At least there’s IRC…

Technorati Tags: beijing, mysql, world tour, sun

Its worth noting that all talks that have been blogged are being linked on the Forge Wiki. Take a look at Notes from the conference. There are some great entries there, and when the slides become live on the website (today, I believe), you can gather heaps of information, if you missed the most successful MySQL Conference & Expo. Book early for next year.

Thank you bloggers!

Technorati Tags: mysql, mysqluc08, mysqluc2008, mysqlconf, conference notes, forge wiki, mysql forge

Hi! So this is completely my notes taken from the conference, without my thoughts attached to it. I should definitely post a lot more about this, and how the community can “improve” in time. Just not today. Believe me, sitting in the talk, was highly painful, and I’m wondering where my aspirin stash might be. The slides will be available soon, and lets just consider this a learning experience. It reminded me of the time Eric Raymond came to the Fedora Project’s very first FUDCon in Boston 2005 (probably the only session without available video :P).

What MySQL can learn from PostgreSQL
Joshua Drake
(more…)