Backup of a single database
Backup a single database to a plain text file, containing the sql commands to restore the tables and their data
mysqldump -u [user] -p [database_name] > [filename].sql
Backup a single database to a gzipped version of the sql file
mysqldump -u [user] -p [database_name] | gzip > [file_name].sql.gz
Backup a single database to a bz2 compressed sql file.
mysqldump -u [user] -p [database_name] | bzip2 > [file_name].sql.bz2
You can usually upload gz, and bz2 files directly to a database using PHPMyAdmin, so is a good idea to have the outpud compressed.
Backup more than one database
You can backup more than one database at the same time.
mysqldump -u [user] -p –databases [database_name_1] [database_name_2] [database_name_n] > [filename].sql
You can use the same options as with the single database to compress the mysqldump command output.
Backup of all databases
mysqldump -u [user] -p –all-databases > [file_name].sql
This will dump all databases to a single file, and you can use that file to restore all databases at once. This is very useful to move your databases from one server to another.
Using the dump file, it is possible to restore the database with all its tables to a new MySQL server.
Create the database
mysql -u [user] -p
The [user]field in this case will usually be root. At the mysql> root.
create database [database_name];
Create a user for that database, it is actually not needed, but it is a good security measure.
grant all privileges on [database_name].* to [new_user]@[hostname] identified by [new_user_password]; exit;
Once again on the Linux command line prompt.
Restore database dump file
mysql -u [new_user] -p [database_name] < [file_name].sql
If the file was compressed, uncompressed it first.
gunzip [file_name].sql.gz bunzip2 [file_name].sql.bz2