Rename MariaDB Schema
This script moves all the tables, triggers, views, functions, events, procedures to a new database schema. It has been tested in MariaDB version 10.2.x.
In the shell script you have to replace the USERNAME, PASSWORD, SERVER, the MYSQL_PORT if needed, the Source Database and the Target Database. If the target database does not exists, it is created with UTF8 encoding, if the database exists and is not empty the script fails.
#!/bin/bash -e
MYSQL_USER=<<USERNAME>>
MYSQL_PASSWORD=<<PASSWORD>>
MYSQL_HOST=<<SERVER>>
MYSQL_PORT=3306
SOURCE_DATABASE=<<SOURCE_DATABASE>>
TARGET_DATABASE=<<TARGET_DATABASE>>
USEROPTIONS="--user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST} --port=${MYSQL_PORT}"
USEROPTIONS_WITH_SOURCE="${USEROPTIONS} ${SOURCE_DATABASE}"
if [ -z "`mysqladmin $USEROPTIONS status | grep 'Uptime'`" ]
then
echo "HALTED: MySQL does not appear to be running."; echo
exit 1
fi
TARGET_EXISTS=$(mysql --skip-column-names --batch -e \
"SHOW DATABASES LIKE '$TARGET_DATABASE'" $USEROPTIONS_WITH_SOURCE )
if [ ! -n "$TARGET_EXISTS" ]; then
mysql -uroot -p -e "CREATE DATABASE $TARGET_DATABASE default character set utf8" $USEROPTIONS
fi
COUNT_TARGET_TABLES=$(mysql --skip-column-names --batch -e \
"select count(*) from information_schema.tables \
where table_schema = '$TARGET_DATABASE'" $USEROPTIONS_WITH_SOURCE )
if [ "$COUNT_TARGET_TABLES" -ne 0 ]; then
echo "The target database $TARGET_DATABASE is not empty. Exiting..."
exit 1;
fi
echo "Dump Views, Triggers, Events, Functions from $SOURCE_DATABASE...";
SOURCE_VIEWS=$(mysql --skip-column-names --batch -e \
"select table_name from information_schema.views \
where table_schema = '$SOURCE_DATABASE'" $USEROPTIONS_WITH_SOURCE )
mysqldump --routines --events --no-create-info --no-data --no-create-db --skip-opt $USEROPTIONS_WITH_SOURCE > $SOURCE_DATABASE-dump-views-triggers-functions-events.sql
mysqldump --no-create-db --skip-opt --no-data $USEROPTIONS_WITH_SOURCE $SOURCE_VIEWS >> $SOURCE_DATABASE-dump-views-triggers-functions-events.sql
sed 's/`SOURCE_DATABASE`.//g' $SOURCE_DATABASE-dump-views-triggers-functions-events.sql > $SOURCE_DATABASE-dump-views-triggers-functions-events.sql
echo "Generate table move scripts from $SOURCE_DATABASE to $TARGET_DATABASE...";
mysql --skip-column-names --batch -e \
"select concat('DROP EVENT $SOURCE_DATABASE.',event_name, ';') FROM information_schema.events where event_schema = '$SOURCE_DATABASE'" $USEROPTIONS_WITH_SOURCE > $SOURCE_DATABASE-rename-database.sql
mysql --skip-column-names --batch -e \
"SELECT Concat('DROP TRIGGER $SOURCE_DATABASE.', Trigger_Name, ';') FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = '$SOURCE_DATABASE'" $USEROPTIONS_WITH_SOURCE >> $SOURCE_DATABASE-rename-database.sql
mysql --skip-column-names --batch -e \
"SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name, ' TO ','$TARGET_DATABASE.',table_name,';') \
FROM information_schema.TABLES \
WHERE table_schema = '$SOURCE_DATABASE' \
and table_type !='VIEW';" $USEROPTIONS_WITH_SOURCE >> $SOURCE_DATABASE-rename-database.sql
echo "Run table move scripts from $SOURCE_DATABASE to $TARGET_DATABASE...";
mysql $USEROPTIONS $TARGET_DATABASE < $SOURCE_DATABASE-rename-database.sql
echo "Recreate views, triggers, events, functions to $TARGET_DATABASE...";
mysql $USEROPTIONS $TARGET_DATABASE < $SOURCE_DATABASE-dump-views-triggers-functions-events.sql