导出大型MySql表 [英] Exporting Large MySql Table

问题描述

我在MySql中有一张表，我使用PhpMyAdmin进行管理。目前它坐在大约96万行。

我有一个老板喜欢在Excel中查看数据，这意味着每周，我必须将数据导出到Excel中。

我正在寻找一种更有效的方法来做到这一点。因为我实际上不能一次完成整个表，因为它超时了。所以我一直被困在这个表格中，以较小的查询结果将其转换成较小的查询并将其导出。

我已经尝试将Excel（和Access）直接连接到我的数据库，但是同样的问题;它超时了有没有办法扩展连接限制？

解决方案

老实说，对于这个大小的数据，我建议做一个mysqldump然后导入该表返回到另一个MySQL的另一个副本，也许在专用于此任务的虚拟机上。从那里，您可以根据需要设置超时等等，而不用担心生产数据库的资源限制。在基于Windows的系统上使用基于Unix的操作系统或进程优先级的 nice ，您应该可以做到这一点，而不会对生产系统造成太大影响。

或者，您可以设置您的生产数据库的副本，并从中提取数据。拥有从生产系统复制各种表格甚至整个数据库的所谓报告数据库实际上是一个相当普遍的做法，在大型环境中，确保您不会意外地杀死生产数据库，为某人提供数据。作为一个额外的优势，您不必等待mysqldump备份完成，然后再开始为您的老板提取数据;您可以立即执行。

I have a table in MySql that I manage using PhpMyAdmin. Currently it's sitting at around 960,000 rows.

I have a boss who likes to look at the data in Excel, which means weekly, I have to export the data into Excel.

I am looking for a more efficient way to do this. Since I can't actually do the entire table at once, because it times out. So I have been stuck with 'chunking' the table into smaller queries and exporting it like that.

I have tried connecting Excel (and Access) directly to my database, but same problem; it times out. Is there any way to extend the connection limit?

解决方案

Honestly for this size of data, I would suggest doing a mysqldump then importing the table back into another copy of MySQL installed somewhere else, maybe on a virtual machine dedicated to this task. From there, you can set timeouts and such as high as you want and not worry about resource limitations blowing up your production database. Using nice on Unix-based OSes or process priorities on Windows-based system, you should be able to do this without too much impact to the production system.

Alternatively, you can set up a replica of your production database and pull the data from there. Having a so-called "reporting database" that replicates various tables or even entire databases from your production system is actually a fairly common practice in large environments to ensure that you don't accidentally kill your production database pulling numbers for someone. As an added advantage, you don't have to wait for a mysqldump backup to complete before you start pulling data for your boss; you can do it right away.

这篇关于导出大型MySql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！