mysql

[rmedidi]

Hi all,

Will anybody guide me in backing up and restoring the databases in mysql of linux.

Plz give me the syntax and from where to issue the command and also inform me whether databases should be closed or opened.

Thnx
XYZ

[Stuart Browne]

Depending on the type of database in use, depends on how valid the resultunt 'dump' of the database is.

Doing a flat-file backup whist the database is live is *OUT*.

You can do a 'mysqldump' to dump the contents of the database, and back that up however.

This is the command I use:

mysqldump -A -c -F -f -q --single-transaction | bzip2 -9 > /path/to/backup.dump.bz2

Options are as follows:

-A : All databases
-a : All create options
-c : Use complete Insert statements
-F : flush logs first
-f : Continue even if there's an error
-q : Don't buffer output
--single-transaction : Go transactions!

If you aren't using a transaction capable database type (InnoDB or some forms of BDB), it'll be silently ignored.

Complete Insert Statements makes no assumptions. It also means it's easier to do recoveries if there are database format changes.. usually..

Depending on the table type depends on the locking this will do. This basically does big SELECT statements to get the data out.

If you want locks, use either '--add-locks' or just '--opt'.

[Steven E. Protter]

Shalom,

In the same directory as the mysql binaries are programs for performing hot and cold backups of the database.

mysql is remarkably durable. I've performed backups with simple OS copies of the db files and had it work on other systems.

SEP

[Sergejs Svitnevs]

Nice exlanation could be found at:
http://gentoo-wiki.com/MySQL/Backup

and

http://dev.mysql.com/doc/refman/5.0/en/backup.html

Regards