Backing up and restoring databases and tables in MySQL

My extreme laziness combined with my terrible memory means that I have to look up the backup/restore commands for MySQL almost every time I do it. For reference, here it is.

How to backup an entire database:

mysqldump -u<user> -p<password> -h<host> -P<port> <database name> > name_of_your_backup.sql

(Don’t forget the > sign)

If your database is huge, like mine currently is, you could pipe it into a gzipped (compressed) file like this.

mysqldump -u<user> -p<password> -h<host> -P<port> <database name> | gzip -9 > name_of_your_backup.sql.gz

How to restore an entire database:

mysql -u... < name_of_your_backup.sql

Or if you’ve gzipped your backup:

gunzip -c name_of_your_backup.sql.gz | mysql -u...

How to backup a single table:

mysqldump -u<user> -p<password> -h<host> -P<port> <database name> <my_table> > my_table.sql

And restore a single table:

mysql -u... < my_table.sql

In summary:

To backup, use mysqldump and the greater-than symbol to write your output to a file.

To restore, use mysql and the less-than symbol.

If you’re backing up an entire database, specify the database name at the end of the mysql command. If you’re backing up a table, specify the database name and the table name.

If you have a large database, use gzip.