Mysql迁移后验证是否相同_检查mysql迁库前后的数据记录数是否一致

$old = ['host' => '127.0.0.1', 'username' => 'root', 'passwd' => ''];

$new = ['host' => '11.11.11.123', 'username' => 'abc', 'passwd' => 'abc'];

$old_mysqli = @new mysqli($old['host'], $old['username'], $old['passwd']);

if ($old_mysqli->connect_errno) {

echo $old['host'].":Connect failed: ".$old_mysqli->connect_error.PHP_EOL;

exit();

}

$new_mysqli = @new mysqli($new['host'], $new['username'], $new['passwd']);

if ($new_mysqli->connect_errno) {

echo $new['host'].":Connect failed: ".$new_mysqli->connect_error.PHP_EOL;

exit();

}

$query = "SHOW DATABASES";

$databases = [];

if ($result = $old_mysqli->query($query)) {

while ($row = $result->fetch_row()) {

if(!in_array($row[0], ['information_schema', 'mysql', 'performance_schema', 'test'])){

$databases[] = $row[0];

}

}

}

if(!empty($databases)){

foreach($databases as $k => $database){

$old_mysqli->select_db($database);

$select_db_res = $new_mysqli->select_db($database);

if(!$select_db_res){

die($new['host'].' database:'.$database.' is not exist');

}

$query = "SHOW TABLES";

if ($result = $old_mysqli->query($query)) {

while ($row = $result->fetch_row()) {

$table = $row[0];

$count_query = "SELECT COUNT(*) FROM `$table` WHERE 1=1";

//echo $database.':'.$count_query.PHP_EOL;

if ($old_count_result = $old_mysqli->query($count_query)) {

while ($row = $old_count_result->fetch_row()) {

$old_count = $row[0];

if ($new_count_result = $new_mysqli->query($count_query)) {

while ($row = $new_count_result->fetch_row()) {

$new_count = $row[0];

//echo $database.':'.$table.':'.$old_count.':'.$new_count.PHP_EOL;

if($new_count != $old_count){

echo $old['host'].':'.$database.':'.$table.':count:'.$old_count.PHP_EOL;

echo $new['host'].':'.$database.':'.$table.':count:'.$new_count.PHP_EOL.PHP_EOL;

}

}

}else if(!empty($new_mysqli->error)){

die($new_mysqli->error);

}

}

}

}

}

}

}

$old_mysqli->close();

$new_mysqli->close();