Welcome to my website

My name is Richard Watson and I'm a professional PHP developer
from Lurgan in Northern Ireland

Exporting MySQL databases using PHP on Windows

Posted by richard
News | 25-11-2015

large mysql php dump export windows

A brief how-to on how to dump a large database using PHP in a Windows environment where PHPMyAdmin struggles with the size of the database being exported.

First, 2 things:

  • My database is about 3.6GB in size.

  • Windows servers are corporate standard and my personal skill sit lies in that direction, this is not a statement on Linux/Unix vs NT based systems

PHPMyAdmin struggles to export large databases without timing out and without resorting to pesky .ini changes which make it a bit of a pain! Fortunately with MySQL on Windows, there is an easy way to spit out those large databases quickly and painlessly -

# Note that constant definitions are actually taken from the Imprete CMS; 
# You could just as easily use variables to achieve the same data, e.g. $db_host, $db_name etc.
define('DB_HOST', 'localhost');
define('DB_NAME', 'imprete');
define('DB_USER', 'richard');
define('DB_PASS', 'password');

# You can modify this as you prefer. It's not exactly infallible as a file naming convention as duplication is possible
$filename = date("d-m-Y_g-is") . ".sql";
$result = exec('c:\\xampp\\mysql\\bin\\mysqldump -h ' . DB_HOST . ' -u' . DB_USER . ' -p' . DB_PASS . ' ' . DB_NAME . ' > ./application/backup/sql/' . $filename, $result);

Let me stress - there should be no space following the -u or -p - in essence it will display as -urichard -ppassword. Also try not to forget to escape your file path for mysqldump as shown above. Did this work OK for you? Let me know below if you have any problems!

Authors note: This is easier on Unix based servers but hey, what can you do?

You are not signed in

Register or sign-in to post comments
Popular tags