phpmyadmin Question posted in
The official documentation can be found here.

Mysqldump big table from Php – Phpmyadmin

(Just in case this is an XY problem): I need to export a really big table (85M rows), I don’t have SSH access. And neither PhpMyAdmin nor SQLYog (remote connecction) are working fine (they export just a 3% of my table, and finish the export without errors, not sure why).

So I thought executing mysqldump from PHP, the problem is I’m not sure if that will kill PHP too, because the code I’ve seen is waiting to mysqldump to finish.

My code will be (not tested): 

ini_set('memory_limit', '-1');
set_time_limit(0);
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = 'pass';
$dbname = 'dbname';
$tableName = 'mytable';
$dumpFile = __DIR__."mydump.sql";

$command = "mysqldump -h$dbhost -u$dbuser -p$dbpass $dbname $tableName | gzip> $dumpFile";

But I’m not sure if it’s better to use “exec”, “shell_exec”, or “system”, or if I need to wait for the output and then send the file to download, or I can execute the command, finish the PHP script and then come back after 1 hour or so and download the file via FTP? (also I’m worried for the filesize, I guess will be like 5 or 7GB).
Is better to create 85 csv files with 1million rows each one?

what is the best way to do this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This code was working fine, it allows me to pass the table name and the start ID for the dump from the URL just in case is helpful to someone else 

    <?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);


$tableName = $_GET['tablename'];
$limit = isset($_GET['limit']) ? $_GET['limit'] : '';
$start = isset($_GET['start']) ? $_GET['start'] : ''; 

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'pass';
$dbname = 'mydb';
$dumpFile = __DIR__."/{$tableName}_{$start}_dump.zip";

echo $dumpFile;
echo '<br>';

$startSql = $start ? 'id >'.$start : ''; 
$limitSql = $limit ? ' limit '.$limit : '';
$whereSql = $limit || $start ? '--where="1 AND '.$startSql.$limitSql.'"' : '';

$command = "mysqldump -h$dbhost -u$dbuser -p$dbpass $whereSql --skip-add-drop-table --no-create-info $dbname $tableName | gzip> $dumpFile";

echo $command;
echo '<br>';

exec($command, $output, $status);
if ($status) {
    exit('error');
}else {
    exit('success');
}

  2. 0

    I am not an expert in Servers but as far as I know, there is not much difference among "exec", "shell_exec", or "system".

    However, you need to have explicit permission in order to run these commands on a Server which depends upon the Server configuration.

    In my project, I usually have to move a huge DB of around 14G often. I use exec as that’s the function which is allowed by the Server. Here is the script I use to dump it:

    exec('mysqldump --host={HOST} --user={USER} --password='.escapeshellarg({PASSWORD}).' {DATABASE_NAME} > DUMP_FILE_PATH', $output, $status);
if ($status) {
    //An error has occured while exporting the database
    return;
}
//go on

    I am not sure what do you mean by:

    Is better to create 85 csv files with 1million rows each one?

    I simply do it with .sql file and then import it. The size does not make any issue when you import and export a DB using terminal or terminal commands in PHP.

    If you need to open the DB in some editor to check its content or modify, then yes, your editor for sure will hang and work like s**t for such a huge file but I am not sure why would you want to do that.

    Update

    As far as time is concerned, it totally depends upon your internet connection but I suggest you run your php script from terminal rather than on a browser because that way there is no time limit, like browsers have 30 seconds.

    If still doesnt work, you can try forcing the script to increase memory and time limit it explicitly using:

    ini_set('memory_limit', '512M');
ini_set('max_execution_time', 0);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search