Topic: backup of the SME MySQL database

[wdepot]

Does the Full Backup Session of the workstation backup built in to SME 7.5 backup include everything needed to restore a server in case of failure INCLUDING the MySQL data files that are stored on the server in var/lib/mysql?  We have been using DAR2 for backup and discovered too late that the disaster recovery backup it makes by default didn't include the MySQL database. Not a good thing when using osCommerce. Fortunately it is easy to add more folders to the DAR backup but I was wondering what the built in SME backup covers since it doesn't list the folders that it includes in a full backup session. Thankfully it was only a boot sector that went bad on the server hard drive and we were able to recover the needed MySQL files without much trouble but I would prefer that the backup files include everything needed to recover the server in case of problems so I can simply reinstall SME and then restore from the backup to get up and running again. I decided to add a daily full workstation backup to our two daily DAR backups for extra protection but I would like to know that we are actually protected.

[janet]

wdepot

Please read this to find your answers
http://wiki.contribs.org/Backup_server_config
which says, when referring to doing a manual backup:
On the old server do the following to dump the mysql tables to a file in /home/e-smith/db/mysql/:
signal-event pre-backup

Note that this step is part of all standard sme backups & is done in DAR2, Affa etc, which conform to sme backup concepts.

You are incorrect to say that the DAR2 disaster recovery backup does not include the mysql databases.
Any backup job created in DAR2 includes the myslq db's.
Before you start arguing otherwise, note that DAR2 or any of the other standard sme backup routines, do not backup the /var/lib/mysql/* folder tree directly, but they do create a dump of the mysql databases during the prebackup routines, as referred to above.
Note you cannot successfully copy all mysql db files if they are open (in use), so a dump is the technically correct way to do it.

Look in your backup file ie smeserver.tgz (or similar) in the /home/e-smith/db/mysql/ folder.

[mmccarn]

The 'pre-backup' event mentioned by mary steps through all of the mysql databases listed by this command:

Code: [Select]

mysql -BNre "show databases;"
And runs the following for each database:

Code: [Select]

mysqldump --add-drop-table -QB "$db" -r /home/e-smith/db/mysql/"$db".dump
So, you should have '/home/e-smith/db/mysql/oscommerce.dump' both on your sme and in your DAR backups (assuming you used the default database name for oscommerce).

[cactus]

I decided to add a daily full workstation backup to our two daily DAR backups for extra protection but I would like to know that we are actually protected.

Using this solution you are not, as you have no way of knowing the status of those files and the state of the database. The database makes uses of buffers in the memory which might not have been recorded on the hard disk yet at the the time of backup, or you are performing a backup in the middle of some database process, making your backup corrupt, such as column, table or row locks might be present at that time while the database engine is reading and/or writing to disk, foreign keys and relations might get corrupt, etc.

There is no certainty you can make a consistent backup by just backing up the data files like you do know. You would at least have to make sure mysqld is not running.
For more details have a look at: http://dev.mysql.com/doc/refman/4.1/en/backup-and-recovery.html, the proper method would be to dump the contents of the database using mysqldump as SME Server does. This would ensure that the data from the memory is flushed and recorded in the tables, before dumping them to the backup file, making a consistent backup.

To re-assure you, as some others have done already, SME Server default backup as well as the DAR backup backup your MySQL data by dumping the data to files that (you or a restore process) can later import into MySQL again. This is the advised method of MySQL, using the tools MySQL provides for this task.

[wdepot]

Interesting, so DAR2 does backup the MySQL Databases but it doesn't restore them when you do an Emergency Restore. I wonder why that would be the case. I guess I should ask if the built in SME backup will properly restore the MySQL databases when you restore from the full emergency backup or not? It seems to me like a complete restore from an emergency backup should restore everything and not leave a part of if for the user to do manually without telling the user that it needs to be done or how to do it.

[cactus]

Interesting, so DAR2 does backup the MySQL Databases but it doesn't restore them when you do an Emergency Restore. I wonder why that would be the case. I guess I should ask if the built in SME backup will properly restore the MySQL databases when you restore from the full emergency backup or not? It seems to me like a complete restore from an emergency backup should restore everything and not leave a part of if for the user to do manually without telling the user that it needs to be done or how to do it.

They all restore. Did you look for errors? Did you complete the restore process?

[wdepot]

I have no idea what you mean by "Did you complete the restore process?" When I did the restore I set DAR2 on our backup server to the same settings used on the original server and told it to restore the backup (*.* to /). Everything was restored but the MySQL database files. I would think DAR2 should automatically run some sort of post-restore event after it has reloaded everything from the backup to finish restoring everything to normal INCLUDING the MySQL database.

[janet]

wdepot

The DAR2 Disaster Recovery backup & restore should restore the mysql databases, if it is not then you should report a bug.
It's been a while now since I used DAR2, but the correct restore procedure is to reinstall a fresh copy of the sme server operating system, then install the DAR2 contrib, then create the Disaster Recovery job, then select to restore from your DAR2 backup file. You must do the Reconfigure and reboot step at the end. Did you follow this procedure ?

Note that DAR2 has been obseleted by the standard backup with Dar procedure now included by default in the Server Manager Backup and Restore panel. Select the backup to workstation option to enable the daily full/incremental routine.

DAR2 should now only be used if you want to do full system backups every day.
You can also use it to do partial backups of selected data (a specific backup job), and then do manual partial restores, but that is not really good practice.

You should still have a full server backup running, and that is where the backup with Dar is good that it adds small daily incremental backups to the first full backup, thus saving space on your backup media and speeding up the daily backup process.

If I were you, I would start using the standard Backup to workstation (that uses backup with Dar), which does one initial full backup and then daily incremental backups. Depending on your requirements scedule it to run over a week or a month, and swap backup (USB) disks either daily or weekly depending on how strictly you need to remove backups off site. Daily removal is "best".

And yes the standard sme backup options also backup and restore the mysql db's, if you follow the restore procedure correctly ie restore to a cleanly installed OS, and then reinstall any contribs.
You should not do a full or disaster recovery restore to a system that already has data on it.
If it is not doing so, then please report a bug.

[janet]

wdepot

I have no idea what you mean by "Did you complete the restore process?" When I did the restore I set DAR2 on our backup server to the same settings used on the original server and told it to restore the backup (*.* to /).

It sounds like you are doing your restores incorrectly. With the DAR2 contrib, you should select to do a Disaster Recovery restore from the DAR2 server manager panel, and you will get a message when the restore is complete, and then (from memory) you select to reconfigure and restart the server, which does all the rquired signal-event commands etc.

Where and how exactly are you "telling it to restore *.* to /", as that sounds like a very wrong procedure.

Everything was restored but the MySQL database files.

They will not be restored if you are just manually copying files back to the server (which it sounds like you are doing).
There are important pre restore and post restore commands. If you use the software tools correctly, then all the required commands and procedures will be automatically performed.

[wdepot]

I guess I should start with what was being backed up. When I created the backup jobs I used the web administation panel in SME Server Manager (http://website/server-manager) and used the default selections entered by the Disk ARchive panel which are:

etc/e-smith/templates-custom
etc/e-smith/templates-user-custom
etc/group
etc/gshadow
etc/passwd
etc/samba/secrets.tdb
etc/samba/smbpasswd
etc/shadow
etc/smbpasswd
etc/ssh
etc/sudoers
home/e-smith
root

Prune and exlude were left blank, the target was properly set to ip_address/sharename with username and password, file system cifs, slice size 2000 (default), compression 5 (default), expiry 10, verbose off, backup day * and time set near midnight.

To restore the server information to our backup server I again went into http://website/server-manager, selected disk archive,  and reentered the backup information exactly as it had been before except I chose manual for the backup day. After saving that I chose restore, entered the date of the last backup, chose disaster recovery enabled, restore *.* and restore to / just as the DAR instructions on screen said to do.

After I was sure DAR had finished the restore operation I choose Reboot or Shutdown from the web panel and selected Reconfigure. I know the restore worked because all of the custom settings, web files and security certificates were in back place and working properly after the restore. The only thing that didn't come back were the MySQL database files.

Thankfully I was able to boot the old server from the SME cd and use SME recover to get to the database files and copy them to our backup server. The failure of the old server happened when I did the Reconfigure right after the SME 7.5 updates were released. The problem was a bad section in the boot area of the first hard drive that caused the system to lock up in the GRUB boot loader. Since the system had shut down normally during the reconfigure after update I know the MySQL database files weren't corrupted.

By the way, I've checked our backup server which has been running for several days now and looked in the home/e-smith/db/mysql folder. There is nothing there. Does DAR delete the MySQL dumps to that folder once it has completed backup or is our install of DAR just not doing the pre-backup MySQL dump to that folder that I have been told in this thread that it is supposed to do?

[janet]

wdepot

Firstly what version of smeserver-dar2 is installed, do
rpm -q smeserver-dar
Really old versions may not do a mysql dump for manually specified backup jobs (ie anything other than the disaster recovery job).
The smecontribs repo shows
smeserver-dar2-0.0.1-23.el4.sme.noarch.rpm

If the mysql dump is not in your backup file then for sure it cannot be restored so please check.
The DAR2 wiki article tells you how to enable the mc plugin to be able to read/open dar files using midnight commander (mc).
Please take a look inside your dar backup file(s), and in /home/e-smith/db/mysql you should see a dump file for each mysql db eg dbname.dump.


Did you do the restore to a clean install of the sme OS ?
Did you restore from the disaster recovery backup job, or one of the other manually specified backup jobs ?
From your description it sounds like you created a user specified backup job (rather than a disaster recovery job), but then selected that backup file to do a disaster recovery restore.
You can only really do a disaster recovery restore from the disaster recovery backup file, is that what you used ?

Does DAR delete the MySQL dumps to /home/e-smith/db/mysql folder once it has completed backup...

The dump files will only be there after the pre backup event runs, so login during a backup and you should see the files there.
I don't recall if they get deleted when the backup finishes, but I think so.
Certainly on a server being restored, any old dump files are deleted before the restore is done.

You should start looking in the messages log file and also the dar backup log files to see what is happening during backup and during restore.

AFAIK all backup jobs did include the mysql dump, as this change was made a long time ago during the early development of smeserver-dar2, for the very reason you are now quoting. Are you using an old version ?

Please perform a disaster recovery backup using DAR2 (Disk Archiver contrib latest version), reinstall a clean sme OS, install smeserver-dar2, setup the disaster recovery job, and then do a disaster recovery restore, on your test server. This should test the whole process to see if mysql db's are restored.
If mysql db's are not restored then please lodge a bug report against the contrib.

You could also do a quick test by creating a backup job that just includes /root. Do a manually instigated backup, then check the resultant backup file using mc, to see if the mysql dump files are included.

[wdepot]

Well, I just installed the latest version of DAR2 onto the server that is running now yesterday afternoon since I figured I probably should match the version loaded onto the fresh install of SME of our primary server computer now that we've replaced the hard drives. Judging by the rpm files stored on my Mac we probably had DAR2 2.3.6 with SME-Dar2 0.0.1-10 loaded when the boot sector of the hard drive went down.

I'm really not sure how clean of an install our backup server had on it when I did the restore. It already had DAR loaded on it but it was on SME 7.3 while the main server was using SME 7.4 as of the last automatic backup before it went down. I don't think the backup server could have had too much installed on it since it still had the index.html file for "Under Construction" in the primary ibay and the var/lib/mysql folder was completely empty after the restore (not even out of date files were present).

All of DAR2 backup jobs we have are disaster recovery jobs. Whenever we created a new DAR job we left the backup selections field with the default loaded by DAR which specifically states "All backup selections needed to perform a Disaster Recovery are preset below."

A check of the messages file shows that the MySQL table dumps are indeed deleted during the post-backup procedure so that explains the empty home/e-smith/db/mysql folder. It seems like it would make more sense to delete them during the pre-backup procedure just before you create the new dumps (why bother deleting the dumps unless you need to replace them) but I guess that doesn't really matter one way or the other. Once I finally figured out how to run Midnight Commander I was able to confirm that all of the backups on our NAS did include the MySQL dump files.

We now have our main server ready to go again with new hard disks and a clean install of SME 7.5 with the contribs we use loaded (DAR2 and AWStats, I guess I'll have to load the AutoFSCK rpm we had manually since I can't find anything in the contributions wiki that shows how to use yum to load it directly from the repositories). I assume the thing to do now is shut down MySQL on the backup server so that the database can't get changed, tell DAR2 to do a full disaster recovery backup (which is the only kind we ever set up), and then have DAR2 on the main server do a disaster recovery restore. Once the restore is done I do a reconfigure/reboot on the main server and everything should be restored, am I correct?

[cactus]

A check of the messages file shows that the MySQL table dumps are indeed deleted during the post-backup procedure so that explains the empty home/e-smith/db/mysql folder. It seems like it would make more sense to delete them during the pre-backup procedure just before you create the new dumps (why bother deleting the dumps unless you need to replace them) but I guess that doesn't really matter one way or the other. Once I finally figured out how to run Midnight Commander I was able to confirm that all of the backups on our NAS did include the MySQL dump files.

They are deleted as they are no longer needed at that moment, they should have been included in the backup at that time IIRC, th epost-backup process cleans up after the backup is done. There is no need to keep them and have them lying around as they might be stale files after updates have been made to MySQL tables.

[janet]

wdepot

I just reviewed the Disk Archive panel. My memory was slightly vague as it has been a while since I last used it.
Your backup and restore procedures appear to be OK, except where you say you manually included the /var/lib/mysql folder.

As you now know, the mysql tables are dumped during the pre backup event, and will be restored if you enable the Disaster recovery restore function.
I cannot exactly explain why the mysql tables were not previously restored, perhaps the disaster recovery restore setting was not enabled.

Note you should NOT include the /var/lib/mysql folder in your backup job.

....I assume the thing to do now is shut down MySQL on the backup server so that the database can't get changed, tell DAR2 to do a full disaster recovery backup (which is the only kind we ever set up), and then have DAR2 on the main server do a disaster recovery restore. Once the restore is done I do a reconfigure/reboot on the main server and everything should be restored, am I correct?

You do not need to shutdown mysql, the dump of the db's will workaround the need for manually doing that.
It is important to enable the disaster recovery restore funtion.

[wdepot]

Well, we've managed to successfully perform the transfer of data back to the original server computer. After setting up matching backup jobs on the two computers using the SME web panel for DAR (without the var/lib/mysql, since the dump files were obviously in the backup) I decided it might be safest to do the actual backup and restore from the command line instead of the web panel. It certainly made it easier to tell when the processes were done. After double checking the backup with dar2-mc to make sure the dump files were in place (which they were) I started the dar2-dr-restore on the original server computer. That performed the task of post-upgrade and reboot automatically and I was able to watch during the boot sequence as the dump files were reloaded into MySQL. I sure can't figure out why the dump files didn't get loaded the first time we did the disaster recovery restore unless it had something to do with using the web panel for the restore or else the fact that we were restoring files from a 7.4 server onto a 7.3 server.