2

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?)

1
  • Damn it, FLUSH PRIVILEGES; fixed my issue and now i can use GRANT command without error. but still is there any good way to take backup of grant using mysqldump?
    – Satish
    Sep 15, 2021 at 16:53

2 Answers 2

3

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.

6
  • I got this error when i restore ERROR 1396 (HY000) at line 16339: Operation CREATE USER failed for 'mariadb.sys'@'localhost'
    – Satish
    Sep 15, 2021 at 18:08
  • 2
    Apologizes, there's still a couple of bugs I need to fix. As you are merging into a fresh instances I suggest using --insert-ignore in the dump which will avoid the issue with the user mariadb.sys. Be very careful if you use --replace means that a new import user that doesn't exist in the dump is needed for the import (MDEV-25537).
    – danblack
    Sep 16, 2021 at 8:45
  • @danblack The man page (in the section for --system) says to use --insert-into, but that doesn't seem to be a recognized option! So I guess it's --insert-ignore as you say. Anyway, the --system= feature is awesome, thanks for implementing it.
    – dbdemon
    Sep 16, 2021 at 10:19
  • yep. --insert-ignore. Glad you like it. Thanks for answering with it. Happy to take bug reports/feature requests.
    – danblack
    Sep 16, 2021 at 10:37
  • And fixing spelling mistakes.
    – danblack
    Sep 16, 2021 at 10:50
0

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
4
  • So i have to install percona-toolkit for pt-show-grants command to just dump grants, in that case i would use my method which has no dependency
    – Satish
    Sep 15, 2021 at 19:25
  • Yes. That is why I posted both methods years ago. Sep 15, 2021 at 19:36
  • 1
    There may be one problem. You might have MairaDB configured not to create users. Run SELECT @@global.sql_mode. If you see NO_AUTO_CREATE_USER, then you must also include the CREATE USER command for each user. A possible workaround might be to run SEt sql_mode=''; before executing your grants file. Sep 15, 2021 at 19:40
  • Older version of MySQL/MariaDB will automatically create the user when you issue GRANT USAGE ... PASSWORD .... Newer versions require making the user first or blanking out sql_mode. Sep 15, 2021 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.