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.
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.