php 脚本比对两个数据库是否一致
<?php
//比较两个数据库是否一样
$db1 = '';
$db2 = '';
$db1_tables = [];
$db2_tables = [];
//db1
$db1_connect_info = array(
'name' => 'db1', //数据库名称
'dbms' => 'mysql', //数据库类型
'host' => '192.168.5.139', //数据库主机名
'dbName' => 'test', //使用的数据库
'user' => 'root', //数据库连接用户名
'pass' => 'rootroot', //对应的密码
);
//db2
$db2_connect_info = array(
'name' => 'db2', //数据库名称
'dbms' => 'mysql', //数据库类型
'host' => '192.168.5.144', //数据库主机名
'dbName' => 'test', //使用的数据库
'user' => 'root', //数据库连接用户名
'pass' => 'rootroot', //对应的密码
);
function init_mysql_connect(){
global $db1;
global $db2;
global $db1_connect_info;
global $db2_connect_info;
try {
$db1 = new PDO(
$db1_connect_info['dbms'].':host='.$db1_connect_info['host'].';dbname='.$db1_connect_info['dbName'],
$db1_connect_info['user'],
$db1_connect_info['pass']
); //初始化一个PDO对象
echo $db1_connect_info['name']."连接成功!".PHP_EOL;
} catch (PDOException $e) {
die ("Error!: " . $e->getMessage() . "<br/>");
}
try {
$db2 = new PDO(
$db2_connect_info['dbms'].':host='.$db2_connect_info['host'].';dbname='.$db2_connect_info['dbName'],
$db2_connect_info['user'],
$db2_connect_info['pass']
); //初始化一个PDO对象
echo $db2_connect_info['name']."连接成功!".PHP_EOL;
} catch (PDOException $e) {
die ("Error!: " . $e->getMessage() . "<br/>");
}
};
function init()
{
$start_time = time();
init_mysql_connect();
diff_tables();
diff_datas();
$end_time = time();
echo "开始时间：".date("Y-m-d H:i:s",$start_time).";".PHP_EOL;
echo "结束时间：".date("Y-m-d H:i:s",$end_time).";".PHP_EOL;
echo "用时：".($end_time-$start_time)." 秒;".PHP_EOL;
}
//比较数据
function diff_datas(){
global $db1;
global $db2;
global $db1_tables;
foreach ($db1_tables as $k1 => $v1) {
//检查两个表的记录数是否相同
$sql1 = "SELECT count(*) FROM ".$v1['TABLE_NAME'];
$stmt = $db1->prepare($sql1);
$stmt->execute();
$table_count_1 = $stmt->fetch(PDO::FETCH_COLUMN);
$stmt = $db2->prepare($sql1);
$stmt->execute();
$table_count_2 = $stmt->fetch(PDO::FETCH_COLUMN);
if ($table_count_1 == $table_count_2) {
echo "两个数据库的".$v1['TABLE_NAME']."表数据记录数量一致".PHP_EOL;
} else {
echo "ERROR: 两个数据库的".$v1['TABLE_NAME']."表数据记录数量不一致".PHP_EOL;
exit();
}
// 循环比对数据
$page_size=5000;
$total_page=$table_count_2/$page_size;
$cur_page=1;
$sql = "SELECT * FROM ".$v1['TABLE_NAME']." LIMIT ?,?;";
$stmt1 = $db1->prepare($sql);
$stmt2 = $db2->prepare($sql);
for ($i=0; $i < $total_page; $i++) {
$start = ($cur_page-1)*$page_size;
$stmt1->bindValue(1, $start, PDO::PARAM_INT);
$stmt1->bindValue(2, $page_size, PDO::PARAM_INT);
$stmt1->execute();
$db1_datas = $stmt1->fetchAll(PDO::FETCH_ASSOC);
$stmt2->bindValue(1, $start, PDO::PARAM_INT);
$stmt2->bindValue(2, $page_size, PDO::PARAM_INT);
$stmt2->execute();
$db2_datas = $stmt2->fetchAll(PDO::FETCH_ASSOC);
if(md5(json_encode($db1_datas))==md5(json_encode($db2_datas))){
echo "两个数据库的".$v1['TABLE_NAME']."表数据记录".$start."-".($start+$page_size)."一致".PHP_EOL;
}else {
//找出那几条记录有问题
$error_rows = [];
foreach ($db1_datas as $k2 => $v2) {
if (md5(json_encode($v2)) != md5(json_encode($db2_datas[$k2]))) {
$error_rows[] = $v2['id'];
}
}
$error_id_str = implode(",", $error_rows);
//退出
echo "ERROR: 两个数据库的".$v1['TABLE_NAME']."表数据记录".$start."-".($start+$page_size)."不一致".PHP_EOL;
echo "ERROR: DIFFERENT ID IS ".$error_id_str.PHP_EOL;
exit();
}
$cur_page++;
}
}
}
//比较表结构
function diff_tables()
{
global $db1;
global $db2;
global $db1_connect_info;
global $db2_connect_info;
global $db1_tables;
global $db2_tables;
//比较两个表的名字数量
$sql1 = "SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '".$db1_connect_info['dbName']."'";
$stmt = $db1->prepare($sql1);
$stmt->execute();
$db1_tables = $stmt->fetchAll(PDO::FETCH_ASSOC);
$db1_md5 = md5(json_encode($db1_tables));
$sql2 = "SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '".$db2_connect_info['dbName']."'";
$stmt = $db2->prepare($sql2);
$stmt->execute();
$db2_tables = $stmt->fetchAll(PDO::FETCH_ASSOC);
$db2_md5 = md5(json_encode($db2_tables));
if ($db1_md5 == $db2_md5) {
echo "db1 和 db2 数据库表数量和名称相同".PHP_EOL;
}else {
echo "ERROR: db1 和 db2 数据库表数量和名称不相同".PHP_EOL;
exit;
}
//比较两个数据库之间表的结构
foreach ($db2_tables as $key => $value) {
$sql1 = "show create table ".$value['TABLE_NAME'];
$stmt = $db1->prepare($sql1);
$stmt->execute();
$row1 = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt = $db2->prepare($sql1);
$stmt->execute();
$row2 = $stmt->fetch(PDO::FETCH_ASSOC);
if (md5(json_encode($row1))==md5(json_encode($row2))) {
echo "两个数据库的表".$value['TABLE_NAME']."结构相同".PHP_EOL;
}else {
echo "ERROR: 两个数据库的表".$value['TABLE_NAME']."结构不相同".PHP_EOL;
exit;
}
}
}
init();