Template:OXLoadBalancingClustering Database: Difference between revisions
No edit summary |
|||
Line 1: | Line 1: | ||
== Master/ | == 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 | |||
* ''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/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'; | ||
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 | ||
=== | 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 | 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 | 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| | | | |||
+------------------+----------+--------------+------------------+ | |||
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; | ||
On the slave (10.20.30.21) check the Slave status | |||
mysql> SHOW 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 | 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/ | === 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 | 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 | 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 | +--------------------+