Linux crontab+php implementation of MySQL database scheduled backup

Introduction

Database backup in large projects is essential, or if there is a big problem on GG, although this article is about using PHP to implement database scheduled backup, but this is not a good solution

To regularly back up the database, the quickest and safest way is to use shell script, powerful operation convenient, and execution speed extremely fast, unlike PHP also need to be parsed by Apache.

Of course, whether it is with PHP scheduled backup, or shell script scheduled backup, can not be separated from crontab this thing, after all, it is the real timer, here a rough talk about Crontab bar

Crontab Introduction to use

Crontab Common basic commands

• CRONTAB-E//Edit timed tasks, open by default with VI
• Crontab-l//list Current Scheduled tasks
• Crontab-r//Delete a scheduled task

Task format:

*    *    *    *    *       program                 分   时    日   月   星期       命令

Crontab there is not much to say, interested can search the relevant information.

Of course, there are commands for MySQL database backup

• Mysqldump-h host-u user_name-ppassword database_name > FileName Backup full data
• Mysqldump-h host-u User_name-ppassword database_name–no-data > filename only back up table structure
• Mysqldump-h host-u User_name-ppassword database_name–no-create-info > filename only backs up data

Note: There are no spaces in the middle of the-ppassword

Here are some of the profiles, not detailed enough, but enough to use, the next look at the code:

Related code

db_config.php

<?php return Array(' Db_host '=' 127.0.0.1 ',//Host address    ' db_name '=' Root ',//Database name    ' Db_user '=' Root ',//username    ' Db_pwd '=' Root ',//password    ' Db_back_path '='/home/db_back ',//backup directory with absolute path recommended    ' Back_file_suffix '=' AAA ',//The suffix name of the backup file    //type = 1 back all backup data and table structure    //type = 2 Just back table structure only backs up tables structure    //type = 3 Just back table data only backs up tables    ' Back_type '=1)?>

db_config.php

<?php  class db_back {    //Here is the default configuration    Private $db _config=Array(' Db_host '=' 127.0.0.1 ',' db_name '=' Ftdtian ',' Db_user '=' Root ',' Db_pwd '=' 123 ',' Db_back_path '='/home/db_back ',' Back_file_suffix '=' Bak ',//type = 1 back all        //type = 2 Just back table structure        //type = 3 Just back table data        ' Back_type '=3);//backup file name    Private $file _name;//shell Command    Private $back _shell; Public  function __construct() {        //Unlimited script timeSet_time_limit (0);//Set time zoneDate_default_timezone_set (' PRC ');//merge configuration file        $this->db_config = Array_merge ($this->db_config,require "db_config.php");$this->set_config (); }//configuration config    Private  function set_config() {           //Current time        $date _format= Date ("Y-m-d-h:i:s", time ());$common _shell="Mysqldump-h%s-u%s-p%s%s";//default backup file name (backup table structure and data)        $file _format=$date _format.' _all. '.$this->db_config[' Back_file_suffix '];//default backup of all shells        $this->back_shell =$common _shell.' >%s ';Switch($this->db_config[' Back_type ']) { Case ' 1 ': Break; Case ' 2 '://Backup table structure only                $file _format=$date _format.' _table_structure. '.$this->db_config[' Back_file_suffix '];$this->back_shell =$common _shell.'--no-data >%s '; Break;//Back up table data only             Case ' 3 ':$file _format=$date _format.' _table_data. '.$this->db_config[' Back_file_suffix '];$this->back_shell =$common _shell.'--no-create-info >%s ';default: Break; }$this->db_config[' Db_back_path '] =$this->db_config[' Db_back_path ']. Directory_separator.date ("Y-m-d", time ());//Create a folder        $this->make_dir ($this->db_config[' Db_back_path ']);//Build file Full path        $this->file_name =$this->db_config[' Db_back_path ']. Directory_separator.$file _format; }//Create a folder    Private  function make_dir($path , $mode = 0755, $recursive = true) {        if(!is_dir ($path))        {Try{mkdir ($path,$mode,$recursive); chmod$path,$mode); }Catch(Exception $e)            {//This can be written in log, no more writing                Echo $e->getmessage (); }        }return true; }//Start Backup     Public  function start_back() {        //String format name generates shell command        $shell= sprintf ($this->back_shell,$this->db_config[' Db_host '],$this->db_config[' Db_user '],$this->db_config[' Db_pwd '],$this->db_config[' db_name '],$this->file_name);Try{//Execute ShellShell_exec ($shell); }Catch(Exception $e)        {Echo $e->getmessage (); }    }}$obj=NewDb_back ();$obj->start_back ();?>

Put these two PHP files in the same directory, we assume under/var/www/html/back/

crontab -e//每天凌晨两点半执行备份302/usr/bin/php  /var/www/html/back/DB_back.php

The whole process is like this, if you need to modify the corresponding configuration, please modify the db_config.php configuration file directly

Note:

• When using PHP's mkdir, you need to determine whether the parent directory of the current directory has the appropriate write permissions, if not, enter the terminal to chmod the parent directory, otherwise the directory will not be created successfully

The code does not write well, if there are bugs or suggestions, thanks for correcting

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Linux crontab+php implementation of MySQL database scheduled backup