What are the changes that are needed to be performed to change a database name for a node once clustering is set-up?

asked 20 Sep '15, 22:46

Aditya's gravatar image

Aditya
10017
accept rate: 6%


These are the changes to be made if the DB name has to be changed for a node in a clustered environment -

Step 1 - Kill Tomcat Process

On Master Node:

To kill tomcat, first find the process id with the ps command, and then kill it. $ ps -ef | grep tomcat
$ kill -9 <pid>

On Child Node:

To kill tomcat, first find the process id with the ps command, and then kill it. $ ps -ef | grep tomcat $ kill -9 <pid>

Step 2 –Perform a dump of the database

On Master Node: Due to MySQL restrictions, the schema cannot directly be renamed even if it were to be relocated on the same server. As a workaround, create a database dump for the database to be migrated. This will produce a SQL script with all the CREATE and INSERT statements required to replicate the table structure.

$ mysqldump -u username -p -v olddatabase_master> old_db_master_dump.sql

On Child Node: Due to MySQL restrictions, the schema cannot directly be renamed even if it were to be relocated on the same server. As a workaround, create a database dump for the database to be migrated. This will produce a SQL script with all the CREATE and INSERT statements required to replicate the table structure.

$ mysqldump -u username -p -v olddatabase_child> old_db_child_dump.sql

Step 3 –Restore the dump under the new database name

On Master Node:

Create the new database wherever it needs to be located, along with its new name. Execute the previously produced SQL script to populate the new database.

$ mysqladmin -u username -p create newdatabase_master $ mysql -u username -p newdatabase_master<old_db_master_dump.sql

On Child Node:

Create the new database wherever it needs to be located, along with its new name. Execute the previously produced SQL script to populate the new database.

$ mysqladmin -u username -p create newdatabase_child $ mysql -u username -p newdatabase_child< old_db_child_dump.sql

Step 4 – Provide User Permissions

On Master Node:

At the MySQL command line prompt, provide the necessary permissions for the database account used by Securonix to access the database.

$ mysql –h newmysqlserver_master –u username -p mysql> grant create,insert,update,delete on newdatabase_master.* to username@’hostname’ identified by 'password'; mysql> flush privileges;

On Child Node:

At the MySQL command line prompt, provide the necessary permissions to the database account used by Securonix to access the database.

$ mysql –h newmysqlserver_child –u username -p mysql> grant create,insert,update,delete on newdatabase_child.* to username@’hostname’ identified by 'password'; mysql> flush privileges;

Step 5–Modify Datasource properties

On Master Node:

The database includes records with references to its old location – these must be changed for the migration to be complete.Run the following query on the master’s database server:

mysql> update datasourceproperties dsp, datasources ds set dsp.propertyvalue = replace(dsp.propertyvalue, ‘olddatabase_master’, ‘newdatabase_master’) where ds.name = ‘SecuronixDB’ and dsp.datasourceid = ds.id;

If the child nodes’ databases are also being relocated, run this query on the master database as well (for each child node being relocated):

mysql> update datasourceproperties dsp, datasources ds set dsp.propertyvalue = replace(dsp.propertyvalue, ‘olddatabase_child’, ‘newdatabase_child’) where ds.name = ‘SecuronixDB_<node name="">’ and dsp.datasourceid = ds.id;

On Child Node:

No changes necessary.

Step 6–Modify Profiler.xml

On Master Node:

Change Profiler.xmlat <main securonix="" directory="">/tomcat-7.0.securonix/conf/Catalina/localhostto connect to the new database.

The URL attribute in the XML should be changed from - url="jdbc:mysql://oldmysqlserver_master:3306/olddatabase_master?autoReconnect=true&rewriteBatchedStatements=true" To -url="jdbc:mysql://newmysqlserver_master:3306/newdatabase_master?autoReconnect=true&rewriteBatchedStatements=true"

On Child Node:

Change Profiler.xmlat <main securonix="" directory="">/tomcat-7.0.securonix/conf/Catalina/localhost to connect to the new database.

The URL attribute in the XML should be changed from - url="jdbc:mysql://oldmysqlserver_child:3306/olddatabase_child?autoReconnect=true&rewriteBatchedStatements=true" To -url="jdbc:mysql://newmysqlserver_child:3306/newdatabase_child?autoReconnect=true&rewriteBatchedStatements=true"

Step 7–Modify hibernate Settings

On Master Node:

If the child database is being relocated or renamed, edit the hibernate.cfg.<child node="" name="">.xml located at securonix_home/conf/hibernate. Find the property named hibernate.connection.url and change it to point to the new database. Perform this action on all master nodes, for all children being relocated.

Before: <property name="hibernate.connection.url">jdbc:mysql://oldmysqlserver_child:3306/olddatabase_child</property>

After: <property name="hibernate.connection.url">jdbc:mysql://newmysqlserver_child:3306/newdatabase_child</property>

On Child Node:

If the master database is being relocated or renamed, edit the hibernate.cfg.master.xml located at securonix_home/conf/hibernate. Find the property named hibernate.connection.url and change it to point to the new database. Perform this action on all child nodes.

Before: <property name="hibernate.connection.url">jdbc:mysql://oldmysqlserver_master:3306/olddatabase_master</property>

After: <property name="hibernate.connection.url">jdbc:mysql://newmysqlserver_master:3306/newdatabase_master</property>

Step 8 – Start Tomcat and Verify Migration

On Master Node: Now that all changes have been made, we can start tomcat and verify the upgrade. Tomcat can be brought up again by running the startup.sh script.

$ cd $TOMCAT_HOME/bin $ ./startup.sh

Once the application is up, login and check the status of the cluster. Perform an activity import and run policy violations to ensure proper operation.

On Child Node: Now that all changes have been made, we can start tomcat and verify the upgrade. Tomcat can be brought up again by running the startup.sh script.

$ cd $TOMCAT_HOME/bin $ ./startup.sh

Once the application is up, login on the master and check the status of the cluster. Perform manual sync from the clustering console to ensure proper operation -

Step 9 – Drop Older Database

On Master Node:

Once we’ve confirmed the migration to be successful, the older database can be removed, if desired. $ mysql –h oldmysqlserver_master –u username -p
mysql> drop database olddatabase_master;

On Child Node:

Once we’ve confirmed the migration to be successful, the older database can be removed, f desired. $ mysql –h oldmysqlserver_child –u username -p
mysql> drop database olddatabase_child;

link

answered 20 Sep '15, 22:50

Aditya's gravatar image

Aditya
10017
accept rate: 6%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×4
×1

Asked: 20 Sep '15, 22:46

Seen: 1,487 times

Last updated: 20 Sep '15, 22:50