MySQL Database Backup

[b4u]

Hi,

I'm in the process of designing a web application, which uses common JSP plus storage in MySQL. Now, I'm no expert in MySQL itself, so I prefer to ask to decide the best way of doing what I want ...


Part Number 1:

I will define a Catalog, or Database if you like, where tables and data will reside that's great, but I need to worry about something else ... backup.

Which is the safest and correct way for backup the database? I mean, let's say I've installed MySQL on "C:\Program Files\MySQL". I created a Database called "testing", and looking at my disk, I can see he has created "C:\Program Files\MySQL\data\testing" with a file named "onetable.frm".

So what I can say from this is: He created sub-directories inside "data" for each Database, and each table will reside in a separate file, with .frm extension. Am I correct so far?

So if I just copy the directory inside of "data", like for example "testing" directory, with all contents, I'll just get a nice clean backup of my entire database and data. Correct?

My confusion is that because I'm used to work with SQL Server 2000, and there, once I create a Database, I'm "forced" to do a backup of the "master" database to be safe ... because there resides information about each and every database defined in SQL Server 2000.

So what about in MySQL? Do I need to do something more than copy the directory to somewhere else?


Part Number 2:

What is the best way of doing a regular auto-backup of a database? Let's imagine the database resides in a Windows 2003 Server Standard edition.

I was thinking about doing a script that would call the command to stop MySQL service, than it would do a copy of the data to a DVD, and after that it would turn the MySQL service up again ... but I believe there must be many other simple and trusty ways of doing this ...


Part Number 3:

Working in a Windows XP computer with a CD-RW (and RW media in it), I can do a copy-paste of files to the CD, and windows will automatically burn them to the media.

Will it work ok in Windows 2003, and in a way that I could do a "copy" in command prompt and achive simmilar results, getting a multisession CD? (Each copy making up for a session)

At the moment, I have no way of testing it, and I was looking for a way of backing up data from the server into a DVD.


Thank you very much.

 

[Armitage]

Originally posted by: b4u
Hi,

I'm in the process of designing a web application, which uses common JSP plus storage in MySQL. Now, I'm no expert in MySQL itself, so I prefer to ask to decide the best way of doing what I want ...


Part Number 1:

I will define a Catalog, or Database if you like, where tables and data will reside that's great, but I need to worry about something else ... backup.

Which is the safest and correct way for backup the database? I mean, let's say I've installed MySQL on "C:\Program Files\MySQL". I created a Database called "testing", and looking at my disk, I can see he has created "C:\Program Files\MySQL\data\testing" with a file named "onetable.frm".

So what I can say from this is: He created sub-directories inside "data" for each Database, and each table will reside in a separate file, with .frm extension. Am I correct so far?

So if I just copy the directory inside of "data", like for example "testing" directory, with all contents, I'll just get a nice clean backup of my entire database and data. Correct?

My confusion is that because I'm used to work with SQL Server 2000, and there, once I create a Database, I'm "forced" to do a backup of the "master" database to be safe ... because there resides information about each and every database defined in SQL Server 2000.

So what about in MySQL? Do I need to do something more than copy the directory to somewhere else?

There are several ways to do it. Copying the data directories as you describe as you describe works fine as long as you shutdown MySQL or lock & flush the tables before you do it. There's a nice perl script called mysqlhotcopy that ships with mysql. It takes care of all the lock & flush details for you on a table by table basis (so the entire DB isn't locked through the whole process), although it looks like you're doing windows and I think the mysqlhotcopy script only does Unix.

One point to remember ... this is a binary copy. If you think you may have to restore to a different architecture you may be SOL. Another downside is that your db is locked to writes during the process, which may take awhile for big databases.

Another way to do it is with the mysqldump utility. It doesn't need any table locking, but if your data may not be in an entirely consistent state ... ie. if you have something like a transaction going on mysqldump may only capture half of it. This dumps out to ascii, so is good for moving across architectures, but its slow, and big files. Although you can pipe the output straight into gzip to help with file size.

One of the cleanest ways is to set up replication. Take the slave offline (updates queue up on the master) and back it up by either of the methods described above. Then turn the slave back on, and let it sync back up to the master. No downtime, and a consistent backup.

Part Number 2:

What is the best way of doing a regular auto-backup of a database? Let's imagine the database resides in a Windows 2003 Server Standard edition.

I was thinking about doing a script that would call the command to stop MySQL service, than it would do a copy of the data to a DVD, and after that it would turn the MySQL service up again ... but I believe there must be many other simple and trusty ways of doing this ...

Personally, I would do nightly backups to a network disk, or even a seperate disk on the same machine, and only backup to removable media maybe once a week.

Part Number 3:

Working in a Windows XP computer with a CD-RW (and RW media in it), I can do a copy-paste of files to the CD, and windows will automatically burn them to the media.

Will it work ok in Windows 2003, and in a way that I could do a "copy" in command prompt and achive simmilar results, getting a multisession CD? (Each copy making up for a session)

At the moment, I have no way of testing it, and I was looking for a way of backing up data from the server into a DVD.


Thank you very much.

Sorry ... no habla microsoft.