1

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
3

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.

|improve this answer|||||
  • 1
    I added an example of before/after database and tables to the question. I don't understand everything yet, but it seems the script does what I need – Thomas Weller Jan 9 '16 at 18:49
  • Thank you for the clarification. However, I still don't understand the part of removing/renaming the prefix. From where do you want to remove the prefix? – Matias SM Jan 9 '16 at 19:32
  • Also, as for your example, the script should work. If you test it and it does, please remember to mark the answer as the solution (click the tick). If it doesn't, share the problem and I will try to further help you. – Matias SM Jan 9 '16 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.