MySQL Dump from Remote Server

A quick tip on setting up remote MySQL dumps.

NOTE: This is not a tip for security, but a short example on remote connections with mysqldump. Ideally this should be done by a scheduled task and dump the output to a file store (e.g. S3) never needing to be exposed (no remote access).

First we'll need to add a new user to MySQL to allow remote access

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'my_strong_password';

Now that we have our new remote user, let's give them the necessary privileges to perform the mysqldump (these are the bare minimum privileges, you may need additional ones if your database/table have triggers or views). Feel free to specify the database to limit exposure (e.g. my_cool_db.* instead of *.*)

GRANT SELECT, PROCESS ON *.* TO 'remote_user'@'%';

Now from your host machine you should be able to perform the mysqldump with the following command:

mysqldump -u remote_user -h 192.168.2.1 -p --single-transaction --column-statistics=0 my_cool_db > backup_my_cool_db_$(date +"%Y_%m_%d").sql

You'll be prompted for your password, enter that now (from example it'll be my_strong_password) press return and it'll start to process.

Replace the value for host 192.168.2.1 with your servers IP address, the db name my_cool_db to your own, and the file name to what you desire.

Quick note on --column-statistics=0 is only needed if you're dumping from a MySQL version lower than 8.

The command above will output the dump to backup_my_cool_db_2021_04_11.sql (date will be current date).

That's it! Now you have a user with remote access which you can use to generate backups for your database! :)