从本地到Godaddy托管插入数百万条记录 [英] Inserting millions of records from local to godaddy hosting

问题描述

我正在尝试将访问数据库中的约800万条记录插入GoDaddy中的mysql数据库中.

我构建了一个桌面Java应用程序来管理每次插入5000条记录的分块.我使用Laravel 5.1来管理服务器中的插入.因此，基本上，Java应用程序向laravel php路由发送了一个请求，然后它负责将其直接插入到我的MySQL数据库中.

人们会将数据直接插入Access中，因此我必须注意MDB文件的更改.这就是为什么我不能只从Access导出并导入到MySQL.

记录的第一部分成功插入，但是比我发送另一个请求时，会出现以下错误:

2015-10-28 10:43:57.844 java[3475:280209] Communications error: <OS_xpc_error: <error: 0x7fff7298bb90> { count = 1, contents =
    "XPCErrorDescription" => <string: 0x7fff7298bf40> { length = 22, contents = "Connection interrupted" }
}>
Got xpc error message: Connection interrupted
org.apache.http.NoHttpResponseException: api.mydomain.com:80 failed to respond

解决方案

确保已安装MySQL odbc连接器: https://dev.mysql.com/downloads/connector/odbc/

为您的MySQL服务器创建DNS(您可以通过odbc管理器或仅使用记事本来完成此操作) 您的DNS文件将如下所示:

[ODBC]
DRIVER=MySQL ODBC 5.3 Unicode Driver ' check what driver is installed
UID=username to the server
PORT=3306
PWD= password to the server
DATABASE=dbname
SERVER=serverip/name

将DNS文件保存在某个地方，命名为GoDaddy_MySQL.dns

打开您的访问数据库.

1. 外部数据
2. ODBC
3. 选择通过创建到数据源的链接"
4. 选择godaddy_mysql.dns文件
5. 如果您的所有连接详细信息正确，Access就会向您显示表&要导入的视图.单击您要从Access dtabase上传数据的表.
6. 现在，您已经在MS Access数据库中链接了实际的MySQL表.

您所需要做的就是简单地通过以下方式将数据从本地表上传到链接表:

您可以使用TOP关键字对上传内容进行分块.如果您添加条件条件(链接表中尚未包含)，则始终可以将新记录自动上载到MySQL服务器.

如果您现在仍将使用Access数据库，则还可以从本地表切换到链接表，以便所有新条目都将自动上载到您的godaddy服务器.

Pseudo: 

insert into linked_table select top 5000 from your local table where local_records_are not in linked table.

I'm trying to insert about 8 million records from an access database to a mysql database in GoDaddy.

I built a desktop java app to manage the inserts chunking 5000 records each time. I use Laravel 5.1 to manage the inserts in the server. So, basically, the java app send a request to a laravel php route, then it take care of inserting directly in my MySQL database.

Edit: people will insert data directly into the Access, so I have to watch the MDB file for changes. This is why I can't just export from Access and import into the MySQL.

The first part of records inserts successfully, but than when I send another request, I get this error:

2015-10-28 10:43:57.844 java[3475:280209] Communications error: <OS_xpc_error: <error: 0x7fff7298bb90> { count = 1, contents =
    "XPCErrorDescription" => <string: 0x7fff7298bf40> { length = 22, contents = "Connection interrupted" }
}>
Got xpc error message: Connection interrupted
org.apache.http.NoHttpResponseException: api.mydomain.com:80 failed to respond

解决方案

Make sure you have MySQL odbc connector is installed: https://dev.mysql.com/downloads/connector/odbc/

create your DNS to your MySQL server (you can do this via odbc manager or just with a notepad) your DNS file will look like this:

[ODBC]
DRIVER=MySQL ODBC 5.3 Unicode Driver ' check what driver is installed
UID=username to the server
PORT=3306
PWD= password to the server
DATABASE=dbname
SERVER=serverip/name

Save the DNS file somewhere lets call it GoDaddy_MySQL.dns

Open up you ACCESS database.

1. External Data
2. ODBC
3. Select "link to the data source by creating"
4. select the godaddy_mysql.dns file
5. if all your connection details are correct Access will show you tables & views to import. Click the tables you would like to upload data from your Access dtabase.
6. Now you have linked the actual MySQL table in your MS Access database.

All you need to do is uploading data from your local table to your linked table simply by:

you can chunk your upload by using the TOP keyword. if you add a where condition with (is not already on the linked table) you can always upload new records automatically to your MySQL server.

if you are and will still use your Access database you can also switch from local to linked tables so all new entry will automatically uploaded to your godaddy server.

Pseudo: 

insert into linked_table select top 5000 from your local table where local_records_are not in linked table.

这篇关于从本地到Godaddy托管插入数百万条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！