I need to export a huge database, with thousands of tables and millions of rows. I'm on shared hosting so I can't use command line exports, and I really have no easy way to get to this data other than through phpMyAdmin or php. cPanel's default "export database" tool doesn't work, it exports a blank database. phpMyAdmin's export as gzip or zip also gives a blank database.

I've been downloading this one SQL file for a few hours now, and it's only at 114 mb of approximately 2 gb of actual data.

What's the best way for me to (safely) export all my data? This is really a huge pain and no data can be lost, but it must be exported out. Are there any scripts or programs I can use?

  • Ask your host to export it for you. – ceejayoz Aug 19 '11 at 17:38
  • @ceejayoz I'm not sure they would know how... – Cyclone Aug 19 '11 at 18:30
  • "I'm not sure they would know how" : change your host provider – Pedro Lobito Aug 21 '11 at 15:55
  • 1
    @Tuga That's one of the reasons why I am changing my hosting provider, and you're being rather unnecessarily rude. You don't post answers requiring a "thank you" for each one, and your provided answer didn't help solve my problem at all. – Cyclone Aug 21 '11 at 18:40

Can you access the database directly? MySqlDump.exe will work remotely in a pinch.

  • 1
    he does not have commandline access – tkrabec Aug 19 '11 at 18:12
  • 1
    That doesn't mean he can't access MySQL remotely. – ceejayoz Aug 19 '11 at 18:35
  • To his own PC? Most folks with that sort of setup don't have mysql behind a firewall, you can hit 3306 directly . . . – Wyatt Barnett Aug 19 '11 at 18:36
  • How do I obtain and operate MySqlDump.exe? – Cyclone Aug 19 '11 at 18:47
  • Download and install mysql locally. For instructions, see the documentation on mysql AB's site. – Wyatt Barnett Aug 19 '11 at 19:16

Take a look at :


You'll find a tutorial that will teach you how to export databases using phpmyadmin, installed by default on cpanel.

Also, cpanel has a backup feature that allows you to backup only databases


Alternatively, you can try to use the function below (stolen from http://davidwalsh.name/backup-mysql-database-php) :


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')

    $link = mysql_connect($host,$user,$pass);

    //get all of the tables
    if($tables == '*')
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result))
            $tables[] = $row[0];
        $tables = is_array($tables) ? $tables : explode(',',$tables);

    //cycle through
    foreach($tables as $table)
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        $return.= 'DROP TABLE '.$table.';';
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";

        for ($i = 0; $i < $num_fields; $i++) 
            while($row = mysql_fetch_row($result))
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) 
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j<($num_fields-1)) { $return.= ','; }
                $return.= ");\n";

    //save file
    $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

Of course, you'll need to provide database credentials to the function, as well as an array of tables you'd like to backup. If you provide a "*" or no tables, a complete database backup will run. The script does the rest!

Don't forget to add set_time_limit(0); to the php file containing the backup_tables() function.


  • As I stated in the question, the cpanel defaults didnt work. Your script breaks at 360 seconds in, the server wont let me run it for more than that. – Cyclone Aug 21 '11 at 0:23
  • Don't forget to add set_time_limit(0); to the php file containing the backup_tables() function. – Pedro Lobito Aug 21 '11 at 15:54
  • The server is configured to halt script execution at 360 seconds regardless of PHP's execution time limits. – Cyclone Aug 21 '11 at 18:39

You could try to use mysqldumper. It's a script that downloads a part of your DB, remembers where it stops, calls itself again and continues to download.

Here is the link: http://www.mysqldumper.net/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.