AppSuite:MoveDBSchemas

This information is valid from 7.6.2 on.

How to move database schemas from one database cluster to another

Summary: With release 7.6.2 it is possible to move whole database schemas between database clusters online. A set of command line tools exists to perform the necessary steps. For dumping and replaying a schema, plain MySQL tools (mysqldump and mysql) are used, while the groupware takes care that all according contexts are locked, so that no data can be manipulated during the process.

General assumptions

The operator performs all steps on one dedicated OX node that has open-xchange-admin installed and is properly configured for administrative/provisioning tasks. It must be possible to connect to all involved database clusters from this node (configdb, source cluster, target cluster). If more than one groupware cluster (hazelcast grid) operate on the same databases, one node of each cluster must be reachable via RMI. Of course all groupware nodes of the whole deployment need to be up to date and the hazelcast grids need to work properly.

Disabling contexts requires that a certain reason, specified by an unique integer identifier, is stored within the context table in the configuration database. The concrete identifier must be configurable, because reason identifiers can be specified by operators on their own and therefore conflict with our delivered default value. If 'disableschema' is called, all currently enabled contexts are deactivated with the configured reason identifier. When 'enableschema' is called afterwards only those contexts in the given schema are enabled, which have the special reason identifier set. This is needed to preserve the deactivation contexts due to other reasons. Therefore the file /opt/open-xchange/etc/plugin/hosting.properties was extended, to allow adjustment of this identifier:

# The reason ID to use when contexts are disabled due to a schema move. This id will not be
# stored in the 'reason_text' table but must not conflict with existing reasons. Otherwise
# wrong contexts may get enabled after a schema move.
SCHEMA_MOVE_MAINTENANCE_REASON=1431655765

Toolset

The according toolset consists of four command line tools:

disableschema

• disables all contexts for a given schema within the configdb
• invalidates context-caches cluster-wide
• terminates according sessions cluster-wide

  Usage: disableschema
   -h,--help                                         Prints a help text          
      --environment                                  Show info about commandline environment
      --nonl                                         Remove all newlines (\n) from output
   -A,--adminuser <adminuser>                      ? master Admin user name      
   -P,--adminpass <adminpass>                      ? master Admin password       
   -m,--schema <schema_name>                       * The name of the schema to disable
   -r,--rmi-hosts <rmi_hosts>                        A list of RMI hosts e.g. 192.168.1.25:1099,192.168.1.26. If no port is given the default RMI port 1099 is taken.. Default: rmi://localhost:1099/

2. dumpschema

- wraps mysqldump and writes the dump into a file

  Usage: dumpschema
   -h,--help                                         Prints a help text          
      --environment                                  Show info about commandline environment
      --nonl                                         Remove all newlines (
  ) from output
   -A,--adminuser <adminuser>                      ? master Admin user name      
   -P,--adminpass <adminpass>                      ? master Admin password       
   -m,--schema <schema_name>                       * The name of the schema to dump.
   -o,--out <dump_file>                            * The name of the dump file.  
   -r,--rmi-host <rmi_host>                          A RMI host address e.g. 192.168.1.25:1099. If no port is given the default RMI port 1099 is taken.. Default: rmi://localhost:1099/

3. replayschema

- wraps the mysql client call to replay the schema
- updates the configdb, so that all contexts of the original schema now reference the new location

  Usage: replayschema [-h] [-m <source_schema> -t <target_cluster_id> -i <dump_file> -A <admin_user> -P <admin_pass> [-l <jmxLogin> -s <jmxPassword> [-p <jmxPort]] -r <rmi-host>]
    -A,--adminuser <admin_user>                 Admin username
    -h                                          Prints a help text
    -i,--in <dump_file>                         The dump file previously created with dumpschema   
    -l,--login <jmxLogin>                       The optional JMX login (if JMX authentication is enabled)
    -m,--source-schema <source_schema>          Source schema name
    -P,--adminpass <admin_pass>                 Admin password
    -p,--port <jmxPort>                         The optional JMX port (default:9999)
    -r,--rmi-host <rmi_host>                    A RMI host address e.g. 192.168.1.25:1099. If no port is given the default RMI port 1099 is taken. Default: rmi://localhost:1099/
    -s,--password <jmxPassword>                 The optional JMX password (if JMX authentication is enabled)
    -t,--target-cluster-id <target_cluster_id>  The target cluster identifier

4. enableschema

- enables all contexts for a given schema within the configdb
- invalidates context-caches cluster-wide

  Usage: enableschema
   -h,--help                                         Prints a help text          
      --environment                                  Show info about commandline environment
      --nonl                                         Remove all newlines (\n) from output
   -A,--adminuser <adminuser>                      ? master Admin user name      
   -P,--adminpass <adminpass>                      ? master Admin password       
   -m,--target-schema <schema_name>                * The name of the schema to enable.
   -r,--rmi-hosts <rmi_hosts>                        A list of RMI hosts e.g. 192.168.1.25:1099,192.168.1.26. If no port is given the default RMI port 1099 is taken.. Default: rmi://localhost:1099/

To move a whole schema the tools are meant to be used in the described order. Every tool is capable to be executed remotely on a given OX backend host via RMI. Every tool that performs cluser-wide operations accepts a list of remote hosts. This option can be used to trigger those operations accross different hazelcast-domains which operate on top of the same database infrastructure. In that case one host of every hazelcast cluster has to be specified.

   2. disableschema (from now on gatling should show only errors)
      $ /opt/open-xchange/sbin/disableschema -A oxadminmaster -P secret -m db1_master_11 -r "192.168.1.227,192.168.1.228"
   3. dumpschema
      $ /opt/open-xchange/sbin/dumpschema -A oxadminmaster -P secret -m db1_master_11 -o /tmp/db1_master_11.dump
   4. replayschema
      $ /opt/open-xchange/sbin/replayschema -A oxadminmaster -P secret -t 9 -i /tmp/db1_master_11.dump -m db1_master_11
   5. enableschema (we expect more and more requests to be successful again)
      $ /opt/open-xchange/sbin/enableschema -A oxadminmaster -P secret -m <newschema> -r "192.168.1.227,192.168.1.228"
   6. stop gatling and start again (we expect all requests to succeed, wait for completion)
   7. dumpschema (again the old one, for comparison)
      $ /opt/open-xchange/sbin/dumpschema -A oxadminmaster -P secret -m db1_master_11 -o /tmp/db1_master_11_cmp.dump
   8. diff both schemas, no rows must have been created/updated/deleted
      $ diff /tmp/db1_master_11.dump /tmp/db1_master_11_cmp.dump
   9. drop schema db1_master_11
  10. start gatling again (we expect all requests to succeed, wait for completion)