Lazy Programmer

Your source for the latest in deep learning, big data, data science, and artificial intelligence. Sign up now

Backing up and restoring databases and tables in MySQL

February 11, 2013

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.

#Backup #Databases #MySQL #Restore