mysqldump all database including user/password and grants

Question

I am running 10.5.12-MariaDB and trying to take full backup of all databases including user accounts/ passwords and Grants but somehow i am not able to take backup of grants so am i missing something.

Example.

Taking full backup

old-DB # mysqldump --opt --all-databases > /root/openstack.sql

Restoring to new DB server

new-DB # mysql < openstack.sql

Now i can see all databases and all users in mysql.user table but grants are missing for all users.

example:

old-DB # mysql -e 'show grants for glance';
+-------------------------------------------------------------------------------------------------------+
| Grants for glance@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `glance`@`%` IDENTIFIED BY PASSWORD '*6FE1E5E532A9189C900FB696AC9DEF84CDE2194A' |
| GRANT ALL PRIVILEGES ON `glance`.* TO `glance`@`%`                                                    |
+-------------------------------------------------------------------------------------------------------+

Its missing on new DB server

new-DB# mysql -e 'show grants for glance';
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'glance' on host '%'

So i did following

old-DB# mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Now MySQLUserGrants.sql has all the users grants command so i just copy paste all command and paste to new-DB i got following error

MariaDB [(none)]> GRANT USAGE ON *.* TO `glance`@`%` IDENTIFIED BY PASSWORD '*6FE1E5E532A9189C900FB696AC9DEF84CDE2194A';
ERROR 1133 (28000): Can't find any matching row in the user table

Question, how do i take full mysql backup which include everything even including grants. (or am i missing something?)

Answer 1

Yes. You can use the --system switch. This exists only in the MariaDB version of mysqldump, although you can use that to dump from a MySQL database.

system=users will dump CREATE USER ... and GRANT ... statements for your users and grants:

mysqldump --opt --system=users --insert-ignore --all-databases > /root/openstack.sql

The --insert-ignore option is important to avoid conflicts with existing users when importing the dump. The option will result in CREATE USER IF NOT EXISTS statements rather than just CREATE USER.

To really include everything, use instead --system=all.

See documentation in the MariaDB KB mysqldump and the man page (search for --system).

This is a recent feature which has been backported to MariaDB 10.2 and later versions.

Answer 2

You are pretty much doing the right thing with backing up the grants logically.

I recommended that same way about 7.5 years ago : Export all MySQL users

You can also use pt-show-grants and append FLUSH PRIVILEGES; to the output

(
   pt-show-grants ...
   echo "FLUSH PRIVILEGES;"
) > user_grants.sql