PrintLogo

Backing Up All MySQL Databases




To back up all databases on a particular server, use the mysqldump command: 

$ mysqldump --all-databases -p | bzip2 -c > databasebackup.sql.bz2
Enter password:
$

This will put all of the databases in a compressed bzip2 file. Transfer the file to your other server and decompress: 

$ bzip2 -d databasebackup.sql.bz2
$ ls data*.sql
databasebackup.sql
$ head -n 7 databasebackup.sql
-- MySQL dump 9.11
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       4.0.22-standard
--
$

Restore your backup using the mysql command: 

$ mysql -p < databasebackup.sql
Enter password:
$

Do be doubly sure that you aren't restoring over something that exists already. This command is ideal for rebuilding a server, but is risky if there are already MySQL databases and tables on the server.



This article comes from NetAdminTools:
http://www.netadmintools.com/

The URL for this story is:
http://www.netadmintools.com/art408.html

Copyright 1997-2008 NetAdminTools.com. Read our Terms of Use.