blocks|key|1743731|text|用Mysqldump试试吧|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1743732|#mysqldump+--host=the.remotedatabase.com+-u+yourusername+-p+yourdatabasename+>+/User/backups/adump.sql|code-block|syntax|javascript|1743733|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html^0|1|9|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Try it with <a href="http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html">Mysqldump</a>

<pre><code>#mysqldump --host=the.remotedatabase.com -u yourusername -p yourdatabasename &gt; /User/backups/adump.sql
</code></pre>

blocks|key|1957090|text|你有权访问SSH吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1957091|您可以在shell中使用以下命令来备份整个数据库：|1957092|mysqldump+-u+[username]+-p[password]+[databasename]+>+[filename.sql]|code-block|syntax|javascript|1957093|这实际上是一个后跟>操作符的命令，该操作符表示“获取前一个命令的输出并将其存储在此文件中。”|offset|length|style|CODE|1957094|注意：-p和mysql密码之间缺少空格不是一个拼写错误。但是，如果您保留-p标志存在，但实际密码为空，则系统将提示您输入密码。有时，建议这样做是为了将密码保存在bash历史记录之外。|1957095|entityMap^0|0|0|0|9|1|0|10|2|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@$K|W|L|X|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@$K|Z|L|10|M|N]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

Have you got access to SSH?

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

<pre><code>mysqldump -u [username] -p[password] [databasename] &gt; [filename.sql]
</code></pre>

This is actually one command followed by the <code>&gt;</code> 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 <code>-p</code> 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.

blocks|key|1957116|text|这个话题出现在我的google搜索结果的第一页，所以这里有一个对新手有用的小贴士。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1957117|您还可以在一行中转储sql并将其压缩：|1957118|mysqldump+-u+[username]+-p[password]+[database_name]+%7C+gzip+>+[filename.sql.gz]|code-block|syntax|javascript|1957119|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>mysqldump -u [username] -p[password] [database_name] | gzip &gt; [filename.sql.gz]
</code></pre>

blocks|key|1957126|text|mysqldump+-h+[domain+name/ip]+-u+[username]+-p[password]+[databasename]+>+[filename.sql]|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1957127|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>mysqldump -h [domain name/ip] -u [username] -p[password] [databasename] &gt; [filename.sql]
</code></pre>

blocks|key|1957214|text|我尝试了这里的所有组合，但这对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1957215|mysqldump+-u+root+-p+--default-character-set=utf8mb4+[DATABASE+TO+BE+COPIED+NAME]+>+[NEW+DATABASE+NAME]|code-block|syntax|javascript|1957216|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>mysqldump -u root -p --default-character-set=utf8mb4 [DATABASE TO BE COPIED NAME] &gt; [NEW DATABASE NAME]
</code></pre>

blocks|key|1957227|text|如果您还没有安装mysql_client，而是使用Docker容器：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1957228|sudo+docker+exec+MySQL_CONTAINER_NAME+/usr/bin/mysqldump+--host=192.168.1.1+-u+username+--password=password+db_name+>+dump.sql|1957229|entityMap^0|8|C|P|6|0|0|3I|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|R|8|@]|D|@]|E|$]]]|I|$]]

If you haven't install <code>mysql_client</code> yet and using <code>Docker</code> container instead:
<code>sudo docker exec MySQL_CONTAINER_NAME /usr/bin/mysqldump --host=192.168.1.1 -u username --password=password db_name &gt; dump.sql</code>

blocks|key|1957247|text|您可以将其直接通过管道传输到您希望将数据copy到的远程服务器：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1957248|mysqldump+-u+your_db_user_name+-p+--set-gtid-purged=OFF+--triggers+--routines+--events+--compress+--skip-lock-tables+--verbose+your_local_sql_db_name+%7C+mysql+-u+your_db_user_name+-p+-h+your_remote_server_ip+your_remote_server_db_name|code-block|syntax|javascript|1957249|您需要在远程sql服务器上创建数据库。|1957250|使用上面的命令，我能够从本地sql+server版本8.0.23复制到运行8.0.25的远程sqlserver|1957251|entityMap^0|K|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|V|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

You can directly pipe it to the remote server where you wish to <code>copy</code> your data to:
<pre class="lang-sql prettyprint-override"><code>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
</code></pre>
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

blocks|key|1957197|text|这就是在成功备份.sql文件后恢复备份的方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1957198|mysql+-u+[username]+[databasename]|code-block|syntax|javascript|1957199|并使用以下命令选择sql文件：|1957200|source+MY-BACKED-UP-DATABASE-FILE.sql|1957201|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>mysql -u [username] [databasename]
</code></pre>

And choose your sql file with this command:

<pre><code>source MY-BACKED-UP-DATABASE-FILE.sql
</code></pre>

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.

How to use MySQL dump from a remote machine

文件存储

数据库

服务器

容器

Docker

如何备份运行在远程服务器上的mysql数据库，我需要将备份文件存储在本地pc中。

问如何从远程计算机使用MySQL转储
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从远程计算机使用MySQL转储EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从远程计算机使用MySQL转储
EN