Can't restore backup from other server when already have current mirror of database

Submitted by cedral on Wed, 09/14/2011 - 13:27 Pro Licensee

I am using a mysql master slave arrangement to keep all my databases mirrored. I am now trying to move over to slave server and upgrade from 8.04 to 10.04 ubuntu. I can't import the backups because they ignore the mysql database creation checkbox...

I get the following when I start with the attached screenshot. How do I import a backup without importing the database?

Re-creating virtual server serengeti.com .. .. a clash was detected : A MySQL database named XXXXX already exists

.. failed! See the progress output above for the reason why.

Status: 
Closed (fixed)

Comments

Submitted by cedral on Wed, 09/14/2011 - 15:29 Pro Licensee

This is blocking my disaster recovery. I need assistance as quickly as possible.

Submitted by andreychek on Wed, 09/14/2011 - 15:35

Well, Jamie may need to take a look at the issue of it trying to create a database.

However, just to get you up and running -- is it an option to remove the database in question from your server?

You can remove the database from Webmin -> Servers -> MySQL. Choose the database name from the dropdown, then select "Drop Database".

Submitted by JamieCameron on Wed, 09/14/2011 - 15:46

So did you already transfer or replicate the database manually? Or is this a single database that is stored on a remote MySQL server that your Virtualmin system is configured to connect to?

Submitted by cedral on Wed, 09/14/2011 - 15:57 Pro Licensee

This is a backup from another machine running Virtualmin Pro which used local databases. I am moving to the new machine which also uses local databases. However, the databases all already exist because the new machine was the mysql replication slave for the old machine. I have many virtual servers some with multiple databases so trying to backup, remove, and restore each database separately for all my virtual servers I am restoring to this machine would be somewhat onerous.

Submitted by JamieCameron on Wed, 09/14/2011 - 16:23

Is data still being replicated, or are you happy to use the data in the backups? If the backed up data is OK, I would suggest wiping all your MySQL databases and users (perhaps with a complete re-install), and then restoring the backups. Of course, you should only do this if your backups included your database contents..

Submitted by cedral on Wed, 09/14/2011 - 16:43 Pro Licensee

Data was replicated after the backups were taken. We took weekly backups because our files and configuration rarely changed. Our databases change lot though so we did realtime replication. I can't really say that it is feasible to do what you are describing. Your software documentation specifically says I could skip database recreation on restore. I kind of need that to work now.

Submitted by JamieCameron on Wed, 09/14/2011 - 16:56

Ok, here is another option - on the restore form under "Features and settings" select "Only those selected below" and select all features except for "Contents of server's MySQL databases".

Then in the "Other restore options" section, change "Only create servers with selected features?" to "Yes".

Submitted by cedral on Wed, 09/14/2011 - 17:03 Pro Licensee

will that leave virtualmin unable to manage the databases after the restore? Perhaps I do need to do something like shutdown mysql and make a copy of /var/lib/mysql, restart it and remove all my databases then do all my restores and shutdown again and replace all the files with the ones from the copy. hacky, but I don't want virtualmin to become unaware of the databases since I want them in the backup files. In this case I don't want to restore them but they are important as they serve as my offsite backups.

Submitted by JamieCameron on Wed, 09/14/2011 - 17:39

Yes, Virtualmin will not know that the domains have MySQL enabled if you follow my procedure from step 7.

The fix is to manually edit each domain's file in /etc/webmin/virtual-server/domains and add the line mysql=1 at the end.

I admit that this procedure is sub-optimal .. what Virtualmin really needs is a way to do a restore when the DB is already in place. I will work on adding that.

Submitted by cedral on Wed, 09/14/2011 - 17:53 Pro Licensee

cool, make sure you still do all the grants and user setup. mirroring your databases does not imply necessarily having mirrored your security information.

Submitted by JamieCameron on Sat, 09/24/2011 - 21:30

Virtualmin 3.89 will include a fix to better handle this case, by preserving existing databases on the remote system. Users and permissions will be re-created as needed.

Submitted by Issues on Sat, 10/08/2011 - 21:45

Automatically closed -- issue fixed for 2 weeks with no activity.