Community Learn Tools Library Leisure
search
English
Home > php教程 > php手册 > body text

MySQL备份脚本，mysql脚本

WBOY
Release： 2016-06-13 09:29:43
Original
951 people have browsed it

MySQL备份脚本，mysql脚本

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:\xampp\php\php.exe -q D:\wamp\www\php_lib\basic\mysqlbackup.php
pause;

linux系统shell备份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

另为一篇shell备份mysql脚本:http://www.cnblogs.com/luoyunshu/p/3435378.html

linux系统下mySQL数据库 备份方法与脚本？

方法一、适合所有格式的mysql数据库,通过对数据库导出导进写个脚本定时执行：
1.导出整个数据库 mysqldump -u 用户名 -p 数据库名 > 导出的文件名 mysqldump -u wcnc -p smgp_apps_wcnc > /存放路径/wcnc.sql
2.导出一个表 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 mysqldump -u wcnc -p smgp_apps_wcnc users> /存放路径/wcnc_users.sql
3.导出一个数据库结构 mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >/存放路径/wcnc_db.sql
定义：
-d 没有数据
--add-drop-table 在每个create语句之前增加一个drop table
4.导入数据库 常用source 命令 进入mysql数据库控制台：
如mysql -u root -p mysql>use 数据库

方法二、针对mysql数据表格式为MyISAM的
假如数据文件在/var/lib/mysql
那么直接写个脚本
cp -r /var/lib/mysql /备份到的文件夹路径

隔机备份用rsync增量，或定时完整备份。
 

谁可以给我一个MYSQL自动备份的脚本

可能是你不会用吧

可以将这个脚本放进crontab，每天凌晨执行一次，自动备份

这个脚本每天最多只执行一次，而且只保留最近五天的备份在服务器上。

代码:

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

#Setting
#设置数据库名，数据库登录名，密码，备份路径，日志路径，数据文件位置，以及备份方式
#默认情况下备份方式是tar，还可以是mysqldump,mysqldotcopy
#默认情况下，用root(空)登录mysql数据库，备份至/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......余下全文>>
 

Related labels：
mysql database
source：php.cn
Previous article：如何学习PHP array_flip() Next article：PHP缓存机制之Output Control，outputcontrol
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
Regular expression to match words I have a script where I am trying to match new job names with existing job names in a data...
From 2024-04-06 21:24:04
0
1
606
Calculate the sum of fields in another table using MySQL SQL query I have a schema like this: User table with attributes "user_id" and "userna...
From 2024-04-06 19:39:29
0
1
441
MySQL: "Select text from... as <variable here or subquery>" I have the following table with the following data: id text language 1 german text german ...
From 2024-04-06 19:25:54
0
2
529
How to group and count in MySQL? I'm trying to write a query that extracts the total number of undeleted messages sent to f...
From 2024-04-06 18:30:17
0
1
353
MySQL gets data from multiple tables I have a eg_design table which contains the following columns: and eg_domains table which ...
From 2024-04-06 18:42:44
0
2
479
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