Template:OXLoadBalancingClustering Database
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 | +--------------------+