MySQL 5.5 replication with Tungsten Replicator

[hydrapolic]

Tungsten Replicator

Info from the website:



Code:

Tungsten Replicator is a high performance, open source, data replication engine for MySQL. It offers a set of features that surpass any open source replicator available today: global transaction IDs to support failover, flexible transaction filtering, extensible transaction metadata, sharding, multiple replication services per process, high performance, and simple, well-documented operation. Tungsten Replicator helps technically focused users solve problems like promoting masters easily from pools of slaves, replicating data between different database versions, replicating efficiently across sites, building complex topologies, and parallelizing data flow between servers. Tungsten Replicator runs equally well in cloud as well as locally hosted environments. Tungsten Replicator users range from tiny start-ups to the largest web properties on the planet. Tungsten Replicator is sponsored by Continuent, Inc., which offers support as well as sponsored development of new replication features. Tungsten Replicator is also the base for Tungsten Enterprise, a commercial database clustering product that improves transaction throughput and keeps data highly available to applications. Please visit the Continuent website for more information about services as well as commercial offerings.



The goal of this howto is to set up a MySQL 5.5 master-slave replication using Tungsten Replicator. The master server will be set up before the slave - this is a typical case when you have a single MySQL instance and you wish to replicate it to some other host without downtime.

The master is mysql55-01 and the slave is mysql55-02. The IPs are saved in /etc/hosts.

1. Setting up MySQL

master:

• Install MySQL 5.5.25a from the mysql overlay
  
  

  Code:

  emerge layman layman -L -a mysql echo "source /var/lib/layman/make.conf" >> /etc/make.conf echo "=virtual/mysql-5.5" >> /etc/portage/package.keywords echo "=dev-db/mysql-5.5.25a" >> /etc/portage/package.keywords emerge mysql /usr/share/mysql/scripts/mysql_install_db --basedir=/usr --user=mysql

  
  
  
• Configure MySQL
  

  Code:

  vim /etc/mysql/my.cnf  max_allowed_packet = 52M  bind-address = 0.0.0.0 /etc/init.d/mysql start

  
  
  
• Fetch employees-db from https://launchpad.net/test-db/ and load it to MySQL
  

  Code:

  mysql < employees.sql

  
  
  
• Install the prerequisites
  

  Code:

  emerge ruby:1.8 sun-jdk

  
  
  
• Install Percona XtraBackup (https://forums.gentoo.org/viewtopic-t-931984.html)
  
  
• Create a snapshot of the master to /backup
  

  Code:

  mkdir /backup innobackupex --ibbackup=/usr/bin/xtrabackup_innodb55 /backup/ innobackupex --apply-log --ibbackup=/usr/bin/xtrabackup_innodb55 /backup/2012-08-02_15-53-12/

  
  
  
• Set up key-based authentication for ssh
  

  Code:

  ssh-keygen add /root/.ssh/id_rsa.pub to slave's /root/.ssh/authorized_keys

  
  
  
• Copy the backup to the slave. If you cannot do it without a password, return to the previous step.
  

  Code:

  scp -r /backup/2012-08-02_15-53-12/ slave:~/

  
  
  slave:
  
• Install MySQL 5.5.25a, install ruby and jdk, set up key-based authentication for ssh.
  
  
• Restore from the backup
  

  Code:

  /etc/init.d/mysql stop rm -rf /var/lib/mysql/* cp -a /root/2012-08-02_15-53-12/* /var/lib/mysql/ chown -R mysql:mysql /var/lib/mysql

  
  
  
• Configure MySQL and start MySQL
  

  Code:

  vim /etc/mysql/my.cnf  max_allowed_packet = 52M  bind-address = 0.0.0.0  server-id = 2 /etc/init.d/mysql start

  
  
  
• Set up access for root
  

  Code:

  mysql grant all on *.* to root@'mysql55-02' with grant option;

  
  
  
• Both, the master and slave have MySQL installed, the master is executing the SQL statements, saving the changes to the binlog. The slave only has a consistent snapshot of the master without any replication ongoing.
  
  
• Be sure to meet the requirements: http://code.google.com/p/tungsten-replicator/wiki/InstallationPreRequisites before install Tungsten Replicator.
  
  2. Installing Tungsten Replicator
  
  master:
  
• Fetch a recent build from: http://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.0-snapshots/index.html (tungsten-replicator-2.0.6-738.tar.gz in my case). Tungsten fails on other systems then Debian and Redhat, so either comment out the corresponding section in tools/ruby/configure/modules/validation_deployment.rb or simply touch /etc/redhat-release. Also, we need to copy wrapper-linux-x86-64 (or wrapper-linux-x86-32) to wrapper (or symlink on both hosts after deploying).
  
  

  Code:

  mkdir /root/tungsten cd /root/tungsten wget 'https://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.0-snapshots/tungsten-replicator-2.0.6-738.tar.gz' tar xvf tungsten-replicator-2.0.6-738.tar.gz cd /root/tungsten/tungsten-replicator-2.0.6-738 touch /etc/redhat-release cp /root/tungsten/tungsten-replicator-2.0.6-738/cluster-home/bin/wrapper-linux-x86-64 /root/tungsten/tungsten-replicator-2.0.6-738/cluster-home/bin/wrapper ./tools/tungsten-installer \ --master-slave \ --master-host=mysql55-01 \ --datasource-user=root \ --datasource-mysql-conf=/etc/mysql/my.cnf \ --service-name=fish \ --home-directory=/opt/tungsten \ --cluster-hosts=mysql55-01,mysql55-02

  
  
  
• Now tungsten is installed on both hosts under /opt/tungsten. We need to start the replication on the master from the specific location when the backup was taken. Examine the position from/backup/2012-08-02_15-53-12/xtrabackup_binlog_pos_innodb
  
  

  Code:

  cat /backup/2012-08-02_15-53-12/xtrabackup_binlog_pos_innodb ./mysqld-bin.000003     168400415

  
  
  
• First, we need to start the master. However, we cannot start the replicator because it would read the binary logs from the beginning and the slave would apply the insert statements that are already present on it. For the first time, we switch off automatic startup on the master
  
  

  Code:

  vim /opt/tungsten/releases/tungsten-replicator-2.0.6-738/tungsten-replicator/conf/static-fish.properties   replicator.auto_enable=false cd /opt/tungsten/tungsten/tungsten-replicator/bin ./trepstart

  
  
  

  Code:

  ./trepctl -service fish status Processing status command... NAME                     VALUE ----                     ----- appliedLastEventId     : NONE appliedLastSeqno       : -1 appliedLatency         : -1.0 clusterName            : default currentEventId         : mysqld-bin.000005:00000000107 currentTimeMillis      : 1343918109404 dataServerHost         : mysql55-01 extensions             : latestEpochNumber      : -1 masterConnectUri       : thl://:/ masterListenUri        : thl://mysql55-01:2112/ maximumStoredSeqNo     : -1 minimumStoredSeqNo     : -1 offlineRequests        : NONE pendingError           : NONE pendingErrorCode       : NONE pendingErrorEventId    : NONE pendingErrorSeqno      : -1 pendingExceptionMessage: NONE resourcePrecedence     : 99 rmiPort                : 10000 role                   : master seqnoType              : java.lang.Long serviceName            : fish serviceType            : local simpleServiceName      : fish siteName               : default sourceId               : mysql55-01 state                  : OFFLINE:NORMAL timeInStateSeconds     : 3.263 uptimeSeconds          : 3.419 Finished status command...

  
  
  
• We are in OFFLINE state. We bring up the master and check the status.
  
  

  Code:

  ./trepctl -service fish online -from-event mysqld-bin.000003:168400415

  
  
  

  Code:

  ./trepctl -service fish status Processing status command... NAME                     VALUE ----                     ----- appliedLastEventId     : mysqld-bin.000006:0000000000000415;0 appliedLastSeqno       : 2 appliedLatency         : 8.138 clusterName            : default currentEventId         : mysqld-bin.000006:0000000000000415 currentTimeMillis      : 1343918198580 dataServerHost         : mysql55-01 extensions             : latestEpochNumber      : 0 masterConnectUri       : thl://:/ masterListenUri        : thl://mysql55-01:2112/ maximumStoredSeqNo     : 2 minimumStoredSeqNo     : 0 offlineRequests        : NONE pendingError           : NONE pendingErrorCode       : NONE pendingErrorEventId    : NONE pendingErrorSeqno      : -1 pendingExceptionMessage: NONE resourcePrecedence     : 99 rmiPort                : 10000 role                   : master seqnoType              : java.lang.Long serviceName            : fish serviceType            : local simpleServiceName      : fish siteName               : default sourceId               : mysql55-01 state                  : ONLINE timeInStateSeconds     : 5.535 uptimeSeconds          : 92.595 Finished status command...

  
  
  
• Now we start the slave. We don't set any coordinates and we don't need to disable auto-start.
  
  slave:
  

  Code:

  cd /opt/tungsten/tungsten/tungsten-replicator/bin ./trepstart

  
  
  

  Code:

  ./trepctl -service fish status Processing status command... NAME                     VALUE ----                     ----- appliedLastEventId     : mysqld-bin.000006:0000000000000415;0 appliedLastSeqno       : 2 appliedLatency         : 58.258 clusterName            : default currentEventId         : NONE currentTimeMillis      : 1343918249534 dataServerHost         : mysql55-02 extensions             : latestEpochNumber      : 0 masterConnectUri       : thl://mysql55-01:2112/ masterListenUri        : thl://mysql55-02:2112/ maximumStoredSeqNo     : 2 minimumStoredSeqNo     : 0 offlineRequests        : NONE pendingError           : NONE pendingErrorCode       : NONE pendingErrorEventId    : NONE pendingErrorSeqno      : -1 pendingExceptionMessage: NONE resourcePrecedence     : 99 rmiPort                : 10000 role                   : slave seqnoType              : java.lang.Long serviceName            : fish serviceType            : local simpleServiceName      : fish siteName               : default sourceId               : mysql55-02 state                  : ONLINE timeInStateSeconds     : 6.74 uptimeSeconds          : 7.325 Finished status command...

  
  
  
• The cookbook is your friend (https://code.google.com/p/tungsten-replicator/wiki/TungstenReplicatorCookbook) and be sure to watch out for triggers. If you have any, read http://code.google.com/p/tungsten-replicator/wiki/TRCAdministration#Tungsten_Limitations
  
  3. Provisioning a new slave
  
  
• Now we have a master-slave replication ongoing and we wish to extend it with an extra slave. In the end we'll have a single master and two slaves, all nodes having the same data. Let's call the old slave the donor and the new slave the youngster. A new slave is promoted from an existing slave. A slave stores the current position to be processed from the master in the database under the tungsten_fish database (fish is the service name specified during the installating).
  
  

  Code:

  mysql> select * from tungsten_fish.heartbeat; select * from tungsten_fish.trep_commit_seqno; +----+-------+--------------------------------------+---------------------+---------------------+------------+------+---------------+ | id | seqno | eventid                              | source_tstamp       | target_tstamp       | lag_millis | salt | name          | +----+-------+--------------------------------------+---------------------+---------------------+------------+------+---------------+ |  1 |     2 | mysqld-bin.000006:0000000000000415;0 | 2012-08-02 16:36:33 | 2012-08-02 16:37:23 |      50252 |    1 | MASTER_ONLINE | +----+-------+--------------------------------------+---------------------+---------------------+------------+------+---------------+ 1 row in set (0.01 sec) +---------+--------+--------+-----------+------------+--------------+--------------------------------------+-----------------+---------------------+----------+---------------------+ | task_id | seqno  | fragno | last_frag | source_id  | epoch_number | eventid                              | applied_latency | update_timestamp    | shard_id | extract_timestamp   | +---------+--------+--------+-----------+------------+--------------+--------------------------------------+-----------------+---------------------+----------+---------------------+ |       0 | 900948 |      0 | 1         | mysql55-01 |            0 | mysqld-bin.000006:0000001003680217;0 |             138 | 2012-08-03 09:26:33 | sbtest   | 2012-08-03 09:24:15 | +---------+--------+--------+-----------+------------+--------------+--------------------------------------+-----------------+---------------------+----------+---------------------+ 1 row in set (0.01 sec)

  
  
  
• The slave stores the seqno and the source_id so it knows from where to fetch the updates and it also knows the exact sequence number of an event to request from the master and to process. When setting up the master-slave replication earlier, we ordered the master to start processing events from a certain point and that data were sent to the slave. However, we cannot do that when promoting a new slave because the old slave would lost the link. We create a consistent backup with XtraBackup from the donor, copy it to the youngster and install Tungsten Replicator on it. After the youngster comes up, it reads the position along with the master and starts requesting the events from the master. The donor will be a bit ahead, but it doesn't matter, both slaves will synchronize with the master. Even though we mirror the slave and create a new one, the donor will NOT be the master for the youngster.
  
  donor:
  
• Create a backup from the donor
  
  

  Code:

  mkdir /backup innobackupex --ibbackup=/usr/bin/xtrabackup_innodb55 /backup/ innobackupex --apply-log --ibbackup=/usr/bin/xtrabackup_innodb55 /backup/2012-08-03_10-13-23/

  
  
  
• Set up key based authentication for ssh in a way that all nodes will be able to connect to all nodes without a password. Copy the backup from the donor to the youngster. Restore the backup on the youngster
  

  Code:

  scp -r /backup/2012-08-03_10-13-23 youngster:~/

  
  
  youngster:
  

  Code:

  cp -av /root/2012-08-03_10-13-23/* /var/lib/mysql/ chown -R mysql:myqsl /var/lib/mysql

  
  
  
• Configure and start MySQL on youngster
  

  Code:

  vim /etc/mysql/my.cnf  max_allowed_packet = 52M  bind-address = 0.0.0.0  server-id = 3 /etc/init.d/mysql start

  
  
  
• Set up permissions in MySQL
  

  Code:

  mysql grant all on *.* to root@'mysql55-03' with grant option;

  
  
  
• Install Tungsten Replicator on youngster. This is done from the master (answer Y)
  
  master:
  

  Code:

  cd /root/tungsten/tungsten-replicator-2.0.6-738 ./tools/tungsten-installer \ --master-slave \ --master-host=mysql55-01 \ --datasource-user=root \ --datasource-mysql-conf=/etc/mysql/my.cnf \ --service-name=fish \ --home-directory=/opt/tungsten \ --cluster-hosts=mysql55-03

  
  
  
• Start the replication on youngster
  
  youngster:
  

  Code:

  cd /opt/tungsten/tungsten/tungsten-replicator/bin ./trepstart

  
  
  
• All nodes should be in ONLINE state. You can verify from the master
  

  Code:

  cd /opt/tungsten/tungsten/tungsten-replicator/bin for i in 1 2 3; do ./trepctl -host mysql55-0${i} -service fish status; done

  
  
  

[hydrapolic]

Added section 3 - Provisioning a new slave.