Getting SQLSTATE[28000] [1045] Access denied for user ‘user’@'IPaddress' (using password: YES) when trying to restore mySQL dump to Azure MySQL DB PaaS

  • Article

I was debugging a Magento deployment on Microsoft Azure for one of my customers as they were trying to restore MySQL dump to Azure MySQL DB PaaS and they kept on getting the following error:

Getting SQLSTATE[28000] [1045] Access denied for user ‘user’@'IPaddress' (using password: YES)

I was able to restore DB dump normally from my machine to the same DB with the same user and password so I suspected that there is something wrong with the DB dump itself and that is when I asked them to share the dump and I found that the dump had the attribute "DEFINER" which requires super privilege which is not allowed on Azure mySQL PaaS and the other issue was that Azure MySQL PaaS is built on INNODB engine and you need to make sure that the script is using this engine.

Actions to solve the issues:

use the following command to remove the attribute “DEFINER” as it requires super privilege

sed's/\sDEFINER=`[^`]*`@`[^`]*`//g' -ibackupfile_name.sql

I use the following command to replace MyISAM engine with INNODB engine which is the core of the mySQL implementation.

 :%s/MyISAM/InnoDB/gc