Backups: Recommended Appproach?

[Linda]

As someone with very little knowledge of mySQL, I am wondering what the best approaches are to backing up a database? Should I be using the 'export as SQL statements' option or the 'backup database option'? Is either method 'safer' than the other?

And are there any difference in speed? I tried the 'export' option on a fairly large forum, and after over half an hour it had not yet finished, which was awkward as it was keeping people from posting while the backup was in progress.

[peterlaursen]

They are equally safe and equally efficient. Actually they use the same code. The backup 'powertols' has a few more options than the 'export' tool.

1: you can deselect utf8 encoding. needed if you backup from MySQL 4.1 or higher and need to import to MySQL 4.0 or lower

2: it can be scheduled

.. and probably some more I just do not remember right now.

Regarding the lock situation:

1) What is table TYPE/ENGINE (MyIsam or InnoD:cool: ?

2) Please attach a screendump of your settings in the export dialogue.

[Linda]

Thanks for the info. 🙂

The tables for the board I was trying to backup are all MyISAM. A screenshot of the settings have been attached. I think I just left it at the default.

Edited to add: Doh, of course, I imagine that's what the 'Lock all tables for read' option does. But would it be safe to have that unchecked?

[Linda]

Would you recommend sticking with the default settings (which I believe are the same as are shown in my screencap), or should I select or deselect any other options? 🙂

[peterlaursen]

Please read this FAQ:

http://webyog.com/faq/24_121_en.html

the problem is the a backup takes time. So if the database is open for writing while backup is done it is impossible to tell if if data are backed up as they were when backup job started or ended. For some systems this can be critical. For a Forum I do not think it will be important!

This is basically a problem of crating a 'consistent snapshot'. Recommended settings depend on the storage engine (MyISAM/InnoD:cool:. What do you use?

[Linda]

Thank you for explaining. I thought it might make generate some errors in the backup if the database was being written to during the backup, but if all that happens is that you miss out any changes after you start the process, then that isn't an issue.

The storage engine appears to be MyISAM.

[Author]

Posts