Wednesday, February 19, 2014

Steps to install Mysql Master-Slave Replication using Tungsten

NOTE: Master note on Tungsten installation and setup: http://datacharmer.blogspot.com/2013/04/installing-and-administering-tungsten.html

Below are the steps I have used to setup Mysql replication using Tungsten

Please review the prerequisites for this installtion from https://code.google.com/p/tungsten-replicator/wiki/InstallationPreRequisites

1.) Install mysql on the master and slave hosts.
You can download mysql community edition from http://dev.mysql.com/downloads/mysql/

2.) Download tungsten from https://code.google.com/p/tungsten-replicator/downloads/list
I am using Tungsten Replicator 2.1.0 Binary Build.

3.) Login to mysql on master and slave as root user and create tungsten user and database:
grant all on *.* to 'tungsten'@'%' identified by 'secret' with grant option;
create database tungsten;

4.) Disable binlog_checksum
set global binlog_checksum=NONE;
This is required else you would receive following error while setting up tungsten:

ERROR >> host1 >> This instance is running with BinLog checksum enabled which is not yet supported (MySQLCheckSumCheck)

5.) Unzip and untar the Tungsten Replicator 2.1.0 downloaded in step 2. I use mysql OS user for all the setup.
tar -xzf tungsten-replicator-2.1.0-343.tar.gz
cd tungsten-replicator-2.1.0-343

6.) Tungsten configurations:
Setup environment:
export PATH=$PATH:$PWD/cookbook (eg. /home/mysql/tungsten-replicator-2.1.0-343/cookbook)
Update config files:
vi cookbook/COMMON_NODES.sh and update the hostnames

#!/bin/bash
# (C) Copyright 2012,2013 Continuent, Inc - Released under the New BSD License
# Version 1.0.5 - 2013-04-03

export NODE1=master01
export NODE2=slave01

Note: Incase you want to have multiple slaves add NODE3, NODE4....

vi cookbook/USER_VALUES.sh and update the TUNGSTEN_HOME locations, etc. This file will be used for installing tungsten components.

#!/bin/bash

# (C) Copyright 2012,2013 Continuent, Inc - Released under the New BSD License

# Version 1.0.5 - 2013-04-03



# User defined values for the cluster to be installed.



# Where to install Tungsten Replicator

export TUNGSTEN_BASE=/home/mysql_data/installs/cookbook



# Directory containing the database binary logs

export BINLOG_DIRECTORY=/home/mysql_data



# Path to the script that can start, stop, and restart a MySQL server

export MYSQL_BOOT_SCRIPT=/etc/init.d/mysql



# Path to the options file

export MY_CNF=/usr/my.cnf



# Database credentials

export DATABASE_USER=tungsten

export DATABASE_PASSWORD=secret

export DATABASE_PORT=3306



# Name of the service to install

export TUNGSTEN_SERVICE=cookbook



# Replicator ports

export RMI_PORT=10000

export THL_PORT=2112



# If set, replicator starts after installation

[ -z "$START_OPTION" ] && export START_OPTION=start



##############################################################################

# Options used by the "direct slave " installer only

# Modify only if you are using 'install_master_slave_direct.sh'

##############################################################################

export DIRECT_MASTER_BINLOG_DIRECTORY=$BINLOG_DIRECTORY

export DIRECT_SLAVE_BINLOG_DIRECTORY=$BINLOG_DIRECTORY

export DIRECT_MASTER_MY_CNF=$MY_CNF

export DIRECT_SLAVE_MY_CNF=$MY_CNF

##############################################################################



##############################################################################

# Variables used when removing the cluster

# Each variable defines an action during the cleanup

##############################################################################

[ -z "$STOP_REPLICATORS" ]            && export STOP_REPLICATORS=1

[ -z "$REMOVE_TUNGSTEN_BASE" ]        && export REMOVE_TUNGSTEN_BASE=1

[ -z "$REMOVE_SERVICE_SCHEMA" ]       && export REMOVE_SERVICE_SCHEMA=1

[ -z "$REMOVE_TEST_SCHEMAS" ]         && export REMOVE_TEST_SCHEMAS=1

[ -z "$REMOVE_DATABASE_CONTENTS" ]    && export REMOVE_DATABASE_CONTENTS=0

[ -z "$CLEAN_NODE_DATABASE_SERVER" ]  && export CLEAN_NODE_DATABASE_SERVER=1

##############################################################################



Validation before actual installation:
export VERBOSE=1
cd /home/mysql/tungsten-replicator-2.1.0-343 and the run the following command.

[mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/validate_cluster

I had strange errors when I tried to run this command after going to /home/mysql/tungsten-replicator-2.1.0-343/cookbook. So make sure you are in tungsten-replicator-2.1.0-343 directory before trying the below command.
This command with read the values you updated in the files above and make sure there are no error. Incase of any errors it will notify you. I received the following errors:
#####################################################################

# Errors for master01

#####################################################################

ERROR >> master01 >> This instance is running with BinLog checksum enabled which is not yet supported (MySQLCheckSumCheck)

#####################################################################

# Errors for slave01

#####################################################################

ERROR >> slave01 >> The slave datasource tungsten@slave01.corp.yahoo.com:3306 (WITH PASSWORD) has a running slave SQL thread (MySQLNoMySQLReplicationCheck)

ERROR >> slave01 >> This instance is running with BinLog checksum enabled which is not yet supported (MySQLCheckSumCheck)


After fixing these error the command was successful:

cookbook/validate_cluster
[mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/validate_cluster

# Performing validation check ...

## 1 (host: master01)

./tools/tungsten-installer \

        --master-slave \

        --master-host=master01 \

        --datasource-user=tungsten \

        --datasource-password=secret \

        --datasource-port=3306 \

        --service-name=cookbook \

        --home-directory=/home/mysql_data/installs/cookbook \

        --cluster-hosts=master01,slave01 \

        --datasource-mysql-conf=/usr/my.cnf \

        --datasource-log-directory=/home/mysql_data -a \

        --datasource-boot-script=/etc/init.d/mysql \

        --rmi-port=10000 \

        --thl-port=2112 \

        --validate-only -a \

        --info \

        --start

INFO  >> Start: Check that the master-host is part of the config

INFO  >> Finish: Check that the master-host is part of the config

#####################################################################

# Tungsten Community Configuration Procedure

#####################################################################

NOTE:  To terminate configuration press ^C followed by ENTER



#####################################################################

# Preliminary checks for master01:/home/mysql_data/installs/cookbook

#####################################################################



#####################################################################

# Preliminary checks for slave01:/home/mysql_data/installs/cookbook

#####################################################################

INFO  >> slave01 >> Start: OpenSSL Library Check

INFO  >> slave01 >> Finish: OpenSSL Library Check

INFO  >> slave01 >> Start: SSH login

INFO  >> slave01 >> Finish: SSH login

INFO  >> slave01 >> Start: Writeable temp directory

INFO  >> slave01 >> Finish: Writeable temp directory

INFO  >> slave01 >> Start: Ruby version

INFO  >> slave01 >> Finish: Ruby version



#####################################################################

# Local checks for /home/mysql_data/installs/cookbook

#####################################################################

INFO  >> master01 >> Start: Writeable home directory

INFO  >> master01 >> Finish: Writeable home directory

INFO  >> master01 >> Start: Operating system

INFO  >> master01 >> Finish: Operating system

INFO  >> master01 >> Start: Java version

INFO  >> master01 >> Finish: Java version

INFO  >> master01 >> Start: Hostname

INFO  >> master01 >> Finish: Hostname

INFO  >> master01 >> Start: Package download check

INFO  >> master01 >> Finish: Package download check

INFO  >> master01 >> Start: Replicator is running check

INFO  >> master01 >> The replicator in /home/mysql_data/installs/cookbook is stopped.

INFO  >> master01 >> Finish: Replicator is running check

INFO  >> master01 >> Start: Replicator RMI port is available check

INFO  >> master01 >> The replicator RMI port is available

INFO  >> master01 >> Finish: Replicator RMI port is available check

INFO  >> master01 >> Start: Transferred log storage check

INFO  >> master01 >> Finish: Transferred log storage check

INFO  >> master01 >> Start: No hidden services check

INFO  >> master01 >> Finish: No hidden services check

INFO  >> master01 >> Start: Replication service checks

INFO  >> master01 >> Start: THL storage check

INFO  >> master01 >> Finish: THL storage check

INFO  >> master01 >> Start: Service transferred log storage check

INFO  >> master01 >> Finish: Service transferred log storage check

INFO  >> master01 >> Start: Service name check

INFO  >> master01 >> Finish: Service name check

INFO  >> master01 >> Start: Different master/slave datasource check

INFO  >> master01 >> Finish: Different master/slave datasource check

INFO  >> master01 >> Start: Parallel replication consistency check

INFO  >> master01 >> Finish: Parallel replication consistency check

INFO  >> master01 >> Start: MySQL client check

INFO  >> master01 >> MySQL client version: /usr/bin/mysql  Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using  EditLine wrapper

INFO  >> master01 >> Finish: MySQL client check

INFO  >> master01 >> Start: Replication credentials login check

INFO  >> master01 >> MySQL server and login is OK for tungsten@master01:3306 (WITH PASSWORD)

INFO  >> master01 >> Finish: Replication credentials login check

INFO  >> master01 >> Start: Replication user permissions check

INFO  >> master01 >> Checking user permissions: GRANT ALL PRIVILEGES ON *.* TO 'tungsten'@'%' IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' WITH GRANT OPTION

INFO  >> master01 >> All privileges configured correctly

INFO  >> master01 >> Finish: Replication user permissions check

INFO  >> master01 >> Start: Binary logs enabled check

INFO  >> master01 >> Finish: Binary logs enabled check

INFO  >> master01 >> Start: MySQL config file is available

INFO  >> master01 >> Finish: MySQL config file is available

INFO  >> master01 >> Start: MySQL Server ID

INFO  >> master01 >> Finish: MySQL Server ID

INFO  >> master01 >> Start: MySQL Server Port

INFO  >> master01 >> Finish: MySQL Server Port

INFO  >> master01 >> Start: MySQL settings check

INFO  >> master01 >> Checking innodb_flush_log_at_trx_commit

INFO  >> master01 >> Checking max_allowed_packet

WARN  >> master01 >> The value of max_allowed_packet is too small for tungsten@master01:3306 (WITH PASSWORD)

INFO  >> master01 >> Finish: MySQL settings check

INFO  >> master01 >> Start: No MySQL replication check

INFO  >> master01 >> Checking that MySQL replication is not running on the slave datasource

INFO  >> master01 >> Finish: No MySQL replication check

INFO  >> master01 >> Start: Mysqldump method availability check

INFO  >> master01 >> mysqldump found at /usr/bin/mysqldump

INFO  >> master01 >> Finish: Mysqldump method availability check

INFO  >> master01 >> Start: MySQL 5.6 binlog Checksum Check

INFO  >> master01 >> Checking that MySQL Binlog Checksum is not enabled

INFO  >> master01 >> Finish: MySQL 5.6 binlog Checksum Check

INFO  >> master01 >> Finish: Replication service checks



#####################################################################

# Remote checks for slave01:/home/mysql_data/installs/cookbook

#####################################################################

INFO  >> slave01 >> Start: Writeable home directory

INFO  >> slave01 >> Finish: Writeable home directory

INFO  >> slave01 >> Start: Operating system

INFO  >> slave01 >> Finish: Operating system

INFO  >> slave01 >> Start: Java version

INFO  >> slave01 >> Finish: Java version

INFO  >> slave01 >> Start: Hostname

INFO  >> slave01 >> Finish: Hostname

INFO  >> slave01 >> Start: Package download check

INFO  >> slave01 >> Finish: Package download check

INFO  >> slave01 >> Start: Replicator is running check

INFO  >> slave01 >> The replicator in /home/mysql_data/installs/cookbook is stopped.

INFO  >> slave01 >> Finish: Replicator is running check

INFO  >> slave01 >> Start: Replicator RMI port is available check

INFO  >> slave01 >> The replicator RMI port is available

INFO  >> slave01 >> Finish: Replicator RMI port is available check

INFO  >> slave01 >> Start: Transferred log storage check

INFO  >> slave01 >> Finish: Transferred log storage check

INFO  >> slave01 >> Start: No hidden services check

INFO  >> slave01 >> Finish: No hidden services check

INFO  >> slave01 >> Start: Replication service checks

INFO  >> slave01 >> Start: THL storage check

INFO  >> slave01 >> Finish: THL storage check

INFO  >> slave01 >> Start: Service transferred log storage check

INFO  >> slave01 >> Finish: Service transferred log storage check

INFO  >> slave01 >> Start: Service name check

INFO  >> slave01 >> Finish: Service name check

INFO  >> slave01 >> Start: Different master/slave datasource check

INFO  >> slave01 >> Finish: Different master/slave datasource check

INFO  >> slave01 >> Start: Parallel replication consistency check

INFO  >> slave01 >> Finish: Parallel replication consistency check

INFO  >> slave01 >> Start: MySQL client check

INFO  >> slave01 >> MySQL client version: /usr/bin/mysql  Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using  EditLine wrapper

INFO  >> slave01 >> Finish: MySQL client check

INFO  >> slave01 >> Start: Replication credentials login check

INFO  >> slave01 >> MySQL server and login is OK for tungsten@slave01:3306 (WITH PASSWORD)

INFO  >> slave01 >> Finish: Replication credentials login check

INFO  >> slave01 >> Start: Replication user permissions check

INFO  >> slave01 >> Checking user permissions: GRANT ALL PRIVILEGES ON *.* TO 'tungsten'@'%' IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' WITH GRANT OPTION

INFO  >> slave01 >> All privileges configured correctly

INFO  >> slave01 >> Finish: Replication user permissions check

INFO  >> slave01 >> Start: Binary logs enabled check

INFO  >> slave01 >> Finish: Binary logs enabled check

INFO  >> slave01 >> Start: MySQL config file is available

INFO  >> slave01 >> Finish: MySQL config file is available

INFO  >> slave01 >> Start: MySQL Server ID

INFO  >> slave01 >> Finish: MySQL Server ID

INFO  >> slave01 >> Start: MySQL Server Port

INFO  >> slave01 >> Finish: MySQL Server Port

INFO  >> slave01 >> Start: MySQL settings check

INFO  >> slave01 >> Checking innodb_flush_log_at_trx_commit

INFO  >> slave01 >> Checking max_allowed_packet

WARN  >> slave01 >> The value of max_allowed_packet is too small for tungsten@slave01:3306 (WITH PASSWORD)

INFO  >> slave01 >> Finish: MySQL settings check

INFO  >> slave01 >> Start: No MySQL replication check

INFO  >> slave01 >> Checking that MySQL replication is not running on the slave datasource

INFO  >> slave01 >> Finish: No MySQL replication check

INFO  >> slave01 >> Start: Mysqldump method availability check

INFO  >> slave01 >> mysqldump found at /usr/bin/mysqldump

INFO  >> slave01 >> Finish: Mysqldump method availability check

INFO  >> slave01 >> Start: MySQL 5.6 binlog Checksum Check

INFO  >> slave01 >> Checking that MySQL Binlog Checksum is not enabled

INFO  >> slave01 >> Finish: MySQL 5.6 binlog Checksum Check

INFO  >> slave01 >> Finish: Replication service checks



INFO  >> Validation finished



NOTE >> Deployment finished



Installation of Master Slave topology:
Execute the below command. (Execute it from tungsten-replicator-2.1.0-343 directory)
cookbook/install_master_slave
[mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/install_master_slave

## 1 (host: master01)

./tools/tungsten-installer \

        --master-slave \

        --master-host=master01 \

        --datasource-user=tungsten \

        --datasource-password=secret \

        --datasource-port=3306 \

        --service-name=cookbook \

        --home-directory=/home/mysql_data/installs/cookbook \

        --cluster-hosts=master01,slave01 \

        --datasource-mysql-conf=/usr/my.cnf \

        --datasource-log-directory=/home/mysql_data -a \

        --datasource-boot-script=/etc/init.d/mysql \

        --rmi-port=10000 \

        --thl-port=2112 \

        --start

WARN  >> master01 >> The value of max_allowed_packet is too small for tungsten@master01:3306 (WITH PASSWORD)

WARN  >> slave01 >> The value of max_allowed_packet is too small for tungsten@slave01:3306 (WITH PASSWORD)

--------------------------------------------------------------------------------------

Topology: 'MASTER_SLAVE'

--------------------------------------------------------------------------------------

# node master01

cookbook  [master]      seqno:         -1  - latency:  -1.000 - ONLINE



# node slave01

cookbook  [slave]       seqno:         -1  - latency:  -1.000 - ONLINE



Deployment completed

Topology         :'master_slave'

Tungsten path    : /home/mysql_data/installs/cookbook

Nodes            : (master01 slave01)

Master services  : (master01)

Slave services   : (slave01)

MySQL version    : 5.6.12-enterprise-commercial-advanced-log

MySQL port       : 3306

MySQL shortcut   : mysql --defaults-file=cookbook/my.cookbook.cnf

Tungsten release : tungsten-replicator-2.1.0-343

Installation log : cookbook/current_install.log



Testing replication:
Tungsten provides built in scripts to test replication:
cookbook/test_cluster
prove cookbook/test_cluster
[mysql@master01 tungsten-replicator-2.1.0-343]$ prove cookbook/test_cluster

cookbook/test_cluster .. ok

All tests successful.

Files=1, Tests=72, 19 wallclock secs ( 0.04 usr  0.01 sys +  7.40 cusr  0.80 csys =  8.25 CPU)

Result: PASS



Other tests:
1.) Get information about replication:
[mysql@master01 tungsten-replicator-2.1.0-343]$ prove cookbook/test_cluster

cookbook/test_cluster .. ok

All tests successful.

Files=1, Tests=72, 19 wallclock secs ( 0.04 usr  0.01 sys +  7.40 cusr  0.80 csys =  8.25 CPU)

Result: PASS

[mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/show_cluster

--------------------------------------------------------------------------------------

Topology: 'MASTER_SLAVE'

--------------------------------------------------------------------------------------

# node master01.corp.yahoo.com

cookbook  [master]      seqno:         59  - latency:   0.465 - ONLINE



# node slave01.corp.yahoo.com

cookbook  [slave]       seqno:         59  - latency:   0.968 - ONLINE



2.) Detailed replication information using cookbook/trepctl command:
[mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/trepctl services

Processing services command...

NAME              VALUE

----              -----

appliedLastSeqno: 59

appliedLatency  : 0.465

role            : master

serviceName     : cookbook

serviceType     : local

started         : true

state           : ONLINE

Finished services command...


Replication Status:
[mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/trepctl status

Processing status command...

NAME                     VALUE

----                     -----

appliedLastEventId     : mysql-bin.000007:0000000000012894;0

appliedLastSeqno       : 59

appliedLatency         : 0.465

channels               : 1

clusterName            : default

currentEventId         : mysql-bin.000007:0000000000012894

currentTimeMillis      : 1374172674662

dataServerHost         : master01.corp.yahoo.com

extensions             :

latestEpochNumber      : 0

masterConnectUri       :

masterListenUri        : thl://master01.corp.yahoo.com:2112/

maximumStoredSeqNo     : 59

minimumStoredSeqNo     : 0

offlineRequests        : NONE

pendingError           : NONE

pendingErrorCode       : NONE

pendingErrorEventId    : NONE

pendingErrorSeqno      : -1

pendingExceptionMessage: NONE

pipelineSource         : jdbc:mysql:thin://master01.corp.yahoo.com:3306/

relativeLatency        : 213.662

resourcePrecedence     : 99

rmiPort                : 10000

role                   : master

seqnoType              : java.lang.Long

serviceName            : cookbook

serviceType            : local

simpleServiceName      : cookbook

siteName               : default

sourceId               : master01.corp.yahoo.com

state                  : ONLINE

timeInStateSeconds     : 427.683

transitioningTo        :

uptimeSeconds          : 432.196

version                : Tungsten Replicator 2.1.0 build 343

Finished status command...


Stop Replication:
[mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/trepctl stop

Do you really want to stop replication service cookbook? [yes/NO] yes

Service stopped successfully: name=cookbook


Start Replication:
[mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/trepctl -service cookbook start

Service started successfully: name=cookbook


Try [mysql@master01 tungsten-replicator-2.1.0-343]$ cookbook/trepctl help for a lot more options.