The SHOW CREATE ROLE command does not exist. I am trying to export users, roles and grants for migration to a new server but there does not seem to be an SQL command to make a .sql statement that creates roles based on the roles that already exist.

Here is a command I use to export users but it breaks if I do not include the "is_role = 'N'" because roles are also stored in the user table:

mysql --skip-column-names --no-auto-rehash --silent --execute="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>'' AND is_role='N' AND user NOT IN ('mysql','mariadb.sys','root');" | mysql --skip-column-names --no-auto-rehash | sed 's/$/;/g' > /tmp/user-grants.sql

Is there a way to create .sql backups so that importing them into a new server will create the roles first, then grants for the role, then create users and grants for the users (e.g. assigning them roles) as well as set the default role if it had a default role to begin with?



Answer Answered by Lon Hammonds in this comment.

Thanks. I voted those 2 issues up. I also posted my workaround solution using SED on those issues and on the Ubuntu Forums at


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.