AppSuite:MoveDBSchemas - Revision history

Thorben: /* Toolset */

2015-04-23T14:29:54Z

Toolset

Thorben: /* Toolset */

2015-04-23T14:27:27Z

Toolset

← Older revision		Revision as of 14:27, 23 April 2015
Line 31:		Line 31:
	--environment Show info about commandline environment		--environment Show info about commandline environment
	--nonl Remove all newlines (\n) from output		--nonl Remove all newlines (\n) from output
	--responsetimeout <responsetimeout> response timeout in seconds for reading response from		--responsetimeout <responsetimeout> response timeout in seconds for reading response from the backend (default 0s; infinite)
	the backend (default 0s; infinite)
	-A,--adminuser <adminuser> ? master Admin user name		-A,--adminuser <adminuser> ? master Admin user name
	-P,--adminpass <adminpass> ? master Admin password		-P,--adminpass <adminpass> ? master Admin password

Thorben at 14:25, 23 April 2015

2015-04-23T14:25:55Z

← Older revision		Revision as of 14:25, 23 April 2015
Line 31:		Line 31:
	--environment Show info about commandline environment		--environment Show info about commandline environment
	--nonl Remove all newlines (\n) from output		--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		-A,--adminuser <adminuser> ? master Admin user name
	-P,--adminpass <adminpass> ? master Admin password		-P,--adminpass <adminpass> ? master Admin password
Line 42:		Line 44:
	-h,--help Prints a help text		-h,--help Prints a help text
	--environment Show info about commandline environment		--environment Show info about commandline environment
	--nonl Remove all newlines (		--nonl Remove all newlines (\n) from output
	) from output		--responsetimeout <responsetimeout> response timeout in seconds for reading response from
			the backend (default 0s; infinite)
	-A,--adminuser <adminuser> ? master Admin user name		-A,--adminuser <adminuser> ? master Admin user name
	-P,--adminpass <adminpass> ? master Admin password		-P,--adminpass <adminpass> ? master Admin password
Line 74:		Line 77:
	--environment Show info about commandline environment		--environment Show info about commandline environment
	--nonl Remove all newlines (\n) from output		--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		-A,--adminuser <adminuser> ? master Admin user name
	-P,--adminpass <adminpass> ? master Admin password		-P,--adminpass <adminpass> ? master Admin password

Steffen.templin: /* Walkthrough */

2014-12-12T11:25:31Z

Walkthrough

← Older revision		Revision as of 11:25, 12 December 2014
Line 110:		Line 110:

	$ /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/enableschema -A oxadminmaster -P secret -m db2_master_15 -r "192.168.1.227,192.168.1.228"

			[[Category: OX7]]
			[[Category: AppSuite]]
			[[Category: Administrator]]
			[[Category: Database]]

Steffen.templin at 10:20, 12 December 2014

2014-12-12T10:20:50Z

← Older revision		Revision as of 10:20, 12 December 2014
Line 82:		Line 82:

	== Walkthrough ==		== 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>.		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 <tt>db1_master_11</tt> from <tt>userdb1</tt> to <tt>userdb2</tt>.

	;Topology overview:		;Topology overview:

Steffen.templin at 09:51, 12 December 2014

2014-12-12T09:51:01Z

← Older revision		Revision as of 09:51, 12 December 2014
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)~~

Steffen.templin: Created page with " {{VersionFrom|7.6.2}}
How to move database schemas from one database cluster to anothe..."

2014-12-12T09:14:34Z

Created page with " {{VersionFrom|7.6.2}} <div class="title">How to move database schemas from one database cluster to anothe..."

New page

{{VersionFrom|7.6.2}}

<div class="title">How to move database schemas from one database cluster to another</div>

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

__TOC__

== 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)

← Older revision		Revision as of 14:29, 23 April 2015
Line 44:		Line 44:
	--environment Show info about commandline environment		--environment Show info about commandline environment
	--nonl Remove all newlines (\n) from output		--nonl Remove all newlines (\n) from output
	--responsetimeout <responsetimeout> response timeout in seconds for reading response from		--responsetimeout <responsetimeout> response timeout in seconds for reading response from the backend (default 0s; infinite)
	the backend (default 0s; infinite)
	-A,--adminuser <adminuser> ? master Admin user name		-A,--adminuser <adminuser> ? master Admin user name
	-P,--adminpass <adminpass> ? master Admin password		-P,--adminpass <adminpass> ? master Admin password
Line 59:		Line 58:
	-A,--adminuser <admin_user> Admin username		-A,--adminuser <admin_user> Admin username
	-h Prints a help text		-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		-i,--in <dump_file> The dump file previously created with dumpschema
	-l,--login <jmxLogin> The optional JMX login (if JMX authentication is enabled)		-l,--login <jmxLogin> The optional JMX login (if JMX authentication is enabled)
Line 76:		Line 76:
	--environment Show info about commandline environment		--environment Show info about commandline environment
	--nonl Remove all newlines (\n) from output		--nonl Remove all newlines (\n) from output
	--responsetimeout <responsetimeout> response timeout in seconds for reading response from		--responsetimeout <responsetimeout> response timeout in seconds for reading response from the backend (default 0s; infinite)
	the backend (default 0s; infinite)
	-A,--adminuser <adminuser> ? master Admin user name		-A,--adminuser <adminuser> ? master Admin user name
	-P,--adminpass <adminpass> ? master Admin password		-P,--adminpass <adminpass> ? master Admin password

AppSuite:MoveDBSchemas - Revision history

Thorben: /* Toolset */

Thorben: /* Toolset */

Thorben at 14:25, 23 April 2015

Steffen.templin: /* Walkthrough */

Steffen.templin at 10:20, 12 December 2014

Steffen.templin at 09:51, 12 December 2014

Steffen.templin: Created page with " {{VersionFrom|7.6.2}} How to move database schemas from one database cluster to anothe..."

Steffen.templin: Created page with " {{VersionFrom|7.6.2}}
How to move database schemas from one database cluster to anothe..."