我的学习生活记录

mysql数据库同步工具

2021-3-11 夙夜 实际技能

linux端没免费现成的工具，今天写了个，做下记录：
<?php

/****
 * 使用当前连接数据库对比其他数据库差异，并将被对比数据库没有的表，字段补全
 * （如果目标数据库多字段不会删除，也不会同步到本地,字段存在但不相同也不会处理）
 * 
 * ***/


$need_handle_target_dbname = '';
$need_handle_target_dbusername = '';
$target_servername = '';
$need_handle_target_dbpasswd = '';
$target_serverport = '';

$need_handle_db = make_db_handle_mysql(
    $target_servername,
    $need_handle_target_dbname,
    $need_handle_target_dbusername,
    $need_handle_target_dbpasswd,
    $target_serverport
);

$template_db = $DB;

// $template_db = make_db_handle_mysql($target_servername,$need_handle_target_dbname,
// $need_handle_target_dbusername,$need_handle_target_dbpasswd,$target_serverport);



//读取两个数据库结构
$ok_table_list = $template_db->fetch_all_assoc("show tables"); // 
$ok_table_list = array_column($ok_table_list, "Tables_in_" . $template_db->dbname);


$error_target_table_list = $need_handle_db->fetch_all_assoc("show tables");
$error_target_table_list = array_column($error_target_table_list, "Tables_in_" . $need_handle_db->dbname);

//判断表是否存在
//存在表
$need_add_table_fields_map = [];
foreach ($ok_table_list as $key => $table_name) {
    if (in_array($table_name, $error_target_table_list)) {
        $need_add_table_fields_map[] = $table_name;
    } else {
        echo "表: $table_name 不存在\n";
    }
}

//存在补字段
$total_add_table_field = [];
$sql_list = [];
foreach ($need_add_table_fields_map as  $table_name) {

    $ok_table_field_list = zhengli_temp_data($template_db, $table_name);
    $error_target_table_field_list = zhengli_temp_data($need_handle_db, $table_name);


    $not_exit_field = [];

    foreach ($ok_table_field_list as  $field_name => $field_info) {
        if (!$error_target_table_field_list[$field_name]) {
            $temp_sql = "ALTER TABLE `" . $table_name . "` ADD `" . $field_info['Field'] . "` ";
            $temp_sql .= $field_info['Type'] . " DEFAULT ";
            $temp_sql .=  ($field_info['Default'] ? $field_info['Default'] : "NULL") . " COMMENT '" . $field_info['Comment'] . "'";
            $not_exit_field[] = $field_info['Field'];
            $sql_list[] = $temp_sql;
        }
         if(count($not_exit_field)) $total_add_table_field[$table_name] = $not_exit_field;
    }
}

foreach ($total_add_table_field as $table_name => $not_exit_field) {
    echo "\n";
    echo "$table_name 不存在的字段有".count($not_exit_field)."个: \n";
    foreach ($not_exit_field as $field_name) {
        echo "\t\t $field_name\n";
    }
}

var_dump($sql_list);exit;

// foreach ($sql_list as $key => $val) {
//     try {
//         $need_handle_db->query($val);
//     } catch (Exception $e) {
//         echo "\r\n" . $e->getMessage();
//     }
//     echo "\r\n all have :  " . count($sql_list) . " /  ok have: " . ($key + 1) . "  \r\n";
// }



// $log_file_path = "diff_databases_synchronous_" . date("Y-m-d") . ".log";
// fopen(__DIR__  . "/" . $log_file_path, "w");
// file_put_contents(__DIR__  . "/" . $log_file_path, print_r($sql_list, true));



function make_db_handle_mysql($serve, $template_dbname, $user, $passwd, $port)
{
    $need_handle_target_db = new DB_MySQL;
    $need_handle_target_db->servername = $serve;
    $need_handle_target_db->dbname = $template_dbname;
    $need_handle_target_db->dbusername = $user;
    $need_handle_target_db->dbpassword = $passwd;
    $need_handle_target_db->serverport = $port;
    return $need_handle_target_db;
}

function zhengli_temp_data($template_db, $table_name)
{
    $temp = $template_db->fetch_all_assoc("SHOW full COLUMNS FROM `" . $table_name . "`");
    $data = [];
    foreach ($temp as $fields_info) {
        foreach ($fields_info as $key => $value) {
            $fields_info[$key] = strtolower($value);
        }
        $data[$fields_info['Field']] = $fields_info;
    }   
    return $data;
}

标签: Linux mysql