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.