Script 1 - How to use mysqldump.exe with a batch file in order to backup a MySQL database on Windows

There are many ways to backup MySQL databases on Windows, including phpMyAdmin and MySQL Administrator.

Configure and run the first PHP script in order to create a batch file.

Review and run the batch file in order to export MySQL data into a .SQL file.

Optionally configure and run the second PHP script in order to move the .SQL file into a .GZ archive.

The first script will create a batch file containing the command line for mysqsldump.exe. The batch file is created in order to use the current system date as part of the dump filename, and also to grab the name of each MySQL database.

The intended output directory structure used in this example is the name of each database: c:\backup\mysql5\$db\.sql

If the output directory structure does not contain a folder having the same name as an existing database, an attempt will be made to create a the directory at c:\backup\mysql5\$db\

'.$outfile; return shell_exec($command_line); }; function compress($source, $archive) { if(!$fh = fopen($source, 'r')){ die('Open failed for '.$source); } if(!$data = file_get_contents($source)){ die('Read failed for '.$source); } if(!fclose($fh)){ die('Close failed for '.$source); } if(!$zp = gzopen($archive, 'w9')){ die('gzopen failed for '.$archive); } if(!gzwrite($zp, $data)){ die('gzwrite failed for '.$zp); } if(!gzclose($zp)){ die('gzclose failed for '.$zp); } return true; }; // The following heredoc is double-spaced for readability within the generated batch file $str = <<'); natcasesort($parts); foreach($parts as $part){ $pieces = explode('/', $part); $dbs[] = $pieces[5]; } $num_dbs = count($dbs); if(empty($num_dbs)){ echo '

Directory scan failed.

'; }else{ foreach($dbs as $db){ $outfile = $target_dir.'\\'.$db; if(!is_dir($outfile)){ if(mkdir($outfile, 0777)){ echo '

Creating '.$outfile.'

'."\n"; }else{ echo '

Cannot create '.$outfile.'

'."\n"; exit; } } // Note the trailing spaces: $str .= '%_exe% %_usr% %_pwd% '; // You may want to alter the following MySQL command line options $str .= '--skip-extended-insert '; $str .= '--add-drop-table '; $str .= '--no-create-db '; $str .= '--skip-add-locks '; $str .= '--skip-tz-utc '; $str .= $db.' > %_target%\\'.$db.'\\%_date%'; $str .= "\r\n\r\n"; } $str .= '@echo off'."\r\n\r\n"; $str .= 'pause'."\r\n\r\n"; if(file_put($batch_file_to_create, $str)){ echo "\n".'

Batch file contents: '.$batch_file_to_create.'

'; }else{ echo "\n".'

Cannot write '.$batch_file_to_create.'

'; } echo "\n".'
'.$str.'
'; } ?>