Split mySQL tables into databases by prefix

Question

I operated a small low cost server that was restricted to one mySQL database. Since most (PHP) programs are aware of the issue of having only one database, the common workaround is to define tables in the same database and distinguish between them by an application specific prefix.

Since I upgraded to a server which allows an unlimited number of databases, I'd now like to split this large database into many smaller databases. I hope that this has a few benefits:

• easier backup
• easier migration / deletion of databases
• better security

Therefore I am now looking for a tool that

• runs on Ubuntu
• splits tables of one database into several databases by table prefix
• is free (gratis)
• has an option to rename or remove the prefix

I'll take care about the rest, like changing the configuration to point to the new database, so this is a pure mySQL task. The tool should, basically, be moving tables with a specific prefix from one database to another, while (optionally) removing/renaming that prefix.

Example:

Before:
Database (the one and only database)
- Table blog1_users
- Table blog1_posts
- Table wordpress_users
- Table wordpress_posts
- Table wordpress_comments

After (without renaming):
Blog1 (database created by the tool)
- Table blog1_users
- Table blog1_posts
Wordpress (database created by the tool)
- Table wordpress_users
- Table wordpress_posts
- Table wordpress_comments

After (with renaming):
Ubuntublog (database created by the tool, renaming from blog1 to ubuntublog)
- Table ubuntublog_users
- Table ubuntublog_posts
DevBlog (database created by the tool, renaming from wordpress to devblog)
- Table devblog_users
- Table devblog_posts
- Table devblog_comments

Answer 1

You can do it with bash and mysql(dump). I don't have a MySQL right now to test it, but should be something like the following:

mysql -uUSER -p SRC_DB -e 'show tables' | tail -n+2 | grep '^PREFIX' | xargs mysqldump --no-create-db -uUSER -p SRC_DB > CREATE_DB_DST.sql

where:

• USER is a user with read access in SRC_DB
• SRC_DB is the database you want to partition
• PREFIX is the prefix of the tables to save in the new (partition) db
• CREATE_DB_DST.sql is the name of the script you will use to create the new DB

Then you can name the database whatever you want (with or without prefix and so).

If you the destination databases are to many to do it manually, you can do it in a bash for loop:

dump_tables() {
    local PREFIX="$1"
    mysql -uUSER -p SRC_DB -e 'show tables' | tail -n+2 | grep "^$PREFIX" | xargs mysqldump --no-create-db -uUSER -p SRC_DB > ${PREFIX}.sql
}

recreate_table () {
    local PREFIX="$1"
    mysql -uUSER -p -e "create database $PREFIX"
    mysql -uUSER -p "$PREFIX" < ${PREFIX}.sql
}

for prefix in PREFIX1 PREFIX2 PREFIX3; do
    dump_tables $prefix
    recreate_table $prefix

I didn't quite understand your requirement of removing the prefix and related. You will have one table per dst DB?

Changing PREFIX can be easily done with bash string manipulation. You can/should also optimize the dump by using the corresponding arguments in mysqldump.

Sorry I can't give you a working example, but I hope you get the idea.