When I upgrade MySQL I like to start with a backup of all my MySQL databases. I have 50+ databases in my localhost server, so it’s best to bulk export them all to a single .sql file with one command. I need this export to be easy to import back into my MySQL server as well. Here’s the best way I’ve found to handle this as of 2020 using MySQL 8, which isn’t all that well documented around on the web.

Export All Databases

To export all databases on my MySQL 8 localhost server I use the following command, which exports with mysqlpump and pipes the output to gzip for compression. MySQL 5.7+ is needed to work with the mysqlpump command, if you’re using an earlier version then you can work with mysqldump instead.

mysqlpump --add-drop-database --user=root --password=root --all-databases --watch-progress | gzip > $(date '+%m%d%Y').sql.gz;

Where root is both the username and password for accessing your MySQL server. mysqldump is commonly what I see used for this, but mysqlpump is more suited towards transferring and restoring databases when a problem occurs or a transfer to another server is being performed. I typically pipe the output to gzip, which drastically reduces the size of the export. With gzip a 1GB .sql export can be compressed down to less than 100MB.

Please Note: I don’t recommend that you ever include the password in the command like this, it’s a bad practice and poses security risks.

Command Options

Here’s a description of the options I’m using with mysqlpump, a full list of available options can be found in the official documentation.

  • --all-databases — Dump all tables in all databases
  • --watch-progress — Display progress indicator
  • --user — MySQL server user
  • --password — MySQL server password

If you’re using MySQL 8+ the --all-databases option won’t include stored routines and events. To include stored routines and events in a dump made using --all-databases you’ll need to add the --routines and --events options explicitly.

Output

If all goes well, you should see output like this on your console:

Dump progress: 227/560 tables, 106396/1731491 rows
Dump progress: 235/560 tables, 171754/1731491 rows
Dump progress: 254/560 tables, 236935/1731491 rows
Dump progress: 287/560 tables, 261836/1731491 rows
Dump progress: 288/560 tables, 268336/1731491 rows
Dump progress: 303/560 tables, 379720/1731491 rows
Dump progress: 305/560 tables, 494136/1731491 rows
Dump progress: 320/560 tables, 652061/1731491 rows
Dump progress: 335/560 tables, 766606/1731491 rows
Dump progress: 388/560 tables, 834979/1731491 rows
Dump progress: 443/560 tables, 862733/1731491 rows
Dump progress: 493/560 tables, 1031784/1731491 rows
Dump progress: 545/560 tables, 1290276/1731491 rows
Dump progress: 547/560 tables, 1434826/1731491 rows
Dump completed in 19093

Import All Databases

To import or restore all databases in the export you can use the following commands to unzip, import, and re-zip the original export.

gunzip 01172020.sql.gz;
mysql --user=root --password=root --verbose < 01172020.sql;
gzip 01172020.sql;

Where 01152020.sql.gz is the name of the export file you want to import.

Command Options

The options I’m using with mysql to handle this import are:

  • --verbose — Display verbose output while running, serves as a progress indicator
  • --user — MySQL server user
  • --password — MySQL server password

Conclusion

If you’re looking for a way to export all MySQL databases on a server, along with a way to import from that export if and when you need it, hopefully, this provides a solid example. As with any SQL or data related process:

Under no circumstances should you blindly use the snippets and approaches mentioned in this article. SQL data and the scripts provided are highly variable, you must understand what is being done in order to work with this approach. Do so at your own risk, I can’t be held liable for any damages.

About the Author

Kevin Leary is a freelance developer in Boston, MA with a strong portfolio of work, and over 13 years of experience as a programming consultant and digital strategy provider. Interested in working together? Hire me for your next project.