mysql从源数据库导出表到目标数据库
#!/usr/bin/env php<?php
/**
* DB同步脚本
**/
ini_set('date.timezone','Asia/Shanghai');
define("SCRIPT_PATH", realpath(dirname(__FILE__)));
# 数据文件根目录
define("DATA_ROOT_PATH", SCRIPT_PATH."/data/");
# 数据文件子目录(按时间命名)
define("DATE_FORMT","Y/m/d");
#######################################################
# 源数据库配置
$source_db_map = array (
'test' => array(’127.0.0.1’,’3306’,’root’,’root’),
);
# 目标数据库配置
$dest_db_map = array(
'test' => array('127.0.0.1','9988','root','root'),
);
######################################################
if (__FILE__ == realpath($_SERVER['SCRIPT_FILENAME']))
{
dumpdb(’test’,’t_users’,’test’);
}
// 从进程管道中读取输出数据
function pfile_get_contents($cmd)
{/*{{{*/
echo ">>> $cmd\n";
$buf='';
$file = popen($cmd,"r");
while (!feof($file)) {
$buf.=fgets($file);
}
pclose($file);
return $buf;
}/*}}}*/
// 将数据表从源数据库导入目标数据库(自动创建表)
function dumpdb($src_db, $table, $dest_db)
{/*{{{*/
global $source_db_map, $dest_db_map;
if (!isset($source_db_map[$src_db])) {die("unknow src_db : $src_db\n");}
if (!isset($dest_db_map[$dest_db])) {die("unknow dest_db : $dest_db\n");}
$scfg = $source_db_map[$src_db];
$dcfg = $dest_db_map[$dest_db];
$mysql_src = "mysql -h".$scfg." -P".$scfg." -u".$scfg." -p".$scfg;
$mysql_dest = "mysql -h".$dcfg." -P".$dcfg." -u".$dcfg." -p".$dcfg;
//1. 导出建表语句
$cmd = $mysql_src." -N -e \"use $src_db; show create table $table \G\"";
$create_sql = pfile_get_contents($cmd);
// 处理建表语句,发现不同mysql版本支持的建表语句不同,这里做下适配
$pos = strpos($create_sql,'CREATE TABLE');
$create_sql = substr($create_sql, $pos);
$create_sql = str_replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS', $create_sql);
$create_sql = str_replace('ON UPDATE CURRENT_TIMESTAMP','', $create_sql);
$create_sql = str_replace('DEFAULT CURRENT_TIMESTAMP','', $create_sql);
echo $create_sql."\n";
//2. 创建数据表
$con = mysql_connect($dcfg.":".$dcfg,$dcfg,$dcfg);
if (!$con) {die('Could not connect: ' . mysql_error());}
mysql_select_db($dest_db, $con);
if (!mysql_query($create_sql,$con)) {die('Error creating table: '.mysql_error());}
mysql_close($con);
//3. 导出数据到文件
$filepath = DATA_ROOT_PATH."/".date(DATE_FORMT);
if (!is_dir($filepath)) mkdir($filepath,0777,true);
$file = $filepath."/".$table;
if (is_file($file)) {die("file is exist: $file \n");}
$cmd = $mysql_src." -N -e \"use $src_db; SELECT * FROM $table\" > $file";
runcmd($cmd);
//4. 载入目标数据库
$cmd = $mysql_dest." -N -e \"use $dest_db; load data infile '$file' ignore into table $table\"";
runcmd($cmd);
}/*}}}*/
function runcmd($cmd)
{/*{{{*/
echo ">>> $cmd\n";
system($cmd);
}/*}}}*/
// vim600: sw=4 ts=4 fdm=marker syn=php
?>
页:
[1]