Sun, Apr 11, 2021 11:18 AM
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! :)