Search code examples
mysqllarge-dataheidisql

How can i export a mysql table which contains approximately 4-5 billions of data?

I have a MySQL database which have several tables that contains billions of data. This is the reason why I'm facing loading time problem while working on local server with live database. So I've decided to shift them on local server separately. I'm using HeidiSQL and this is really painful for me to connect the server again and again.

But the problem is, when I try for exporting the table (separate table) , it crashes and disconnects the server.

Is there any way , that I can export the tables that is more than 10/12 GB in size(each)??

Solution

  • Break the process down into 3 reliable steps:

    A) Export the table to a file on the server using:

    select * from mytable into outfile 'mytable.dat'

    See documentation.

    B) Move the file from the server to your local machine.

    C) Load the data into your table using:

    load data infile 'mytable.dat' into table mytable

    See documentation.