Make sure to use the –databases option when backing up single MySQL databases

Targeting a single database with these options:

mysqldump -u MY_DB_BACKUP_USER -p MY_DB_NAME --add-drop-table --add-drop-database > my_db_name_mmddyyyy.sql

will successfully backup the database, but if you were to restore the database backup file back into a database with a newer schema (additional tables for example) like so:

mysql -u my_db_access_user -p MY_DB_NAME < my_db_name_mmddyyyy.sql

you would end up with a “merged” version of the database that would have new tables from the more recent schema and restored tables from the backup:

I learned this the hard way recently when attempting to restore a database to an earlier version following a failed Redmine 3.0.2 upgrade. While this didn’t break anything (other than maybe a future upgrade attempt that doesn’t properly handle existing tables), it wasn’t what I intended.

Going forward, if I want to backup a single database and have the backup file contain the statement necessary to first drop the database before restoring it (a full restore for example), I’ll need to call the mysqlbackup command like so:

mysqldump -u MY_DB_BACKUP_USER -p --databases MY_DB_NAME --add-drop-table --add-drop-database > my_db_name_mmddyyyy.sql

Note the --databases statement. If you didn’t know this, I hope it helps you from making the same mistake.

SQL in easy steps, 3rd Edition errata

I was reading through SQL in easy steps, 3rd Edition and made it to page 152 where it covered Sub-query calculated fields. It’s a fancy way of saying a SELECT statement within a SELECT statement, but isn’t that hard to grasp if you’re familiar with how variable interpolation works with Bash or similar shells.

My problem was understanding how even though a column wasn’t listed as one to be returned by SELECT it was seemingly returned as a byproduct of an ORDER BY statement. I couldn’t find this mentioned in any of the MySQL documentation or another MySQL book I have so I was getting pretty frustrated. I fired my MySQL this morning and opened up subquery-calc.sql and had a good laugh: The mental block I was having was due to an error in the book’s code that wasn’t present in the SQL file.

I fired off an errata report in the hopes that it will be correct in future printings so others won’t be as confused as I was.

Continue reading