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.
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