Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
MySQL 5.5 replication with Tungsten Replicator
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
hydrapolic
Tux's lil' helper
Tux's lil' helper


Joined: 07 Feb 2008
Posts: 113

PostPosted: Thu Aug 02, 2012 3:02 pm    Post subject: MySQL 5.5 replication with Tungsten Replicator Reply with quote


    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 (http://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




Last edited by hydrapolic on Sun Aug 05, 2012 9:44 am; edited 1 time in total
Back to top
View user's profile Send private message
hydrapolic
Tux's lil' helper
Tux's lil' helper


Joined: 07 Feb 2008
Posts: 113

PostPosted: Fri Aug 03, 2012 10:44 am    Post subject: Reply with quote

Added section 3 - Provisioning a new slave.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum