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.