使用mysqldump同步两台服务器中不同数据库相同表结构的表

时间：2024-01-31 13:30:18

简介

　　 基本的需求是这样的：现在有两个服务器host1与host2，里面的MySQL服务分别存着两个数据库db1与db2，假设我想将数据库db1的A表中的数据同步到数据库db2的B中，而且A与B的表结构完全一样！

　　特别要注意的要求是：必须忽略主键冲突的记录！也就是说，只有当A中存在的记录但是在B中不存在时才往数据库B中insert，如果A与B都有的数据还是以B中的数据为准！

　　相信大家看到题目中的业务问题描述首先想到的是数据库的主从同步以及安装各种第三方插件的方案，但是由于业务原因不支持上面提到的方式。

　　另外既有的方式是我用python脚本实现的，但是随着业务数据量的增加python脚本的执行效率肯定不如shell，所以想着在效率瓶颈来临之前优化一下既有的实现方案。

　　本文主要介绍一下使用shell脚本执行MySQL命令实现的思路以及具体shell脚本的实现。

本地初始化测试

　　首先在本地搭建一下测试的环境，比较简单，只需要2个不同数据库相同的表就可以了，因为我是在本地测试，所以host1与host2的ip都是127.0.0.1。

　　我在本地分别创建了两个数据库：students1与students2，两张相同的表叫s_test。

　　students1中有3条数据：

　　students2中有1条数据，且这条数据的主键与students1中的某一条一样，数据不一样：

　　现在要做的是：将students1中主键为2跟3的数据同步到students2中（因为students2中没有这两条数据），但是得保留students2主键为1的那条数据！

使用mysqldump命令实现的思路与过程详解 

　　mysqldump命令主要用来备份与同步数据库，我现在的实现思路是先将A表的数据dump下来，然后再使用dump下来的数据去同步B表。

　　这里特别提醒一点：一定要只dump表A的数据，而不是结构+数据！首先表A与表B的表结构本身是一模一样的！另外如果同步了结构+数据的话生成的sql文件开头会有一个drop目标表的语句！这样的话B中的数据就全没了！不符合一开始的要求！

从表A中dump数据的语句

mysqldump -h 127.0.0.1 -P 3306 -uroot -p123  -t  students1 s_test --insert-ignore=TRUE  --where="age>12"   > s_test.sql

　　注意：

　　（1）这里指定 -t 参数的意思是只导出数据不导出结构；

　　（2）--insert_ignore=TRUE 表示在导入数据时如果遇到主键冲突的情况就忽略这条记录；

　　（3）--where表示筛选出哪些记录导出。

　　看一下导出来的s_test.sql文件：

LOCK TABLES `s_test` WRITE;
/*!40000 ALTER TABLE `s_test` DISABLE KEYS */;
INSERT  IGNORE INTO `s_test` VALUES (1,\'whw\',22),(2,\'naruto\',23),(3,\'sasuke\',24);
/*!40000 ALTER TABLE `s_test` ENABLE KEYS */;
UNLOCK TABLES;

　　注意这里是 insert ignore 语法。

简单的shell脚本 

#!/bin/bash
# 这里是本地测试 实际中的host是数据库A所在的host与port
mysqldump -h 127.0.0.1 -P 3306 -uroot -p123 -t  students1 s_test --insert-ignore=TRUE  --where="age>12"   > s_test.sql

# 本地测试 实际中的host是数据库B所在的host与port
mysql -h 127.0.0.1 -P 3306 -uroot  -p123 students2 < s_test.sql

# 删掉临时生成的sql文件
rm -rf ./s_test.sql

测试结果

　　执行完上面的脚本后我们再看看students2中的结果：

　　我们可以看到，students2中原来的数据没有被修改，只是把他之前没有的数据insert进去了。

脚本完善 ***

　　基本的实现思路就是这样，只不过上面的脚本着实很low，接下来加上了日志与多表同步的处理。。。

#!/bin/bash

source  /etc/profile

# 实际中可以换成source数据库的信息
sou_mysql_host=\'127.0.0.1\'
sou_mysql_port=\'3306\'
sou_mysql_user=\'root\'
sou_mysql_pwd=\'123\'
sou_mysql_db_name=\'students1\' #仅支持一个数据库

sou_mysql_table_name=(
# 两张数据库中相同的表的名字
s_test
# 如果有其他表的话再添加就好了！可以同步多张相同的表！
# s_test2
)

# 实际中可以换成destination数据库的信息
des_mysql_host=\'127.0.0.1\'
des_mysql_port=\'3306\'
des_mysql_user=\'root\'
des_mysql_pwd=\'123\'
des_mysql_db_name=\'students2\' #仅支持一个数据库
#记得先同步表结构 不然会失败

# 存放sql文件与日志的目录
sync_db_dir=\'./syncdata/\'
log_file=\'./logs/mysql_to_mysql.log\'

[ -d ./logs ] || mkdir ./logs
[ -d ./syncdata ] || mkdir ./syncdata

func_writeLog(){
        local getString=$1
        if [ $? -eq 0 ];then
                echo "TIME:$(date +"%Y%m%d_%H:%M"),The ${getString}"
                echo "TIME:$(date +"%Y%m%d_%H:%M"),The ${getString}"  >>${log_file}
        else
                echo "TIME:$(date +"%Y%m%d_%H:%M"),The ${getString}"
                echo "TIME:$(date +"%Y%m%d_%H:%M"),The ${getString}" >>${log_file}
        fi
}


func_sourceBackupCmd(){
    for tabname in $(echo ${sou_mysql_table_name[@]}|sed \'s/ / /g\');do
        /usr/local/mysql/bin/mysqldump -h${sou_mysql_host} -P${sou_mysql_port} -u${sou_mysql_user} -p${sou_mysql_pwd}  \
        --insert-ignore=true \
        --default-character-set=utf8 \
        -t ${sou_mysql_db_name} ${tabname} >${sync_db_dir}/${sou_mysql_db_name}_${tabname}.sql
        func_writeLog "${sou_mysql_db_name}_${tabname}.sql backup ok"
    done
}

func_syncMysqlCmd(){
    for sqlname in $(ls ${sync_db_dir}/*.sql);do
        /usr/local/mysql/bin/mysql -h${des_mysql_host} -P${des_mysql_port} -u${des_mysql_user} -p${des_mysql_pwd}  \
        --default-character-set=utf8 ${des_mysql_db_name} < ${sqlname}
        func_writeLog "${sqlname} load ok"
        [ $? -eq 0 ] && rm -f ${sqlname}
        func_writeLog "${sqlname} deleted ok"
    done
}


main(){
    func_sourceBackupCmd;
    func_syncMysqlCmd;
}
main

参考文章

MySql数据库备份与恢复-使用mysqldump 导入与导出方法总结

rsync+inotify同步备份MYSQL数据

相关文章