将一个巨大的 MySQL 表从远程复制到本地数据库 [英] Copy a huge MySQL table from a remote to a local database

问题描述

我对远程 MySQL 数据库具有只读访问权限，其中包含一个非常大的表(数亿行).

I have read-only access to a remote MySQL database, which contains a very large table (hundreds of millions of lines).

为了更快地访问该表，我想将其复制到我的本地数据库.

To get faster access to that table, I want to copy it to my local database.

最好的方法是什么?

"SELECT INTO OUTFILE" 不起作用，因为我没有远程数据库所需的权限.

"SELECT INTO OUTFILE" doesn't work, because I don't have the required permissions on the remote database.

我尝试使用Java来SELECT所有行FROM远程表，将它们保存到本地文本文件，然后使用LOAD DATA INFILE; 然而，选择打破了

I tried to use Java to SELECT all rows FROM the remote table, save them to a local text file, then use LOAD DATA INFILE; however, the select broke with

线程main"中的异常java.lang.OutOfMemoryError: Java heap space".

"Exception in thread "main" java.lang.OutOfMemoryError: Java heap space".

推荐答案

在远程数据库上使用mysqldump命令提取所需数据库的SQL语句.然后将提取的文件复制到本地系统并执行将在本地系统中创建数据库的 sql 文件.

Use the mysqldump command on the remote database to extract the SQL statements of the database required. Then copy the extracted file to your local system and execute the sql file which will create the database in the local system.

这里是mysqldump的例子http://www.roseindia.net/tutorial/mysql/mysqlbackup/mysqldump.html

Here is the mysqldump example http://www.roseindia.net/tutorial/mysql/mysqlbackup/mysqldump.html

这篇关于将一个巨大的 MySQL 表从远程复制到本地数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！