如何使用PHP备份SQL数据库？

How do i backup a SQL database using PHP.

如何使用PHP备份SQL数据库。

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

是否有供应商不可知的方式来执行符合ANSI SQL的方法？

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

如果不是，您可以列出如何为每个数据库供应商执行此操作？

4 个解决方案

#1

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

每个数据库系统都附带一些转储其内容的程序。

PostgreSQL: pg_dump
PostgreSQL：pg_dump
MySQL: mysqldump
MySQL：mysqldump
...
...

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

您可以使用system（）或shell_exec（）从PHP调用该程序。

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:

例如，如果您将PostgreSQL与启用的Ident身份验证一起使用，并希望将数据库测试直接作为SQL文本转储到浏览器，那么它就像下面这样简单：


<?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:

当使用MySQL将数据库用户和密码存储到〜/ .my.cnf时，它也非常简单：


<?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.

#2

I love phpmybackuppro

我喜欢phpmybackuppro

#3

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

这是一个非常复杂的过程。我建议你使用phpmyadmin或类似的。

#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.

虽然备份数据库“符合ANSI SQL”是可能且令人钦佩的，但您仍会遇到可移植性问题。最明显的是，虽然MySQL的转储/恢复格式相当快，但它主要通过使用SQL的非标准扩展来实现这一点。所以你不能把转储交给PostGresql：它不会工作。实际上，PostGresql在处理大量INSERT语句时也很慢。它的本机转储格式使用它自己的自定义SQL语句，该语句针对一次插入大量数据进行了优化。

#1