See You Again

MYSQL表结构自动同步

前段时间把自动化环境和测试环境的数据库做了隔离(仍然在同一个 mysql 实例),这渐渐暴露出一个问题:测试环境的表结构会时常进行更新,而自动化环境却容易被遗漏。于是需要一个方案在每次CI构建的时候去做自动同步。

有一个基于 python 的库 SchemaSync 可以做这个事情,但是它对一些版本有要求,安装比较麻烦,而且五六年没更新了。想想这个其实也挺简单的,用几句 shell 就可以搞定,于是自己写了一个实现schema_sync.sh

原理类似 mysql 的主从同步,即每次从 binlog 中解析出最近一段时间对表的修改操作(CREATE、ALTER、DROP),然后在目标库重做一遍。主要用到的工具是 mysql 自带的两个工具:mysqlbinlogmysql,当然这里的前提是打开 binlog。

  1. #!/bin/bash
  2. # 一些程序及日志路径配置
  3. MYSQL_BINLOG_DIR=~/data/mysql/logs
  4. MYSQL_DIR=~/local/mysql
  5. LOG_DIR=~/data/schema-sync
  6. # 需要同步的源 DB 和目标 DB,注意要一一对应
  7. SRC_DB=(db_01 db_02 db_03)
  8. DEST_DB=(db_auto_01 db_auto_02 db_auto_03)
  9. # 数据库实例配置
  10. HOST=127.0.0.1
  11. PORT=3306
  12. USER=user
  13. PASS=123456
  14. # 每次同步修改的时间段,这里取一天
  15. START=$(date -d'-1 day' '+%Y-%m-%d %H:%M:%S')
  16. END=$(date '+%Y-%m-%d %H:%M:%S')
  17. for i in `seq 1 ${#SRC_DB[@]}`; do
  18. i=$((i - 1))
  19. SRC=${SRC_DB[$i]}
  20. DEST=${DEST_DB[$i]}
  21. STAMP=$(date "+%s")
  22. SQL=$LOG_DIR/$SRC.$STAMP.sql
  23. echo $SRC $DEST $STAMP $SQL
  24. echo -n '' > $SQL
  25. for binlog in $MYSQL_BINLOG_DIR/mysql-bin.0*; do
  26. echo $binlog
  27. # 这里用 awk 解析出来需要的 sql 语句
  28. cd $MYSQL_DIR && bin/mysqlbinlog -h$HOST -P$PORT -u$USER -p$PASS --disable-log-bin --force-if-open --database=$SRC --start-datetime="$START" --stop-datetime="$END" $binlog | awk 'BEGIN {
  29. RS="\n/\\*\\!\\*/;"
  30. IGNORECASE = 1
  31. }
  32. /(\nCREATE +TABLE)|(\nDROP +TABLE)|(\nALTER +TABLE)/ {
  33. print $0
  34. }' >> $SQL
  35. done
  36. if [ -s $SQL ]; then
  37. sed -i -e 's/\r//g' -e 's/^$/;/g' -e "s/\`${SRC}\`/\`${DEST}\`/g" -e '$ a;' $SQL
  38. bin/mysql -f -h$HOST -P$PORT -u$USER -p$PASS $DEST < $SQL
  39. else
  40. rm -f $SQL
  41. fi
  42. done

最后注意下 binlog 文件会很大,及时清理历史文件会大大提高脚本的执行速度。

2016-02-15 喜欢

Copyright © 2015-2018 转载请注明出处

回到顶部 ↑