MySQL 将mysql数据库从远程服务器复制到本地计算机
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15435144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy mysql database from remote server to local computer
提问by megas
I'm under VPN and I don't have SSH access to remote server.
我在 VPN 下，我没有远程服务器的 SSH 访问权限。
I can connect to remote database by console
我可以通过控制台连接到远程数据库
mysql -u username -p -h remote.site.com
Now I'm trying to clone the remote database to local computer
现在我正在尝试将远程数据库克隆到本地计算机
mysqldump -u username -p -h remote.site.com mysqldump | mysql -u root -ppassword webstuff
And I've got error
我有错误
mysqldump: Got error: 1045: Access denied for user 'webstaff'@'10.75.1.2'
(using password: YES) when trying to connect
How to copy mysql database from remote server to local computer?
如何将mysql数据库从远程服务器复制到本地计算机？
回答by Julian H. Lam
Assuming the following command works successfully:
假设以下命令成功运行：
mysql -u username -p -h remote.site.com
The syntax for mysqldumpis identical, and outputs the database dump to stdout. Redirect the output to a local file on the computer:
的语法mysqldump相同，并将数据库转储输出到stdout. 将输出重定向到计算机上的本地文件：
mysqldump -u username -p -h remote.site.com DBNAME > backup.sql
Replace DBNAMEwith the name of the database you'd like to download to your computer.
替换DBNAME为您要下载到计算机的数据库的名称。
回答by Ghigo
Check syntax and execute one command at a time, then verify output.
检查语法并一次执行一个命令，然后验证输出。
mysqldump -u remoteusername -p remotepassword -h your.site.com databasename > dump.sql
mysql -u localusername -p localpassword databasename < dump.sql
Once you've matched all passwords, you can use pipe.
匹配所有密码后，您可以使用管道。
回答by Ecd
Please check this gist.
请检查这个要点。
https://gist.github.com/ecdundar/789660d830d6d40b6c90
https://gist.github.com/ecdundar/789660d830d6d40b6c90
#!/bin/bash
# copymysql.sh
# GENERATED WITH USING ARTUR BODERA S SCRIPT
# Source script at: https://gist.github.com/2215200
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
REMOTESERVERIP=""
REMOTESERVERUSER=""
REMOTESERVERPASSWORD=""
REMOTECONNECTIONSTR="-h ${REMOTESERVERIP} -u ${REMOTESERVERUSER} --password=${REMOTESERVERPASSWORD} "
LOCALSERVERIP=""
LOCALSERVERUSER=""
LOCALSERVERPASSWORD=""
LOCALCONNECTION="-h ${LOCALSERVERIP} -u ${LOCALSERVERUSER} --password=${LOCALSERVERPASSWORD} "
IGNOREVIEWS=""
MYVIEWS=""
IGNOREDATABASES="select schema_name from information_schema.SCHEMATA where schema_name != 'information_schema' and schema_name != 'mysql' and schema_name != 'performance_schema' ;"
# GET A LIST OF DATABASES
databases=`$MYSQL $REMOTECONNECTIONSTR -e "${IGNOREDATABASES}" | tr -d "| " | grep -v schema_name`
# COPY ALL TABLES
for db in $databases; do
# GET LIST OF ITEMS
views=`$MYSQL $REMOTECONNECTIONSTR --batch -N -e "select table_name from information_schema.tables where table_type='VIEW' and table_schema='$db';"
IGNOREVIEWS=""
for view in $views; do
IGNOREVIEWS=${IGNOREVIEWS}" --ignore-table=$db.$view "
done
echo "TABLES "$db
$MYSQL $LOCALCONNECTION --batch -N -e "create database $db; "
$MYSQLDUMP $REMOTECONNECTIONSTR $IGNOREVIEWS --compress --quick --extended-insert --skip-add-locks --skip-comments --skip-disable-keys --default-character-set=latin1 --skip-triggers --single-transaction $db | mysql $LOCALCONNECTION $db
done
# COPY ALL PROCEDURES
for db in $databases; do
echo "PROCEDURES "$db
#PROCEDURES
$MYSQLDUMP $REMOTECONNECTIONSTR --compress --quick --routines --no-create-info --no-data --no-create-db --skip-opt --skip-triggers $db | \
sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql $LOCALCONNECTION $db
done
# COPY ALL TRIGGERS
for db in $databases; do
echo "TRIGGERS "$db
#TRIGGERS
$MYSQLDUMP $REMOTECONNECTIONSTR --compress --quick --no-create-info --no-data --no-create-db --skip-opt --triggers $db | \
sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql $LOCALCONNECTION $db
done
# COPY ALL VIEWS
for db in $databases; do
# GET LIST OF ITEMS
views=`$MYSQL $REMOTECONNECTIONSTR --batch -N -e "select table_name from information_schema.tables where table_type='VIEW' and table_schema='$db';"`
MYVIEWS=""
for view in $views; do
MYVIEWS=${MYVIEWS}" "$view" "
done
echo "VIEWS "$db
if [ -n "$MYVIEWS" ]; then
#VIEWS
$MYSQLDUMP $REMOTECONNECTIONSTR --compress --quick -Q -f --no-data --skip-comments --skip-triggers --skip-opt --no-create-db --complete-insert --add-drop-table $db $MYVIEWS | \
sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql $LOCALCONNECTION $db
fi
done
echo "OK!"
回答by CloudyMarble
This can have different reasons like:
这可能有不同的原因，例如：
- You are using an incorrect password
- The MySQL server got an error when trying to resolve the IP address of the client host to a name
- No privileges are granted to the user
- 您使用的密码不正确
- MySQL 服务器在尝试将客户端主机的 IP 地址解析为名称时出错
- 没有权限授予用户
You can try one of the following steps:
您可以尝试以下步骤之一：
To reset the password for the remote user by:
要重置远程用户的密码，请执行以下操作：
SET PASSWORD FOR some_user@ip_addr_of_remote_client=PASSWORD('some_password');
To grant access to the user by:
通过以下方式授予用户访问权限：
GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON YourDB.* TO user@Host IDENTIFIED by 'password';
Hope this helps you, if not then you will have to go through the documentation
希望这对您有所帮助，否则您将不得不阅读文档
回答by Ankur Rastogi
Often our databases are really big and the take time to take dump directly from remote machine to other machine as our friends other have suggested above.
通常我们的数据库非常大，并且需要时间将转储直接从远程机器转移到其他机器，正如我们的其他朋友在上面建议的那样。
In such cases what you can do is to take the dump on remote machine using MYSQLDUMP Command
在这种情况下，您可以做的是使用MYSQLDUMP 命令在远程机器上进行转储
MYSQLDUMP -uuser -p --all-databases > file_name.sql
MYSQLDUMP -uuser -p --all-databases > file_name.sql
and than transfer that file from remote server to your machine using Linux SCP Command
然后使用Linux SCP 命令将该文件从远程服务器传输到您的机器
scp user@remote_ip:~/mysql_dump_file_name.sql ./
scp user@remote_ip:~/mysql_dump_file_name.sql ./
回答by Kai Noack
Copy mysql database from remote server to local computer
将mysql数据库从远程服务器复制到本地计算机
I ran into the same problem. And I could not get it done with the other answers. So here is how I finally did it (yes, a beginner tutorial):
我遇到了同样的问题。我无法用其他答案完成它。所以这是我最终做到的（是的，初学者教程）：
Step 1: Create a new database in your local phpmyadmin.
第 1 步：在本地 phpmyadmin 中创建一个新数据库。
Step 2: Dump the database on the remote server into a sql file (here I used Putty/SSH):
第 2 步：将远程服务器上的数据库转储到 sql 文件中（这里我使用了 Putty/SSH）：
mysqldump --host="mysql5.domain.com" --user="db231231" --password="DBPASSWORD" databasename > dbdump.sql
Step 3: Download the dbdump.sqlfile via FTP client (should be located in the root folder)
第三步：dbdump.sql通过FTP客户端下载文件（应该在根目录下）
Step 4: Move the sql file to the folder of your localhost installation, where mysql.exeis located. I am using uniform-server, this would be at C:\uniserver\core\mysql\bin\, with XAMPP it would be C:\xampp\mysql\bin
第四步：将sql文件移动到你的本地主机安装文件夹，所在mysql.exe的位置。我正在使用统一服务器，这将在C:\uniserver\core\mysql\bin\，使用 XAMPP 将是C:\xampp\mysql\bin
Step 5: Execute the mysql.exeas follows:
第五步：执行mysql.exe如下：
mysql.exe -u root -pYOURPASSWORD YOURLOCALDBNAME < dbdump.sql
Step 6: Wait... depending on the file size. You can check the progress in phpmyadmin, seeing newly created tables.
第 6 步：等待...取决于文件大小。您可以在 phpmyadmin 中查看进度，查看新创建的表。
Step 7: Done. Go to your local phpmyadmin to check if the database has been filled with the entire data.
第 7 步：完成。转到您本地的 phpmyadmin 以检查数据库是否已填满整个数据。
Hope that helps. Good luck!
希望有帮助。祝你好运！
Note 1: When starting the uniformer-server you can specify a password for mysql. This is the one you have to use above for YOURPASSWORD.
注1：启动uniformer-server时，可以为mysql指定密码。这是您必须在上面使用的您的密码。
Note 2: If the login does not work and you run into password problems, check your password if it contains special characters like !. If so, then you probably need to escape them \!.
注意 2：如果登录无效并且您遇到密码问题，请检查您的密码是否包含特殊字符，如!. 如果是这样，那么您可能需要逃避它们\!。
Note 3: In case not all mysql data can be found in the local db after the import, it could be that there is a problem with the mysql directives of your dbdump.sql
注3：如果导入后本地db中不能找到所有mysql数据，可能是你的dbdump.sql的mysql指令有问题
回答by Dejan
Better yet use a oneliner:
更好的是使用oneliner：
Dump remoteDB to localDB:
将 remoteDB 转储到 localDB：
mysqldump -uroot -pMypsw -h remoteHost remoteDB | mysql -u root -pMypsw localDB
Dump localDB to remoteDB:
将 localDB 转储到 remoteDB：
mysqldump -uroot -pmyPsw localDB | mysql -uroot -pMypsw -h remoteHost remoteDB
回答by Abhay Singh
C:\Users\>mysqldump -u root -p -h ip address --databases database_name -r sql_file.sql
Enter password: your_password
回答by tomodach1
This answer is not remote server but local server. The logic should be the same. To copy and backup my local machine MAMP database to my local desktop machine folder, go to console then
这个答案不是远程服务器，而是本地服务器。逻辑应该是一样的。要将我的本地机器 MAMP 数据库复制并备份到我的本地台式机文件夹，请转到控制台然后
mysqldump -h YourHostName -u YourUserNameHere -p YourDataBaseNameHere > DestinationPath/xxxwhatever.sql
In my case YourHostNamewas localhost. DestinationPathis the path to the download; you can drag and drop your desired destination folder and it will paste the path in.
在我的情况下YourHostName是localhost. DestinationPath是下载路径；您可以拖放所需的目标文件夹，然后将路径粘贴到其中。
Then password may be asked:
然后可能会询问密码：
Enter password: xxxxxxxx
