MySQL 8.0 Migrating Roles from one server to Another

All we need is an easy explanation of the problem, so here it is.

Hi there in my company we are starting to implement MySQL 8.0 (because we are using AWS Serverless 2) and one of the "new features" that MySQL 8.0 that has is the "User Roles".

My question is when we do a certain amount of Roles that we assing to specific users when we want to "move" those users from a database to another with mysqldump those roles will be migrated to or how can we do this?

This starts because when we do in the other MySQL versions the mysqldump we never could migrate the users and passwords from one database to another.

I hope all of you could understand my situation and thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

mysqldump dumps users and roles as INSERT statements to populate data in the system tables in the mysql schema. So to dump these, you would use:

mysqldump mysql

The new tool mysqlpump has an option for dumping users and roles as CREATE USER and GRANT statements instead:

mysqlpump --exclude-databases=% --users

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply