I'm on RDS and this is the situation:
Unable to progress with my RDS instance.
RDS instance master user is 'admin'
- Can connect up to the instance using the admin user (MySQL WB or commandLine):
C:\Users\xx>mysql -u 'admin' -p --host=md-blahblah-db-instance.blahblah.ap-southeast-rds.amazonaws.com
Enter password: ********
Welcome to the MySQL monitor. etc etc
Type '\c' to clear the current input statement.
- Now I'm in but (on MySQL WB or commandLine) cannot 'see' schema:
mysql> use uxxx
ERROR 1044 (42000): Access denied for user 'admin'@'fxxxx.lnk.telstra.net' to database 'uxxx'
mysql> GRANT ALL PRIVILEGES ON umb.* TO 'admin'@'%' WITH GRANT OPTION;
ERROR 1044 (42000): Access denied for user 'admin'@'fxxxx.lnk.telstra.net' to database 'uxxx'
mysql> GRANT ALL PRIVILEGES ON umb.* TO 'admin'@'%' IDENTIFIED BY 'somepass';
ERROR 1044 (42000): Access denied for user 'admin'@'fxxxx.lnk.telstra.net' to database 'uxxx'
mysql> CREATE USER test identified by 'password';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
mysql> flush privileges;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
Have tried using ideas from the accepted answer at User cannot see databases in mysql workbench but can't progress as can't create another user. MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES) suggests that I have could possibly have another user named admin@'fxxxx.lnk.telstra.net', hence this problem.
Quoting:
Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%' when connecting from localhost.
'bill'@'localhost' does match 'bill'@'%', but would match (e.g.) ''@'localhost' beforehand.
The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).
Reluctant to start again as a lot of CloudFormation stacks are associated with this database. Any ideas? Appreciate all help.
