3

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

Satish
  • 211
  • 2
  • 5
  • 13
  • 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 '21 at 16:53

2 Answers2

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.

dbdemon
  • 6,351
  • 4
  • 19
  • 38
  • I got this error when i restore ERROR 1396 (HY000) at line 16339: Operation CREATE USER failed for 'mariadb.sys'@'localhost' – Satish Sep 15 '21 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 '21 at 08: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 '21 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 '21 at 10:37
  • And fixing spelling mistakes. – danblack Sep 16 '21 at 10:50
  • @danblack ah, I reported it as a bug - MDEV-26622 - superfluous now! – dbdemon Sep 16 '21 at 10:55
  • is there a non-mariadb way to do it? Using Oracle mysqldump? – Nick Bonilla Apr 21 '23 at 15:23
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
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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 '21 at 19:25
  • Yes. That is why I posted both methods years ago. – RolandoMySQLDBA Sep 15 '21 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. – RolandoMySQLDBA Sep 15 '21 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. – RolandoMySQLDBA Sep 15 '21 at 19:41