Php implements mysql database backup and linux automatic timing backup code. This article introduces two types of database record filing code, one is a common database backup class written in php, the other is an automatic and regular mysql database backup code provided by linux friends. This article introduces two types of database filing code, one is a common database backup class written in php, the other is an automatic and scheduled mysql database backup code provided for linux friends. if you need it, you can refer to it.
Save the following php code as the backdata. class. php file.
| The code is as follows: |
|
|
/*
*
* A simple Mysql backup data Class
*
*/
Class backupData {
Private $ mysql_link; // link ID
Private $ dbName; // database name
Private $ dataDir; // directory where data is 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 = '') {// The table does not exist
Continue;
}
****** ********
// If a table exists, delete it first.
$ Sqls. = "drop table if exists $ tablename; n ";
// Read the table structure
$ Rs = mysql_query ("show create table $ tablename", $ this-> mysql_link );
$ Row = mysql_fetch_row ($ rs );
// Obtain the table structure as an SQL statement
$ Sqls. = $ row ['1']. "; nn ";
Unset ($ rs );
Unset ($ row );
// ************************ The second half of the SQL statement is formed ******* *******
// Query all the 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', 'Directory','', '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 a 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 (the table name cannot be an array in the conventional 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;
}
}
?> |
Create a new file and save it to the same directory as backdata. class. php.
Usage:
| The code is as follows: |
|
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. you only need a link ID.
// If you want to back up all the tables in a database during data backup, write as follows:
$ Dbbck-> backupTables ("cms", "./", array ('*'));
// When backing up data, if you want to back up only one table in a database, you can write as follows:
$ Dbbck-> backupTables ("cms", "./", array ('user '));
// When backing up data, if you want to back up multiple tables in a database, you can write as follows:
$ Dbbck-> backupTables ("cms", "./", array ('user', 'acl', 'In oin '));
// Annotation: $ dbbck-> backupTables ("parameter 1", "parameter 2", array,
|
Parameter 1: database name,
Parameter 2: location of the backup data to be stored (that is, the directory address)
Third: the tables you want to save
The code for automatic and scheduled backup in linux is as follows:
I have referenced many tutorials on the Internet and added my own tests. the following scripts are available for test.
| The code is as follows: |
|
|
#! /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 mailbox address to send backup failure message reminder
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 "# for details about mysqldump parameters, see help mysqldump-help
# Determine whether the backup file storage directory exists; otherwise, the directory is created.
If [! -D $ BACKUP_DIR];
Then
Mkdir-p "$ BACKUP_DIR"
Fi
# Before starting backup, write the backup information header to the log file
Echo ""> $ LOGFILE
Echo ""> $ LOGFILE
Echo "----------------"> $ LOGFILE
Echo "backup date:" $ (date + "% y-% m-% d % H: % M: % S") >>$ LOGFILE
Echo "----------------"> $ LOGFILE
# Switch to the backup directory
Cd $ BACKUP_DIR
# Use the mysqldump command to back up the database and name the backup file with the formatted timestamp
Mysqldump $ OPTIONS> $ DUMPFILE
# Determine whether the database backup is successful
If [[$? = 0]; then
# Create a compressed backup file package
Tar czvf $ ARCHIVE $ DUMPFILE >>$ LOGFILE 2> & 1
# Enter a message indicating a successful backup to the log file.
Echo "[$ ARCHIVE] Backup Successful !" >>$ LOGFILE
# Delete the original backup file. you only need to keep the compressed package of the database backup file.
Rm-f $ DUMPFILE
Else
Echo "Database Backup Fail !" >>$ LOGFILE
# Send an email notification to the website administrator after the backup fails. you need support from mailutils or similar email tools.
# Mail-s "Database: $ DATABASE Daily Backup Fail" $ WEBMASTER
Fi
# Output the reminder message for the backup process to end
Echo "Backup Process Done" |
Usage:
Save the above code to:/usr/sbin/DataBackup (the file name is random, as long as it does not have the same name as the original command of the system; the code can be put anywhere, put it in the sbin directory for easy execution. the files/directories under the sbin directory can be directly called on the terminal, similar to the directory specified by the PATH variable in windows)
Add executable permissions to the script: sudo chmod + x/usr/sbin/DataBackup
Execute the script: sudo DataBackup
If you need to regularly execute the backup command, you only need to put the following code in the crontab File (sudo vim/etc/crontab:
01 3 *** root/usr/sbin/DataBackup # it indicates that the DataBackup script will be executed at every day
Note the following:
In linux, shell scripts define variables in the format of key = value. Note that there is no space before and after "=" between the two. Otherwise, the system cannot confirm the variables.
For the line marked in red, the first single quotation mark character "'" is not a single quotation mark. its input key is below the ESC key on the keyboard.
This script is only suitable for backup of some small sites, because it backs up all the databases instead of incremental backup, and is not suitable for large-capacity database backup.
Compared with the two methods, regular backup is the best method, which can save time for the machine to automatically file records when the minimum number of people access, while php backup requires manual operations, you can also use scheduled tasks in windows.
...
