How do i backup a SQL database using PHP?

Question

How do i backup a SQL database using PHP.

Is there a vendor agnostic way to do this that conforms to ANSI SQL?

If not maybe you can list how to do it for each of the database vendors?

Answer 1

Every database system comes with some program for dumping its contents.

• PostgreSQL: pg_dump
• MySQL: mysqldump
• ...

You can simply call that program from PHP using system() or shell_exec().

For example, if you use PostgreSQL with enabled Ident authentication and want to dump the Database test directly as SQL text to the browser, it's as simple as:

<?php
header('Content-type: text/plain');
system('pg_dump test');
?>

When using MySQL with database user and password stored into ~/.my.cnf, it is also very simple:

<?php
header('Content-type: text/plain');
system('mysqldump test');
?>

However, don't do this:

<?php
header('Content-type: text/plain');
system('mysqldump -utestuser -ptestpassword test');
?>

because transmitting a password as command line argument is very insecure.

Answer 2

I love phpmybackuppro

Answer 3

It's a pretty complicated process. I recommend you use phpmyadmin or similar.

Answer 4

Whilst backing up a database "conformant to ANSI SQL" is possible and admirable, you will still encounter portability issues. The most obvious is that whilst MySQL's dump/restore format is fairly fast, it achieves this principally by using a non-standard extension to SQL. So you can't just hand the dump to PostGresql: it won't work. In fact, PostGresql is also quite slow at handling a huge amount of INSERT statements. It's native dump format uses it's own custom SQL statement optimized for inserting a large amount of data at once.