MySQL backup script, mysql script_PHP tutorial

MySQL backup script, mysql script

mysqlbackup.php:

<?<span>php

    </span><span>//</span><span>备份mysql</span>
    
    <span>set_time_limit</span>(0<span>);
    date_default_timezone_set(</span>'PRC'<span>);
    
    </span><span>//</span><span>配置</span>
    <span>$configs</span> = <span>array</span><span>(
        </span>'host1'=><span>array</span><span>(
            </span>'localhost',
            'root',
            'root',
            <span>array</span>(),  <span>//</span><span>为空备份全部数据库，否则备份这些数据库</span>
            'D:/xampp/mysql/bin/mysqldump',    <span>//</span><span>备份工具</span>
            <span>dirname</span>(<span>__FILE__</span>)."/localhost",  <span>//</span><span>目录加主机名</span>
            5,  <span>//</span><span>删除前5天的SQL文件</span>
        ),<span>);

    </span><span>foreach</span>(<span>$configs</span> <span>as</span> <span>$config</span><span>) {
        </span><span>$logsfile</span> = <span>$config</span>[5].'/'.<span>date</span>('ymd').'.log'<span>;
        logs(</span><span>$logsfile</span>, <span>$config</span>[0]." backup\n"<span>);
        backup(</span><span>$config</span><span>);
    }

    </span><span>function</span> backup(<span>$config</span><span>) {
        </span><span>list</span>(<span>$host</span>, <span>$username</span>, <span>$password</span>, <span>$databases</span>, <span>$backuptool</span>, <span>$backupdir</span>, <span>$day</span>) = <span>$config</span><span>;
        </span><span>$command</span> = "<span>$backuptool</span> -u <span>$username</span> -h <span>$host</span> -p<span>$password</span> %s > %s"<span>;  
        </span><span>$logsfile</span> = <span>$backupdir</span>.'/'.<span>date</span>('ymd').'.log'<span>;
        </span><span>$backfilename</span> = <span>$backupdir</span>.'/'.<span>date</span>('Ymd')."%s.sql";   <span>//</span><span>备份的SQL文件，以数据库命名</span>
        
        <span>if</span>(!<span>is_dir</span>(<span>$backupdir</span><span>)) {
            </span><span>mkdir</span>(<span>$backupdir</span>, 0755 , <span>true</span><span>); 
        }
        
        </span><span>//</span><span>删除前十天的备份文件</span>
        get_dir_files(<span>$backupdir</span>, <span>$returnval</span><span>);
        </span><span>if</span>(<span>$returnval</span><span>) {
            </span><span>foreach</span>(<span>$returnval</span> <span>as</span> <span>$v</span><span>) {
                </span><span>$time</span> = <span>filemtime</span>(<span>$v</span><span>);
                </span><span>if</span>(<span>$time</span> < <span>strtotime</span>("-<span>$day</span> day") && (<span>pathinfo</span>(<span>$v</span>,PATHINFO_EXTENSION))=='zip'<span>) {
                    </span><span>unlink</span>(<span>$v</span><span>);
                }
            }
        }
        
        </span><span>if</span>(!<span>$databases</span><span>) {
            </span><span>$databases</span> = getdatabases(<span>$host</span>, <span>$username</span>, <span>$password</span><span>);
        }

        </span><span>//</span><span>开始备份</span>
        <span>foreach</span>(<span>$databases</span> <span>as</span> <span>$database</span><span>) {
            </span><span>$outputfile</span> = <span>sprintf</span>(<span>$backfilename</span>, <span>$database</span><span>);
            </span><span>$execcommand</span> = <span>sprintf</span>(<span>$command</span>, <span>$database</span>, <span>$outputfile</span><span>);

            </span><span>try</span><span> {
                </span><span>if</span>(<span>system</span>(<span>$execcommand</span>) === <span>false</span><span>) {
                    </span><span>throw</span> <span>new</span> <span>Exception</span>('execute backup command error!'<span>);
                }
                
                </span><span>//</span><span>文件过大时会压缩失败（测试的那个SQL文件4.62G，压缩失败，没创建那个压缩文件。测试2.81G可以）</span>
                <span>if</span>(<span>file_exists</span>(<span>$outputfile</span><span>)) {
                    </span><span>$zip</span> = <span>new</span><span> ZipArchive();
                    </span><span>$filename</span> = <span>pathinfo</span>(<span>$outputfile</span>,<span>PATHINFO_FILENAME);
                    </span><span>$zipname</span> = <span>$backupdir</span>.'/'.<span>$filename</span>.'.zip';  <span>//</span><span>zip文件的路径</span>
                    <span>if</span>(<span>$zip</span>->open(<span>$zipname</span>, ZIPARCHIVE::OVERWRITE) === <span>true</span><span>) {
                        </span><span>$zip</span>->addFile(<span>$outputfile</span>, <span>$filename</span>.'.'.<span>pathinfo</span>(<span>$outputfile</span>,<span>PATHINFO_EXTENSION));
                        
                        </span><span>$zip</span>-><span>close();
                    }</span><span>else</span><span> {
                        </span><span>throw</span> <span>new</span> <span>Exception</span>('ZipArchive open error!'<span>);
                    }
                }
                
                </span><span>if</span>(!<span>file_exists</span>(<span>$zipname</span>) || (<span>filesize</span>(<span>$zipname</span>)==0<span>)) {
                    </span><span>throw</span> <span>new</span> <span>Exception</span>('ZipArchive create error!'<span>);
                }
                
                </span><span>$message</span> = <span>date</span>('Y-m-d H:i:s')."<span>$database</span> backup complete!\r\n"<span>;
                logs(</span><span>$logsfile</span>, <span>$message</span><span>);
            }</span><span>catch</span>(<span>Exception</span> <span>$e</span><span>) {
                </span><span>$message</span> = <span>date</span>('Y-m-d H:i:s').<span>$e</span>->getLine().' '.<span>$e</span>->getMessage()."\r\n"<span>;
                logs(</span><span>$logsfile</span>, <span>$message</span><span>);
            }

        }
    }

    </span><span>function</span> getdatabases(<span>$host</span>, <span>$username</span>, <span>$password</span><span>) {
        </span><span>$databases</span> = <span>array</span><span>();
        
        </span><span>try</span><span> {
            </span><span>$mysqli</span> = <span>new</span> Mysqli(<span>$host</span>, <span>$username</span>, <span>$password</span><span>);
            </span><span>$result</span> = <span>$mysqli</span>->query("show databases"<span>);
            </span><span>if</span>(<span>$result</span><span>) {
                </span><span>while</span>(<span>$row</span> = <span>$result</span>-><span>fetch_row()) {
                    (</span><span>current</span>(<span>$row</span>)!='information_schema' && <span>current</span>(<span>$row</span>)!='mysql' && <span>current</span>(<span>$row</span>)!='performance_schema') && <span>$databases</span>[] = <span>current</span>(<span>$row</span><span>);
                }
                </span><span>$result</span>-><span>free_result();
                </span><span>$mysqli</span>-><span>close();
            }</span><span>else</span><span> {
                </span><span>throw</span> <span>new</span> <span>Exception</span>('No databases!'<span>);
            }
        }</span><span>catch</span>(<span>Exception</span> <span>$e</span><span>) {
            </span><span>$message</span> = <span>date</span>('Y-m-d H:i:s').<span>$e</span>->getLine().' '.<span>$e</span>->getMessage()."\r\n"<span>;
            logs(</span><span>$logsfile</span>, <span>$message</span><span>);
        }    
        
        </span><span>return</span> <span>$databases</span><span>;
    }
    
    </span><span>function</span> logs(<span>$file</span>, <span>$contents</span><span>) {
        </span><span>$dirname</span> = <span>dirname</span>(<span>$file</span><span>);
        </span><span>try</span><span> {
            </span><span>if</span>(!<span>is_dir</span>(<span>dirname</span>(<span>$file</span>)) || !<span>is_writeable</span>(<span>dirname</span>(<span>$file</span><span>))) {
                </span><span>throw</span> <span>new</span> <span>Exception</span>("file is not direcotory or file can't write"<span>);
            }
            </span><span>file_put_contents</span>(<span>$file</span>, <span>$contents</span>,<span> FILE_APPEND);
        }</span><span>catch</span>(<span>Exception</span> <span>$e</span><span>) {
            </span><span>$message</span> = <span>date</span>('Y-m-d H:i:s').<span>$e</span>->getLine.' '.<span>$e</span>->getMessage()."\r\n"<span>;
            logs(</span><span>$logfile</span>, <span>$message</span><span>);
        }
    }
    
    
    </span><span>//</span><span>获取当前目录下的文件（不包含子文件夹）</span>
    <span>function</span> get_dir_files(<span>$currPath</span>, &<span>$returnVal</span>=<span>array</span><span>()) {
        </span><span>if</span>(<span>is_dir</span>(<span>$currPath</span><span>)) {
            </span><span>$currPath</span> = (<span>substr</span>(<span>$currPath</span>,-1,1)=='/')?<span>substr</span>(<span>$currPath</span>,0,<span>strlen</span>(<span>$currPath</span>)-1):<span>$currPath</span><span>;

            </span><span>if</span>(<span>$handler</span> = <span>opendir</span>(<span>$currPath</span><span>)) {
                </span><span>while</span>((<span>$fileName</span> = <span>readdir</span>(<span>$handler</span>)) !== <span>false</span><span>) {
                    </span><span>if</span>(<span>$fileName</span> != '.' && <span>$fileName</span> != '..' && <span>$fileName</span>[0] != '.'<span>) {
                        </span><span>if</span>(<span>is_file</span>(<span>$currPath</span> . '/' . <span>$fileName</span><span>)) {
                            </span><span>$returnVal</span>[] = <span>$currPath</span> . '/' . <span>$fileName</span><span>;
                        }
                    }
                }
                </span><span>closedir</span>(<span>$handler</span><span>);
            }
        }
    }
</span>?>

mysqlbackup.bat:

D:xamppphpphp.exe -q D:wampwwwphp_libbasicmysqlbackup.php
pause;

Linux system shell backup MySQL:

#!/bin/<span>sh</span><span>
# </span><span>sed</span> -i <span>'</span><span>s/^M//g</span><span>'</span> /home/taskschd/backup.<span>sh</span><span>
#注意：</span>^M的输入方式是 Ctrl + v ，然后Ctrl +<span> M
dbs</span>=<span>(test)
ROOT_DIR</span>=/home/backup/
<span>for</span> dbname <span>in</span><span> ${dbs[@]}
 </span><span>do</span><span>
  #备份数据
  BACKUP_DIR</span>=$ROOT_DIR$dbname<span>'</span><span>_</span><span>'</span>$(<span>date</span> +%Y%m%<span>d).sql
  </span>/usr/local/mysql/bin/mysqldump --opt -uroot -pabc $dbname ><span> $BACKUP_DIR
  #删除三天前数据
  delete_file</span>=$dbname<span>'</span><span>_</span><span>'</span>$(<span>date</span> -d <span>"</span><span>-5 day</span><span>"</span> <span>"</span><span>+%Y%m%d</span><span>"</span>)<span>'</span><span>.sql</span><span>'</span>  
  <span>rm</span><span> $ROOT_DIR$delete_file  
 </span><span>done</span>

Another shell backup mysql script: http://www.cnblogs.com/luoyunshu/p/3435378.html

mySQL database backup under linux system Methods and scripts?

Method 1. Suitable for all formats of mysql database, write a script to export and import the database and execute it regularly:
1. Export the entire database mysqldump -u username -p database name> exported file name mysqldump - u wcnc -p smgp_apps_wcnc > /storage path/wcnc.sql
2. Export a table mysqldump -u username -p database name table name> exported file name mysqldump -u wcnc -p smgp_apps_wcnc users> /storage path /wcnc_users.sql
3. Export a database structure mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >/storage path/wcnc_db.sql
Definition:
-d No data
--add-drop-table Add a drop table before each create statement
4. Import the common source command of the database into the mysql database console:
Such as mysql -u root -p mysql>use database

Method 2, for the mysql data table format is MyISAM
If the data file is in /var/lib/mysql
then write a script directly
cp -r /var/lib/mysql /Folder path to backup to

Use rsync incremental for offline backup, or scheduled full backup.

Who can give me a script for automatic backup of MYSQL

Maybe you don’t know how to use it

You can put this script into crontab and execute it once every morning for automatic backup

This script can only be executed once a day at most, and only the last five days of backup on the server.

Code:

#!/bin/bash
#This is a ShellScript For Auto DB Backup
#Powered by aspbiz
#2004-09

#Setting
#Set the database name, database login name, password, backup path, log path, data file location, and backup method
#The default backup method is tar, but it can also be mysqldump, mysqldotcopy
#By default, use root (empty) to log in to the mysql database and back up to /root/dbxxxxx.tgz
DBName=mysql
DBUser=root
DBPasswd=
BackupPath=/root/
LogFile=/root/db.log
DBPath=/var/lib/mysql/
#BackupMethod=mysqldump
#BackupMethod=mysqlhotcopy
#BackupMethod=tar
#Setting End

NewFile="$BackupPath"db$(date +%y%m%d).tgz
DumpFile="$BackupPath"db$(date +%y%m%d)
OldFile ="$BackupPath"db$(date +%y%m%d --date='5 days ago').tgz

echo "-------------- -----------------------------" >> $LogFile
echo $(date +"%y-%m -%d %H:%M:%S") >> $LogFile
echo "-------------------------- " >> $LogFile
#Delete Old File
if [ -f $OldFile ]
then
rm -f $OldFile >> $LogFile 2>&1
echo " [$OldFile]Delete Old File Success!" >> $LogFile
else
echo "[$OldFile]No Old Backup File!" >> $LogFile
fi

if [ -f $NewFile ]
then
echo "[$NewFile]The Backup File is exists,Can't Backup!" >> $LogFile
else
case $BackupMethod in
mysqldump)
if [ -z $DBPasswd ]
then
mysqldump -u $DBUser --opt $DBName > $DumpFile
else
mysqldump -u $DBUser -p $DBPasswd --opt $DBName > $DumpFile
f...The rest of the text>>

http://www.bkjia.com/PHPjc/834767.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/834767.htmlTechArticleMySQL backup script, mysql script mysqlbackup.php: ? php // Backup mysql set_time_limit (0 ); date_default_timezone_set( ' PRC' ); // Configuration $configs = array ( 'host1'= array ( 'localh...