MySQL backup and restore

This is more to serve as a reminder for myself on how to backup and restore databases using the command line.

1. Backing up a database:

Where username is of course your username and dbname is the database that you are trying to backup. Userpass is optional - if you don't enter it on the command line, you will be prompted for it:

mysqldump -u username -p[userpass] dbname > filename.sql

or you can get fancier, if you need to specify the host or port:

mysqldump --user=username --password=password --port=port --host=hostname dbname > backupfile.sql

2 Backing up with compression

mysqldump -u username -p[userpass] dbname | gzip > filename.sql.gz

3. Restoring a database:

mysql -u username -p[root_password] database_name < filename.sql

4. Backing up all databases

mysqldump -u root -p[userpass] --all-databases > alldatabases.sql

5. Restoring all databases

Same as restoring a single one, except you don't specify a database:

mysql -u root -p[userpass] < alldatabases.sql