<?php namespace app\api\controller; use think\Controller; use think\Db; use think\Log; class Databackup extends Controller { /*** * * 【步骤一】从线上备份数据表 * */ public function redrict(){ $doc_root=$_SERVER['DOCUMENT_ROOT']; $file_path_name=$doc_root.'/sqlbackup'; if(!file_exists($file_path_name)){ mkdir($file_path_name,0777); } //mysqldump.exe的绝对路径,安装mysql自带的有,可以搜索一下路径 $mysqldump_url='/www/server/mysql/bin/mysqldump'; $host='';//数据库所在的服务器地址 $User='root';//数据库用户名 $Password='123321';//数据库密码 $databaseName='';//数据库名 //【important】这里把表分开来执行,一次性执行会报错。 // $tables = 'eb_driverinfo,eb_drivingorder_ton,eb_mine_field,eb_china_code'; $tablesArray = explode(',',$tables); foreach ($tablesArray as $items){ $name = $items; $process=$mysqldump_url." -h".$host." -u".$User." -p".$Password." ".$databaseName." ".$name." >".$file_path_name."/".$name.'.sql'; $er=system($process); if($er!==false){ echo $name.':导出成功<br/>'; }else{ echo $name.':导出失败<br/>'; } } } /*** * * 【步骤二】把导出的sql文件准成数据表 * * */ public function executeSqlfile(){ $tables = 'eb_driverinfo,eb_drivingorder_ton,eb_mine_field,eb_china_code'; // $tables = 'eb_business_mix,eb_fixed_order,eb_fixed_suborder,eb_car_plate,eb_orderinfo_ton'; $tablesArray = explode(',',$tables); foreach ($tablesArray as $items){ $name = $items; $_sql = file_get_contents('/www/wwwroot/ceshicn/sqlbackup/'.$name.'.sql'); //创建表格的语句 $createSql = self::cut('CREATE TABLE','DEFAULT CHARSET=utf8',$_sql); // dump($createSql); $createSql = 'CREATE TABLE '.$createSql.' DEFAULT CHARSET=utf8;'; $userids = Db::connect('db4')->execute($createSql); if($userids){ echo $name.':创建成功<br/>'; }else{ echo $name.':创建失败<br/>'; } // exit; //写入的语句 $start = substr($_sql,stripos($_sql,"INSERT INTO")); $result = substr($start,0,strrpos($start,");")); $result = $result.');'; $result = explode('INSERT INTO', $result); foreach ($result as $_value) { if($_value){ $sql = 'INSERT INTO '.$_value.';'; $userids = Db::connect('db4')->execute($sql); if($userids){ echo $name.':写入成功<br/>'; }else{ echo $name.':写入失败<br/>'; } } } } } /*** * * 【步骤四】把这个环境的eb_driver_systemlabel表转储成文件 * * */ public function redrict2(){ $doc_root=$_SERVER['DOCUMENT_ROOT']; $file_path_name=$doc_root.'/sqlbackup'; if(!file_exists($file_path_name)){ mkdir($file_path_name,0777); } $mysqldump_url='/www/server/mysql/bin/mysqldump';//mysqldump.exe的绝对路径,安装mysql自带的有,可以搜索一下路径 $host='localhost';//数据库所在的服务器地址 $User='';//数据库用户名 $Password='';//数据库密码 $databaseName='';//数据库名 $tables = 'eb_driver_systemlabel'; $tablesArray = explode(',',$tables); foreach ($tablesArray as $items){ $name = $items; $process=$mysqldump_url." -h".$host." -u".$User." -p".$Password." ".$databaseName." ".$name." >".$file_path_name."/".$name.'.sql'; $er=system($process); if($er!==false){ echo $name.':导出成功<br/>'; }else{ echo $name.':导出失败<br/>'; } self::executeSqlfileToData($name); } } /*** * * 【步骤五】把转储的sql文件更新到线上数据库 * * */ public static function executeSqlfileToData($tables){ $tablesArray = explode(',',$tables); foreach ($tablesArray as $items){ $name = $items; $_sql = file_get_contents('/www/wwwroot/ceshicn/sqlbackup/'.$name.'.sql'); //创建表格的语句 $createSql = self::cut('CREATE TABLE','DEFAULT CHARSET=utf8',$_sql); // dump($createSql); $createSql = 'CREATE TABLE '.$createSql.' DEFAULT CHARSET=utf8;'; $userids = Db::connect('www')->execute($createSql); if($userids){ echo $name.':创建成功<br/>'; }else{ echo $name.':创建失败<br/>'; } // exit; //写入的语句 $start = substr($_sql,stripos($_sql,"INSERT INTO")); $result = substr($start,0,strrpos($start,");")); $result = $result.');'; $result = explode('INSERT INTO', $result); foreach ($result as $_value) { if($_value){ $sql = 'INSERT INTO '.$_value.';'; $userids = Db::connect('www')->execute($sql); if($userids){ echo $name.':写入成功<br/>'; }else{ echo $name.':写入失败<br/>'; } } } } } public static function cut($begin,$end,$str){ $b = mb_strpos($str,$begin) + mb_strlen($begin); $e = mb_strpos($str,$end) - $b; return mb_substr($str,$b,$e); } }