Added on Feb 1st, 2015 and marked as backup database mysql

Of course you make backups of your MySQL-database, don’t you? I sure do, but not by hand anymore. The result is I’ve totally forgotten how to create a backup manually and seem to be unable to memorize it again. Any time I need to do it, I need to search for it on the internet. Which is the reason for this page, so I can search for it when I need it again.

tl;dr

mysqldump -uroot -p database_name > db-export.sql
mysql -u root -p database_name < db-export.sql

Create a database backup

Occasionaly I need to make some drastic changes to a database to fix some messed up stuff (yeah, I’m looking at you WordPress). When you like to live dangerously, you’re gonna make those changes in the live database and thus a recent backup is essential in case you botch up.

Enter the accurately named mysqldump:

mysqldump -uroot -p database_name > db-export-one.sql

Only the database with the name database_name will be included in the export file.

To export multiple databases at once:

mysqldump -uroot -p --databases database1 database2 > db-export-multiple.sql

You can specify as many databases as needed, just separate them with a space.

To export all databases:

mysqldump -uroot -p --all-databases > db-export-all.sql

If you need to export only a specific table:

mysqldump -uroot -p database_name table_name > db-table-export.sql

Restore a database backup

Just kidding. You never make changes to a live database, do you? No, instead you make a backup, import it to a development server and make your changes. Then you reverse the process, dump the changed database and import it to the live server.

To import a SQL-file you can use the regular mysql command:

mysql -u root -p database_name < db-export.sql

Note: if you used mysqldump to export the file, it will contain DROP TABLE commands. Tables that are both present in database_name and the SQL-file will be removed and recreated.