Tips and Tricks - MYSQL - how to dump a database

So you want to backup your mysql database??? Mysql has many ways of doing it, i will explain the easier one: mysqldump.


Being strict, msqldump is not a "backup utility", it creates a script with  "CREATE TABLE" and "INSERT INTO" commands that you can run in your server to re-create your database.  mysqldump  is shipped with MYSQL so everybody should have it installed.

Using mysqldump command line is very straightforward, just type this in your command line or terminal (using your username and password):

mysqldump -u USERNAME -pPASSWORD OUR_DATABASE > filename.sql

This will give us a file (filename.sql) containing "CREATE TABLE" and "INSERT INTO" commands for OUR_DATABASE.
Some times you need to dump ALL your databases (you are moving you dev machine) in your MYSQL server:

mysqldump -u USERNAME -pPASSWORD --all-databases > filename.sql

If you are using MAMP (in macOS), your mysqldump binary is located in /Applications/MAMP/Library/bin/mysqldump.


There is a lot more options here in the official MYSQL documentation: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html


To restore our database, we have to we have to run this script in our MYSQL server. You can do it using any utility (phpmyadmin for instance) or jus (again) the command line:

mysql -u USERNAME -pPASSWORD DATABASE_NAME < filename.sql

So, good backup!! ;-)