Differences between Amazon Aurora MySQL-compatible and MySQL
There are many articles about the fundamental differences between Amazon Aurora MySQL-compatible(Hereafter referred to as Aurora MySQL) and MySQL(Hereafter referred to as Native MySQL).
So, in this page I would like to say differences between two databases from my experience focused on Aurora MySQL - I assume that you know Native MySQL.
Aurora MySQL Administrator does not have permission to configure I/O.
This is actually very obvious, even if you have an database administrative permission, you cannot config any I/O related configuration at all. Because host machine is controlled by the cloud(AWS).
You need to configure variables/settings using parameter group for Aurora MySQL.
In Native MySQL, you can change some configuration using "SET GLOBAL" command
In Addition, you can configure the settings using my.cnf (or something similar file) in native MySQL.
But you cannot do that because you don't have control of the configuration file.
Instead, you can configure it using parameter groups. You need to configure parameter groups and link with the cluster(or database).
You need to learn some additional commands because you are not the owner(real owner) of the database.
If you're a DBA, you can use "KILL <spid>" in order to kill a session. But it does not work on Aurora MySQL(if you are not the owner of the query), you need to use some AWS developed system procedure such as "CALL mysql._rds_kill(thread-ID);".
As you do not have permission to control system tables, you need to use some other commands for the log files as well. As an example, "CALL mysql.rds_rotate_general_log;" will recycle the ganeral_log (if you have already configured to use general_log).
You should be really careful about the programmable object due to the definer.
If you're DBA and you use Native MySQL, then you can change definer of the programmable object(Procedure, Function. views, event etc) as you want.
But you cannot define the definer by yourself (unless you use the master user) in Aurora MySQL. It just does not work. This cause an issue when you backup data and import data to another database. You need to remove definer or replace the definer(to a masteruser) from the dump file.
In case, the user name does not exist anymore, then you need to 1) backup the object, 2) drop the object and 3) recreate the object without the definer (or a master user).
Useful Articles
Amazon Aurora MySQL-compatible
- Common DBA tasks for MySQL DB instances: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html
- MySQL on Amazon RDS SQL reference: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.SQLRef.html
- Resolve Definer Errors When Importing to Amazon RDS MySQL: https://aws.amazon.com/premiumsupport/knowledge-center/definer-error-mysqldump/
- When Should I Use Amazon Aurora and When Should I use RDS MySQL?: https://www.percona.com/blog/2018/07/17/when-should-i-use-amazon-aurora-and-when-should-i-use-rds-mysql/
MySQL/MariaDB
- SET Syntax for Variable Assignment: https://dev.mysql.com/doc/refman/5.7/en/set-variable.html
- Server Status Variables: https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html
- Using Option Files: https://dev.mysql.com/doc/refman/5.7/en/option-files.html
Good luck!
No comments:
Post a Comment