Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

3 Answers 3

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

share|improve this answer
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
show 4 more comments

Take a look at :

http://www.siteground.com/tutorials/php-mysql/mysql_export.htm

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

https://yourdomain.com:2083/frontend/x3/backup/wizard-backup-type.html?type=mysql


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

backup_tables('localhost','username','password','blog');


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

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

    //get all of the tables
    if($tables == '*')
    {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result))
        {
            $tables[] = $row[0];
        }
    }
    else
    {
        $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";
            }
        }
        $return.="\n\n\n";
    }

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

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.

GL!

share|improve this answer
    
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
add comment

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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