Tag: MySQL Database

How to configure multi master MySQL replication

How to configure multi master MySQL replication

By | | Comments 2 comments

Introduction ~

I was planning to write article on Multi Master MySQL replication since long time; Finally started now!. Please refer the the article on “How to configure MySQL replication with one Master” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Let me inform you all that Multi Master replication in MySQL is purely based on following two variables. It has nothing to do with replication technology used in MySQL replication….

mysql> show variables  like  ‘%increment_%’;

+—————————————+——-+
| Variable_name                                   | Value |
+—————————————+——-+
| auto_increment_increment    |  1    |
| auto_increment_offset               |  1    |
+—————————————+—–+
2 rows in set (0.00 sec)

mysql>

** Requirements ~

a) Master Hosts (2 master in my case) ~

master-1 => 10.66.66.194
master-2 => 10.66.90.135
b) Replication Slave (1 slave) ~
slave => 10.66.75.137

c) MySQL server (with replication support)

** Let us understand how it works ?

* Master-1 Server =>

Set following variables…

mysql> set auto_increment_increment=5;
mysql> set auto_increment_offset=1;

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+————————–+——-+
2 rows in set (0.00 sec)

mysql>

** Create Table ~

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

** Add Record now ~

mysql> INSERT INTO class VALUES (”,’Arun Bagul’);
mysql> INSERT INTO class VALUES (”,’Ravi Bhure’);
mysql> INSERT INTO class VALUES (”,’Karthik Appigita’);
mysql> INSERT INTO class VALUES (”,’Ameya Pandit’);

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      3 | Ravi Bhure       |
|      5 | Karthik Appigita |
|      7 | Ameya Pandit     |
+——–+——————+
4 rows in set (0.00 sec)

mysql>

* Master-2 Server =>

Set following variables…

mysql> set auto_increment_increment=2;
mysql> set auto_increment_offset=2;

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 2     |
| auto_increment_offset    | 2     |
+————————–+——-+
2 rows in set (0.00 sec)

mysql>

** Create Table ~

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

** Add Record now ~

mysql> INSERT INTO class VALUES (”,’Nilkanth Parab’);
mysql> INSERT INTO class VALUES (”,’Nishit Shah’);
mysql> INSERT INTO class VALUES (”,’Ram Krishna’);
mysql> INSERT INTO class VALUES (”,’Suhail Thakur’);

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      2 | Nilkanth Parab   |
|      4 | Nishit Shah      |
|      6 | Ram Krishna      |
|      8 | Suhail Thakur    |
+——–+——————+
4 rows in set (0.00 sec)

mysql>

** What is the importance of “auto_increment_increment” and “auto_increment_offset” ~

mysql> desc class;
+——–+————-+——+—–+———+—————-+
| Field  | Type        | Null | Key | Default | Extra          |
+——–+————-+——+—–+———+—————-+
| rollno | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
+——–+————-+——+—–+———+—————-+

auto_increment_offset => This is BASE value for column with “auto_increment” attribute (please refer the above example)
auto_increment_increment => This is the increment value for column with “auto_increment” attribute

** If you combine the both tables (master-1 and master-2) the final table will look like this ~

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      2 | Nilkanth Parab   |
|      3 | Ravi Bhure       |
|      4 | Nishit Shah      |
|      5 | Karthik Appigita |
|      6 | Ram Krishna      |
|      7 | Ameya Pandit     |
|      8 | Suhail Thakur    |
+——–+——————+
8 rows in set (0.00 sec)

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

Where –
N => nth number of master server (on master-1 keep it 1 and on master-2 keep it 2 and so on..)
M => Total number of Master Server (2 in our case but better to keep this value high so that we can add new master server easily)

log-slave-updates => Slave server does not log to its own binary log any updates that are received from a Master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log.

** Make sure that MySQL is running and up on all master servers and slave server-

How to setup Multi Master MySQL replication ? –

Step 1] Create Database/Tables on  Master  & Slave Servers –

You can create DB on all master & slave server or  create on one server and export that DB on  rest of all servers…

Master-1 => Create DB and Table

mysql> create database student;

mysql> use student;

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

mysql> show tables;
+——————-+
| Tables_in_student |
+——————-+
| class             |
+——————-+
1 row in set (0.00 sec)

mysql> desc class;
+——–+————-+——+—–+———+—————-+
| Field  | Type        | Null | Key | Default         |      Extra          |
+——–+————-+——+—–+———+—————-+
| rollno | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
+——–+————-+——+—–+———+—————-+
2 rows in set (0.00 sec)

mysql> SELECT * FROM class;
Empty set (0.00 sec)
mysql>

* Now take dump of “student” DB and export it on all master and Slave server…

[root@master-1~]# mysqldump -u root -p  -d student > /home/arunsb/student.sql

* SCP the dump file on master-2 and slave server ~
[root@master-1~]# scp /home/arunsb/student.sql arunsb@10.66.90.135:/tmp/student.sql
[root@master-1~]# scp /home/arunsb/student.sql arunsb@10.66.75.137:/tmp/student.sql

Login on master-2 and slave ~

mysql> create database student;

[root@master-2~]# mysql -u root -p student < /tmp/student.sql
Enter password:
[root@master-2~]#

[root@master-2~]# mysql -u root -p
Enter password:

mysql> use student

mysql> SELECT * FROM class;
Empty set (0.00 sec)
mysql>

** Please repeat the same steps on Slave server as well…

Step 2] Update “my.cnf” config file on master-1,master-2 and slave server –

[root@master-1~]# cat /etc/my.cnf

###########################
##MySQL replication setting

#Master setting(1)
server-id = 1
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db = student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log
auto_increment_offset=1
auto_increment_increment=5
log-slave-updates

##slave setting
master-port=3306
master-host=10.66.90.135
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
###########################

[root@master-1~]#

[root@master-2~]# cat /etc/mysql/my.cnf

###########################
##MySQL replication setting

#Master setting(2)
server-id = 2
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db=student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log
auto_increment_offset=2
auto_increment_increment=5
log-slave-updates

##slave setting
master-port=3306
master-host=10.66.66.194
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
###########################

[root@master-2~]#

* please create directory for binary log and set permission…

[root@master-1~]# mkdir -p /var/log/mysql/binary/
[root@master-1~]# chown mysql:adm  /var/log/mysql/ /var/log/mysql/binary/

[root@master-2~]# mkdir -p /var/log/mysql/binary/
[root@master-2~]# chown mysql:adm  /var/log/mysql/ /var/log/mysql/binary/

** MySQL Replication Slave ~

[root@slave~]# cat  /etc/my.cnf

[mysqld]

########################################
##slave setting
server-id=4
master-port=3306
master-host=10.66.90.135
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
########################################

[root@slave~]#


Step 3] Give Replication permission on both masters ~

** Master (1 & 2) ~

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’10.66.%.%’ IDENTIFIED BY ‘mypwd’;
Query OK, 0 rows affected (0.00 sec)

mysql>

Step 4] Restart MySQL on both master as well as replication slave server ~

** Please verify setting on master-1 and master-2 server…

* Master-1

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 5     |
| auto_increment_offset    | 1     |
+————————–+——-+
2 rows in set (0.00 sec)

* Master-2

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 5     |
| auto_increment_offset    | 2     |
+————————–+——-+
2 rows in set (0.00 sec)

** Please verify ‘master’ & ‘slave’ status on both masters(1 & 2) and slave –

mysql> show master status;
mysql> show slave status;

** Multi Master replication is started…

Step 5] Add few records on Master-1 & Master-2 server at same time ~

Add records on both master server at same time and check master and replication slave status as shown above….

** Add following on master-1

mysql> INSERT INTO class VALUES (”,’Arun Bagul’);
mysql> INSERT INTO class VALUES (”,’Ravi Bhure’);
mysql> INSERT INTO class VALUES (”,’Karthik Appigita’);
mysql> INSERT INTO class VALUES (”,’Ameya Pandit’);

** Add following on master-2

mysql> INSERT INTO class VALUES (”,’Nilkanth Parab’);
mysql> INSERT INTO class VALUES (”,’Nishit Shah’);
mysql> INSERT INTO class VALUES (”,’Ram Krishna’);
mysql> INSERT INTO class VALUES (”,’Suhail Thakur’);

** Please verify the numbers of records on both masters and slave….

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      2 | Nilkanth Parab   |
|      6 | Ravi Bhure       |
|     11 | Karthik Appigita |
|     16 | Ameya Pandit     |
|     17 | Nishit Shah      |
|     22 | Ram Krishna      |
|     27 | Suhail Thakur    |
+——–+——————+
8 rows in set (0.00 sec)

mysql>

* So we all learned to configure multi-master MySQL replication. Enjoy!!

Regards,
Arun Bagul

Open Source and Extract, Transform & Load (ETL)

Open Source and Extract, Transform & Load (ETL)

By | | Comments 0 Comment

What is ETL ?

Extract, Transform, and Load (ETL) is procedure to

a) Extracting data from outside sources
b) Transforming the data as ther operational requirements
c) Loading it into the end target (database or data warehouse)

1) Talend Open Studio ~ http://www.talend.com

Talend Open Studio operates as a code generator allowing data transformation scripts and underlying programs to be generated either in Perl or in Java.
Its GUI is made of a metadata repository and a graphical designer. The jobs are designed using graphical components, for transformation, connectivity or other operations. The threads created can be executed from within the studio or as standalone scripts.

2) CloverETL ~     http://www.cloveretl.com/

CloverETL is a Java-based data integration framework used to transform, cleanse, standardize and distribute data to applications, databases or warehouses. Its component-based structure allows easy customization and embeddability.

* CloverETL Designer – the graphical user interface to create and modify data transformations for CloverETL Server and Engine.
* CloverETL Engine – executes the transformations (run-time); can be embedded as a library. Available under LGPL.
* CloverETL Server – full-fledged server application with a rich WEB-based administrative interface, which leverages the existing CloverETL Engine.

3) Pentaho ~ http://www.pentaho.com/

The Pentaho BI Project is Open Source application software for enterprise reporting, analysis, dashboard, data mining, workflow and ETL capabilities for Business Intelligence (BI) needs.

4) Apatar ~ http://www.apatar.com/

Apatar is an open source ETL (Extract-Transform-Load) and mashup data integration software application. Other open source data integration projects are Clover.ETL, Pentaho Project, Talend Open Studio or Enhydra Octopus.

5) http://www.azinta.com/
Regards,

Arun