How to use MySQL dump from a remote machine

Question

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

User · Answer

Tried all the combinations here  but this worked for me    mysqldump -u root -p --default-character-set utf8mb4  DATABASE TO BE COPIED NAME   gt   NEW DATABASE NAME

User · Answer

Try it with Mysqldump   mysqldump --host the remotedatabase com -u yourusername -p yourdatabasename  gt   User backups adump sql

User · Answer

mysqldump -h  domain name ip  -u  username  -p password   databasename   gt   filename sql

User · Answer

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  gt  dump sql

User · Answer

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

User · Answer

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   gt   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    Deduct the amount from their credits 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

User · Answer

Have you got access to SSH   You can use this command in shell to backup an entire database   mysqldump -u  username  -p password   databasename   gt   filename sql    This is actually one command followed by the  gt  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

User · Answer

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  gt   filename sql gz

[mysql] How to use MySQL dump from a remote machine

Examples related to mysql

Examples related to mysqldump