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.