Friday, January 05, 2007

Backup of DataBase from MYSQL

Every database admin job is to take DB backup time to time.
there is two method's i am using..
Method 1st :
mysqldump client is a backup program used to dump a database or a collection of databases for backup or transfer to another SQL server. The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

The most common use of mysqldump is probably for making a backup of an entire database:
shell> mysqldump db_name > backup-file.sql
You can read the dump file back into the server like this:
shell> mysql db_name < backup-file.sql

Or like this:
shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:
shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:
shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option:
shell> mysqldump --all-databases > all_databases.sql
more u can read

Method 2nd:
find out where ur mysql sotre's the database generally default path will be:
:var/lib/mysql this is datadir=var/lib/mysql which is mention in mysql configuration file.

All the DB's u willfind on mysql dir, so u can copy the mysql dir.
this kind of backup used when u want to uninstall and then again install with different version.
simply move to other location mysql dir, and then copy that.

No comments:

SHOW ENGINE INNODB STATUS

  The SHOW ENGINE INNODB STATUS command in MySQL provides detailed information about the internal state of the InnoDB storage engine. This ...