0

I have lots of 5+gb .sql files and I need to be able to run query on all of them (that's what my client needs). However, I was not able to figure out the way to do it. The query is simple: select column where keyword equals to specified word.

I saw some suggestions, that I should import db to mysql phpmyadmin server, but the file is too big.

Another solution I found was using pdo:

$db = new PDO($dsn, $user, $password);
$sql = file_get_contents('path/to/.sql');

$qr = $db->exec($sql);

But the same problem occured - files are too big.

How do I make this thing happen?

dealer.
  • 5
  • 2
  • Are you trying to run 5GB+ worth of select queries? What are you doing with the results? When you start working with 5GB files you're pretty well past phpmyadmin, and should begin looking at how to work with mysql at the command line level. With that said, there are tools specifically designed to work with large import files (such as BigDump), so I'm sure you can find something to suit your needs. – RToyo May 30 '18 at 15:22
  • Thank you for your comment. I simply need a way to select few columns of a big big file without having any idea how – dealer. May 30 '18 at 15:29
  • Does your 5GB file contain the data? Sorry, I'm still trying to fully understand your situation. If your file is a file of delimited data (e.g. a CSV file), you can use MySQL's built in functionality to import the file into a table (you could check the answers in [this question](https://stackoverflow.com/questions/1626059/mysql-bulk-load-command-line-tool) to get started), and then query that table. – RToyo May 30 '18 at 15:37

1 Answers1

1

What you mean by "too big" is that its bigger than the upload limit in phpmyadmin.

Use a desktop tool or command line to import the files to the MySQL server, then the only limit you will encounter will be the size of the servers hard drive.

DO NOT try to read multiple 5GB files using PHP, you'll just overload the server and it'll crash.

ThallerThanYall
  • 197
  • 2
  • 14
  • Thank You for Your answer. By saying too big, I meant I am getting this error: "Fatal error: Out of memory" blah blah. I believe it's what you just told me not to do – dealer. May 30 '18 at 15:14
  • I saw that the max sql file you import to phpmyadmin is 256MiB. Thought that the cause of the problem would have something similar to 'the pdo solution' i stated – dealer. May 30 '18 at 15:16
  • I will go look for some desktop tool. Will accept this answer as soon as possible – dealer. May 30 '18 at 15:18
  • 1
    Basically what is happening is that when you're reading the sql file, PHP is just putting all that data into the RAM. There is less RAM on the server than the size of the data you are trying to read, and so the server is telling you that it can't do anything because it's RAM is full. – ThallerThanYall May 30 '18 at 15:19
  • I'm on OSX and use Sequel Pro, however uploads WILL take a long time. And I mean a long time. I'd look into command line tools for files that big. – ThallerThanYall May 30 '18 at 15:20
  • I am not sure how to use command line nor even how to even open one (it is not the windows one right?). Do you have some recommendations on where I could look? – dealer. May 30 '18 at 15:26
  • Windows now supports BASH command line (https://www.howtogeek.com/249966/how-to-install-and-use-the-linux-bash-shell-on-windows-10/) Once installed, you'll need to look up how to SSH to your server and use mysql commands to import to the database from a file. This resource should help (https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb) – ThallerThanYall May 30 '18 at 15:28
  • There are plenty of [free SSH clients](https://www.ssh.com/ssh/download/) that run natively in Windows; you don't need to go to the extent of running BASH directly. It's just a matter of whether your server allows you to connect over SSH. – RToyo May 30 '18 at 15:39
  • Well yeah there is that, but why would you want to use a program that runs on top of windows and has SOME of the features of bash (and is more complicated to use) rather than just using a bash terminal? But yeah, I agree, the bigger issue is whether you can actually get SSH access. The argument here though really would be that if the client wants to ready SQL this big, they really should have a server capable of this (not that this is usually the case, but ho hum). – ThallerThanYall May 30 '18 at 15:48