Fixing backups on Craft CMS since MySQL broke mysqldump in 8.0.32
MySQL start my week off with a load of unnecessary manual labour
Posted 30th January 2023 • Development • #development
In a minor patch release last week, MySQL broke the mysqldump command, which is what Craft CMS uses to run database backups. It's also how we export the databases to work on locally.
After talking it through with the developers behind Ploi and SnapShooter, the consensus is to update the database user permissions for the database user (thanks, Simon). This bug affects MySQL 5.7.41 and 8.0.32.
- Ploi backups are not affected. Ploi runs automated backups through the ploi database user, which already has the RELOAD permission
- SnapShooter backups are affected. SnapShooter uses the site's database user
- Pulling databases is affected. We use Craft Scripts on the CLI to pull database backups, which connects using the site's database user
This leaves me with needing to manually update 88 site database users to allow us to export again. Thanks, MySQL. If you're in a similar position as us, here are the steps we're taking:
ssh [email protected]
mysql -u ploi -p
select `User`,`HOST`, `Process_priv`,`Reload_priv` from mysql.user;
(Your database user will have an N in both columns)
GRANT RELOAD,PROCESS ON *.* TO 'staging_db_user'@'127.0.0.1';
GRANT RELOAD,PROCESS ON *.* TO 'production_db_user'@'127.0.0.1';
select `User`,`HOST`, `Process_priv`,`Reload_priv` from mysql.user;
(Your database user should have a Y in both columns now)
FLUSH PRIVILEGES;If you didn't write down the ploi database user password when your server was created and you've lost it, you can reset the password by running the code below.
ssh [email protected]
sudo su
sudo mysql
ALTER USER 'ploi'@'%' IDENTIFIED BY 'NEW_PASSWORD';
ALTER USER 'ploi'@'127.0.0.1' IDENTIFIED BY 'NEW_PASSWORD';
FLUSH PRIVILEGES;Once you've done that, you'll need to go into the server in Ploi, click the Manage tab, scroll down to Database settings and update the Database root password so Ploi knows the new password.