Veranderen database prefix

Een van mijn opdrachtgevers wilde binnen zijn (bestaande)hostingpakket verschillende CMS Made Simple installaties draaien. Geen enkel probleem zou je denken. Echter het hostingpakket van deze opdrachtgever heeft maar één mysql database geinstalleerd.

Op mijn ontwikkel server had ik voor iedere website reeds een andere database aangemaakt, deze databases hadden allen dezelfde tabel prefix 'cmsprefix_'

Om de tabel namen van mijn ontwikkel server op een handige manier te hernoemen heb ik een shell script geschreven, deze wil in deze blog graag met u delen.

Uitgangs situatie database namen ontwikkel server

website01 - databasenaam01.cmsprefix_tabelnaam
website02 - databasenaam02.cmsprefix_tabelnaam

Gewenste situatie database namen productie server

website01 - databasenaam01.cmsprefix01_tabelnaam
website02 - databasenaam01.cmsprefix02_tabelnaam

SQL rename query

Het veranderen van de prefix van een (CMS Made Simple) database tabel kan met de volgende SQL query.

RENAME TABLE databasenaam01.cmsprefix_tabelnaam TO databasenaam01.cmsprefix01_tabelnaam;

Een geautomatiseerde oplossing

Het uitschrijven van bovengenoemde RENAME query voor een groot aantal tabellen is een tijdsintensieve klus.

Maar dit kan geautomatiseerd worden met een MySQL rename shell script. Hiermee wordt verbinding gemaakt met de mysql database, de tabelnamen uitgelezen en voor iedere tabelnaam een "RENAME" query geprint.

MySQL rename shell script stelt eisen aan de hostingomgeving

Niet op iedere hosting omgeving is het mogelijk om shell scripten te draaien, meestal kan dit alleen op (*)nix machines.

Daarnaast:

• Dien je ssh toegang te hebben
• Zal de mysql command line tool 'mysqlshow' aanwezig moeten zijn
• Dien je te beschikken over de mysql database (inlog)gegevens

Een uitgewerkt shell script

#!/usr/bin/sh

<<DOCUMENTATION

script_name: change_mysql_table_prefix.sh 
purpose: Change the prefix of a mysql database table

- pipe the output of this script to a .sql file (sh this_script.sh > ./output.sql)
- verify the output (cat ./output.sql)
- execute it (mysql -u user-p -h localhost db_name < ./output.sql)

DOCUMENTATION

##############################################################
# config
##############################################################

DB_HOSTNAME="localhost"
DB_USERNAME="db_username"
DB_PASSWORD="db_password"
DB_NAME="db_name"

# some database do not have any prefix yet: old_db_prefix = '';
HAVE_PREFIX="0"
OLD_DB_PREFIX="^|"
NEW_DB_PREFIX="cmsprefix01_"

# some databases already have a prefix old_db_prefix = 'cmsprefix_' 
HAVE_PREFIX="1"
OLD_DB_PREFIX="cmsprefix_"
NEW_DB_PREFIX="cmsprefix01_"

##############################################################
# program
##############################################################

for x in `/usr/bin/mysqlshow --user=$DB_USERNAME --password=$DB_PASSWORD --host=$DB_HOSTNAME $DB_NAME | grep $OLD_DB_PREFIX | grep -v Tables | tr -d " | "` 
do 

    if [ $HAVE_PREFIX = 1 ]
        then
            y=`echo $x | sed "s/$OLD_DB_PREFIX/$NEW_DB_PREFIX/g"`
            echo "RENAME TABLE $DB_NAME.$x TO $DB_NAME.$y;"
        else
            echo "RENAME TABLE $DB_NAME.$x TO $DB_NAME.$NEW_DB_PREFIX$x;" 
    fi            
      
done

Video

Voor een werkend voorbeeld bekijk onderstaande video.