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
      --responsetimeout <responsetimeout>            response timeout in seconds for reading response from the backend (default 0s; infinite)
   -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/

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 (\n) from output
      --responsetimeout <responsetimeout>            response timeout in seconds for reading response from the backend (default 0s; infinite)
   -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/

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
       --responsetimeout <responsetimeout>      response timeout in seconds for reading response from the backend (default 0s; infinite)
    -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

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
      --responsetimeout <responsetimeout>            response timeout in seconds for reading response from the backend (default 0s; infinite)
   -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.

Walkthrough

We consider a deployment with three MySQL master-slave clusters. On top of the database layer two groupware clusters (separate hazelcast grids) exist. We choose one node of every cluster as target for our RMI calls. Those nodes are then responsible to distribute cache invalidation events to all other nodes within the same hazelcast grid. All operations are performed on a separate provisioning node. This node is not part of any other hazelcast grid and does not serve any client requests. We are going to move schema db1_master_11 from userdb1 to userdb2.

Topology overview

Databases:

configdb

userdb1

cluster id: 6

schema: db1_master_11

userdb2

cluster id: 9

Groupware:

gw1:

node 1 (192.168.1.227)

node 2

gw2:

node 1 (192.168.1.228)

node 2

$ /opt/open-xchange/sbin/disableschema -A oxadminmaster -P secret -m db1_master_11 -r "192.168.1.227,192.168.1.228"

$ /opt/open-xchange/sbin/dumpschema -A oxadminmaster -P secret -m db1_master_11 -o /tmp/db1_master_11.dump
Dumping schema 'db1_master_11'... done.

$ /opt/open-xchange/sbin/replayschema -A oxadminmaster -P secret -t 9 -i /tmp/db1_master_11.dump -m db1_master_11
Connected to localhost
Pumping into schema 'db2_master_15' done.

$ /opt/open-xchange/sbin/enableschema -A oxadminmaster -P secret -m db2_master_15 -r "192.168.1.227,192.168.1.228"