mysqldump

mysqldump -u root -pSECRET –quote-names –databases  mydatabasename > mydatabasename.sql

If find it best to include the –databases switch as this will add the ‘DROP DATABASE’ and ‘CREATE DATABASE’ statements when restoring – proceed carefully.
Quote Names:
Quoting the file names can be an insurance against users who may have created field names with spaces in them 🙁

mysqldump -u root -pSECRET –quote-names –databases mydatabasename > mydatabasename.sql

Dump All Databases:

mysqldump -u root -pSECRET –quote-names –all-databases > fullbackup.sql

Zip the ouput file:

nice mysqldump -u root -pSECRET –quote-names –databases dbname1 dbname2 dbname3 | nice gzip -9 > mybackup.sql.gz

Compatibility:

mysqldump -u root -p  –compatible=mysql40 –databases databasename > mybackup.sql

Other compatibility options include:

ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options

Dump structure-only:

mysqldump -u root -p –no-data –databases dbname1 dbname2 > mybackup.sql

Backup to another MySQL Server

mysqldump -u root -p –host=host1 mydatabase | mysql -u root -p –host=host2 -C newdatabase

The C option specifies to use compression. Note databases must exist already on the target server.

Restore

mysql -u root -p dbname < mybackup.sql

Character Sets / Collation Problems
Convert the database exporting first like this:

mysqldump -u username -p –default-character-set=latin1 –compatible=mysql40 dbname > dump.sql

Then import overwriting the current database (another backup would be wise):

mysql -u username -p –default-character-set=utf8 dbname < dump.sql

MySQL Adding Users
In case you need to access the MySQL database remotely (as in the above example to backup to another server):

mysql -u root -p
grant all privileges on *.* to ‘jonny’@’%’ identified by ‘thepassword’;
flush privileges;
exit