Thursday, January 31, 2013

Moving Confluence between 2 Linux Servers (Confluence 4.0.3 and MySQL 5.x)

Today we will upgrade a large confluence installation (over 2Gb files and a 3Gb Database)

Since this is a production confluence, we will first want to create a QA environment, where we will recreate
the running installation and make sure nothing goes wrong!

Step 1:  We first create 2 VM's, 4Gb memory each, running CentOS:

As you can see, one VM is going to be the Confluence host, and the other will host MySQL.

Step 2:  We copy the confluence configuration and data directories to the QA VM:

cat <confluence installation directory>/WEB-INF/classes/

this file will show you where the confluence home is, look for something that looks like this:

confluence.home = /opt/confluence/confluence-data

You can run this little script to find out various information about the confluence installation:

[root@01 bin]# /<confluence dir>/bin/ 
If you encounter issues starting up Confluence Standalone, please see the Installation guide at
Using CATALINA_BASE:   /opt/confluence
Using CATALINA_HOME:   /opt/confluence
Using CATALINA_TMPDIR: /opt/confluence/temp
Using JRE_HOME:        /opt/confluence/jre/
Using CLASSPATH:       /opt/confluence/bin/bootstrap.jar
Using CATALINA_PID:    /opt/confluence/work/
Server version: Apache Tomcat/6.0.32
Server built:   February 2 2011 2003
Server number:
OS Name:        Linux
OS Version:     2.6.18-308.16.1.el5
Architecture:   amd64
JVM Version:    1.6.0_26-b03
JVM Vendor:     Sun Microsystems Inc.
[root@01 bin]# 

So this was easy, we just tar up the directories and move them over to our VM:

tar cvf confluence.tar .
tar cvf confluence-data.tar .

Step 3:  getting a MySQL database dump (for a Postgres version, you can see my post here )

Since this is a production database, the default mysqldump setting will lock the tables during the dump and this might cause problems with the confluence instance using the database. There are different things that can be done to avoid this (like backup from a slave mysql, using mysqlhotcopy, etc.) but if the tables are InnoDB then we can add the parameter –single-transaction to mysqldump and avoid this issue.

You can check cat /etc/my.cnf and see if you are running InnoDB tables or not.  In our case we are, so we issue this command:

# mysqldump --single-transaction -u root -p  c4_1 > Confluence_backupfile-11-26-2012.v2.sql

However, before this step, let's make sure we are dumping the right database, so we have to log into the MySQL console, and check the databases to make sure.  (In the case above, I already did, so I know it's the c4_1 database!)

[root@02 ~]#  mysql -h localhost -u root -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13470
Server version: 5.0.95-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


I run this command: 

SELECT table_schema "database_name",     sum( data_length + index_length ) / 1024 /1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;

So I can get the sizes in human form... :)

So as you can see, c4_1 is 23.9Gb.... so this will take a little time.

After we have this file, and it looks like this(!):

[root@02 ]# ls -lah
total 42G
drwxrwxrwx  8 root            root         4.0K Nov 26 19:21 .
drwxr-xr-x 31 root            root         4.0K Oct  9 22:09 ..
-rw-r--r--  1 root            root          24G Nov 26 21:03 Confluence_backupfile-11-26-2012.v2.sql
[root@02 ]# 

We will now need to import it into the QA MySQL server.  

So since this is a new CentOS 6.2:

[root@MySQL-QA ~]# cat /etc/redhat-release 
CentOS release 6.2 (Final)
[root@MySQL-QA ~]# 

I already installed MySQL during the setup wizard in the beginning, so we should have a working
installation, I will start it up, and it will bring up its script:

[root@MySQL-QA ~]# /etc/init.d/mysqld start
Initializing MySQL database:  WARNING: The host 'MySQL-QA' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
Filling help tables...

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h MySQL-QA password 'new-password'

Alternatively you can run:

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with
cd /usr/mysql-test ; perl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

So as you can see, it's asking to run /usr/bin/mysql_secure_installation which we will do now:

[root@MySQL-QA ~]# /usr/bin/mysql_secure_installation


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] Y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!
[root@MySQL-QA ~]# 

Now if we check our databases as we did earlier we'll see that we are brand new:

So we will need to create the database and then import the data to it:

mysql> create database c4_1;
mysql> GRANT ALL PRIVILEGES ON c4_1.* TO root@localhost IDENTIFIED BY 'password01';

let's see that it's there:

mysql> show databases ;
| Database           |
| information_schema |
| c4_1               |
| mysql              |
3 rows in set (0.00 sec)


Ok, let's see what privilidges our users have:

select * from mysql.user where User='root';

or for a more clear version :

SHOW GRANTS FOR 'root'@'localhost';

mysql> SHOW GRANTS FOR 'root'@'localhost';
| Grants for root@localhost                                                                                                              |
| GRANT ALL PRIVILEGES ON `c4_1`.* TO 'root'@'localhost'                                                                                 |
2 rows in set (0.00 sec)


Ok, now we can go on to importing the database, which we will do like this:

mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]


[root@MySQL-QA boaz]# mysql -u root -p c4_1 < Confluence_backupfile-11-26-2012.sql

I recommend you take the /etc/my.cnf settings from the original server, otherwise you may run into some trouble if it's not a typical installation.  In my case I got an error like this:

ERROR 1598 (HY000) at line 41: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

So this error is discussed over here but the jist of it, try to copy the settings in the production database.

You can check there is progress being made by logging into another tty and going into the SQL console:

As you can see above, we are now at 1800Mb, so another 22Gb or so to go...

Now we want to make sure we can connect to the MySQL DB remotely, so we need to add a binding in the /etc/my.cnf file, like this:

bind-address= (or whatever the server IP is)

and then restart the server:

/etc/init.d/mysqld restart

now most likely you have iptables running on it, so you want to stop or disable it, in my case I stop it then disable it:

then check if you can telnet to port 3306 from the Confluence server:
initially as you can see, it gave me a "No route to host"  but after disabling iptables, its working:

Next we will need to allow the other machine's IP/Hostname to connect to the MySQL DB, and we do this as follows:

Connect to the MySQL console:

 [root@MySQL-QA boaz]# mysql -h localhost -u root -p

Allow root or whichever user you're using to connect from whichever IP/Hostname:

mysql> GRANT ALL ON *.* to root@'' IDENTIFIED BY 'password01';
Query OK, 0 rows affected (0.05 sec)

Step 4: Configuring Confluence to connect to the DB and get it working.

First off, edit the server.xml file and change the connection to the database:

<Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"
         validationQuery="Select 1" />


the bolded/highlighted host name was the production database previously.  I added this hostname in /etc/hosts so it can resolve.

You can use the script below to put in /etc/init.d/  call it confluence or something:

# Startup script for jakarta tomcat
# chkconfig: - 85 20
# description: Confluence running
# processname: confluence
# pidfile: /opt/kb/confluence4/work/ # config:# Source function library.
. /etc/rc.d/init.d/functions
# Source networking configuration.
. /etc/sysconfig/network
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0

# Set Tomcat environment.
export JAVA_HOME=/opt/kb/confluence4/jre
export JRE_HOME=/opt/kb/confluence4/jre
export CATALINA_HOME=/opt/kb/c4
export CATALINA_TMPDIR=/opt/kb/confluence4/temp
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$CATALINA_HOME/lib/servlet-api.jar:$CATALINA_HOME/bin/bootstrap.jar
#export CATALINA_OPTS="-Dbuild.compiler.emacs=true -Xms2048m -Xmx4096m -XX:MaxPermSize=1024m"
export CATALINA_OPTS="-Xms3072m -Xmx3072m -XX:MaxPermSize=1024m"
export PATH=$JAVA_HOME/bin:$PATH:/usr/bin:/usr/lib/bin

case "$1" in
        # Start daemon.
        echo -n "Starting Confluence: "
        [ $RETVAL = 0 ] && touch /var/lock/subsys/confluence ;;
        # Stop daemons.
        echo -n "Shutting down Confluence: "
        [ $RETVAL = 0 ] && rm -f /var/lock/subsys/confluence ;;
        $0 stop
        $0 start
        [ -e /var/lock/subsys/confluence ] && $0 restart ;;
        status confluence
echo "Usage: $0 {start|stop|restart|status}"
exit 1
exit 0

You also need to add a user confluence, do so by doing the following:

[root@confluence-QA boaz]# adduser  -c "Confluence User" -p "password01" -d "/home/confluence" confluence

You can put whichever password you want of course, this is just for show.

One note, typically confluence will start on a port higher than 1024 because it's running Tomcat on the back-end.  so the port you will need to connect to can be found in server.xml, typically 8090.

So in order to do that and not have iptables block that port, disable iptables on the confluence box as well,
or put in a rule to allow TCP for 8090.

That's it, you should now have a working copy of confluence on the new server!

Start it up:  /etc/init.d/confluence start

and watch the logs at <directory>/logs/atlassian-confluence.log  and catalina.out

when you see in catalina.out for example:

INFO: Server startup in 220538 ms

then you can go to the browser and put in the URL for the server.

No comments:

Post a Comment