SHOW CREATE ROLE
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 https://ubuntuforums.org/showthread.php?t=2451555