Error importing a database with triggers

Symptoms

When importing or restoring a database with triggers, the following error occurs:

"Failed to restore the database. Error 1227. Access denied. Perhaps, the dump you want to restore has triggers that can be restored only with root permissions."

Causes

Importing databases in the ispmanager panel is performed without superuser rights to prevent possible damage to the database server.

Solution

Databases containing triggers need to be imported via the console under a superuser (under the root user). To achieve that, execute the following function:

mysql -h<ip address> -P<port> -uroot <base name> -p < <dump file>

Information about the database server (IP address, port, password) can be found in the settings of a required database server:

• For lite/pro/host version: “Databases” - “Database servers” - select a DB server - “Edit”;
• For business version: "Cluster nodes" - select a cluster node - three dots button - "Database servers" - select a DB server - “Edit”.

Removing triggers from the database

An alternative solution is to remove triggers from the dump file before importing it. Make a backup copy of the file, go to the directory of the dump file and execute the following command:

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i <dump file>