mysqldump - i often use these to copy tables from one server to another since i find the phpmyadmin dump to be very slow and sometimes i get a page timeout. aside from copying a database to another you can also use it to backup your database or all of your databases. a mysql dump file contains sql statements to create databases, populate tables, or populate rows. Most of the time i saved the dump file as a .sql but you could also specify to save it as a .csv or perhaps .xml etc.
When restoring it i use the command \. on the mysql command prompt, i find it much easier that way than typing it on the shell.
i keep on forgetting the syntax to these commands so i better jot them down. These are syntax from the manual and is the most common syntax used in creating a mysql database dump.
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases
example in backing up the mysql database:
shell> mysqldump db_name > [backup-file.sql]
dump all databases:
shell> mysqldump --all-databases > all_databases.sql
and restoring it back:
shell> mysql db_name < backup-file.sql
this is what I use:
mysqldump -u [user] -p[pass] [dbname] > [filename.sql]
add it in cron:
#!/bin/sh
date=`date -I`
mysqldump --opt --all-databases | bzip2 -c > /var/backup/backup-$date.sql.bz2
