The best backup stratergy

[uk_martin]

I would appreciate the advice of users as to what they think in their experience is the best way to back up multiple databases from multiple web hosts sites.

I currently have 3 web sites which have 1, 3 and 5 databases on each, respctively. There are a number of these databases which are for Message Boards (usually phpB:cool: so it is important that the backup be as up to date as possible.

I would really like to schedule / automate the task to happen each night, with either an SQL / CSV file being saved to a set location on my machine, or for the database to be synchronised with a “shadow” database on my local server.

There seem to be a number of options, e.g. saving a database as an SQL file, as Batch Scripts, or synchronising with other databases.

I currently use SQLYog 3.71 on an XP machine, with MySQL installed on a local Win2003 Server. All of my web sites are Linux based.

What are the pro's and con's of each way of doing things, and what is the best way to set things up?

[Shadow]

If we stick to SQLyog, then the “Database Syncronizaion Wizard” is the righttool for you. SQLyog uses a separete executable (sja.exe) to perform these tasks. You can determine from which db to which db do you want to perform the sync, you may add criteria to restric unneeded data flow, and sync sessions can be scheduled via Win's built-in scheduler.

Its greates advantage is, that you have a “live” copy of the data. A cach is that the target database must conform to the source one (at least the synced tables must match).

SQLyog does not offer any more backup techniques that could be scheduled. Of course, you may use Win's scheduler to launch for example mysqldump in a predetermined time.

If you use CSV as a backup format, then you'll have to store the SQL statements responsible for creting the tables elsewhere, thus reducing the required storage place, but complicating the db maintenance (just imagine that you change the structure of your db, but forget to commit these changes to the SQL scripts in the backup). SQL format, however, uses complete SQL statements that create the tables and insert data. MySqlAdministrator offers an interface to schedule such backups.

[Author]

Posts