2

In my setup for a web application using PHP and MySQL, I am using separate databases for each user's private data. So in essence, every authenticated user will have access to only two different databases myappdb_public and myappdb_user_###. User credentials will be saved in a separate database myappdb_creds. The idea behind this setup is to increase the security of private data while maintaining a shared data resource.

  1. Is there a way to create a new database from a "template" of another database structural setup.
  2. Is there a way to update all "child" databases structures when the template is updated?

I have used replication before, but that was a totally different setup and not sure if there is a way to replicate structure only across multiple databases on the same local server.

1

Replication

"Database Replication" refers to dynamically copying any incoming insert/update/delete statements being made to one database to another identical database. The primary and secondary databases are considered "replicas" because at all times they contain the same data produced by the same history, though the secondary may be delayed by a small stretch of time. The secondary may be used for read-only queries in place of the primary. And the secondary may be transformed into a primary in case of fail-over.

Migration

What you are seeking is loosely called "Database Migration", also called schema migration. The idea here is to reproduce the same structure (DDL) as another database. But each produced database is independent of the others, each having different users’ data stored in them.

  • A prime example example is multi-tenancy.
  • Another use might be as part of a vertical-market app being licensed to various customers where on each new sale you need to establish a fresh database in a certain configuration as expected by the app.

Migration tools

Developers and DBAs used to do this migration work manually, devising their own ad-hoc solutions or habits. Fortunately we now have a choice of tools to help with this tedious though crucial chore.

Flyway

My favorite tool is Flyway. Built in Java, but wrapped as command-line executables so you can use from scripts or other tools.

Flyway runs through a collection of SQL scripts, and optionally certain Java libraries you may write. The order of execution is determined by a certain file naming scheme.

A table is automatically created within the database to track scripts run. When new scripts are encountered, Flyway detects them and appropriately runs those that have not yet been applied to that particular database.

Flyway or other such tools are extremely helpful not only in production but also in testing and development. You can recreate a throw-away database automatically, and even bring it up to any particular point in the history of its schema evolution.

Anyone considering Flyway should also consider another quite similar product: Liquibase.

Other choices exist as well, such as the commercial tool Redgate.

Templates

Some databases such as Postgres provide for templates where you can create some tables and indexes and so in a database. After that, you can create a fresh new database whose initial state is copied from that state.

Indeed, Postgres always works that way, where template0 and template1 databases are used transparently by default when you ask for a database without explicitly specifying any other template.

Templates only solve the genesis problem, getting started. You will almost certainly evolve your database later. You'll add or drop tables, columns, indexes, and so on. You may need to run code to convert or update existing data en masse. For this you'll want to use migration tools, as the templates cannot help you after that initial genesis. So in my opinion, I would not bother with defining a template, instead using migration scripts for all that initial setup.

  • thank you! I never knew about these tools. I will do some further research to see which one will work best for my situation. Thank you for the great advice! – amaster Oct 15 '17 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.