To export a MySQL database to a text file with SQL that can easily be inserted into another MySQL database, simply run:
mysqldump -uUsername -pPassword dbName > outPutFilename.sql
In the example above, Username is your MySQL user, Password is your MySQL password, dbName is the name of the database you want to back up and outPutFilename.sql is what the resulting SQL file will be called.
To recreate the database on another host (or after a new install) simply do:
mysql -uUser -pPass dbName < backup.sql
You have to create the dbName database (just an empty database -
mysqladmin -p create dbName) before you run the above command.
I have a script that I run nightly using cron to back up a database. Copy and paste, edit as needed and save. Make it executable and add it to cron if you want nightly backups.
Code:
#!/bin/bash
dbuser=username
dbpass=password
dbname=dbname
output=/home/hw/backups/
datum=`date +%Y%m%d`
mysqldump -u"$dbuser" -p"$dbpass" $dbname > "$output"/"$dbname"_"$datum".sql
bzip2 "$output"/"$dbname"_"$datum".sql
rm -f "$output"/"$dbname"_"$datum".sql
The script will create backups in the /home/hw/backups dir and compress them (a *lot*) using bzip2. The filenames will be dbname_20031013.sql.bz2 if the date is Oct 13th and the database name is "dbname".
Hope that helps a bit.
