Template:OXLoadBalancingClustering Database

Master/Slave database 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.

Master configuration

Open the MySQL configuration file with you favorite editor

$ vim /etc/mysql/my.cnf

Modify or enable the following configuration options

bindaddress             = 10.20.30.217
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/Slave 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';

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

Slave configuration

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

Open the MySQL configuration file with you favorite editor

$ vim /etc/mysql/my.cnf

Modify or enable the following configuration options. Just like the master, the slave 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';

Start the MySQL slave replication

mysql> START SLAVE;

Check the slave status, sometimes it can take a while until the replication starts. Slave_IO_Running shows that the MySQL slave is exchanging data with the MySQL master.

mysql> SHOW SLAVE STATUS \G;
Slave_IO_Running: Yes
Slave_SQL_Running: 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/Slave

On the master, create a new database in MySQL:

mysql> CREATE DATABASE foo;

Check if this database is available on the slave:

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

Delete the database on the master

mysql> DROP DATABASE foo;

Check if the database has been removed at the slave

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