传统的数据库搬迁思路是把数据库表的结构及数据都查询出来，然后通过循环进行数据结构重组拼接。然后导出！数据量少的话，这样当然是没毛病。当数据量太大的时候，服务器的内存开销就吃不住了，很容易炸掉，导致服务器瘫掉。当然我之前也这么干的 ，所以也一直想办法解决这个问题 ，当你体会到大数据搬迁的那酸爽、提心吊胆你就明白了
一天我看到一条sql
SELECT COUNT( * ) as cnt FROM information_schema.TABLES WHERE TABLE_SCHEMA = 旧数据库名
CREATE TABLE IF NOT EXISTS 新数据库名.表名 LIKE 旧数据库名.表名
INSERT 新数据库名.表名 SELECT * FROM 旧数据库名.表名
相信看到这几条sql 就应该有思路了，没错就是这三条sql!
废话不说了 上代码
<?php
namespace action\swoole;
/**
* 换域名
*/
class changesiteAction extends \Action{
private $bucket = 'oss';
private $oldPrefix = '';
private $newPrefix = '';
private $maxkeys = 1000;
private $initDB = array('aaa',bbb',ccc','ccc'); //多分支数据库
private $upgrading = array(
'tag' => 'copying',
'room' => '1');
private $config = array(
'demo_action_admin_certificateconfig',
'demo_action_admin_dealerentranceconfig',
'demo_action_cash_config',
'demo_action_customer_config',
'demo_action_dealer_config',
'demo_action_delivery_config',
'demo_action_itemcomm_config',
'demo_action_poster_config',
'demo_action_seckill_config',
'demo_action_shop_config',
'demo_action_user_config',
'demo_action_admin_skinconfig',
'demo_action_wechat_config',
'demo_action_xiaoi_config',
'demo_action_yunpay_config',
'demo_model_item_config',
'demo_sysapi_ultraconfig',
'demo_sysapi_payconfig'
);
//重新连接
public function reload(){
if($this->linker->server->reload()) $message = array('msgcode'=>'RELOAD');
else $message = array('msgcode'=>'FAILURE');
PUSH($this->linker,$message);
}
//心跳连接
public function heartbeat() {
$res = $this->linker->messaging;
$roomid = $res['roomid'];
PUSH($this->linker,array('errcode'=>'heartbeat'),'adventure_'.$roomid);
return false;
}
public function init(){
// 此时已经可以进行通信！
PUSH($this->linker,$this->upgrading);
return $this->upgrading;
}
//第一步
public function copyStart(){
$arg = $this->linker->messaging;
if(empty($arg['newsite'])) return PUSH($this->linker,array('errcode'=>'ERR_POST_NEWSITE','errmsg'=>'新域名为空'));
if(empty($arg['oldsite'])) return PUSH($this->linker,array('errcode'=>'ERR_POST_OLDSITE','errmsg'=>'旧域名为空'));
$newsite=strtolower(trim($arg['newsite']));
$oldsite=strtolower(trim($arg['oldsite']));
$m = $this->m(DBMATRIX);
//检测旧域名是否存在
$site = $m->sel()->from('customer')->where(array("domain = ",$oldsite))->exe();
if(empty($site)){
PUSH($this->linker,array('errcode'=>'ERR_EXIST_DOMAIN','errmsg'=>'旧域名不存在'));
return false;
}
// 检查新域名是否被使用
$mysql = "select id from demo_customer where domain = '{$newsite}'";
$myres = $m->exe(false,$mysql);
if(!empty($myres)){
PUSH($this->linker,array('errcode'=>'DOMAIN_HAS_USE','errmsg'=>'新域名已使用过'));
return false;
}
//查询数据库表的数量
$oldDB = preg_replace("/[\W]/", "_", $oldsite);
$sql = "SELECT COUNT( * ) as cnt FROM information_schema.TABLES WHERE TABLE_SCHEMA = '{$oldDB}'";
$tables_num = $this->m($this->initDB[$site['branch']])->exe(false,$sql)['cnt'];
//添加域名修改记录表
$mysql="select * from demo_changesite_log where newsite='{$newsite}' and oldsite = '{$oldsite}'";
$logres=$this->m(DBMATRIX)->exe(false,$mysql);
if(empty($logres)){
$data['sql_sum'] = $tables_num;
$data['siteid']=$site['id'];
$data['newsite']=$newsite;
$data['oldsite']=$oldsite;
$data['step'] = 1;
//$data['admid'] = $_SESSION['admid'];
$res=$this->m(DBMATRIX)->ins('changesite_log')->values($data)->exe();
if(empty($res)){
PUSH($this->linker,array('errcode'=>'ERR_INS_LOG','errmsg'=>'修改记录添加失败'));
return false;
}
return PUSH($this->linker,array('errcode'=>'FINISHREADY','id'=>$res,'numsql'=>$tables_num,'step'=>1));
}
return PUSH($this->linker,array('errcode'=>'FINISHREADY','id'=>$logres['id'],'numsql'=>$tables_num,'step'=>$logres['step']));
}
//第二步
public function copyData(){
$arg = $this->linker->messaging;
if(empty($arg['id'])) return PUSH($this->linker,array('errcode'=>'ERR_POST_ID','errmsg'=>'ID为空'));
$log = $this->m(DBMATRIX)->sel('oldsite,newsite')->from('changesite_log')->where(array('id =',$arg['id']))->exe();dump($log);
if(empty($log)) return PUSH($this->linker,array('errcode'=>'ERR_LOG','errmsg'=>'记录不存在','id'=>$arg['id']));
$newsite=strtolower(trim($log['newsite']));
$oldsite=strtolower(trim($log['oldsite']));
$oldDB = preg_replace("/[\W]/", "_", $oldsite);
$newDB = preg_replace("/[\W]/", "_", $newsite);
$site=$this->m(DBMATRIX)->sel('id,branch')->from('customer')->where(array('domain=',$oldsite))->exe();dump($site);
//切换数据库
$sql='use `'.$newDB.'`';
$res=$this->m($this->initDB[$site['branch']])->exe(false,$sql);
if(!$res){
//创建新数据库
$sql='create database `'.$newDB.'`';
$this->m($this->initDB[$site['branch']])->exe(false,$sql);
$sql='use `'.$newDB.'`';
$res = $this->m($this->initDB[$site['branch']])->exe(false,$sql);
if(!empty($res)) return PUSH($this->linker,array('errcode'=>'ERR_DATABASW','errmsg'=>'数据库创建失败'));
}
/**
* 这里直接进行表结构及数据的复制，直接省去由于害怕数据丢失的备份
* CREATE TABLE admin_jingzhunfenxiao_com.admin2 LIKE 3n4w_jingzhunfenxiao_com.demo_admin; 复制表结构
* INSERT admin_jingzhunfenxiao_com.admin2 SELECT * FROM 3n4w_jingzhunfenxiao_com.demo_admin; 复制表数据
* array(286) {
[0] => array(1) {
["Tables_in_zzz_zzz_com"] => string(15) "demo_agent_item"
}
[1] => array(1) {
["Tables_in_zzz_zzz_com"] => string(17) "demo_agent_record"
}
[2] => array(1) {
["Tables_in_zzz_zzz_com"] => string(15) "demo_agent_spec"
}
}
**/
$sql = "show tables";
$tables = $this->m($oldsite)->exe(true,$sql);
$numsql = 0;$errsql = '';
if(!empty($tables)){
$m = $this->m($oldsite);
foreach ($tables as $k => $va) {
$numsql = $k;
$tablename = $va['Tables_in_'.$oldDB];
$ssql = "show tables {$tablename}";
$tab = $m->exe(false,$ssql);
if(empty($tab)){
$tsql = "CREATE TABLE IF NOT EXISTS {$newDB}.{$tablename} LIKE {$oldDB}.{$tablename}";
$tres = $m->exe(false,$tsql);
if(empty($tres)){
$errsql = $k.'-1';break;
}
$dsql = "INSERT {$newDB}.{$tablename} SELECT * FROM {$oldDB}.{$tablename}";
$dres = $m->exe(false,$dsql);
}
PUSH($this->linker,array('errcode'=>'SUCCESS_CREATE','num'=>$numsql+1));
}
//有错误导致创建没有继续执行，记录错误位置
if(!empty($errsql)){
$mysql="update demo_changesite_log set step=2 , sqlid={$errsql} where id={$arg['id']}";
$myres=$this->m(DBMATRIX)->exe(false,$mysql);echo $mysql;die;
return PUSH($this->linker,array('errcode'=>'ERR_CREATE','errmsg'=>'数据断开连接'));
}
}
$mysql="update demo_changesite_log set step=2 , sqlid={$numsql} where id={$arg['id']}";
$myres=$this->m(DBMATRIX)->exe(false,$mysql);
return PUSH($this->linker,array('errcode'=>'FINISHCOPYDATA','errmsg'=>'复制成功'));
}
//第三步 复制oss文件
// SOCKET 模式搬迁
public function startSocketCopy(){
$arg = $this->linker->messaging;
if(empty($arg['id'])) return PUSH($this->linker,array('errcode'=>'ERR_POST_ID','errmsg'=>'ID为空'));
$log = $this->m(DBMATRIX)->sel('oldsite,newsite')->from('changesite_log')->where(array('id =',$arg['id']))->exe();
if(empty($log)) return PUSH($this->linker,array('errcode'=>'ERR_LOG','errmsg'=>'记录不存在'));
$newsite=strtolower(trim($log['newsite']));
$oldsite=strtolower(trim($log['oldsite']));
$this->oldPrefix = $oldsite.'/';
$this->newPrefix = $newsite.'/';
$this->copyInSocket($this->fullTheList($this->oldPrefix));
$mysql="update demo_changesite_log set step=3 where id={$arg['id']}";
$myres=$this->m(DBMATRIX)->exe(false,$mysql);
return PUSH($this->linker,array('errcode'=>'FINISHCOPYOSS','errmsg'=>'OSS文件搬迁完成'));
}
//第四步 修改总站配置及删除旧库
public function alterConfig(){
$arg = $this->linker->messaging;
if(empty($arg['id'])) return PUSH($this->linker,array('errcode'=>'ERR_POST_ID','errmsg'=>'ID为空'));
$log = $this->m(DBMATRIX)->sel('oldsite,newsite')->from('changesite_log')->where(array('id =',$arg['id']))->exe();
if(empty($log)) return PUSH($this->linker,array('errcode'=>'ERR_LOG','errmsg'=>'记录不存在'));
$newsite=strtolower(trim($log['newsite']));
$oldsite=strtolower(trim($log['oldsite']));
$m=$this->m(DBMATRIX);
//搬总站配置
for($i=0;!empty($this->config[$i]);$i++){
// 找出新域名名配置
$mysql = "select id,http_host from `".$this->config[$i]."` where `http_host` = '{$newsite}'";
$config_from = $m->exe(false,$mysql);
if($config_from) continue;
// 找出原域名配置
$mysql = "select id,http_host from `".$this->config[$i]."` where `http_host` = '{$oldsite}'";
$config_from = $m->exe(false,$mysql);
if(!$config_from){
$mysql = "insert into `".$this->config[$i]."`(http_Host) values('{$newsite}') ";
$myre = $m->exe(false,$mysql);
if(!$myre) return PUSH($this->linker,array('errcode'=>'ERR_CONFIG','errmsg'=>'数据断开连接'));
}else{
// 新域名配置插入表
$mysql = "update `".$this->config[$i]."` set http_host='{$newsite}' where http_host = '{$oldsite}'";
$myre = $this->m(DBMATRIX)->exe(false,$mysql);
if(!$myre) return PUSH($this->linker,array('errcode'=>'ERR_CONFIG','errmsg'=>'数据断开连接'));
}
}
//修改customer
$site = $m->sel('id,version,branch')->from('customer')->where(array('domain=',$newsite))->exe();
if(!$site){
$site = $m->sel('id,version,branch')->from('customer')->where(array('domain=',$oldsite))->exe();
$res = $m->upd('customer')->set(array('domain'=>$newsite))->where(array('id=',$site['id']))->exe();
if(!$res) return PUSH($this->linker,array('errcode'=>'ERR_CUSTOMER','errmsg'=>'数据断开连接'));
}
//把新域名写入域名修改表（customer_update）
$list=$m->sel()->from('customer_update')->where(array('siteid=',$site['id']," and domain=",$newsite))->exe();
if(!$list){
$data = date("Y-m-d H:i:s");
$sql="insert into demo_customer_update (siteid,domain,time) values({$site['id']},'{$newsite}','{$data}')";
$log=$m->exe(false,$sql);
if(!$log) return PUSH($this->linker,array('errcode'=>'ERR_INSERT','errmsg'=>'数据断开连接'));
}
//删除旧数据库
$database = preg_replace("/[\W]/", "_", $oldsite);
$sql='DROP DATABASE `'.$database.'`';
//$res= $this->m($this->initDB[$site['branch']])->exe(false,$sql);
//if(!$res) return PUSH($this->linker,array('errcode'=>'ERR_DROP_DATABASE','errmsg'=>'数据断开连接'));
$mysql="update demo_changesite_log set step=6 ,state=1 where id={$arg['id']}";
$res=$m->exe(false,$mysql);
return PUSH($this->linker,array('errcode'=>'FINISHTASK','errmsg'=>'任务完成'));
}
private function fullTheList($Prefix){
$ObjectListInfo = A($this->linker,'oss/object/listObjects',array($this->bucket,array('max-keys'=>$this->maxkeys,'prefix'=>$Prefix)));
$ObjectList = $subObjectList = $ObjectListInfo->getObjectList();
$PrefixList = $subPrefixList = $ObjectListInfo->getPrefixList();
/*
每次只能获取1000条
循环拼接第1000条后面的文件或文件夹到数组中
*/
while(true){
$numObject = count($subObjectList);
$numPrefix = count($subPrefixList);
if($numObject+$numPrefix == 0) break;
if($numObject+$numPrefix < $this->maxkeys) break;
/*
只有文件时，取文件
只有文件夹时，取文件夹
都有时，取较大值
*/
while(true){
if(!$numObject) {$lastObject = $subPrefixList[$numPrefix-1]->getPrefix();break;}
if(!$numPrefix) {$lastObject = $subObjectList[$numObject-1]->getKey();break;}
$Key = $subObjectList[$numPrefix-1]->getKey();
$Prefix = $subPrefixList[$numObject-1]->getPrefix();
$lastObject = strcmp($Key,$Prefix)>0?$Key:$Prefix;
break;
}
$subObjectListInfo = A($this->linker,'oss/object/listObjects',array($this->bucket,array('max-keys'=>$this->maxkeys,'prefix'=>$Prefix,'marker'=>$lastObject)));
$subObjectList = $subObjectListInfo->getObjectList();
$subPrefixList = $subObjectListInfo->getPrefixList();
$ObjectList = array_merge($ObjectList,$subObjectList);
$PrefixList = array_merge($PrefixList,$subPrefixList);
}
return array('ObjectList'=>$ObjectList,'PrefixList'=>$PrefixList);
}
/*
递归函数：传入完整的文件与文件夹列表
参数：array(
'ObjectList'=>$ObjectList,
'PrefixList'=>$PrefixList
);
*/
private function copyInSocket($the){
foreach($the['ObjectList'] as $obj){
$key = $obj->getKey();
if(preg_match('/\+/',$key)) continue;
$newKey = $this->newKey($this->oldPrefix,$this->newPrefix,$key);
$res = A($this->linker,'oss/object/copyObject',array($this->bucket,$key,$this->bucket,$newKey));
PUSH($this->linker,array('errcode'=>'COPYOBJECT'));
}
foreach($the['PrefixList'] as $pre) $this->copyInSocket($this->fullTheList($pre->getPrefix()));
}
private function newKey($oldPrefix,$newPrefix,$obj){
return preg_replace('/^'.str_replace('/','\/',$oldPrefix).'/',$newPrefix,$obj);
}
}
前端代码
html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>换域名</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="http://3n4w.oss-cn-shenzhen.aliyuncs.com/public/client-resource/layui-master/dist/css/layui.css">
<link rel="stylesheet" type="text/css" href="/admin/public/common.css">
<link rel="stylesheet" type="text/css" href="/admin/public/reset.css"/>
<link rel="stylesheet" type="text/css" href="domain.css"/>
<!-- 注意：如果你直接复制所有代码到本地，上述css路径需要改成你本地的 -->
</head>
<body >
<blockquote class="layui-elem-quote">
<span class="title">域名更换操作</span>
</blockquote>
<div class="content">
<div class="layui-inline">
<div class="layui-form-item">
<label class="layui-form-label">旧域名</label>
<div class="layui-input-inline">
<input type="text" name="username" lay-verify="required" placeholder="请输入" autocomplete="off" class="layui-input" id="oldsite">
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">新域名</label>
<div class="layui-input-inline">
<input type="text" name="username" lay-verify="required" placeholder="请输入" autocomplete="off" class="layui-input" id="newsite">
</div>
</div>
</div>
<div class="layui-inline">
<button class="layui-btn save-btn fl" id="save-btn">保存</button>
<button class="layui-btn reload fl" id="reload">重启</button>
</div>
</div>
<div class="progress" id="progress">
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>操作进度</legend>
</fieldset>
<ul class="layui-timeline">
<li class="layui-timeline-item">
<i class="layui-icon layui-timeline-axis"></i>
<div class="layui-timeline-content layui-text">
<div class="layui-timeline-title"><label class="step1">换域名准备阶段</label><label class="state st1"></label></div>
</div>
</li>
<li class="layui-timeline-item">
<i class="layui-icon layui-timeline-axis"> </i>
<div class="layui-timeline-content layui-text">
<div class="layui-timeline-title"><label class=" step2">数据库表数据复制</label><label class="state st2"></label></div>
</div>
</li>
<li class="layui-timeline-item">
<i class="layui-icon layui-timeline-axis"></i>
<div class="layui-timeline-content layui-text">
<div class="layui-timeline-title"><label class="step3">OSS存储文件复制</label><label class="state st3"></label></div>
</div>
</li>
<li class="layui-timeline-item">
<i class="layui-icon layui-timeline-axis"></i>
<div class="layui-timeline-content layui-text">
<div class="layui-timeline-title"><label class="step4">配置文件修改</label><label class="state st4"></label></div>
</div>
</li>
<li class="layui-timeline-item">
<i class="layui-icon layui-timeline-axis"></i>
<div class="layui-timeline-content layui-text">
<div class="layui-timeline-title"><label class="step5">操作已完成</label></div>
</div>
</li>
</ul>
</div>
<script type="text/javascript" src="http://3n4w.oss-cn-shenzhen.aliyuncs.com/public/client-resource/layui-master/dist/layui.js"></script>
<!-- 注意：如果你直接复制所有代码到本地，上述js路径需要改成你本地的 -->
<script type="text/javascript" src="domain.js"></script>
<script>
</script>
</body>
</html>js
layui.use(['form'], function() {
var form = layui.form,
$ = layui.jquery;
var websocket = new WebSocket('ws://192.168.1.1:9997?swoole=dev&mca=swoole/changesite/init&http_host=www.baidu.com');
var save_btn = document.getElementById("save-btn");
var oldsite = document.getElementById("oldsite");
var newsite = document.getElementById("newsite");
var progress = document.getElementById("progress");
var finishcopy = document.getElementById("finishcopy");
var reload_btn = document.getElementById('reload');
reload_btn.onclick = function(){
var copyStart = {
mca : 'swoole/changesite/reload'};
websocket.send(JSON.stringify(copyStart));
}
var reload = {
mca : 'swoole/changesite/reload',
arg : 'arg1',};
var beating = {
mca : 'swoole/changesite/beating',
arg : 'arg1',};
var copyNum = 0;var id = 0;var step =0;
var copyReady = function(num){console.log(num);
$('.step1').addClass('has-end').parents('.layui-timeline-content').siblings('.layui-icon').addClass('has-over');
$('.st1').html('已完成');
$('.st2').html("正在复制<b class='has-copy'>0</b> /<b class='tabnum'>"+num+" </b>");
if(step==1){
copyData();
}
if(step==2){
startSocketCopy();
}
if(step==3){
$('.step2').addClass('has-end').parents('.layui-timeline-content').siblings('.layui-icon').addClass('has-over');
$('.st2').html("已完成");
alterConfig();
}
}
var copyData = function(){
var copyStart = {
mca : 'swoole/changesite/copyData',
id : id};
websocket.send(JSON.stringify(copyStart));
}
var startSocketCopy = function(){
$('.step2').addClass('has-end').parents('.layui-timeline-content').siblings('.layui-icon').addClass('has-over');
$('.st2').html("已完成");
$('.st3').html("正在复制<b class='has-file'>0</b>");
var copyStart = {
mca : 'swoole/changesite/startSocketCopy',
id : id};console.log(copyStart);
websocket.send(JSON.stringify(copyStart));
}
var alterConfig = function(){
$('.step3').addClass('has-end').parents('.layui-timeline-content').siblings('.layui-icon').addClass('has-over');
$('.st3').html("已完成");
$('.st4').html("正在修改配置文件");
var copyStart = {
mca : 'swoole/changesite/alterConfig',
id : id};
websocket.send(JSON.stringify(copyStart));
}
var taskEnd = function(){
$('.step4').addClass('has-end').parents('.layui-timeline-content').siblings('.layui-icon').addClass('has-over');
$('.st4').html("已完成");
$('.step5').addClass('has-end').parents('.layui-timeline-content').siblings('.layui-icon').addClass('has-over');
}
save_btn.onclick = function(){
progress.style.display = 'block';
var copyStart = {
mca : 'swoole/changesite/copyStart',
oldsite : oldsite.value,
newsite : newsite.value};
websocket.send(JSON.stringify(copyStart));
}
websocket.onopen = function (evt) {
console.log("Connected to WebSocket server.");
websocket.send(JSON.stringify(reload));
window.setInterval(function(){websocket.send(JSON.stringify(beating));}, 10000);
};
websocket.onmessage = function (evt) {
var that = this;
var data = eval('('+evt.data+')');
console.log(data);
switch(data.message.errcode){
case 'FINISHREADY':
id = data.message.id;
step = data.message.step;
copyReady(data.message.numsql);
break;
case 'FINISHCOPYDATA':
startSocketCopy();
break;
case 'FINISHCOPYOSS':
alterConfig();
break;
case 'FINISHTASK':
taskEnd();
break;
case 'SUCCESS_CREATE':
$('.has-copy').html(data.message.num);console.log(data.message.num);
break;
case 'COPYOBJECT':
$('.has-file').html(++copyNum);
break;
//default:console.log(data.message.errmsg);break;
}
};
websocket.onclose = function (evt) {console.log("Disconnected");};
websocket.onerror = function (evt, e) {console.log('Error occured: ');console.log(evt.data);};
});css
.content {
padding: 20px 80px;
}
button.layui-btn.save-btn.fl {
margin-left: 100px;
}
.layui-timeline {
padding-left: 100px;
}
.finish {
background-color: #a53333;
}
.progress{
display: none;
}
.state{
color: #c74949;
padding-left: 75px;
}
.has-over{
background-color: #c74949;
}
.has-end{
color: #c74949;
}
