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

How can we do mysqldump to another DB using php script such that triggers do not get dumped along with that?开发者_JS百科

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);