Mysql select ends with ‘killed’

MySQLmysql-5.1mysqldump

Okay, I'm sure this will turn out to be something dumb, but then I'm a dumb n00b DBA.

I've got a largish table, about 163 million rows and something like 44 GiB. After some experiments with mysqldump for backups, I attempted to build a tab-separated output using select in batch mode.

I tried

echo 'select * from node;' | mysql -u xx -p -B database > file

and it terminates with the message "Killed".

so I tried logging into mysql in batch more, same query, same result.

I tried logging in in the normal fashion, and doing select * from node;

Still the same result: "Killed". HOWEVER

create table temp_node (select * from node);

works fine.

So I'm clearly missing something basic, but googling hasn't helped. Have pity on a n00b and tell me what's happening?

Please note the question isn't about running the command per se, it's that when I run the command, or execute select * from node; from the mysql> prompt, mysql terminates with the message "Killed."

Oh, and how I can export that damned table for loading later?

Best Answer

Okay, I thought that was a familiar message. That's from LINUX saying "process ran out of memory."

Well, there's not a lot more to say. The mysql process itself runs out of memory and is terminated by the OS. I dn't have a good feel for the implementation, but it appears that it's trying something like pulling the whole table into memory to do the select *.

Related Question