Database restore does not work from Virtualmin backup

[websitemaster]

SYSTEM INFORMATION
OS type and version:	Ubuntu Linux 20.04.3
Webmin version:	1.981
Virtualmin version:	6.17-3 Pro
Related products version:	10.3.31-MariaDB-0ubuntu0.20.04.1

---

After deleting PHP8.0 on my server, I ran “autoclean” and “autoremove” to delete the unnecessary dependencies. Autoremove then deleted the MySql server and some other important packages. In any case, I was able to reinstall everything except MariaDB. MariaDB then turned out to be super complicated.

First, of course, I tried to reinstall the same version so that I could continue with the current database data in the / var / lib / mysql folder. Unfortunately that never worked. Mainly because I no longer knew what version was installed before deleting it. According to the error messages, either the MariaDB version was not the same as before or the data was inconsistent or a problem with the IP * files.

In any case, after several hours I decided to make a backup copy of the folder /var/lib/mysql and delete it so that I could do a completely new MariaDB installation. This in the belief that I have a backup of every virtual server on Virtualmin which contains the database.

Unfortunately that was not the case! Unfortunately I have not been able to restore the database of all virtual servers up to now. Unfortunately, I was unable to restore the database of some very important websites and applications with sensitive data. The backups are all saved in exactly the same way with the same cron job from Virtualmin. And the database backup is activated for all of them. The daily backup has the following options:

Virtual Servers:
Server to save: All Virtual Servers
Include sub-servers of those selected: yes
Limit to servers on plan: Any plan

Features and settings:
Features to backup: Only those selected below …
(all except ProFTPD virtual server configuration are selected)
Virtualmin settings to also backup:
(all are selected except: FTP directory restrictions)

Destination and format:
/ home / backup /% Y-% m-% d
Additional destination options: Do strftime-style time substitutions on file or directory name: YES
Transfer each virtual server after it is backed up: YES
Backup format: One file per server
Create destination directory? YES
Action on error: Halt the backup immediately
Backup compression format: Default
Backup level: Full (all files)

Command to run after backup: backup to Backblaze

I cannot understand why most backups restore the database smoothly and a few not. I noticed that there are two projects that are installed on sub domains. These are both time trackers that contain important data that we urgently need. Unfortunately, it is not possible to restore the database on the main domain either! This means that the restoration of the following virtual servers does not work:
time.domain.com & domain.com

Now I have a very big problem and I hope that you have a solution for it, because I now only have the physical databases which were in the /var/lib/mysql folder. Unfortunately I don’t have a complete dump of all databases. I assumed that the virtual server backup works. I’ve also used this a few times and restored the database without any problems.

The backup creates the following three files:
domain.com.tar.gz
domain.com.tar.gz.dom
domain.com.tar.gz.info

Unfortunately, I couldn’t find any data in the main tarfile. Where is the database dump stored?

I very much hope that you can help me very soon. Because there are some projects among them which urgently need the data again.

Peter

[calport]

@websitemaster

I assumed that the virtual server backup works. I’ve also used this a few times and restored the database without any problems.

Your assumption is correct: Virtualmin backup works. You have used it in the past, you say, and have successfully restored from Virtualmin backups. Why do you now have doubts about a functionality which has worked before?

Additionally, I am happy to confirm that the backup options that you have specified will include a dump of the database in the archive.

Extract the archive and look for a SQL file in a hidden directory named .backups

[websitemaster]

Now i’m really worried. Because now I have actually seen that the Virtualmin backup does not always work! With most virtual servers it worked and in the .backup folder there is a gzip file which turns out to be an SQL dump after unzipping. but for those virtual server backups that did not work via restore in Virtualmin there is no such file!

So with the working backups these two mysql files are in the .backup folder:

domain.com_mysql
domain.com_mysql_domain.gz

In the virtual server backups which the restore of the database via Virtualmin Restore did not work, it unfortunately looks bleak. Only the following file can be found there:

domai.com_mysql

That means that I don’t have a database backup for my 5 important client projects!

That is not good at all. Your backup does not work the same for all virtual servers! For some it does a SQL dump and for some it doesn’t !?

Since I now know that I have of course immediately initiated a mysqldump of all databases on my system, which is also backed up daily. Unfortunately, that doesn’t solve my current problem. Because I lost 5 databases! And the only backup is the physical content of /var/lib/mysql.

How can I convert the data in this folder to a proper SQL dump? Is there a solution?

[websitemaster]

I have now checked all virtual server backups where the restore via Virtualmin did not work and unfortunately my fears are confirmed. In all of them the SQL dump of the database is missing! Such an SQL file is available for the two backups which have an application on a subdomain. The content but only the following:

-- MySQL dump 10.13  Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost    Database: kimai
-- ------------------------------------------------------
-- Server version	8.0.26-0ubuntu0.20.04.2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping events for database 'kimai'
--

--
-- Dumping routines for database 'kimai'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-09-27  0:29:50

Where kimai is the name of the Subdomain: kimai.domain.com. That’s not the name of the database. The database name should be projectname_kimai. The database name of the original Domain is projectname_projectname.

This is exactly the same with the other project which resides on a Subdomain:

-- MySQL dump 10.13  Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost    Database: time
-- ------------------------------------------------------
-- Server version	8.0.26-0ubuntu0.20.04.2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping events for database 'time'
--

--
-- Dumping routines for database 'time'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-09-27  0:57:31

The right database name is the following:
projectname_time - Subdomain database name
projectname_projectname - Main Domain database name

Did the naming cause the error here?

You should get to the bottom of this problem. Because that can happen to anyone who trusts your backup.

Nevertheless, I would be extremely happy if you had a solution for my acute problem. I absolutely need this data and it is now only in physical format in a backup of the /var/lib/mysql folder.

[calport]

@websitemaster

Now i’m really worried. Because now I have actually seen that the Virtualmin backup does not always work!

There is one scenario, not entirely uncommon, in which some of your databases might not be included in the backup, even when Virtualmin backup is operating normally: if the database is owned by some other user but is used by a virtual server of the user for whom the backup is being run, then when a backup is taken, only the files owner by the user are part of the backup, not the database - because the database belongs to another user and not the user for which the backup is being run.

If the five databases that are missing from your backups are owned by a different user / virtual server than the one you expect, @websitemaster, then they will not be found in the archive that you are looking into but in some other archive that you have of the user who actually owns the database.

Or it could simply be that you excluded databases from the backup of five virtual servers from which the databases are missing. Virtualmin is used on thousands of servers so when something enexpected happens, it tends to be the human rather than the software which is at fault.

[calport]

@websitemaster

Nevertheless, I would be extremely happy if you had a solution for my acute problem. I absolutely need this data and it is now only in physical format in a backup of the /var/lib/mysql folder.

Such solutions exist, of course. If you have used ISAM then it is quite trivial to recover the database.

[websitemaster]

I don’t really understand the above scenario. What I can say is that all the virtual servers and the database owners are all exactly the same. I.e. the administration username of the virtual server websitemaster is websitemaster and the owner of the database is websitemaster. this is the case with all virtual servers. The backup job saves all virtual servers at once! The settings are therefore the same for everyone. That means that the database is not excluded for 5 virtual servers. Because the settings in Virtualmin Backup are the same for all virtual servers. And the backup is running for the root user for all virtual servers.

The database engine is InnoDB not myISAM. It’s a little more complicated. So far I have only found one variant on the net. If the version of MySql is exactly the same as the one that was used when i did the back up of the files in the /var/lib/mysql folder then you can simply copy the database folder into the new installation. That’s the problem. I don’t know which version was installed before the database server was deleted.

If you know a solution how to convert InnoDB folders into a SQL dump I would be extremely happy for a link to this solution and my customers would be very grateful for a solution to this problem.

[websitemaster]

What I have just seen is that the administration name for the virtual servers with subdomains is the same as the one for the subdomain. Is that the problem you described?

[websitemaster]

Since there is no input from you about what I should do after this gross error with Virtualmin Backup, I have to set up a new server for better or worse and change the MySql version until I have found the right version that fits the mysql folders. So that I can create a SQL dump of it.

But if I have been successful and have been able to restore the 5 missing databases does the problem still exist. I would like to backup also the database from these 5 virtual servers. I.e. the problem still exists. I would be happy if you could solve the virtualmin backup problem.

In order to rule out any user rights problems, here is a list of the owners of a project in which the database is missing during the backup:

Virtual Server Main Domain:

Domain name	websitemaster.ch
Administration username	websitemaster
Administration group	websitemaster
Created on	10/10/2020 3:57:45 PM by root
Home directory	/home/websitemaster

Virtual Server Subdomain:

Domain name	kimai.websitemaster.ch
Administration username	websitemaster
Administration group	websitemaster
Created on	10/11/2020 3:07:53 PM by root
Home directory	/home/websitemaster/domains/kimai.websitemaster.ch

From this two Virtual Servers the backup of the database is missing in the virtualmin backup. All Backup Features details are listet in the first message in this support case. In short all features and settings are selected to backup. also the Contents of server’s MariaDB databases. The backup is run for all virtual servers ones a day from the root user. Which actually should own or at least should have access to all databases.

I checked all the other virtual server backups to find out whether the database was backed up in another project because of another owner. unfortunately this is not the case. the mysqldump of this project is nowhere!

Let me know if you need more details to find out why the mysqldump doesn’t work in this case. Thanks for your help in advance.

Best Regards,
Peter

[calport]

@websitemaster

But if I have been successful and have been able to restore the 5 missing databases does the problem still exist.

The most certain way to answer this question would be to restore the 5 missing databases, make an archive via Virtualmin backup and then check the archive for the existence of a SQL dump of the databases in the .backup directory.

Let us know how it goes.

[websitemaster]

That was to be expected. I was able to restore the missing databases. Only the problem still exists. The 5 virtual servers that did not have a MySql dump in the archive still do not. The Virtualmin backup job has a problem with databases that start with the same name. A very clear pattern can be seen. The databases that were not backed up all start with the same name! So out of these 5 projects there are always at least two databases that start with the same name and the other one or others followed by an underline. E.g.
projectname
projectname_timetracker
projectname_es

I.e. if there are more than one database starting with the same name, Virtualmin Backup will not back them up! Why does Virtualmin Backup have a problem with this? And what can I do to include these databases in the backup archive?

[calport]

I am.running a test backup with databases which are named along the lines outlined by you. If the backup that my install of Virtualmin generates does not contain all the databases then it is a confirmed bug in Virtualmin and a bug report can be filed.

Standby for results…

[calport]

I have used a test system almost identical to the one you have: Ubuntu 20

The databases have been created (a few starting with the same name):

Backup with default options (a bit different from yours, I know):

And this is what I get:

It all works perfectly on my system. All databases are backed up.

[websitemaster]

Thank you for the test run. I have just created two new virtual servers to test this again. The original domain: coconsenyurt.ch and a subdomain for the Timetracker software: time.coconsenyurt.ch.

The databases are called as follows:
coconsenyurt - (Virtual Server: coconsenyurt.ch)
coconsenyurt_kimai (Virtual Server: time.coconsenyurt.ch)

There is no SQL dump in the backup archive of the virtual server of the subdomain!

Important: The two databases do not belong to the same virtual server! The two databases have almost the same name because they belong to the same project. In my case, there are the following two virtual servers:
coconsenyurt.ch. (Main Domain - Virtual Server 1)
time.coconsenyurt.ch. (Sub Domain - Virtual Server 2

As far as I can see, in your test the databases with the same name at the beginning are all in the same virtual server. In my case they are not! Test the case with two different virtual servers. I assume that the databases are then not in the archive.

[websitemaster]

Have you been able to find out anything yet?

[jotst]

To use exact the same DB prefix for different Users/ Virt Servers, is also in other CP’s not a good idea.

Also mistakes then are more common to do things wrong because messing up.
You can do a lot with adminer and or phpmyadmin, but take care while things that are not supported in VM then…

So only to bring back what you need and then correct and test things asap in VM

I suggest to have slightly different prefix DB names as project1. project1a. if same overal project t okeep them appart, i do sometimes also use in dbname short code for used php version if the pho version is important for project migrations to not mix. You can do that then also with the Mariadb / mysql versions used

Joe has writen in other topic only the database versions packages out of OS are supported.

So do you use such, or have installed on your own a Maradb version that was not in OS package, then it could be that things are going wrong i don’t know.

I only know yes if you do use other then OS package it is more complicated , also the installation…

i can’t help while no knowledge of ubuntu at all!

Alway’s check after doing config changes at minimum the re-check Checking Configuration in Virtualmin system, then you have a short yes or no overview of what is recognized by virtualmin and also some versions info’s. Also do a " Validate Virtual Servers” to check for problems

Example from re-check one test i did run and yes not supported versions by VM but that test see correct versions and is completed without errors:

The following PHP versions are available : 7.2.24 (/bin/php-cgi), 7.4.25 (/bin/php74-cgi), 8.0.11 (/bin/php80-cgi)
The following PHP-FPM versions are available on this system : 7.2.24 (php-fpm) 7.4.25~RC1 (php74-php-fpm) 8.0.11 (php80-php-fpm)
The following PHP execution modes are available : none fpm
Apache is configured to host SSL websites.
MariaDB 10.6.4 is installed and running.

Achgr i see the problem wen remove packages then some dependencies are removed also , yup that is something to take care of is not a VM thing, this happens to often.