Magento2 - Migrating the database from one server to another

Question

I am in the process of migrating a Magento2 website from a single dedicated server to a split setup with Magento on one server and the Database on it's own dedicated server.

What is the recommended approach with migrating the database?

I've read several articles but none of them discuss the compexities involving transactions, table spaces and SUPER privalidges?

They simply state mysqldump and import as if it's incredibly straightforward - my experience with Magento2 as an ecommerce platform suggests this is not usually the case?

For example I have used this command to dump the live database:

mysqldump -u -p --no-tablespaces > mysqldump_live26082024.sql

Now when running the import command:

mysql -h -u -p  < mysqldump_live26082024.sql

I get the following error:

ERROR 1419 (HY000) at line 821: You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators v ariable)

Is this a common problem or is it an issue caused by my MySQL configuration?

Any help is greatly appreciated.

Answer 1

If you are using Linux :

1. systemctl stop mysqld
2. Add log_bin_trust_function_creators = 1 to my.cnf under /etc
3. systemctl start mysqld

If you are using AWS RDS, such as MariaDB.

1. Set a Parameter Groups a name and description, using those Family (MariaDB)
2. Set the Parameter Value "log_bin_trust_function_creators" to 1.
3. Save and Reboot the instance RDS

Answer 2

use this:

mysql -u -p database_name < path_to_mysqldump_live26082024.sql