mysql merge

by · 2019年04月17日 · 604 Words · ~3min reading time | Improve on


原文链接： mysql merge
#!/bin/sh
#
# SCRIPT:	mysqlmerge.sh
# AUTHOR:	dreamingfish123@gmail.com
# DATE:		2014-02-17
# REV:		1.0.T
#
# PURPOSE:	Merge Bugzilla databases
set -e
# config, please modify to match your system env.
# ============
ORIGIN=/media/bugzilla
WORKSPACE=/media/bugzilla
# ============
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASS=root
MYSQL_MAX_ALLOWED_PACKET=1000000000
# ============

# === Init params..
DATE=`date +%Y%m%d`
DAY=`date +%a`
SRCPATH=$ORIGIN/$DAY
TARPATH=$WORKSPACE/$DATE
LOGFILE=$WORKSPACE/bugzilla_merge.log
# ===
MYSQL_CONNPARAM="-h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS --max-allowed-packet=$MYSQL_MAX_ALLOWED_PACKET"
TAR_DBNAME=mergetest
TABLES1="products versions bugs bug_see_also bugs_fulltext attach_data attachments cc components longdescs"
TABLES2="profiles rep_platform op_sys"

echo "==========="	>> $LOGFILE
echo ""			>> $LOGFILE
echo "[*] Init params..">> $LOGFILE
echo "DATE:`date +%y/%m/%d_%H:%M:%S` DAY:$DAY"	>> $LOGFILE
echo "SRCPATH:$SRCPATH"	>> $LOGFILE
echo "TARPATH:$TARPATH" >> $LOGFILE
echo "LOGFILE:$LOGFILE"	>> $LOGFILE

if [ ! -d $SRCPATH ]; then
	echo "SRCPATH not exists..:$SRCPATH"	>> $LOGFILE
	exit 1
fi

# === Delete old tmp sql files
DATE_LAST_WEEK=`date -d last-week +%Y%m%d`
if [ -d $WORKSPACE/$DATE_LAST_WEEK ]; then
	rm -rf $WORKSPACE/$DATE_LAST_WEEK
fi

if [ ! -d $TARPATH ]; then
	mkdir -p "$TARPATH"
fi

cp $SRCPATH/*.sql.gz $TARPATH/
gzip -d $TARPATH/*.gz

dbopt_newdb() {
	echo "Import DB: $2"	>> $LOGFILE
	mysql $MYSQL_CONNPARAM <<EOF
	drop database if exists $1;
	create database $1;
	use $1;
	source $2;
EOF
}

dbopt_inittar() {
	echo "Init Base DB: $1 by mysqldump DB: $2"	>> $LOGFILE
	mysql $MYSQL_CONNPARAM <<EOF
	drop database if exists $1;
	create database $1;
EOF

	mysqldump $MYSQL_CONNPARAM $2 | mysql $MYSQL_CONNPARAM $1
}

dbopt_merge1() {
	echo "Do Merge.."	>> $LOGFILE
	echo $1 $2 $3 $4	>> $LOGFILE
	DB2="${2}2"
	mysql $MYSQL_CONNPARAM <<EOF
	use $2;
	delete from products where name='Product-TmpB';
	update bugs set bug_id=bug_id+$3;
	update attachments set attach_id=attach_id+$4;
	update bug_see_also set id=id+$3;
	update bugs_fulltext set bug_id=bug_id+$3;
	update classifications set id=id+$4;
	update components set id=id+$4;
	update longdescs set comment_id=comment_id+$3;
	update products set id=id+$4;
	update profiles set userid=userid+$4;
	update rep_platform set id=id+$4;
	update op_sys set id=id+$4;
	update versions set id=id+$4;
	update classifications a set id=(select b.id from $1.classifications b where a.name =b.name) where a.name in (select name from $1.classifications);
	update profiles a set userid=(select b.userid from $1.profiles b where a.login_name =b.login_name) where a.login_name in (select login_name from $1.profiles);
	update profiles a set userid=(select b.userid from $1.profiles b where a.extern_id=b.extern_id) where a.extern_id in (select extern_id from $1.profiles);
	update rep_platform a set id=(select b.id from $1.rep_platform b where a.value=b.value) where a.value in (select value from $1.rep_platform);
	update op_sys a set id=(select b.id from $1.op_sys b where a.value=b.value) where a.value in (select value from $1.op_sys);
	drop database if exists $DB2;
	create database $DB2;	
EOF

	mysqldump $MYSQL_CONNPARAM --databases $2 --complete-insert --tables $TABLES2 | mysql $MYSQL_CONNPARAM $DB2
	mysql $MYSQL_CONNPARAM <<EOF
	use $DB2;
	delete from profiles where userid in (select userid from $1.profiles);
	delete from rep_platform where id in (select id from $1.rep_platform);
	delete from op_sys where id in (select id from $1.op_sys);
EOF

	mysqldump $MYSQL_CONNPARAM --databases $DB2 --no-create-info --complete-insert --tables $TABLES2 | mysql $MYSQL_CONNPARAM $1
	mysqldump $MYSQL_CONNPARAM --databases $2 --no-create-info --complete-insert --tables $TABLES1 | mysql $MYSQL_CONNPARAM $1 
}

dbopt_export() {
	echo ""		>> $LOGFILE
	echo "[*] Export New DB: $TARPATH/$1.sql"	>> $LOGFILE
	mysqldump $MYSQL_CONNPARAM --databases $1 --complete-insert --tz-utc=FALSE > $TARPATH/$1.sql
}

echo ""		>> $LOGFILE
echo "[*] List files to process.."	>> $LOGFILE
cd $TARPATH
for i in `ls *.sql`; do
	echo $i		>> $LOGFILE
done

echo ""		>> $LOGFILE
echo "[*] Start process.."	>> $LOGFILE
conOffset=$((10000))
curOffset=$((0))
conOffset2=$((5000))
curOffset2=$((0))
for sqlfile in `ls *.sql`; do
	echo ""			>> $LOGFILE
	echo "File:$sqlfile"	>> $LOGFILE	
	DBNAME=${sqlfile%.sql*}	
	echo "DB:$DBNAME"	>> $LOGFILE
	dbopt_newdb $DBNAME $TARPATH/$sqlfile
	if [ $curOffset -le 0 ]; then
		dbopt_inittar $TAR_DBNAME $DBNAME	
	else
		dbopt_merge1 $TAR_DBNAME $DBNAME $curOffset $curOffset2 
	fi
	curOffset=$((curOffset+conOffset))
	curOffset2=$((curOffset2+conOffset2))
	#echo $curOffset
done

dbopt_export $TAR_DBNAME

echo ""		>> $LOGFILE
echo "[*] Source to remote sql"		>> $LOGFILE
mysql -utest -ptest --max-allowed-packet=1000000000 -hremoteserver <<EOF
drop database if exists mergetest;
create database mergetest;
use mergetest;
source $TARPATH/$TAR_DBNAME.sql
EOF

Tags:

`