Backing up MySQL database

[r042wal]

I am new to MySQL. I have an application using MySQL running on Windows Server 2000. If I were using Microsoft SQL Server 2000, I would need special software to back up an open SQL Server database. Does this same problem exist with My SQL?

In otherwords, could I take the native NTbackup.exe that comes with Server 2000 and back up an open MySQL database without stopping the engine first?

Thanks.

 

[TonyGroves]

Simply backing-up the database files is unreliable, as MySQL uses caching to improve performance. Even if you flush the cache, subsequent updates could leave the files in an inconsistent state. There are a few other options:
(1) Use "mysqlhotcopy" to copy the table files, though I don't know if this works under Windows.
(2) Use "mysqldump" to dump the tables to text files which contain the SQL commands necessary to recreate the tables. This is an excellent method.

 

[r042wal]

I need to add this server's database to my nightly backup. If it was SQL Server, I could make a snapshot of the open database and back it up to tape automatically.

What I am asking is, if the database is added to the backup job using NTbackup, it it getting backed up?

If it is not getting backed up, how can I back up the database each night automatically. Do I need Veritas BackupExec which is what is usually used for SQL Server?

Thanks

 

[TonyGroves]

You don't need any special backup software. You just need to be sure that you are backing-up database files which are in a consistent state. One way to do that is to stop the MySQL server and include the database files (probably in c:\mysql\data) in your backup command. If you don't want to stop the server, then you would need to follow one of the procedures I mentioned above, and make sure those files are backed-up instead of the live database files.

 

[likelylad]

If you have MySQL Administrator installed on the server, you could schedule a backup from it.

If you have any issues you can restore directly from these files.

Not ideal but another option