Virtualmin - Moving MySQL 5.7 to 8 - SUPER privilege(s) and DEFINER errors

While attempting to restore Virtualmin domains from Ububtu 16.04 with AWS MySQL RDS 5.7 to Ubuntu 18.04 with a remote AWS RDS MySQL 8.0 I ran into an issue where MySQL had SUPER privilege(s) and DEFINER errors when running the restore:

Example Error

Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

Example Restore

virtualmin restore-domain --source /root/backups --all-domains --all-features

How to resolve

Enable log_bin_trust_function_creators = 1; in your custom parameters for AWS RDS MySQL and reboot the instance.

Get a export of your MySQL databases (I ended up using using webmin to grab a backup of all databases and SCPed those to the new server). Then proceed with the following replace commands to clean up the SQL dumps (after extracting the gzips)

sed -i.bak 's#MyISAM#InnoDB#g' *
sed -i.bak 's#SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION\.SQL_LOG_BIN;##g' *
sed -i.bak 's#SET @@SESSION\.SQL_LOG_BIN= 0;##g' *
sed -i.bak "s#SET @@GLOBAL\.GTID_PURGED='.*';##g" *
sed -i.bak "s#SET @@SESSION\.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN##g" *
sed -i.bak 's#NO_AUTO_CREATE_USER##g' *

Note: The first replacement above is to convert your tables from MyISAM to InnoDB, ignore if you want to keep MyISAM. If you do not want a lot of .bak.bak.bak files, remove the '.bak' from the commands above.

Virtualmin

When running the restore Virtualmin will fail on importing the mysql database. However, the database and user information will be created. After it has been created import your SQL above. This can be done through Webmin or command line.

If you are importing multiple domains, you can run the following command to continue importing your other domains. Repeat the steps above if you get another MySQL import error.

virtualmin restore-domain --source /root/backups --all-domains --all-features --only-missing

MySQL 8 Gotchas

With MySQL 8 you may run into an authentication issue after doing the above. Usually this shows up as a 500 error on your site or a 2054 from mysql. Run the following command to fix the authentication type for your user.

ALTER USER 'YOUR_USERNAME'@'YOUR_HOSTNAME' IDENTIFIED WITH mysql_native_password BY 'YOUR_PASSWORD';

Ref: https://aws.amazon.com/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/