使用PHP备份MySql数据库 [英] Backup MySql database with PHP

问题描述

我在MySql中有一个非常大的数据库，我需要每天备份它。

我需要能够从任何计算机，因此我想到了一个php脚本做这个，并把这个php脚本在线（offcourse与密码保护和授权等，所以只有我可以访问它）。

我应该使用什么命令，并且可以更改备份的设置（例如 Add AUTO_INCREMENT value = true ）？

我会感谢的例子...

此外，如果这是一个坏的方法（不安全，或者可能给坏的备份与坏的sql文件），还有什么其他方法吗？
我有shell访问，我有一个VPS（ubuntu服务器）。

我的Mysql版本是5.1

感谢

解决方案

不需要在数据库备份中涉及PHP。您只需要使用 mysqldump 备份数据库的脚本，和设置CRON作业以定期执行脚本：

  mysqldump db_name> backup-file.sql 
  

...将备份您的数据库到一个文件，通过重定向输出mysqldump到指定的文件名。

Peter提出了一个好处，命令只会给你一天的归档 - 任何归档两天的老被覆盖。这将允许您滚动日志七天：

  CURRENT_DAY_OF_WEEK =`date'+％u'` 
 FILENAME =mysqlbackup _$ CURRENT_DAY_OF_WEEK.sql
 
 mysqldump db_name> $ FILENAME 
  

请注意，文件权限将适用 - 如果用户执行该脚本没有该文件夹的权限。

I have a pretty large db in MySql, and I need to take backups of it every day or so.

I need to be able to take backups from any computer, so therefore I thought about making a php script to do this and put this php script online (offcourse with password protection and authorization etc so that only I can access it).

I wonder however, how is this done properly?

What commands should I use, and is it possible to change settings of the backup (for instance Add AUTO_INCREMENT value = true)?

I would appreciate examples...

Also, if this is a bad method (unsafe, or maybe gives bad backups with bad sql files), what other method would be preferred? I have shell-access and I have a VPS (ubuntu server).

My Mysql version is 5.1

Thanks

解决方案

There's no need to involve PHP in the database backup. You just need a script that uses mysqldump to backup the database, and setup a CRON job to periodically execute the script:

mysqldump db_name > backup-file.sql

...will backup your database to a file, by redirecting the output from the mysqldump to the specified file name.

Peter brought up a good point, that the command would only give you one day of archiving--any archive over two days old would be overwritten. This would allow you have a rolling log going back seven days:

CURRENT_DAY_OF_WEEK=`date '+%u'`
FILENAME="mysqlbackup_"$CURRENT_DAY_OF_WEEK".sql"

mysqldump db_name > $FILENAME

Also be aware that file permissions will apply - can't write a file if the user executing the script doesn't have permissions to the folder.

这篇关于使用PHP备份MySql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！