Template:OXLoadBalancingClustering Database: Difference between revisions

From Open-Xchange
No edit summary
Line 1: Line 1:
== Master/Slave 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.
 
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
Line 5: Line 7:
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. Please set a strong password here.


=== Master configuration ===
=== 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
Open the MySQL configuration file with you favorite editor
  $ vim /etc/mysql/my.cnf
  $ vim /etc/mysql/my.cnf


Modify or enable the following configuration options
Modify or enable the following configuration options in the mysqld-section
  bindaddress            = 10.20.30.217
  bindaddress            = 10.20.30.217
  server-id              = 1
  server-id              = 1
  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.
* ''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.
* ''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.
To apply the configuration changes, restart the MySQL server.
Line 32: Line 36:
  mysql> GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'10.20.30.215' 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
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;
  mysql> SHOW MASTER STATUS;
  +------------------+----------+--------------+------------------+
  +------------------+----------+--------------+------------------+
Line 43: Line 47:
  $ scp /var/log/mysql/mysql-bin.* root@10.20.30.219:/var/log/mysql
  $ scp /var/log/mysql/mysql-bin.* root@10.20.30.219:/var/log/mysql


=== Slave configuration ===
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;


Set the MySQL system user as owner to the binary log that has just been copied to the slave.
On the slave (10.20.30.219) start the MySQL slave replication
  $ chown mysql:adm /var/log/mysql/*
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
Open the MySQL configuration file with you favorite editor
  $ vim /etc/mysql/my.cnf
  $ 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.
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
  bindaddress            = 10.20.30.219
  server-id              = 2
  server-id              = 2
Line 68: Line 84:
  mysql> GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'10.20.30.213' IDENTIFIED BY 'secret';
  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';
  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|              |                  |
+------------------+----------+--------------+------------------+
   
   
Start the MySQL slave replication
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;
  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.
On the slave (10.20.30.21) check the Slave status
  mysql> SHOW SLAVE STATUS \G;
  mysql> SHOW SLAVE STATUS;
Slave_IO_Running: Yes
 
Slave_SQL_Running: Yes
"Slave_IO_Running" and "Slave_SQL_Running" should be set to "yes".


Also check the syslog if the replication has been sucessfully started
Also check the syslog if the replication has been sucessfully started
Line 81: Line 111:
  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
  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 ===
=== Testing Master/Master ===


On the master, create a new database in MySQL:
On the first master, create a new database in MySQL:
  mysql> CREATE DATABASE foo;
  mysql> CREATE DATABASE foo;


Check if this database is available on the slave:
Check if this database is available on the second master:
  mysql> SHOW DATABASES;
  mysql> SHOW DATABASES;
  +--------------------+
  +--------------------+
Line 96: Line 126:
  +--------------------+
  +--------------------+


Delete the database on the master
Delete the database on the second master:
  mysql> DROP DATABASE foo;
  mysql> DROP DATABASE foo;


Check if the database has been removed at the slave
Check if the database has been removed at first master
  mysql> SHOW DATABASES;
  mysql> SHOW DATABASES;
  +--------------------+
  +--------------------+

Revision as of 08:11, 23 February 2011

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 

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/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=7266"