简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Heim > php教程 > php手册 > Hauptteil

MySQL备份脚本，mysql脚本

WBOY
Freigeben： 2016-06-13 09:29:43
Original
935 Leute haben es durchsucht

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>?>
Nach dem Login kopieren

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>
Nach dem Login kopieren

另为一篇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......余下全文>>
 

Verwandte Etiketten：
mysql数据库
Quelle：php.cn
Vorheriger Artikel：如何学习PHP array_flip() Nächster Artikel：PHP缓存机制之Output Control，outputcontrol
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
Aktuelle Ausgaben
Regulärer Ausdruck zum Abgleichen von Wörtern Ich habe ein Skript, in dem ich versuche, neue Jobnamen mit vorhandenen Jobnamen in einer ...
Aus 2024-04-06 21:24:04
0
1
606
Berechnen Sie die Summe der Felder in einer anderen Tabelle mithilfe einer MySQL-SQL-Abfrage Ich habe ein Schema wie dieses: Benutzertabelle mit den Attributen „user_id“ und „username...
Aus 2024-04-06 19:39:29
0
1
441
MySQL: „Wählen Sie Text aus... als <Variable hier oder Unterabfrage>' Ich habe die folgende Tabelle mit den folgenden Daten: id Textsprache 1 Deutscher Text Deu...
Aus 2024-04-06 19:25:54
0
2
529
Wie gruppiere und zähle ich in MySQL? Ich versuche, eine Abfrage zu schreiben, die die Gesamtzahl der nicht gelöschten Nachricht...
Aus 2024-04-06 18:30:17
0
1
353
MySQL ruft Daten aus mehreren Tabellen ab Ich habe eine Tabelle „eg_design“, die die folgenden Spalten enthält: und eine Tabelle „eg...
Aus 2024-04-06 18:42:44
0
2
479
verwandte Themen
Mehr>
Beliebte Empfehlungen
Beliebte Tutorials
Mehr>
Verwandte Tutorials
Beliebte Empfehlungen
Aktuelle Kurse
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage
Über uns Haftungsausschluss Sitemap
Chinesische PHP-Website：Online-PHP-Schulung für das Gemeinwohl，Helfen Sie PHP-Lernenden, sich schnell weiterzuentwickeln！