MySQL Backup

[Anon]

Hi people, I was wondering what could be the best way to back up a MySQL database: using a crontab and making a daily dump? Would the database be available during such dump?

Or perhaps just copying the binary files from the mysql dir onto a tape drive weekly?

I'm particularly concerned in performance/downtime issues, thanks.

--
Antonio

[Anon]

http://www.mysql.com/documentation/mysql/bychapter/manual_Common_problems.html#Backup

[Anon]

We automate out backups by a script running, taring the files up, then zipiing the tar files and then storing them on a different harddisk.

They can also be sent off site via ftp then
When I am doin fit by hand I use :-

mysqldump -u root -p database_name > output_file_name

That will prompt you for your password and export the database into a text file that you can use to recreate the database on your new server as follows:

mysql -u root -p database_name < input_file_name (which was previously output_file_name)

You can also read the file and edit if needed

Darren

[Anon]

You can do mysqldump one time then restart musql with update_log enabled. then back up the update log instead of the database.

You can use the dump and the update log to build a mirror database on another server or other hard drive.

[Anon]

Pertaining to this question I was wondering does anyone know what the actual file names are? I just had linux crash so I have to access the hard drive from another machine.

[Anon]

The databases will be stored in

/path.to.mysql/var

copy that entire directory and you will have you databases.

For interest sake, under the var directory, each database will have directory whose name matches the database name. in that directory, for each table in the database, there will be a .ISM, .ISD, and .frm file, each with the same name as the table.

brad.

ps- a aword of warning to those who just tar your var directory to backup your mysql db's. This doesn't always work. I tried moving from machine to another, and the databses would not be recognized. Granted, one machine was Mac, the other Intel, both had different flavors of BSD, and slightly different version of MySQL. I didn't experiment any further, because it was enough for me to go back to mysqldumps (which will work regardless of platform or OS), but it's something to keep in mind.

[Anon]

How can I verify the integrity and correctness of the backup file (output_file) ?