Difference between revisions of "AppSuite:MoveDBSchemas"

(Created page with "<!-- Author: Steffen Templin <steffen.templin@open-xchange.com> --> {{VersionFrom|7.6.2}} <div class="title">How to move database schemas from one database cluster to anothe...")
 
Line 36: Line 36:
 
     -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/
 
     -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
+
;dumpschema
- wraps mysqldump and writes the dump into a file
+
* wraps mysqldump and writes the dump into a file
  
 
   Usage: dumpschema
 
   Usage: dumpschema
Line 50: Line 50:
 
     -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/
 
     -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
+
;replayschema
- wraps the mysql client call to replay the schema
+
* 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
+
* 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>]
 
   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>]
Line 66: Line 66:
 
     -t,--target-cluster-id <target_cluster_id>  The target cluster identifier
 
     -t,--target-cluster-id <target_cluster_id>  The target cluster identifier
  
4. enableschema
+
;enableschema
- enables all contexts for a given schema within the configdb
+
* enables all contexts for a given schema within the configdb
- invalidates context-caches cluster-wide
+
* invalidates context-caches cluster-wide
  
 
   Usage: enableschema
 
   Usage: enableschema
Line 81: Line 81:
 
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.
 
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 the 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 <tt>db1_master_11</tt> from <tt>userdb1</tt> to <tt>userdb2</tt>.
  
 +
;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
  
    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"
      $ /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
      $ /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.
    4. replayschema
+
 
      $ /opt/open-xchange/sbin/replayschema -A oxadminmaster -P secret -t 9 -i /tmp/db1_master_11.dump -m db1_master_11
+
$ /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)
+
Connected to localhost
      $ /opt/open-xchange/sbin/enableschema -A oxadminmaster -P secret -m <newschema> -r "192.168.1.227,192.168.1.228"
+
Pumping into schema 'db2_master_15' done.
    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/enableschema -A oxadminmaster -P secret -m db2_master_15 -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_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)
 

Revision as of 09:51, 12 December 2014


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

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 the 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"