exporting importing large mysql tables

Portal Home > Knowledgebase > Articles Database > exporting importing large mysql tables

Posted by mjfroggy, 01-30-2013, 01:51 AM
I have two tables within a mysql database which both tables contain Blob data One table is 797MB in size and the other is 195MB in size My issue is since they are so large I can not export them via phpmyadmin and not sure how to do it via ssh on the server These are cpanel servers by the way. The database is already setup on the server that I have to move the tables too. I am just not sure how to get the two tables there and ensure nothing is lost. Suggestions?
Posted by krizag, 01-30-2013, 02:00 AM
Have you got root access in the server? You can dump the tables from shell: for T in `mysql -u root -pmypassword -N -B -e ‘show tables from database_name’`; do echo $T; mysqldump database_name $T > database_name_$T.gz ; done
Posted by Mayvena, 01-30-2013, 03:43 AM
Or if you don't feel confident enough you can use the free version of SQLyog.
Posted by creativeartist, 01-31-2013, 12:30 PM
You can do it command line from mysql. If you have phpmyadmin or workbench, you can do it so easily. If you are using phpmyadmin , i think you should increase the execution time. Mysql workbench http://www.mysql.com/products/workbench/
Posted by gnusys, 01-31-2013, 01:09 PM
Open a ticket with your host and ask them to provide you a dump of the database/ table
Posted by KMyers, 01-31-2013, 01:40 PM
There is a cool PHP tool called "BigDump", it allows you to export massive databases with a simple PHP script. Here is the page for it http://www.ozerov.de/bigdump/
Posted by neilmcaliece, 01-31-2013, 07:09 PM
I'm interested in this but the above link isn't working for me. Can you please verify that it's correct.
Posted by mitkid, 02-03-2013, 06:13 AM
Ok, he said he has a cPanel available. In the backup section you should be able to have a backup option fot the databases, if you don't then backup your whole website and inside the archive you should have an sql file with your databases.