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.

CloudyMarble · Accepted Answer · 2011-12-09T10:32:48.347

86

Try it with Mysqldump

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

edited Dec 09 '11 at 10:32

answered Dec 09 '11 at 10:27

CloudyMarble

36,908
70
97
130

1

To use this command we should enable the access from remote on the mysql server? – Gspoon Jun 18 '20 at 09:36
@Gspoon Yes, it connects from the computer it runs on to the server, just like any other client. – Alejandro Sep 04 '20 at 15:18
1

If you get Unknown table 'COLUMN_STATISTICS error you should take a look at https://serverfault.com/questions/912162/mysqldump-throws-unknown-table-column-statistics-in-information-schema-1109 answer – naveen.panwar Dec 16 '20 at 16:04

Ian Jamieson · Answer 2 · 2015-03-12T16:27:38.827

57

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.

edited Mar 12 '15 at 16:27

answered Dec 09 '11 at 10:28

Ian Jamieson

4,376
2
35
55

10

man mysql: If you use the short option form (-p), you **cannot** have a **space** between the option and the password. – Tanky Woo Jan 09 '14 at 11:49
3

Good one explaining the "no space" between -p and the actual password – okey_on Mar 15 '15 at 15:50
1

if you use SSH you should mention SCP or SFTP usage to store the dump file in local PC as asked in the question – izzulmakin Feb 28 '18 at 01:52
You should make sure that the firewall setting have the 3306 port open for TCP. – Gspoon Jun 18 '20 at 09:47

score 44 · Answer 3 · answered Feb 16 '14 at 05:36

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:

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.

Excellent! Thanks man. I had mysql access to remote server, but couldn't dump DBs. The error was `mysqldump: Got error: 1044: "Access denied for user 'USER'@'%' to database 'DB_NAME'" when using LOCK TABLES` , but using `--single-transaction` option in `mysqldump` solved my problem. — IAmAliYousefi, Mar 05 '19 at 14:39

score 20 · Answer 4 · answered May 03 '14 at 23:37

20

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]

answered May 03 '14 at 23:37

Unnawut

7,500
1
26
33

score 3 · Answer 5 · edited Jun 16 '15 at 08:28

3

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

edited Jun 16 '15 at 08:28

Artjom B.

61,146
24
125
222

answered Jun 16 '15 at 07:52

3

Is it really `-p[password]` and not `-p [password]`? – Artjom B. Jun 16 '15 at 08:29
4

@ArtjomB. Yes, and if you're password has special characters put it in quotes: `-p'pa$$w0rd!!!!'` – Ryan Taylor Jul 15 '15 at 01:06

score 1 · Answer 6 · answered Aug 02 '18 at 20:18

1

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]

answered Aug 02 '18 at 20:18

jsibs

666
2
7
25

score 0 · Answer 7 · answered Jan 21 '21 at 05:05

0

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

answered Jan 21 '21 at 05:05

logbasex

1,688
1
16
22

score 0 · Answer 8 · answered May 23 '21 at 14:41

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

score -4 · Answer 9 · edited Feb 09 '19 at 17:15

-4

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

edited Feb 09 '19 at 17:15

Community

1
1

answered Sep 25 '15 at 14:42

Nico

25
1

3

That is how you *restore* a backup, not how you create one. – Quentin Sep 25 '15 at 14:45

How to use MySQL dump from a remote machine

9 Answers9

Linked