数据库的备份及恢复

突然想起以前laravel框架里做的数据库的备份和恢复，现在拿来和大家一起分享一下

首先先说下数据库的备份吧

   /**
     * 数据库备份
     */
    public function backupsData()
    {
        $sql_filename="ftdm_".date("YmdHis")."."."sql";
        $to_file_name = "database/".$sql_filename;
        $tables = DB::select("SHOW TABLES");
        $object =  json_decode( json_encode( $tables),true);

        //print_r($object);die;

        $names = array();
        foreach($object as $k => $v){
            $names[] = $v["Tables_in_cms"];
            }

        echo "运行中，请耐心等待...<br/>";

        $info = "-- ----------------------------
";
        $info .= "-- 日期：".date("Y-m-d H:i:s",time())."
";
        $info .= "-- 李凯
";
        $info .= "-- 备份
";
        $info .= "-- ----------------------------

";
        file_put_contents($to_file_name,$info,FILE_APPEND);

        //将每个表的表结构导出到文件
        foreach($names as $val) {

            $sql = "show create table " . $val;
            //$res = mysql_query($sql,$link);
            $ress = DB::select($sql);
            $res = json_decode(json_encode($ress), true);
            //print_r($res);die;
            // $row = mysql_fetch_array($res);
            $info = "-- ----------------------------
";
            $info .= "-- Table structure for `" . $val . "`
";
            $info .= "-- ----------------------------
";
            $info .= "DROP TABLE IF EXISTS `" . $val . "`;
";
            $sqlStr = $info . $res[0]["Create Table"] . ";

";
            //追加到文件
            file_put_contents($to_file_name, $sqlStr, FILE_APPEND);

        }

            //将每个表的数据导出到文件
            foreach($names as $val){
                $sql = "select * from ".$val;
                $ress = DB::select($sql);
                $res = json_decode( json_encode( $ress),true);
                //如果表中没有数据，则继续下一张表
                if(count($res)<1) continue;
                //
                $info = "-- ----------------------------
";
                $info .= "-- Records for `".$val."`
";
                $info .= "-- ----------------------------
";
                file_put_contents($to_file_name,$info,FILE_APPEND);
                //读取数据


                foreach($res as $z){
                    $sqlStr = "INSERT INTO `".$val."` VALUES (";
                    foreach($z as $zd){
                        $sqlStr .= """.$zd."", ";
                    }
                    //去掉最后一个逗号和空格
                    $sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);
                    $sqlStr .= ");
";
                    file_put_contents($to_file_name,$sqlStr,FILE_APPEND);
                }


                file_put_contents($to_file_name,"
",FILE_APPEND);

            }

         //将备份信息入库
        $file_size = filesize("database/".$sql_filename);
        $times=date("Y-m-d H:i:s");
        $re = DB::table("database_backups")->insert(
            [
                "file_name" => $sql_filename,
                "file_size" => $file_size,
                "backups_time" => $times,
            ]);
        if($re){

            echo "<script>alert("备份成功");location.href="showdatabase"</script>";


        }

    }


    /**
     * @return IlluminateContractsViewFactory|IlluminateViewView
     */
    public function showRecover(){

        $re=DB::table("database_backups")->orderBy("sid", "desc")->paginate(5);;
        return view("Admin/database.recover",["re"=>$re]);

    }


    public function renewData(){
        header("content-type:text/html;charset=utf-8");
        $id=Request::input("id");
        $results = DB::select("select * from database_backups where sid = :id", ["id" => $id]);
        $filename=$results[0]->file_name;
        $dbname="cms";
        $mysql_file="database/".$filename; //指定要恢复的MySQL备份文件路径,请自已修改此路径

            if (file_exists($mysql_file)) {
                $sql_value="";
                $cg=0;
                $sb=0;
                $sqls=file($mysql_file);
                foreach($sqls as $sql)
                {
                    $sql_value.=$sql;

                }
               // print_r($sql_value);die;
                $a=explode(";
", $sql_value); //根据";
"条件对数据库中分条执行
                $total=count($a)-1;
                DB::select("set names "utf8"");
                for ($i=0;$i<$total;$i++)
                {
                    DB::select("set names "utf8"");
                    //执行命令
                    if(DB::select($a[$i]))
                    {
                        $cg+=1;
                    }
                    else
                    {
                        $sb+=1;
                        $sb_command[$sb]=$a[$i];
                    }
                }
                echo "操作完毕，共处理 $total 条命令，成功 $sb 条，失败 $cg 条--------";
                echo "<a href="showdatabase">点击返回</a>";

                //显示错误信息
                if ($sb>0)
                {
                    echo "<hr><br><br>成功命令如下：<br>";
                    for ($ii=1;$ii<=$sb;$ii++)
                    {
                        echo "<p><b>第 ".$ii." 条命令（内容如下）：</b><br>".$sb_command[$ii]."</p><br>";
                    }
                }  //-----------------------------------------------------------
            }else{
                echo "MySQL备份文件不存在，请检查文件路径是否正确！";
            }




    }