View previous topic :: View next topic |
Author |
Message |
hydrapolic Tux's lil' helper
Joined: 07 Feb 2008 Posts: 126
|
Posted: Thu Aug 02, 2012 3:02 pm Post subject: MySQL 5.5 replication with Tungsten Replicator |
|
|
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
|
Last edited by hydrapolic on Sun Aug 05, 2012 9:44 am; edited 1 time in total |
|
Back to top |
|
|
hydrapolic Tux's lil' helper
Joined: 07 Feb 2008 Posts: 126
|
Posted: Fri Aug 03, 2012 10:44 am Post subject: |
|
|
Added section 3 - Provisioning a new slave. |
|
Back to top |
|
|
|
|
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
|
|