How to automate migration (schema and data) for PHP/MySQL application

Question

I have an application in PHP/MySQL. I am searching for an automated way upgrading database behind the application. I don't need to have the compatibility with older versions once it is upgraded.

I have read jeff's and K. Scott Allen's articles on this.

I am still not sure how to implement this for a PHP/MySQL application.

Is there any simple and good process for this?

Answer 1

One way you can go about it is to dump the database into a large sql file using mysqldump. Just take that file and source it within the new installation.

Answer 2

I have a "Schema" object that I use - but you could do the same without classes..

What you want to do is create a 'db_schema_versions' table:

CREATE TABLE db_schema_versions (`table` varchar(255) NOT NULL PRIMARY KEY, `version` INT NOT NULL)

After your database can track what version # it is on - it can do SQL upgrades automatically.

You should lock your schema table while upgrading schema. This way you wont have two requests at the same moment trying to upgrade your schema.

So - keep track of the version you are upgrading from - build a big switch - something like this:

class SNTrack_Db_Schema extends MW_Db_Schema_Abstract {
  protected $table = "sntrack_db_schema";
  protected $version = 5;

  protected function upgrade($fromVersion) {
    // don't break
    switch($fromVersion) {
      case 0:
        $this->db->query('CREATE TABLE sntrack_inbound_shipment (
            `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            `from` VARCHAR(255) NOT NULL,
            `date` DATE NOT NULL,
            `invoice` VARCHAR(255) NOT NULL,
            `notes` TEXT
          )');
        $this->setVersion(1);
      case 1:
        $this->db->query('ALTER TABLE sntrack_details ADD `shipment_id` INT');
        $this->db->query('ALTER TABLE sntrack_product ADD `inventory` INT NOT NULL DEFAULT 0');
        $this->db->query('CREATE TABLE sntrack_inventory_shipment (
            `shipment_id` INT NOT NULL,
            `product_id` INT NOT NULL,
            `qty` INT NOT NULL,
            PRIMARY KEY (`shipment_id`, `product_id`)
          )');
        $this->setVersion(2);
...etc

Answer 3

Similar to gnarf's suggestion I would run with the following:

• For every change to the schema create an SQL file that when run will take you from the old version to the new version (this could be one file per major version or lots of smaller changes).
• Create a separate file listing each of the SQL file names in the order they must be applied in (oldest at the top, newest at the bottom)
• Create a simple "versioning" table (all it needs is a single VARCHAR column) in your database

Now you need to write a simple script that works in the following way:

• Query the versioning table for the name of the last SQL update file applied
• If there are newer SQL alteration files to be run execute them in sequence
• record the name of the newest SQL file applied

I hope that makes sense

Answer 4

You may use command-line mysql-diff tool that compares MySQL database schemas (live or from SQL script) and outputs difference as SQL ALTER script.

mysql-diff outputs the diff, you look at the diff, and if it is OK just execute the diff in mysql, like this:

mysql-diff <server> newdb.sql > newdb.sql
less newdb.sql # Is it OK?
mysql < newdb.sql # apply the diff

Answer 5

You can't. You either

1. Write update files with all sql's that were executed on source enviroment and then execute them (mentioned above), SVN-like. Need php script for execution and manual work for sql writing

2. Post-analyze both enviroments and suggest to user which updates should be migrated. Basically same thing as the first one, except that you have one big step for migration, not lots of small chunks. SQLyog can analyze diffrences for both schema and data.