Database Migration¶
We support database migration from H2 to mysql or mariadb.
Migration steps¶
stop code scene service
dump the h2 database content:
java -cp ${path_to_jdbc_driver}/h2-1.4.193.jar org.h2.tools.Script -url "jdbc:h2:file:${path_to_database_file}/caacs_enterprise.db" -user sa -script /tmp/codescene.h2.sql -options SIMPLE
prepare dump file for mysql/mariadb:
cat /tmp/codescene.h2.sql | grep "INSERT INTO" | grep -v "RAGTIME_MIGRATIONS" | grep -v "DATABASECHANGELOGLOCK" | grep -v "DATABASECHANGELOG" | sed 's/PUBLIC.//g' | sed 's/\\/\\\\\\/g' | sed 's/KEY,/\`KEY\`,/g' | echo -e "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n$(cat -)\nSET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;" > /tmp/codescene.mysql.sql
start code scene using external database
clean up mysql/mariadb database before import:
mysqldump --column-statistics=0 --default-character-set=utf8mb4 -h${db-host} -d -u${db-user} -p${db-password} --add-drop-table ${db-name} > /tmp/codescene.schema.sql mysql --default-character-set=utf8mb4 -h${db-host} -u${db-user} -p${db-password} ${db-name} < /tmp/codescene.schema.sql echo -e "DELIMITER // CREATE FUNCTION STRINGDECODE(str TEXT CHARSET utf8mb4) RETURNS text CHARSET utf8mb4 DETERMINISTIC BEGIN declare pos int; declare escape char(6) charset utf8mb4; declare unescape char(3) charset utf8mb4; set pos = locate('\\u', str); while pos > 0 do set escape = substring(str, pos, 6); set unescape = char(conv(substring(escape,3),16,10) using ucs2); set str = replace(str, escape, unescape); set pos = locate('\\u', str, pos+1); end while; return str; END// DELIMITER ;" | mysql --default-character-set=utf8mb4 -h${db-host} -u${db-user} -p${db-password} ${db-name}
import data into mysql/maridb database:
mysql --default-character-set=utf8mb4 -h${db-host} -u${db-user} -p${db-password} < /tmp/codescene.mysql.sql