How can we do mysqldump to another DB using php script such that triggers do not get dumped along with that?

Question

How can we do mysqldump to another DB using php script such that triggers do not get dumped along with that?

Answer 1

Something like this should work:

$db_name = "db";
$outputfile = "/somewhere";
$new_db_name = 'newdb';

$cmd = 'mysqldump --skip-triggers %s > %s  2>&1';
$cmd = sprintf($cmd, escapeshellarg($db_name), escapeshellcmd($output_file));
exec($cmd, $output, $ret); 
if ($ret !=0 ) {
    //log error message in $output
}

Then to import:

$cmd = 'mysql --database=%s < %s 2>&1';
$cmd = sprintf($cmd, escapeshellarg($new_db_name), escapeshellcmd($output_file));
exec($cmd, $output, $ret); 
//etc.

unlink($outputfile);

Note that the new database will need to be created first. You will also probably need to specify a username and password to each command.

edit

You could also do this in one command e.g.

exec('mysqldump --skip-triggers sourcedb | mysql --database targetdb 2>&1', $output, $return);