Template:OXLoadBalancingClustering Database

From Open-Xchange

Master/Master database setup

Even if the OX handles the database servers as master and slave, you should configure them as a master/master setup.

Startup both database machines and install the mysql server packages 

$ apt-get install mysql-server

During the installation, a dialog will show up to set a password for the MySQL 'root' user. Please set a strong password here.

First Master configuration

The first server is a master in this context and the second one is the slave.

Open the MySQL configuration file with you favorite editor 

$ vim /etc/mysql/my.cnf

Modify or enable the following configuration options in the mysqld-section 

bind-address             = 0.0.0.0
server-id               = 1
log-bin                 = /var/log/mysql/mysql-bin.log
  • bindaddress specifies the network address where MySQL is listening for network connections. Since the MySQL slave and both Open-Xchange Servers are dedicated machines it is required to have the master accessible through the network.
  • server-id is just a number within a environment with multiple MySQL servers. It needs to be unique for each server.
  • log-bin enables the MySQL binary log which is required for Master/Master replication. In general every statement triggered at the database is stored there to get distributed through the database cluster.

To apply the configuration changes, restart the MySQL server. 

$ /etc/init.d/mysql restart

Then login to MySQL with the credentials given at the MySQL installation process 

$ mysql -u root -p
Enter password:

Configure replication permissions for the MySQL slave server and the MySQL user "replication". This account is used by the MySQL slave to get database updates from the master. Please choose a strong password here. 

 mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.20.30.219' IDENTIFIED BY 'secret';

Now setup access for the Open-Xchange Server database user openexchange to configdb and the groupware database for both groupware server addresses. These databases do not exist yet, but will be created during the Open-Xchange Server installation. 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'10.20.30.213' IDENTIFIED BY 'secret';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'10.20.30.215' IDENTIFIED BY 'secret';

On the master (10.20.30.217) verify that the MySQL master is writing a binary log and remember the values 

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      1082|              |                  |
+------------------+----------+--------------+------------------+

Copy the MySQL master binary log and the index file to the slave. This is required for initial synchronization. 

$ scp /var/log/mysql/mysql-bin.* root@10.20.30.219:/var/log/mysql

On the slave (10.20.30.219) set the MySQL system user as owner to the binary log that has just been copied to the slave. 

$ chown mysql:adm /var/log/mysql/*

On the slave (10.20.30.219) set the server as a slave of 10.20.30.217. Replace the log file information by the values you retrieved from the master. 

mysql> CHANGE MASTER TO MASTER_HOST='10.20.30.217', MASTER_USER='replication', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1082;

On the slave (10.20.30.219) start the MySQL slave replication 

mysql> START SLAVE;

On the slave (10.20.30.219) check the Slave status 

mysql> SHOW SLAVE STATUS;

"Slave_IO_Running" and "Slave_SQL_Running" should be set to "yes".

Second Master configuration

The first server is a slave in this context and the second one is the master.

Open the MySQL configuration file with you favorite editor 

$ vim /etc/mysql/my.cnf

Modify or enable the following configuration options in the mysqld-ection. Just like the other server, this one requires a unique server-id and needs to listen to an external network address. Activating the binary log is not required at the slave. 

bindaddress             = 10.20.30.219
server-id               = 2

To apply the configuration changes, restart the MySQL server. 

$ /etc/init.d/mysql restart

Then login to MySQL with the credentials given at the MySQL installation process 

$ mysql -u root -p
Enter password:

Configure the replication from the master based on the 'replication' user and the masters binary log status. The values for MASTER_LOG_FILE and MASTER_LOG_POS must equal the output of the SHOW MASTER STATUS command at the MySQL master. 

mysql> CHANGE MASTER TO MASTER_HOST='10.20.30.217', MASTER_USER='replication', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1082;

Now setup access for the Open-Xchange Server database user 'openexchange' to configdb and the oxdb for both groupware server addresses. These databases do not exist yet, but will be created during the Open-Xchange Server installation. 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'10.20.30.213' IDENTIFIED BY 'secret';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'10.20.30.215' IDENTIFIED BY 'secret';

On the master (10.20.30.219) verify that the MySQL master is writing a binary log and remember the values 

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      1082|              |                  |
+------------------+----------+--------------+------------------+

On the slave (10.20.30.217) set the MySQL system user as owner to the binary log that has just been copied to the slave. 

$ chown mysql:adm /var/log/mysql/*

On the slave (10.20.30.217) set the server as a slave of 10.20.30.219. Replace the log file information by the values you retrieved from the master. 

mysql> CHANGE MASTER TO MASTER_HOST='10.20.30.219', MASTER_USER='replication', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1082;

On the slave (10.20.30.217) start the MySQL slave replication 

mysql> START SLAVE;

On the slave (10.20.30.21) check the Slave status 

mysql> SHOW SLAVE STATUS;

"Slave_IO_Running" and "Slave_SQL_Running" should be set to "yes".

Also check the syslog if the replication has been sucessfully started 

$ tail -fn20 /var/log/syslog
Jul 26 19:03:45 dbslave mysqld[4718]: 090726 19:03:45 [Note] Slave I/O thread: connected to master 'replication@10.20.30.217:3306',  replication started in log 'mysql-bin.000001' at position 1082

Testing Master/Master

On the first master, create a new database in MySQL: 

mysql> CREATE DATABASE foo;

Check if this database is available on the second master: 

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| foo                |
| mysql              |
+--------------------+

Delete the database on the second master: 

mysql> DROP DATABASE foo;

Check if the database has been removed at first master 

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
Retrieved from "https://wiki.open-xchange.com/wiki/index.php?title=Template:OXLoadBalancingClustering_Database&oldid=7273"