I have read-only access to a remote MySQL database, which contains a very large table (hundreds of millions of lines).
我对远程MySQL数据库具有只读权限，该数据库包含一个非常大的表（数亿行）。
To get faster access to that table, I want to copy it to my local database.
为了更快地访问该表，我想将其复制到我的本地数据库。
What is the best way to do this?
做这个的最好方式是什么？
"SELECT INTO OUTFILE" doesn't work, because I don't have the required permissions on the remote database.
“SELECT INTO OUTFILE”不起作用，因为我没有远程数据库所需的权限。
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
我尝试使用Java从远程表中选择所有行，将它们保存到本地文本文件，然后使用LOAD DATA INFILE;然而，选择打破了
"Exception in thread "main" java.lang.OutOfMemoryError: Java heap space".
“线程中的异常”主“java.lang.OutOfMemoryError：Java堆空间”。
3 个解决方案
#1
1
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命令提取所需数据库的SQL语句。然后将提取的文件复制到本地系统并执行sql文件，该文件将在本地系统中创建数据库。
Here is the mysqldump example http://www.roseindia.net/tutorial/mysql/mysqlbackup/mysqldump.html
这是mysqldump示例http://www.roseindia.net/tutorial/mysql/mysqlbackup/mysqldump.html
#2
1
Try to set Synchronization, latest version of PHPMyAdmin provides an option to set synchronization. You need to set source DB as remote database and destination to your local database.
Setting up a PHP (and PHPMyAdmin too) on local machine is not a big task. if table is much bigger you may need to increase maximum execution time for phpmyadmin script.
Alternatively if you can access remote MySQL port then you can try to connect to remote db from your local machine as mysql -h remote_IP -u usernmae -pPassword. if it connects then you can definitely use mysqldump command on local machine. check this link
尝试设置同步，最新版本的PHPMyAdmin提供了设置同步的选项。您需要将源DB设置为本地数据库的远程数据库和目标。在本地计算机上设置PHP（以及PHPMyAdmin）也不是一项大任务。如果表大得多，则可能需要增加phpmyadmin脚本的最大执行时间。或者，如果您可以访问远程MySQL端口，那么您可以尝试从本地计算机连接到远程数据库，如mysql -h remote_IP -u usernmae -pPassword。如果它连接，那么你肯定可以在本地机器上使用mysqldump命令。检查此链接
#3
1
The problem with your Java program is likely to be because the MySQL JDBC driver stores the entire ResultSet in memory by default. With a huge table, this is highly likely to cause an OutOfMemoryError.
Java程序的问题很可能是因为MySQL JDBC驱动程序默认将整个ResultSet存储在内存中。使用庞大的表，这很可能导致OutOfMemoryError。
You can stop the MySQL driver from doing this by following the instructions in the ResultSet section of this page in the MySQL documentation (which I found via this blog post).
您可以按照MySQL文档（我在此博客文章中找到）中本页的ResultSet部分中的说明停止MySQL驱动程序。
I was able to reproduce an OutOfMemoryError with a simple Java program that simply read each row out of a table with over 120 million rows. After making the changes suggested in the MySQL documentation, my Java program completed without any memory issues.
我能够使用一个简单的Java程序重现OutOfMemoryError，该程序只读取超过1.2亿行的表中的每一行。在MySQL文档中建议进行更改后，我的Java程序完成后没有任何内存问题。
#1
1
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命令提取所需数据库的SQL语句。然后将提取的文件复制到本地系统并执行sql文件，该文件将在本地系统中创建数据库。
Here is the mysqldump example http://www.roseindia.net/tutorial/mysql/mysqlbackup/mysqldump.html
这是mysqldump示例http://www.roseindia.net/tutorial/mysql/mysqlbackup/mysqldump.html
#2
1
Try to set Synchronization, latest version of PHPMyAdmin provides an option to set synchronization. You need to set source DB as remote database and destination to your local database.
Setting up a PHP (and PHPMyAdmin too) on local machine is not a big task. if table is much bigger you may need to increase maximum execution time for phpmyadmin script.
Alternatively if you can access remote MySQL port then you can try to connect to remote db from your local machine as mysql -h remote_IP -u usernmae -pPassword. if it connects then you can definitely use mysqldump command on local machine. check this link
尝试设置同步，最新版本的PHPMyAdmin提供了设置同步的选项。您需要将源DB设置为本地数据库的远程数据库和目标。在本地计算机上设置PHP（以及PHPMyAdmin）也不是一项大任务。如果表大得多，则可能需要增加phpmyadmin脚本的最大执行时间。或者，如果您可以访问远程MySQL端口，那么您可以尝试从本地计算机连接到远程数据库，如mysql -h remote_IP -u usernmae -pPassword。如果它连接，那么你肯定可以在本地机器上使用mysqldump命令。检查此链接
#3
1
The problem with your Java program is likely to be because the MySQL JDBC driver stores the entire ResultSet in memory by default. With a huge table, this is highly likely to cause an OutOfMemoryError.
Java程序的问题很可能是因为MySQL JDBC驱动程序默认将整个ResultSet存储在内存中。使用庞大的表，这很可能导致OutOfMemoryError。
You can stop the MySQL driver from doing this by following the instructions in the ResultSet section of this page in the MySQL documentation (which I found via this blog post).
您可以按照MySQL文档（我在此博客文章中找到）中本页的ResultSet部分中的说明停止MySQL驱动程序。
I was able to reproduce an OutOfMemoryError with a simple Java program that simply read each row out of a table with over 120 million rows. After making the changes suggested in the MySQL documentation, my Java program completed without any memory issues.
我能够使用一个简单的Java程序重现OutOfMemoryError，该程序只读取超过1.2亿行的表中的每一行。在MySQL文档中建议进行更改后，我的Java程序完成后没有任何内存问题。