mysql数据差异导入

import mysql.connector

def get_table_schema(cursor, table_name):
    cursor.execute(f"SHOW CREATE TABLE {table_name}")
    return cursor.fetchone()[1]

def get_table_data(cursor, table_name):
    cursor.execute(f"SELECT * FROM {table_name}")
    return cursor.fetchall()

def compare_databases(host1, user1, password1, host2, user2, password2):
    conn1 = mysql.connector.connect(host=host1, user=user1, password=password1)
    conn2 = mysql.connector.connect(host=host2, user=user2, password=password2)
    
    cursor1 = conn1.cursor()
    cursor2 = conn2.cursor()
    
    cursor1.execute("SHOW TABLES")
    tables = cursor1.fetchall()
    
    for table in tables:
        table_name = table[0]
        
        schema1 = get_table_schema(cursor1, table_name)
        schema2 = get_table_schema(cursor2, table_name)
        
        if schema1 != schema2:
            print(f"Schema difference in table {table_name}")
        
        data1 = get_table_data(cursor1, table_name)
        data2 = get_table_data(cursor2, table_name)
        
        if data1 != data2:
            print(f"Data difference in table {table_name}")
    
    cursor1.close()
    cursor2.close()
    conn1.close()
    conn2.close()

# 示例调用
compare_databases('host1', 'user1', 'password1', 'host2', 'user2', 'password2')

import subprocess

def dump_difference(host, user, password, table_name, output_file):
    subprocess.run([
        'mysqldump',
        '--host=' + host,
        '--user=' + user,
        '--password=' + password,
        '--no-create-info',
        '--compact',
        '--skip-extended-insert',
        '--compact',
        table_name,
        '--result-file=' + output_file
    ])

def import_difference(host, user, password, input_file):
    subprocess.run([
        'mysql',
        '--host=' + host,
        '--user=' + user,
        '--password=' + password,
        '<', input_file
    ])

# 示例调用
dump_difference('host1', 'user1', 'password1', 'table_name', 'difference.sql')
import_difference('host2', 'user2', 'password2', 'difference.sql')