Things I Learned Restoring a MySQL Dump File

I got an e-mail yesterday from a client who wants to move on from the MySQL-based CiviCRM solution I found for them years ago and needs one more backup before they switch over. I figure this is going to require a bit of query writing on my part to actually extract the data they need from CiviCRM’s highly-normalized tables so I decided to restore an old SQL dump file I had from their server into my local installation of MySQL to map out a few relevant items.

mysql -u <user> -p < sqldump.sql

It actually went pretty smoothly but there were a few things I ran into …

Get ready to do some editing of the SQL dump file

The first thing I ran into was the dump trying to set sql_mode to NO_AUTO_CREATE_USER which has been deprecated and no longer worked in my 8.x installation. Simply removing that worked but it was in 37 places and the dump file was over 100 MB so Notepad++ was definitely my friend today. It uses a lot less resources than something like DBeaver.

Know what’s in the file and import into an installation you can easily restore

It turned out the dump was actually of the entire MySQL server which just contained the CiviCRM / WordPress system but the dump also grabbed its mysql administration database and managed to overwrite the users on my current installation and change the root password. Fortunately, it was only a sandbox installation of MySQL and I had a backup I was able to restore in a minute. This led to more editing of the dump file to remove the commands related to the mysql database and another successful restore which did not mess up my install.

Also, being selective about which databases get dumped is a great idea.

mysqldump -u <user> -p --databases db1 db2 db3 > dump.sql

ERROR 1726 – Storage engine ‘MyISAM’ does not support system tables

The dump file was still setting the wordpress wpadmin user as the definer for each table and this led to an issue when I tried to re-dump the databases to another file since my server doesn’t have the wpadmin user. When It tried to create it, I got the above error which I was finally able to fix with:

mysqld --upgrade=FORCE

This forces MySQL to upgrade the data dictionary and system tables even if it’s not needed and resolved the issue to where I was able to finally create the user.

ChatGPT is a great assistant but it still drops acid occasionally