MySQL backup and restore databases with views

We recently installed OS X Leopard Server at work. I had to take MySQL databases from our old Tiger Server and restore them onto the new XServes. Both servers are running MySQL Server version 5.0.45 but our Tiger server is running MySQL Client version 5.0.22 while our Leopard servers are running MySQL Client version 5.1.18. According to MySQL’s documentation (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html):

Prior to release 5.0.48, this option did not create valid SQL if the database dump contained views. The recreation of views requires the creation and removal of temporary tables and this option suppressed the removal of those temporary tables. As a workaround, use –compress with the –add-drop-table option and then manually adjust the dump file.

I tried the –compress option but it didn’t work. I still had to manually adjust the SQL file. The I tried the GUI tool from MySQL, http://dev.mysql.com/downloads/gui-tools/5.0.html (MySQL Administrator).

Once you create the backup, you can restore it using the same application on the newer server. It will even include the views. Once you start using the newer client, you shouldn’t have a problem restoring databases with views from backups made with the mysqldump command.