Template:OXLoadBalancingClustering Database: Difference between revisions

From Open-Xchange
Line 1: Line 1:
== Master/Master database setup ==
== 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.
This article describes the setup process "Master/Master replication" for new database nodes. During configuration and initialization, other database operations must be prohibited.
 
The Master/Master replication is a vice versa setup of Master/Slave configurations. This Means each server is afterwards the slave of the other.
 
Server IPs in the example are 1.1.1.1 and 9.9.9.9


Startup both database machines and install the mysql server packages
Startup both database machines and install the mysql server packages
  $ apt-get install mysql-server
  $ 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.
During the installation, a dialog will show up to set a password for the MySQL 'root' user.
 
=== 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
Open the MySQL configuration file on both servers with you favorite editor
  $ vim /etc/mysql/my.cnf
  $ vim /etc/mysql/my.cnf


Modify or enable the following configuration options in the mysqld-section
Modify or enable the following configuration options in the mysqld-section. use 1 for ${unique Number} on the server 1.1.1.1 and 2 for 2.2.2.2:
  bind-address            = 0.0.0.0
  bind-address            = 0.0.0.0
  server-id               = 1
  server-id             = ${unique Number}
  log-bin                = /var/log/mysql/mysql-bin.log
  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.
* ''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 in a replication cluster.
* ''server-id'' is just a unique number within a environment with multiple MySQL servers. It needs to be unique for each server in a replication cluster.
* ''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.
* ''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.


Line 28: Line 29:
  $ mysql -u root -p
  $ mysql -u root -p
  Enter password:
  Enter password:
=== Master configuration ===
Choose one server to start with as the first Master (here we use 1.1.1.1).


Create a MySQL user with rights "REPLICATION". This account is used by the MySQL slave to fetch database updates. In this example, the username is "replication":
Create a MySQL user with rights "REPLICATION". This account is used by the MySQL slave to fetch database updates. In this example, the username is "replication":


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


Verify that the MySQL daemon writes a binary log and remember the log Position:
Verify that the MySQL daemon writes a binary log and remember the log Position:
Line 42: Line 46:


Copy the MySQL binary logs and the index file to the slave. This is required for the initial synchronization.
Copy the MySQL binary logs and the index file to the slave. This is required for the initial synchronization.
  $ scp /var/log/mysql/mysql-bin.* root@10.20.30.219:/var/log/mysql
  $ scp /var/log/mysql/mysql-bin.* root@9.9.9.9:/var/log/mysql


=== Slave configuration ===
=== Slave configuration ===


On the slave (10.20.30.219) set the MySQL system user as owner of the binary log that has just been copied to the slave.
On the 9.9.9.9, set the MySQL system user as owner of the binary log that has just been copied to the slave.
  $ chown mysql:adm /var/log/mysql/*
  $ chown mysql:adm /var/log/mysql/*


Configure 10.20.30.217 as Master Server. Use the remembered log file position from the master.
Configure 1.1.1.1 as Master Server. Use the remembered log file position 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=xxx;
  mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='replication', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=xxx;


Start the MySQL slave replication
Start the MySQL slave replication
Line 60: Line 64:
"Slave_IO_Running" and "Slave_SQL_Running" should be set to "yes".
"Slave_IO_Running" and "Slave_SQL_Running" should be set to "yes".


=== Second Master configuration ===
This means, the first Master/Slave Replication is working and the "reverse" replication needs to be prepared. Please now create the replication user on 9.9.9.9:
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
  mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'1.1.1.1' IDENTIFIED BY 'secret';
$ 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.
Verify that the MySQL daemon writes a binary log and remember the log Position:
bind-address            = 0.0.0.0
server-id              = 2
log-bin                = /var/log/mysql/mysql-bin.log
 
 
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;
 
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;
  mysql> SHOW MASTER STATUS;
  +------------------+----------+--------------+------------------+
  +------------------+----------+--------------+------------------+
  | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +------------------+----------+--------------+------------------+
  +------------------+----------+--------------+------------------+
  | mysql-bin.000001 |      1082|              |                  |
  | mysql-bin.000001 |      1111|              |                  |
  +------------------+----------+--------------+------------------+
  +------------------+----------+--------------+------------------+
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.
=== Reverse Master/Slave configuration ===
  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;
1.1.1.1 is now the slave in this context and 9.9.9.9 one is the master. Log in to 1.1.1.1
 
Configure 9.9.9.9 as Master Server. Use the remembered log file position from the master (1.1.1.1).
  mysql> CHANGE MASTER TO MASTER_HOST='9.9.9.9', MASTER_USER='replication', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=xxx;


On the slave (10.20.30.217) start the MySQL slave replication
start the MySQL slave replication
  mysql> START SLAVE;
  mysql> START SLAVE;


On the slave (10.20.30.21) check the Slave status
and check the status
  mysql> SHOW SLAVE STATUS;
  mysql> SHOW SLAVE STATUS;



Revision as of 06:42, 3 March 2011

Master/Master database setup

This article describes the setup process "Master/Master replication" for new database nodes. During configuration and initialization, other database operations must be prohibited.

The Master/Master replication is a vice versa setup of Master/Slave configurations. This Means each server is afterwards the slave of the other.

Server IPs in the example are 1.1.1.1 and 9.9.9.9

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.

Open the MySQL configuration file on both servers with you favorite editor

$ vim /etc/mysql/my.cnf

Modify or enable the following configuration options in the mysqld-section. use 1 for ${unique Number} on the server 1.1.1.1 and 2 for 2.2.2.2:

bind-address            = 0.0.0.0
server-id              = ${unique Number}
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 unique number within a environment with multiple MySQL servers. It needs to be unique for each server in a replication cluster.
  • 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:

Master configuration

Choose one server to start with as the first Master (here we use 1.1.1.1).

Create a MySQL user with rights "REPLICATION". This account is used by the MySQL slave to fetch database updates. In this example, the username is "replication":

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

Verify that the MySQL daemon writes a binary log and remember the log Position:

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

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

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

Slave configuration

On the 9.9.9.9, set the MySQL system user as owner of the binary log that has just been copied to the slave.

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

Configure 1.1.1.1 as Master Server. Use the remembered log file position from the master.

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

Start the MySQL slave replication

mysql> START SLAVE;

An check the status

mysql> SHOW SLAVE STATUS;

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

This means, the first Master/Slave Replication is working and the "reverse" replication needs to be prepared. Please now create the replication user on 9.9.9.9:

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

Verify that the MySQL daemon writes a binary log and remember the log Position:

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

Reverse Master/Slave configuration

1.1.1.1 is now the slave in this context and 9.9.9.9 one is the master. Log in to 1.1.1.1

Configure 9.9.9.9 as Master Server. Use the remembered log file position from the master (1.1.1.1).

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

start the MySQL slave replication

mysql> START SLAVE;

and check the 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              |
+--------------------+

Creating Open-Xchange user

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';