How to backup and restore a MySQL database with mysqldump

mysqldump is a very handy utility which can be useful if you work with MySQL databases ,you'll often need to backup your databases and restore them or import them back .

The mysqldump utility produces a set of SQL statements when executed against a database will create the same table structure and table rows or data.

mysqldump is fairly easy to use ,you can use it from the command line with different commands and options to tackle different use cases.

Lets see some practical examples to backup your databases with mysqldump :

Say you have a set of mySql databases ,you can backup them all with one command

mysqldump --all-databases --user=root --password > backup-all-databases.sql 

This will create backup-all-databases.sql file in your current directory which has a dump for all databases .

All the options are self explanatory --user and --password are for specifying mySql username and password .

If you need to backup only one database ,you just need to specify its name

mysqldump --all-databases mydb1 --user=root --password > backup-mydb1.sql 

Restoring and importing database backups



As you can see creating backups for your mySql databases is easy using the mysqldump utility .Restoring and importing backups back is also easy by using mysql utility .

For example ,lets import backup-mydb1.sql

mysql -u root -p < backup-mydb1.sql

This may take a while depending on your database(s) size .

Please note that you can dump either only database tables (structure) ,only data rows or both of them by default .

Make sure to see all available mysqldump commands from this link

Conclusion


You can also use phpmyadmin or other commercial solutions for advanced features when backing up or restoring your databases but still mysqldump can be very useful in most situations .

Note: We also publish our tutorials on Medium and DEV.to. If you prefer reading in these platforms, you can follow us there to get our newest articles.

You can reach the author via Twitter:

About the author

Ahmed Bouchefra
is a web developer with 5+ years of experience and technical author with an engineering degree on software development. You can hire him with a click on the link above or contact him via his LinkedIn account. He authored technical content for the industry-leading websites such as SitePoint, Smashing, DigitalOcean, RealPython, freeCodeCamp, JScrambler, Pusher, and Auth0. He also co-authored various books about modern web development that you can find from Amazon or Leanpub


Get our Learn Angular 8 in 15 Easy Steps ebook in pdf, epub and mobi formats, plus a new Angular 8 tutorial every 3 days.


comments powered by Disqus
DMCA.com Protection Status