topic Re: mysql in Operating System - Linux

mysql

rmedidi — Thu, 15 Jun 2006 02:24:13 GMT

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

Re: mysql

Stuart Browne — Thu, 15 Jun 2006 05:02:45 GMT

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'.

Re: mysql

Steven E. Protter — Thu, 15 Jun 2006 06:51:29 GMT

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

Re: mysql

Sergejs Svitnevs — Thu, 15 Jun 2006 07:07:15 GMT

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