Backup and restore MySQL database mysql which holds user permissions

2 posts / 0 new
Last post
#1 Wed, 09/19/2018 - 04:45
jlinkels

Backup and restore MySQL database mysql which holds user permissions

Originating server: Webmin version 1.740 Virtualmin version 4.16.gpl Debian Linux 7

Destination server: Webmin version 1.890 Virtualmin version 6.03 Debian Linux 8

I have migrated a domain example.com by creating a backup of this domain in the old server and restored it on the new server. That went fine.

However, I have a database in this domain (Kimai). This database has users permission etc set in the mysql database. mysql is the name of the database holding the users permissions. Not the name of the MySQL server.

The transfer of the kimai database went fine. But the permissions are not there. I cannot access it. The mysql database on the new server is there. But it was not updated with the user settings for kimai.

I think I can make a manual backup/restore of the mysql database from webmin. But I don't understand why this was not done while backup/restore of the domain.

How can I transfer the mysql database from the old to the new server as part of the virtualmin backup/restore? I did backup the virtualmin global settings, but this database is not included.

And, of course, how do I make this a part of the backups of the domain? If this were not a transfer, but a backup to use after a crash, how can I be sure that database users are part of the backup for disaster recovery?

I tried to Google this, but all I get is results of backing up MySQL databases, not the mysql database.

jlinkels

Sun, 09/23/2018 - 10:35
jlinkels

The way I eventually did this was to backup the databases in Webmin, transfer them and restored them on the new server. Als in Webmin. All worked well, I had to restart MySQL in order to have the privileges being active.

What I still don't understand is that I have transferred domains before, and once the domains were on the new server, the databases continued to work. I am sure I never did it in this way before. But I do not recall what I did do in order to get it working at that time.

The disadvantage of the Webmin backup database procedure is that all databases are being backed up. Some databases are a few GB. Which is not that large for real environments, but still...

I also know that somewhere it is recommended to always regularly backup the databases through a Webmin schedule. But I am pretty sure my domain databases are being dumped prior to a regular virtual domain backup. Which means that a dump is backed up. Not the MySQL files.

jlinkels

Topic locked