Tips and Tricks in using mysqldump and mysql restore to Azure Database for MySQL
While importing data into Azure Database for MySQL, errors may occur. This blog will walk through common issues that you may face and how to resolve it. Access denied; you need (at least one of) the SUPER privilege(s) for this operation: Error ERROR 1227 (42000) at line 101: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Operation failed with exitcode 1 Issue Importing a dump file that contains definers will result in the above error. As all of us know, only super users can perform and create definers in other schemas. Azure Database for MySQL is a managed PaaS solution and SUPER privileges is restricted. Solution Either replace the definers with the name of the admin user that is running the import process or remove it. The admin user can grant privileges to create or execute procedures by running GRANT command as in the following examples: GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost'; GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost'; Example: Before: DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`127.0.0.1`*/ /*!50003 …… DELIMITER ; After: DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`AdminUserName`@`ServerName`*/ /*!50003 …… DELIMITER ; importing triggers while binary logging is enabled: Error ERROR 1419 (HY000) at line 101: 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 variable) Operation failed with exitcode 1 Issue Importing a dump file that contains triggers will result in the above error if binary logging is enabled. Solution To mitigate the issue, you need to enable the parameter “log_bin_trust_function_creators” from Azure portal parameters blade. storage engine not supported: Error ERROR 1030 (HY000) at line 114: Got error 1 from storage engine Operation failed with exitcode 1 Issue You will see the above error when you use a storage engine other than InnoDB and MEMORY. Read more on support engine types here: Storage engine support Solution Before the import process make sure that you are using a supported engine type; InnoDB and MEMORY are the only supported engine types in Azure Database for MySQL. If you dumped the data from a different engine type, edit the file and replace the storage engine. For example, exchange ENGINE=MYISAM with ENGINE=InnoDB. Note: You can always dump the schema first using the command: mysqldump --no-data option, and then dump the data using option: mysqldump --no-create-info option Example : Before: CREATE TABLE `MyTable` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `DeviceID` varchar(50) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; After: CREATE TABLE `MyTable` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `DeviceID` varchar(50) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; InnoDB storage engine row format: Error ERROR 1031 (HY000) at line 114: Table storage engine for 'mytable' doesn't have this option Operation failed with exitcode 1 Issue In Azure Database for MySQL, four row format options are supported: DYNAMIC, COMPACT and REDUNDANT, the COMPRESSED row format is supported under certain conditions. Solution We support compressed format on General Purpose or Memory Optimized. Customer needs to enable the parameter “innodb_file_per_table” from Azure portal parameters blade, and key_block_size must be 8 or greater than 8. In default, key_block_size is 8. Please visit the Performance considerations guide for best practices while migrating into Azure Database for MySQL. Thank You !Export and import MySQL users and privileges to Azure Database for MySQL
In Azure Database for MySQL, “mysql.user” table is a read-only table, to migrate your mysql.user table you can use the following command to script out the users table content before migration: Generate create user statements: Before going to generate the scripts , we need to check the variable secure_file_priv on your database which limit the directories where you can load or writing files to : mysql> SHOW VARIABLES LIKE "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ After checking the path, now you can generate the create users from your database: Option 1: mysql> SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','YourPassword''',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile '/var/lib/mysql-files/create_users.sql'; The generated file contents will look like: create user 'myuser1'@'%' identified by 'YourPassword'; create user 'myuser2'@'%' identified by 'YourPassword'; create user 'myuser3'@'%' identified by 'YourPassword'; create user 'myuser4'@'%' identified by 'YourPassword'; create user 'replication_user'@'%' identified by 'YourPassword'; modify the password above based on your requirements and run the above generated create user statements on Azure database for MySQL. option 2: if you are not aware of the passwords in your database and you want to let the password change on the client side , you can generate a user creation script with temporary password and expired option: SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','Temp@123''', ' password expire',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile '/var/lib/mysql-files/create_users.sql'; The generated file contents will look like: create user 'myuser1'@'%' identified by 'Temp@123' password expire; create user 'myuser2'@'%' identified by 'Temp@123' password expire; create user 'myuser3'@'%' identified by 'Temp@123' password expire; create user 'myuser4'@'%' identified by 'Temp@123' password expire; create user 'replication_user'@'%' identified by 'Temp@123' password expire; run the above generated create user statements on Azure Database for MySQL. after the user logged in to the database with that temp password , and tried to run any query the user will be notified that he must change the password before running any query , and the user can use “set password = ‘newpassword’ ” statement to reset the password: mysql> select 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> mysql> set password = 'Mysql@1234'; Query OK, 0 rows affected (0.01 sec) mysql> select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) Export users’ privileges: To export the user privileges, you can run the below query: SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile '/var/lib/mysql-files/user_grants.sql'; The generated file will look like: SHOW GRANTS FOR 'myuser1'@'%'; SHOW GRANTS FOR 'myuser2'@'%'; SHOW GRANTS FOR 'myuser3'@'%'; SHOW GRANTS FOR 'myuser4'@'%'; SHOW GRANTS FOR 'myuser5'@'%'; SHOW GRANTS FOR 'replication_user'@'%'; You can source the generated file to get the required commands , before sourcing the file to generate a ready contents of command lines , connect to your local database with options -Ns to remove the headers from the output: mysql -Ns -h root -p mysql> source /var/lib/mysql-files/user_grants.sql GRANT USAGE ON *.* TO 'myuser1'@'%' GRANT USAGE ON *.* TO 'myuser2'@'%' GRANT USAGE ON *.* TO 'myuser3'@'%' GRANT USAGE ON *.* TO 'myuser4'@'%' GRANT USAGE ON *.* TO 'myuser5'@'%' GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%' Connect to your Azure database for MySQL and run the above generated commands: mysql -h ServerName.mysql.database.azure.com -u username@servername -p mysql> GRANT USAGE ON *.* TO 'myuser1'@'%'; GRANT USAGE ON *.* TO 'myuser3'@'%'; GRANT USAGE ON *.* TO 'myuser4'@'%'; GRANT USAGE ON *.* TO 'myuser5'@'%'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%';Query OK, 0 rows affected (0.03 sec) mysql> GRANT USAGE ON *.* TO 'myuser2'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT USAGE ON *.* TO 'myuser3'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT USAGE ON *.* TO 'myuser4'@'%'; Query OK, 0 rows affected (0.04 sec) mysql> GRANT USAGE ON *.* TO 'myuser5'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%'; Query OK, 0 rows affected (0.02 sec) Please note that select into outfile is not supported in Azure Database for MySQL. Instead, use the Create users and Create privilege queries below and run them from workbench and copy the outputs: Example: Create users: SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','YourPassword''',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session'); Create Privileges: SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session'); And run the generated commands: SHOW GRANTS FOR 'myuser1'@'%'; SHOW GRANTS FOR 'myuser2'@'%'; SHOW GRANTS FOR 'myuser3'@'%'; SHOW GRANTS FOR 'myuser4'@'%'; SHOW GRANTS FOR 'myuser5'@'%'; SHOW GRANTS FOR 'replication_user'@'%'; Please visit the Performance considerations guide for best practices while migrating into Azure Database for MySQL. Thank You !
