MySQL database backup and Linux automatic timing backup code in PHP

Save the following PHP code as a backdata.class.php file

The code is as follows

Copy Code

<?php /* * * Simple one MySQL backup data class * */ Class backupdata{ Private $mysql _link;//Link identification Private $dbName; Database name Private $dataDir; The directory where the data is to be stored private $tableNames;//Table name Public function __construct ($mysql _link) { $this->mysql_link = $mysql _link; } Public Function Backuptables ($dbName, $dataDir, $tableNames) {//Start Backup $this->dbname = $dbName; $this->datadir = $dataDir; $this->tablenames = $tableNames; $tables = $this->delarray ($this->tablenames); $sqls = '; foreach ($tables as $tablename) { if ($tablename = =) {//table does not exist Continue } Here is the first half of the form SQL ************** If there is a table, delete it first $sqls. = "DROP TABLE IF EXISTS $tablename; n"; Reading table structure $rs = mysql_query ("Show CREATE TABLE $tablename", $this->mysql_link); $row =mysql_fetch_row ($RS); Get table structure to compose SQL $sqls. = $row [' 1 ']. "; nn "; Unset ($RS); Unset ($row); The following is the second half of the form SQL ************** Search out all the data in the table $rs =mysql_query ("SELECT * from $tablename", $this->mysql_link); Number of fields in a table $field =mysql_num_fields ($RS); form This SQL statement: INSERT into ' groups ' VALUES (' 1499e0ca25988d ', ' director ', ', ', ' 0 '); " while ($rows =mysql_fetch_row ($rs)) { $comma = ';/comma $sqls. = "INSERT into ' $tablename ' VALUES ("; for ($i =0; $i < $field; $i + +) { $sqls. = $comma. "'". $rows [$i]. "'"; $comma = ', '; } $sqls. = "); nnn"; } } $backfilepath = $this->datadir.date ("Ymdhis", Time ()). SQL '; Write to File $filehandle = fopen ($backfilepath, "w"); Fwrite ($filehandle, $SQLS); Fclose ($filehandle); } Private Function Delarray ($array) {//Processing incoming array foreach ($array as $tables) { if ($tables = = ' * ') {//All tables (the table name cannot be grouped into an array in the usual way) $newtables =mysql_list_tables ($this->dbname, $this->mysql_link); $tableList = Array (); for ($i = 0; $i < mysql_numrows ($newtables); $i + +) { Array_push ($tableList, Mysql_tablename ($newtables, $i)); } $tableList = $tableList; }else{ $tableList = $array; Break } } return $tableList; } } ?>

We then create a new file with backdata.class.php saved in the same directory

How to use:

The code is as follows

Copy Code

Require_once ("backdata.class.php"); $link = @mysql_connect ("localhost", "Database name", "password") or Die (' could not connect to server. '); mysql_query ("Use CMS", $link); mysql_query ("Set names UTF8", $link); $dbbck =new Backupdata ($link);//Instantiate it, as long as a link identity on the line When backing up your data, if you want to back up all the tables in a database, you can write this: $dbbck->backuptables ("CMS", "./", Array (' * ')); When backing up your data, if you want to back up only one table in a database, you can write this: $dbbck->backuptables ("CMS", "./", Array (' user ')); When backing up your data, if you want to back up multiple tables in a database, you can write this: $dbbck->backuptables ("CMS", "./", Array (' user ', ' ACL ', ' informatoin ')); Note: $dbbck->backuptables ("Parameter 1", "Parameter 2", Array ());

Parameter 1 is: database name,
Parameter 2 is the location where the backup data is to be stored (that is, the directory address)
The third one is: you have to save those tables.

The following is the code for automatic scheduled backups in Linux

Referring to many tutorials on the Web, plus your own tests, the following scripts are available for testing.

The code is as follows

Copy Code

#!/bin/bash #Shell Command for Backup MySQL Database everyday automatically by Crontab #Author: Carlos Wong #Date: 2010-08-24 #配置参数 User=root #数据库用户名 "> User name Password=xxxxx #数据库用户密码 Database=tieniuzai #数据库名称 Webmaster=tieniuzai@qq.com #管理员邮箱地址 to send a backup failure message reminder backup_dir=/var/www/data_backup/topons/#备份文件存储路径 Logfile=/var/www/data_backup/topons/data_backup.log #日记文件路径 Date= ' Date ' +%y%m%d-%h%m ' #日期格式 (as filename) dumpfile= $DATE. SQL #备份文件名 Archive= $DATE. Sql.tgz #压缩文件名 Options= "-u$user-p$password–opt–extended-insert=false–triggers=false-r–hex-blob–flush-logs–delete-master-logs- B $DATABASE "#mysqldump parameter details see Help Mysqldump-help #判断备份文件存储目录是否存在, otherwise create the directory if [!-D $BACKUP _dir]; Then Mkdir-p "$BACKUP _dir" Fi #开始备份之前, write the backup header to the journal file echo "" >> $LOGFILE echo "" >> $LOGFILE echo "——————————————— –" >> $LOGFILE echo "BACKUP Date:" $ (DATE + "%y-%m-%d%h:%m:%s") >> $LOGFILE echo "——————————————— –" >> $LOGFILE #切换至备份目录 CD $BACKUP _dir #使用mysqldump the command back up the database and name the backup file in a formatted timestamp Mysqldump $OPTIONS > $DUMPFILE #判断数据库备份是否成功 if [[$ = = 0]]; Then #创建备份文件的压缩包 Tar czvf $ARCHIVE $DUMPFILE >> $LOGFILE 2>&1 #输入备份成功的消息到日记文件 echo [$ARCHIVE] Backup successful! >> $LOGFILE #删除原始备份文件, just keep the compressed package of the database backup file Rm-f $DUMPFILE Else echo "Database Backup fail!" >> $LOGFILE #备份失败后向网站管理者发送邮件提醒, requires support for mailutils or similar terminals to send mail tools #mail-S "Database: $DATABASE daily Backup Fail" $WEBMASTER Fi #输出备份过程结束的提醒消息 echo "Backup Process Done"

Use:

Save the above code to:/usr/sbin/databackup (file name is arbitrary, as long as the system does not have the same name as the original command; code can be placed anywhere, placed in the Sbin directory only for easy execution, sbin directory files/directories can be directly called in the terminal, Similar to the directory specified under Windows path variable)
To add executable permissions to a script: sudo chmod +x/usr/sbin/databackup
Execute script: sudo databackup
If you need to perform a scheduled backup command, simply place the following code in the crontab file (sudo vim/etc/crontab):
3 * * * root/usr/sbin/databackup #它代表着将于每天3点执行DataBackup脚本

Small attention to the next:

The shell script in Linux defines the format of the variable as: key=value, notice that there is no space before and after the "=" between them, otherwise the system cannot confirm the variable.
The line marked in red, the first character that resembles a single quote "'" is actually not a single quote, and its input key is below the ESC key of the keyboard.
This script is only suitable for backups of small sites because it is a full backup of the database instead of an incremental backup and is not suitable for large-capacity database backups.

Timing backup is the best way to save time for the machine to automatically record when the least people visit, and the PHP backup class needs to be manipulated, but it can also be implemented using the Scheduled tasks under Windows.