MySQL NDB (building shared database storage) (WebMail Pro 6 PHP)

From AfterLogic Wiki

Contents 1 MySQL documentation reference 2 Configuration 3 Installation Guide 3.1 Preliminary installation steps 3.2 Configuring the servers 3.3 Copying database dump 3.4 Setting master on Server 2 3.5 Setting master on Server 1 4 High availability configuration

MySQL documentation reference

Installing MySQL from a Standard Source Distribution

MySQL Cluster Extract

Configuration

Each data node or SQL node requires my.cnf file that provides two pieces of information: a connectstring that tells the node where to find the management node, and a line telling the MySQL server on this host (the machine hosting the data node) to enable the NDBCLUSTER storage engine.

The management node needs a config.ini file telling it how many replicas to maintain, how much memory to allocate for data and indexes on each data node, where to find the data nodes, where to save data to disk on each data node, and where to find any SQL nodes.

• Start management node.
• Start ALL data nodes prior to starting SQL nodes.
• Start SQL nodes
• Stop SQL nodes
• Stop mgm-data nodes

Installation Guide

Master-Master Replication With MySQL 5.x.x (5.0.77 in our case)

S1 - host "number one", IP: 192.168.0.181
S2 - host "number two", IP: 192.168.0.180

Preliminary installation steps

Install and start MySQL server on BOTH hosts (S1 & S2):

yum -y install mysql mysql-server

/etc/init.d/mysqld start

Set administrator password for BOTH of systems (S1 & S2):

mysqladmin -uroot password MyPassWord1

Create Admin Users on both the servers:

CREATE USER 'root'@'%' IDENTIFIED BY 'MyPassWord1';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Create Slave Replication User:

S1:

GRANT REPLICATION SLAVE ON *.* TO 's2_replicator'@'%' IDENTIFIED BY 'ugachagaugauga';
FLUSH PRIVILEGES;

S2:

GRANT REPLICATION SLAVE ON *.* TO 's1_replicator'@'%' IDENTIFIED BY 'ugachagaugauga';
FLUSH PRIVILEGES;

Let assume that the database replica_db is already existing on S1 - containing tables with records. So we have to create an empty database with the same name as the existing database on S2.

# mysql -u root -h 192.168.0.180 -p

CREATE DATABASE replica_db;
quit;

Configuring the servers

S1:

# vi /etc/my.cnf

Add the following lines to [mysqld] section of the '/etc/my.conf' file:

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

master-host = 192.168.0.180
master-user = s1_replicator
master-password = ugachagaugauga
master-connect-retry = 60
replicate-do-db = replica_db

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = replica_db

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10
max_binlog_size = 500M

Afterwards restart the MySQL server.

/etc/init.d/mysqld restart

S2:

# vi /etc/my.cnf

Add the following lines to [mysqld] section of the '/etc/my.conf' file:

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = 192.168.0.181
master-user = s2_replicator
master-password = ugachagaugauga
master-connect-retry = 60
replicate-do-db = replica_db

log-bin= /var/log/mysql/mysql-bin.log
binlog-do-db = replica_db

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10
max_binlog_size = 500M

Afterwards restart the MySQL server.

# /etc/init.d/mysqld restart

Copying database dump

S1: Export MySQL Dump

Now we create a dump of the existing database and transfer it to S2.

# mysql -u root -p

USE replica_db;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output should look like this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    26918 | replica_db   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Open a second terminal for system 1, create the dump and transfer it to system 2. Don't leave the MySQL-shell at this point - otherwise you'll lose the read-lock.

# cd /tmp/
# mysqldump -u root -pMyPassWord1 --opt replica_db > replica_sqldump.sql
# scp replica_sqldump.sql root@192.168.0.180:/tmp/

Afterwards close the second terminal and switch back to the first. Remove the read-lock and leave the MySQL-shell.

UNLOCK TABLES;
quit;

S2: Import MySQL Dump

Time to import the database dump on system 2.

mysqladmin --user=root --password=MyPassWord1 stop-slave
cd /tmp/
mysql -u root -pMyPassWord1 replica_db < replica_sqldump.sql

Setting master on Server 2

Now we need information about the master status on S2.

# mysql -u root -p
USE replica_db;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output should look like this. Note down the file and the position - you'll need both later.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |    34017 | replica_db   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Afterwards remove the read-lock.

UNLOCK TABLES;

At this point we're ready to become the master for S1. Replace 'MyPassWord1' with the password you choose and be sure that you replace the values for MASTER_LOG_FILE and MASTER_LOG_POS with the values that you noted down previously.

CHANGE MASTER TO MASTER_HOST='192.168.0.181', MASTER_USER='s2_replicator', MASTER_PASSWORD='MyPassWord1', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=98;

Now start the slave ...

START SLAVE;

... and take a look at the slave status. It's very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they're not, something went wrong and you should take a look at the logs.

SHOW SLAVE STATUS;

mysql> SHOW SLAVE STATUS;
+----------------------+---------------+---------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State       | Master_Host   | Master_User   | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------+---------------+---------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Connecting to master | 192.168.0.181 | s2_replicator |        3306 |            60 | mysql-bin.000002 |               34017 | slave-relay.000001 |            98 | mysql-bin.000002      | No               | Yes               | replica_db      |                     |                    |                        |                         |                             |          0 |            |            0 |               34017 |              98 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL |
+----------------------+---------------+---------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

Setting master on Server 1

Open a MySQL-shell on S1 ...

mysql -u root -p

... and stop the slave.

STOP SLAVE;

At this point we're ready to become the master for S2. Replace 'MyPassWord1' with the password you choose and be sure that you replace the values for MASTER_LOG_FILE and MASTER_LOG_POS with the values that you noted down at step above!

CHANGE MASTER TO MASTER_HOST='192.168.0.180', MASTER_USER='s1_replicator', MASTER_PASSWORD='MyPassWord1', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=34017;

Now start the slave ...

START SLAVE;

... and take a look at the slave status. It's very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they're not, something went wrong and you should take a look at the logs.

SHOW SLAVE STATUS;

mysql> SHOW SLAVE STATUS;
+----------------------+---------------+---------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State       | Master_Host   | Master_User   | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------+---------------+---------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Connecting to master | 192.168.0.180 | s1_replicator |        3306 |            60 | mysql-bin.000001 |               34017 | slave-relay.000001 |            98 | mysql-bin.000001      | No               | Yes               | replica_db      |                     |                    |                        |                         |                             |          0 |            |            0 |               34017 |              98 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL |
+----------------------+---------------+---------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.01 sec)

If all went ok, the master-master replication is working now. Check your logs on both systems if you encounter problems.

High availability configuration

As far as we can tell based on our own experience, the above configuration is not suitable for high-availability systems. If that's the kind of system you have, additional configuration is required.

Last edit: 2011/10/11

WebMail Pro PHP documentation
Installation System Requirements Installation Instructions for Windows Installation Instructions for Linux Upgrading instructions Configuring calendar support Install on cPanel Install on Plesk Panel Install on DirectAdmin	Configuring WebMail Getting started Configuring default domain settings Managing domains and users Managing subadmins Configuring WebMail via settings.xml Advanced configuration via config.php file Configuring web domain names Use POPPASSD to let users change their passwords Configuring voice messages support Configuring reminders Use Webmail without a database AdminPanel Guide	Customization Translating to another language How to place banner ads Adding global signature for outgoing mails Modifying or removing copyright info Creating your own skin Adding toolbar buttons Adding menu item Adding language constant Modifying columns in message list How to add new tab in account settings area	Integration Creating domains, logging accounts in, deleting domains and accounts via API Embedding WebMail into your web application via IFRAME Enable LDAP authentication Use address book stored in LDAP server like Active Directory or OpenLDAP Use Sieve for filtering API reference Logging in programmatically Synchronization Outlook Sync Add-in SyncML support	Clustering Overview Multiserver NFS configuration (building shared file storage) MySQL NDB (building shared database storage) Setting MySQL for high availability Clustering PHP Using Round robin DNS (building load balancer)	See Also Changelog Importing contacts Keyboard Shortcuts License Agreement Support Credits