简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Home > Backend Development > PHP Tutorial > body text

MySQL backup script, mysql script_PHP tutorial

WBOY
Release： 2016-07-13 10:23:58
Original
1166 people have browsed it

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>?>
Copy after login

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>
Copy after login

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>>

www.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...
Related labels：
mysql database
source：php.cn
Previous article：ThinkPHP notes - enable debug debugging mode, thinkphpdebug_PHP tutorial Next article：How to learn PHP array_flip()_PHP tutorial
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
How to list data in a section by ID using while loop in PHP? I have a mysql table with these columns: series_id, series_color, product_name In the outp...
From 2023-11-17 20:03:03
0
1
290
How to make table cell values ​​hyperlinked in Dash? (Using Plotly, Dash, Pandas, etc.) I want to set the cell value under the "JobLink" column as a hyperlink. When I c...
From 2023-11-17 18:47:10
0
1
283
Best way to preload route data before accessing the route. Before rendering the page for a given route, I want to first get the necessary data synchr...
From 2023-11-17 14:54:42
0
2
379
New title: New script does not correctly display strange character encoding of stored data I'm trying to rewrite an old website. It is in Persian and uses Persian/Arabic characters....
From 2023-11-17 10:51:01
0
2
273
Nuxt.js SSG (Static Site Generator) Get API Data When building a project using a static site generator, I understand that documentation cre...
From 2023-11-16 21:36:07
0
1
176
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！