Hello friends! If you plan to migrate your database from on-prem servers to RDS (either Aurora or MySQL RDS), you usually don’t have much choice but to do so using logical backups such as mysqldump, mysqlpump, mydumper, or similar. (Actually, you could do a physical backup with Percona XtraBackup to S3, but given that it has not been mentioned at any time which brand —MySQL, Percona Server for MySQL, or MariaDB — or which version —5.5, 5.6 or MariaDB 10.X — is the source, many of those combinations are unsupported for this strategy, so logical backup is the way to go.)
Depending on the size of the instance or the schema to be migrated, we can choose one tool or another to take advantage of the resources of the servers involved and save time.
In this blog, for the sake of simplicity, we are going to use mysqldump, and generate a single table, but the most curious thing is that we are going to create objects which have a certain DEFINER, and it must not be changed.
If you want to create the same lab, you can find it here.
Next, I leave below the list of objects to migrate (the schema is called “migration” and has the following objects):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | mysql Source> SELECT * FROM (SELECT event_schema AS SCHEMA_NAME, event_name AS OBJECT_NAME, definer, 'EVENT' AS OBJECT_TYPE FROM information_schema.events UNION ALL SELECT routine_schema AS SCHEMA_NAME, routine_name AS OBJECT_NAME, definer, 'ROUTINE' AS OBJECT_TYPE FROM information_schema.routines UNION ALL SELECT trigger_schema AS SCHEMA_NAME, trigger_name AS OBJECT_NAME, definer, 'TRIGGER' AS OBJECT_TYPE FROM information_schema.triggers UNION ALL SELECT table_schema AS SCHEMA_NAME, table_name AS OBJECT_NAME, definer, 'VIEW' AS OBJECT_TYPE FROM information_schema.views UNION ALL SELECT table_schema AS SCHEMA_NAME, table_name AS OBJECT_NAME, '', 'TABLE' AS OBJECT_TYPE FROM information_schema.tables Where engine <> 'NULL' ) OBJECTS WHERE OBJECTS.SCHEMA_NAME = 'migration' ORDER BY 3, 4; +-------------+-----------------------+---------+-------------+ | SCHEMA_NAME | OBJECT_NAME | DEFINER | OBJECT_TYPE | +-------------+-----------------------+---------+-------------+ | migration | persons | | TABLE | | migration | persons_audit | | TABLE | | migration | func_cube | foo@% | ROUTINE | | migration | before_persons_update | foo@% | TRIGGER | | migration | v_persons | foo@% | VIEW | +-------------+-----------------------+---------+-------------+ 5 rows in set (0.01 sec) |
That’s right, that’s all we got.
The classic command that is executed for this kind of thing is usually the following:
1 | $ mysqldump --single-transaction -h source-host -u percona -ps3cre3t! migration --routines --triggers --compact --add-drop-table --skip-comments > migration.sql |
What is the next logical step to follow in the RDS/Aurora instance (AKA the “Destination”)?
- Create the necessary users (you can do this using the pt-show-grants tool to extract the users and their permissions).
- Create the schema “migration.”
- Import the schema from the command line.
Here we must make a clarification: as you may have noticed, the objects belong to the user “foo,” who is a user of the application, and it is very likely that for security reasons, the client or the interested party does not provide us with the password.
Therefore, as DBAs, we will use the user with all the permissions that AWS allows us to have (unfortunately, AWS does not allow the SUPER permission), which will be a problem that we will show below, which we will solve with absolute certainty.
So, the command to execute the data import would be the following:
1 | $ mysql -h <instance-endpoint> migration -u percona -ps3cre3t! -vv < migration.sql |
And this is where the problems begin:
If you want to migrate to a version of RDS MySQL/Aurora 5.7 (which we don’t recommend as the EOL is October 31, 2023!!) you will probably get the following error:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | -------------- DROP TABLE IF EXISTS `persons` -------------- Query OK, 0 rows affected -------------- /*!40101 SET @saved_cs_client = @@character_set_client */ -------------- Query OK, 0 rows affected -------------- /*!50503 SET character_set_client = utf8mb4 */ -------------- Query OK, 0 rows affected -------------- CREATE TABLE `persons` ( `PersonID` int NOT NULL, `LastName` varchar(255) DEFAULT NULL, `FirstName` varchar(255) DEFAULT NULL, `Address` varchar(255) DEFAULT NULL, `City` varchar(255) DEFAULT NULL, PRIMARY KEY (`PersonID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ... lot of messages/lines -------------- /*!50003 CREATE*/ /*!50017 DEFINER=`foo`@`%`*/ /*!50003 TRIGGER `before_persons_update` BEFORE UPDATE ON `persons` FOR EACH ROW INSERT INTO persons_audit SET PersonID = OLD.PersonID, LastName = OLD.LastName, City = OLD.City, changedat = NOW() */ -------------- ERROR 1227 (42000) at line 23: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Bye |
By the way, do you need help upgrading to MySQL 8.0? Do you need to stay on MySQL 5.7 a bit longer? We will support you either way. Learn more
What does this error mean? Since we are not executing the import (which is nothing more and nothing less than executing a set of queries and SQL commands) with the user “foo,” who is the owner of the objects (see again the define column of the first query shown above), the user “percona” needs special permissions such as SUPER to impersonate and “become” “foo” — but as we mentioned earlier, that permission is not possible in AWS.
So?
Several options are possible; we will list some of them
- Edit the migration.sql file, and in each definition that there is a DEFINER other than percona, replace it with percona or directly eliminate the DEFINER clause. Pros: it works. Cons: Objects will be executed with the user’s security context “percona” which is not only dangerous but also wrong.
- Apply the solution that my colleague Sveta proposes here, but you must use mysqlpump. Even so, the migrated objects remain with the DEFINER with which they have been imported.
- As a last resort, request the password of the user “foo,” which is not always possible.
As you will see, the solution is not simple. I would say complex but not impossible.
Let’s see what happens if the RDS/Aurora version is from the MySQL 8 family. Using the same command to perform the import, this is the output:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | -------------- DROP TABLE IF EXISTS `persons` -------------- Query OK, 0 rows affected -------------- /*!40101 SET @saved_cs_client = @@character_set_client */ -------------- Query OK, 0 rows affected -------------- /*!50503 SET character_set_client = utf8mb4 */ -------------- Query OK, 0 rows affected -------------- CREATE TABLE `persons` ( `PersonID` int NOT NULL, `LastName` varchar(255) DEFAULT NULL, `FirstName` varchar(255) DEFAULT NULL, `Address` varchar(255) DEFAULT NULL, `City` varchar(255) DEFAULT NULL, PRIMARY KEY (`PersonID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci -------------- Query OK, 0 rows affected ... lot of messages/lines -------------- /*!50003 CREATE*/ /*!50017 DEFINER=`foo`@`%`*/ /*!50003 TRIGGER `before_persons_update` BEFORE UPDATE ON `persons` FOR EACH ROW INSERT INTO persons_audit SET PersonID = OLD.PersonID, LastName = OLD.LastName, City = OLD.City, changedat = NOW() */ -------------- ERROR 1227 (42000) at line 23: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation |
Oops! A different message appeared, saying something like, “You need (at least one of) SUPER or SET_USER_ID privileges for this operation.”
Therefore, all we have to do now is assign the following permission to the “percona” user:
1 | mysql Destination> GRANT SET_USER_ID ON *.* TO 'percona'; |
And bingo! The import finishes without problems. I am going to show you some of the commands that would have continued to fail and worked.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -------------- /*!50003 CREATE*/ /*!50017 DEFINER=`foo`@`%`*/ /*!50003 TRIGGER `before_persons_update` BEFORE UPDATE ON `persons` FOR EACH ROW INSERT INTO persons_audit SET PersonID = OLD.PersonID, LastName = OLD.LastName, City = OLD.City, changedat = NOW() */ -------------- Query OK, 0 rows affected -------------- CREATE DEFINER=`foo`@`%` FUNCTION `func_cube`(num INT) RETURNS int DETERMINISTIC begin DECLARE totalcube INT; SET totalcube = num * num * num; RETURN totalcube; end -------------- Query OK, 0 rows affected |
Besides that, the objects belong to the user they correspond to (I mean, the DEFINER, the security context).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | mysql Destination> SELECT * FROM (SELECT event_schema AS SCHEMA_NAME, event_name AS OBJECT_NAME, definer, 'EVENT' AS OBJECT_TYPE FROM information_schema.events UNION ALL SELECT routine_schema AS SCHEMA_NAME, routine_name AS OBJECT_NAME, definer, 'ROUTINE' AS OBJECT_TYPE FROM information_schema.routines UNION ALL SELECT trigger_schema AS SCHEMA_NAME, trigger_name AS OBJECT_NAME, definer, 'TRIGGER' AS OBJECT_TYPE FROM information_schema.triggers UNION ALL SELECT table_schema AS SCHEMA_NAME, table_name AS OBJECT_NAME, definer, 'VIEW' AS OBJECT_TYPE FROM information_schema.views UNION ALL SELECT table_schema AS SCHEMA_NAME, table_name AS OBJECT_NAME, '', 'TABLE' AS OBJECT_TYPE FROM information_schema.tables Where engine <> 'NULL' ) OBJECTS WHERE OBJECTS.SCHEMA_NAME = 'migration' ORDER BY 3, 4; +-------------+-----------------------+---------+-------------+ | SCHEMA_NAME | OBJECT_NAME | DEFINER | OBJECT_TYPE | +-------------+-----------------------+---------+-------------+ | migration | persons | | TABLE | | migration | persons_audit | | TABLE | | migration | func_cube | foo@% | ROUTINE | | migration | before_persons_update | foo@% | TRIGGER | | migration | v_persons | foo@% | VIEW | +-------------+-----------------------+---------+-------------+ 5 rows in set (0.01 sec) |
Conclusion
As you can see, there are no more excuses. It is necessary to migrate to MySQL 8. These kinds of small details help make it possible more easily.
A migration of this type is usually always problematic; it requires several iterations in a test environment until everything works really well, and everything can still fail. Now my dear reader, knowing that MySQL 8 solves this problem (as of version 8.0.22), I ask you, what are you waiting for to migrate?
Of course, these kinds of migrations can be complex. But Percona is at your service, and as such, I share Percona Utilities That Make Major MySQL Version Upgrades Easier from my colleague Arunjith that can guide you so that the necessary migration reaches a good destination.
And remember, you always have the chance to contact us and ask for assistance with any migration. You can also learn how Percona experts can help you migrate to Percona Server for MySQL seamlessly:
Upgrading to MySQL 8.0 with Percona
I hope you enjoyed the blog, and see you in the next one!
