How to use MySQL dump from a remote machine

How can I backup a mysql database which is running on a remote server, I need to store the back up file in the local pc.

Try it with Mysqldump

#mysqldump --host=the.remotedatabase.com -u yourusername -p yourdatabasename > /User/backups/adump.sql

Have you got access to SSH?

You can use this command in shell to backup an entire database:

mysqldump -u [username] -p[password] [databasename] > [filename.sql]

This is actually one command followed by the > operator, which says, "take the output of the previous command and store it in this file."

Note: The lack of a space between -p and the mysql password is not a typo. However, if you leave the -p flag present, but the actual password blank then you will be prompted for your password. Sometimes this is recommended to keep passwords out of your bash history.

No one mentions anything about the --single-transaction option. People should use it by default for InnoDB tables to ensure data consistency. In this case:

mysqldump --single-transaction -h [remoteserver.com] -u [username] -p [password] [yourdatabase] > [dump_file.sql]

This makes sure the dump is run in a single transaction that's isolated from the others, preventing backup of a partial transaction.

For instance, consider you have a game server where people can purchase gears with their account credits. There are essentially 2 operations against the database:

1. Deduct the amount from their credits
2. Add the gear to their arsenal

Now if the dump happens in between these operations, the next time you restore the backup would result in the user losing the purchased item, because the second operation isn't dumped in the SQL dump file.

While it's just an option, there are basically not much of a reason why you don't use this option with mysqldump.

This topic shows up on the first page of my google result, so here's a little useful tip for new comers.

You could also dump the sql and gzip it in one line:

mysqldump -u [username] -p[password] [database_name] | gzip > [filename.sql.gz]

mysqldump -h [domain name/ip] -u [username] -p[password] [databasename] > [filename.sql]

Tried all the combinations here, but this worked for me:

mysqldump -u root -p --default-character-set=utf8mb4 [DATABASE TO BE COPIED NAME] > [NEW DATABASE NAME]

If you haven't install mysql_client yet and using Docker container instead:

sudo docker exec MySQL_CONTAINER_NAME /usr/bin/mysqldump --host=192.168.1.1 -u username --password=password db_name > dump.sql

You can directly pipe it to the remote server where you wish to copy your data to:

mysqldump -u your_db_user_name -p --set-gtid-purged=OFF --triggers --routines --events --compress --skip-lock-tables --verbose your_local_sql_db_name | mysql -u your_db_user_name -p -h your_remote_server_ip your_remote_server_db_name

You need to have created the db on your remote sql server.

Using the above command, I was able to copy from my local sql server version 8.0.23 to my remote sqlserver running 8.0.25

This is how you would restore a backup after you successfully backup your .sql file

mysql -u [username] [databasename]

And choose your sql file with this command:

source MY-BACKED-UP-DATABASE-FILE.sql