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?
Add a comment
|
4 Answers
Every database system comes with some program for dumping its contents.
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');
?>
2 Comments
Ross
Am I right in thinking you could also execute this via an SQL query? Some shared hosting providers don't allow system calls.
vog
If you have a hosting provider who doesn't allow system() or shell_exec(), you should switch to a serious one. Such a restriction doesn't really add security. It's just annoying the customers.
Comments
It's a pretty complicated process. I recommend you use phpmyadmin or similar.
Comments
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.
