Backing Up and Restoring MySQL Databases
If you've used MySQL for any length of time you've run into situations where you need to back up and restore databases. If you haven't thought about backups, now is the time to do it! You should regularly back up your important data to removable media (CD-ROM, Zip drive, floppy, etc.) so if your computer crashes you can get your data back. MySQL backup is no different. You can actually back up your data quite easily using MySQL. If you want to be really raw about it you can simply copy off all the files in /var/lib/mysql. If you check that directory it will show you all the files your MySQL database is using. This method is clumsy at best, however, and restoring data is a real problem if you have to rebuild a database. Your best option is actually mysqldump. This quick program will allow you to backup and restore your databases with ease and confidence.
MysQLDump
MySQLDump is (currently) outlined at http://www.mysql.com/doc/en/mysqldump.html on the MySQL official website. In a nutshell to back up a database you're going to have to issue the following at a command prompt:
# mysqldump -u root -p -A
This is sort of a complex command so let me explain. The first parts after the mysqldump command will log into the MySQL server as user root (-u flag) and ask you for a password (-p flag). The -A flag is used to back up all the databases. If you only want to back up a single database you can use '-database nameOfDatabase' where nameOfDatabase' is the name of the database you want to back up. Once this command completes you will have a .sql file that is your actual backup file. If you want to specify the name of this file you might want to try using:
# mysqldump -u root -p -A > mybackup.sql
where 'mybackup.sql' is the name and extension of the backup file. You'll notice the first time you do this that the backup file is really big (depending on the size of your database). You'll probably want to zip this file. You can actually zip the file right after it gets backed up and name the output by using:
# mysqldump -u root -p -A | gzip -c > mybackup.sql.gz
where 'mybackup.sql.gz' is the filename and extensions of the backup. You're basically doing a backup of all the databases with this command, piping the output to the gzip program with the '|' or pipe symbol, than redirecting the output to a file called 'mybackup.sql.gz' using the '>' or redirect symbol. Once you've made your backup be sure to store it in a safe place. On the computer with the database is not a safe place. If the computer crashes you'll have no way to get your backup back.
Restoring a Database
If you have to rebuild your database from scratch and/or you don't have existing databases, you can restore a mysqldump backup file quite easily. Simply use:
# mysql -u root -p databaseName < databaseName.sql
This will prompt you for a password and then pass the backup file 'databaseName.sql' back into MySQL and create the database 'databaseName' with the restored material. If you have to resore and existing database from your backup you'll need to use MySQLImport.
MySQLImport
You can use MySQLimport for a finer grain of control in your backup restoration. However, with MySQL dump files (which are actually just lists of SQL commands to create and restore a database) it is much easier to use the mysql client. For instance you can restore a database from a mysqldump file using:
# mysql -u root -p newdbname < /path/to/dump.sql
mysqlimport allows for some flags to control whether or not you want to overwrite existing data, only append new data, etc. For instance, to restore the database from a backup only appending new data from the backup but leaving the duplicate existing data intact you can use:
# mysqlimport -u root -p --ignore dbname < /path/to/dump.sql
This will leave records with duplicate unique ID's or data intact.
Conclusion
If you still need more help check out the MySQL Documentation online. There are lots of references and code samples to help you along your way.