Implement mysql database backup and linux automatic scheduled backup code in php

The article introduces two types of database filing codes, one is a commonly used database backup class written by us in PHP, and the other is a code for automatic scheduled backup of mysql database for Linux friends. Students in need can refer to it. one time.

Save the following php code into the backdata.class.php file

The code is as follows

Copy code

/*
*
*A simple 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==''){//When the table does not exist
                       continue;
            }
                                                                      //**********************The following is the first half of the SQL**************
//If the table exists, delete it first
                 $sqls .= "DROP TABLE IF EXISTS $tablename;n";
//Read table structure
                $rs = mysql_query("SHOW CREATE TABLE $tablename",$this->mysql_link);
                $row=mysql_fetch_row($rs);
//Get the table structure to form SQL
                $sqls.=$row['1'].";nn";
               unset($rs);
               unset($row);
                                                                      //************************The following is the second half of the SQL**************
//Search for all data in the table
                $rs=mysql_query("select * from $tablename",$this->mysql_link);
//Number of fields in the table
               $field=mysql_num_fields($rs);
//Form this SQL statement: "INSERT INTO `groups` VALUES('1499e0ca25988d','Director','','0');"
while($rows=mysql_fetch_row($rs)){
$ COMMA = ''; // The 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 file
          $filehandle = fopen($backfilepath, "w");
            fwrite($filehandle, $sqls);
           fclose($filehandle);
}
Private function delarray($array){ //Process the incoming array
foreach($array as $tables){
If ($ tables == '*') {// All tables (you cannot form an array in a conventional manner when obtaining the name of the table)
$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 create a new file and save it in the same directory as background.class.php

How to use:

The code is as follows

代码如下

复制代码

require_once("backdata.class.php");
$link = @mysql_connect("localhost","数据库名","密码") or die ('Could not connect to server.');
mysql_query("use cms",$link);
mysql_query("set names utf8",$link);
$dbbck=new backupData($link);//实例化它，只要一个链接标识就行了
//备份数据时，如想备份一个数据库中的所有表，你可这样写：
$dbbck->backupTables("cms","./",array('*'));
//备份数据时，如想备份一个数据库中的仅一个表时，你可这样写：
$dbbck->backupTables("cms","./",array('user'));
//备份数据时，如想备份一个数据库中的多个表时，你可这样写：
$dbbck->backupTables("cms","./",array('user','acl','informatoin'));
//注解：$dbbck->backupTables("参1","参2",array());中，

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, just a link identifier is enough //When backing up data, if you want to back up all tables in a database, you can write like this: $dbbck->backupTables("cms","./",array('*')); //When backing up data, if you want to back up only one table in a database, you can write like this: $dbbck->backupTables("cms","./",array('user')); //When backing up data, if you want to back up multiple tables in a database, you can write like 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 (i.e. directory address)
The third one is: Which tables do you want to save

The following is the code for automatic scheduled backup in Linux

After referring to many tutorials on the Internet, plus my own testing, the following script has been tested and works.

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 #数据库用户名" >用户名
PASSWORD=××××× #数据库用户密码
DATABASE=TIENIUZAI #数据库名称
WEBMASTER=tieniuzai@qq.com #管理员邮箱地址，用以发送备份失败消息提醒
BACKUP_DIR=/var/www/Data_Backup/topons/ #备份文件存储路径
LOGFILE=/var/www/Data_Backup/topons/data_backup.log #日记文件路径
DATE=`date ‘+%Y%m%d-%H%M’` #日期格式（作为文件名）
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 参数详情见帮助 mysqldump －help

#判断备份文件存储目录是否存在，否则创建该目录
if [ ! -d $BACKUP_DIR ] ;
then
mkdir -p “$BACKUP_DIR”
fi

#开始备份之前，将备份信息头写入日记文件
echo ” ” >> $LOGFILE
echo ” ” >> $LOGFILE
echo “———————————————–” >> $LOGFILE
echo “BACKUP DATE:” $(date +”%y-%m-%d %H:%M:%S”) >> $LOGFILE
echo “———————————————– ” >> $LOGFILE

#切换至备份目录
cd $BACKUP_DIR
#使用mysqldump 命令备份制定数据库，并以格式化的时间戳命名备份文件
mysqldump $OPTIONS > $DUMPFILE
#判断数据库备份是否成功
if [[ $? == 0 ]]; then
#创建备份文件的压缩包
tar czvf $ARCHIVE $DUMPFILE >> $LOGFILE 2>&1
#输入备份成功的消息到日记文件
echo “[$ARCHIVE] Backup Successful!” >> $LOGFILE
#删除原始备份文件，只需保留数据库备份文件的压缩包即可
rm -f $DUMPFILE
else
echo “Database Backup Fail!” >> $LOGFILE

#备份失败后向网站管理者发送邮件提醒，需要mailutils或者类似终端下发送邮件工具的支持
#mail -s “Database:$DATABASE Daily Backup Fail” $WEBMASTER
fi
#输出备份过程结束的提醒消息
echo “Backup Process Done”

#!/bin/bash #Shell Command For Backup MySQL Database Everyday Automatically By Crontab #Author : Carlos Wong #Date : 2010-08-24 #Configuration parameters USER=root #Database username" >Username PASSWORD=××××× #Database user password DATABASE=TIENIUZAI #Database name WEBMASTER=tieniuzai@qq.com #Administrator’s email address, used to send backup failure message reminders BACKUP_DIR=/var/www/Data_Backup/topons/ #Backup file storage path LOGFILE=/var/www/Data_Backup/topons/data_backup.log #Log file path DATE=`date ‘+%Y%m%d-%H%M’` #Date format (as file name) DUMPFILE=$DATE.sql #Backup file name ARCHIVE=$DATE.sql.tgz #Compressed file name OPTIONS="-u$USER -p$PASSWORD –opt –extended-insert=false –triggers=false -R –hex-blob –flush-logs –delete-master-logs -B $DATABASE” #mysqldump See help for parameter details mysqldump -help # Determine whether the backup file storage directory exists, otherwise create the directory if [ ! -d $BACKUP_DIR ] ; then mkdir -p “$BACKUP_DIR” fi # Before starting the backup, write the backup information header into the journal file echo ” ” >> $LOGFILE echo ” ” >> $LOGFILE echo “——————————————————” >> $LOGFILE echo “BACKUP DATE:” $(date +”%y-%m-%d %H:%M:%S”) >> $LOGFILE echo “——————————————————” >> $LOGFILE #Switch to backup directory cd $BACKUP_DIR #Use the mysqldump command to back up the specified database and name the backup file with a formatted timestamp mysqldump $OPTIONS > $DUMPFILE #Determine whether the database backup is successful if [[ $? == 0 ]]; then #Create a compressed package of the backup file tar czvf $ARCHIVE $DUMPFILE >> $LOGFILE 2>&1 #Enter the backup successful message to the journal file echo “[$ARCHIVE] Backup Successful!” >> $LOGFILE #Delete the original backup file, just keep the compressed package of the database backup file rm -f $DUMPFILE else echo “Database Backup Fail!” >> $LOGFILE #Send an email reminder to the website administrator after the backup fails, which requires the support of mailutils or a similar terminal email tool #mail -s “Database:$DATABASE Daily Backup Fail” $WEBMASTER fi #Output a reminder message for the end of the backup process echo “Backup Process Done”

Use:

Save the above code to: /usr/sbin/DataBackup (The file name is arbitrary, as long as it does not have the same name as the original command of the system; the code can be placed anywhere, placed in the sbin directory just for convenience of execution, sbin Files/directories under the directory can be called directly in the terminal, similar to the directory specified by the PATH variable under Windows)
Add executable permissions to the script: sudo chmod +x /usr/sbin/DataBackup
Execute the script: sudo DataBackup
If you need to execute the backup command regularly, just put the following code into the crontab file (sudo vim /etc/crontab):
01 3 * * * root /usr/sbin/DataBackup #It means that the DataBackup script will be executed at 3 o'clock every day

A little attention:

The format of variables defined by shell scripts under Linux is: key=value . Note that there must be no spaces before and after the "=" between them, otherwise the system cannot confirm the variable.
In the line marked in red, the first single quote-like character "`" is actually not a single quote. Its input key is below the ESC key on the keyboard.
This script is only suitable for the backup of some small sites, because it backs up the entire database rather than incremental backup, and is not suitable for large-capacity database backup.

Compared to the two methods, scheduled backup is the best method. This can save time and allow the machine to automatically register when the least number of people access it. The PHP backup class requires manual operation. Of course, it can also be implemented using scheduled tasks under Windows. .

Implement mysql database backup and linux automatic scheduled backup code in php_PHP tutorial