Tungsten是一款开源数据同步软件,可以实现跨平台、跨版本、异种数据库之间的复制.听起来是不是很像某款软件呢?嘿嘿.没错.那就是Oracle公司的GoldenGate数据同步工具,当然还有SharePlex等等.Tungsten官方宣传的功能很强大,号称实时同步、支持故障转移的全局事务 IDs，灵活的事务过滤，可扩展的事务元数据，分区，每个进程的多复制服务.
下面简单搭建环境测试一下
Master:192.168.23.108 MySQL5.5.37
Slave:192.168.23.109 MySQL5.6.15
Tungsten Replicator:tungsten-replicator-2.2.0-292.tar.gz
Master:
my.cnf参数:
[client]
socket=/tmp/mysql.sock
[mysqld]
server-id=2
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
innodb_buffer_pool_size=50M
innodb_flush_log_at_trx_commit=1
max_allowed_packet=100M
log-bin=/mysql/log/mysql-bin
log_bin_trust_function_creators = 1
innodb_fast_shutdown = 0
binlog-do-db=percona
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid
Slave:
my.cnf参数
[client]
socket=/tmp/mysql.sock
port=3306
[mysqld]
server-id=3
port=3306
basedir=/usr/local/mysql
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
log-bin=/mysql/log/mysql-bin
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
relay-log=relay-bin
relay-log-index=relay-bin.index
replicate-do-db=percona
slave-skip-errors=1146
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid
Master、Slave添加OS的tungsten用户,mysql组
useradd -g mysql tungsten
Master、SlaveMySQL添加tungsten账户
create user tungsten@'%' identified by 'XXXXX';grant all on *.* to tungsten@'%' with grant option;flush privileges;
配置主从SSH信任通信
Master:
ssh-keygen -t rsa -f ~/.ssh/id_rsa
scp id_rsa.pub tungsten@192.168.23.109:/home/tungsten/.ssh/
Slave:
cat id_rsa.pub>>authorized_keys
Master:测试SSH登录不需要密码
ssh tungsten@192.168.23.108
Master和Slave分别安装Ruby和OPENJDK
yum install -y ruby java-1.7.0-openjdk
Master、Slave建立Tungsten安装目录
mkdir -p /Tungsten
chown tungsten:mysql -R /Tungsten
Master:
安装Tungsten---注意:安装之前先导出主库数据在导入从库
./tungsten-installer --master-slave --master-host=192.168.23.108 --datasource-user=tungsten --datasource-password=XXXXX --service-name=log --home-directory=/Tungsten --cluster-hosts=192.168.23.108,192.168.23.109 --start-and-report
安装完成会显示如下信息
WARN >> 192.168.23.109 >> The value of max_allowed_packet is too small for tungsten@192.168.23.109:3306 (WITH PASSWORD)
INFO >> 192_168_23_109 >> Getting services list
INFO >> 192_168_23_109 >> Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: -1
appliedLatency : -1.0
role : slave
serviceName : log
serviceType : local
started : true
state : GOING-ONLINE:SYNCHRONIZING
Finished services command...
INFO >> 192_168_23_108 >> Getting services list
INFO >> 192_168_23_108 >> ..
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: -1
appliedLatency : -1.0
role : master
serviceName : log
serviceType : local
started : true
state : ONLINE
Finished services command...
主从服务器都会在/Tungsten目录生产Tungsten文件
查看Tungsten状态
[tungsten@localhost ]$ replicator status
Tungsten Replicator Service is running: PID:9063, Wrapper:STARTED, Java:STARTED
起停Tungsten
./replicator stop|start
查看TungSten同步状态
[tungsten@localhost ]$ trepctl -host 192.168.23.108 services
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: -1
appliedLatency : -1.0
role : master
serviceName : log
serviceType : local
started : true
state : ONLINE
Finished services command...
[tungsten@localhost ]$ trepctl -host 192.168.23.109 services
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: -1
appliedLatency : -1.0
role : slave
serviceName : log
serviceType : local
started : true
state : ONLINE
Finished services command...
现在用表test测试一下快快插入100W的数据同步性能如何
Master:
mysql> show tables;
+-------------------+
| Tables_in_percona |
+-------------------+|
| t |
+-------------------+
2 rows in set (0.00 sec)
mysql> create table test (id int);
Query OK, 0 rows affected (0.17 sec)
Slave:
mysql> show tables;
+-------------------+
| Tables_in_percona |
+-------------------+
| t |
| test |
+-------------------+
3 rows in set (0.00 sec)
Master:
mysql> insert into test values (1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into test values (2);
Query OK, 1 row affected (0.02 sec)
mysql> insert into test values (3);
Query OK, 1 row affected (0.07 sec)
mysql> insert into test values (4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test select * from test;
Query OK, 5 rows affected (0.07 sec)
Records: 5 Duplicates: 0 Warnings: 0
....
....
....
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2621440 |
+----------+
1 row in set (23.38 sec)
详细查看Tungsten同步状态
[tungsten@localhost ]$ trepctl -host 192.168.23.108 status
Processing status command...
NAME VALUE
---- -----
appliedLastEventId : mysql-bin.000057:0000000000006076;96
appliedLastSeqno : 26
appliedLatency : 0.496
channels : 1
clusterName : default
currentEventId : mysql-bin.000057:0000000000006076
currentTimeMillis : 1397206140435
dataServerHost : 192.168.23.108
extensions :
host : 192.168.23.108
latestEpochNumber : 0
masterConnectUri :
masterListenUri : thl://192.168.23.108:2112/
maximumStoredSeqNo : 26
minimumStoredSeqNo : 0
offlineRequests : NONE
pendingError : NONE
pendingErrorCode : NONE
pendingErrorEventId : NONE
pendingErrorSeqno : -1
pendingExceptionMessage: NONE
pipelineSource : jdbc:mysql:thin://192.168.23.108:3306/
relativeLatency : 57.435
resourcePrecedence : 99
rmiPort : 10000
role : master
seqnoType : java.lang.Long
serviceName : log
serviceType : local
simpleServiceName : log
siteName : default
sourceId : 192.168.23.108
state : ONLINE
timeInStateSeconds : 2620.153
transitioningTo :
uptimeSeconds : 2633.722
useSSLConnection : false
version : Tungsten Replicator 2.2.0 build 292
Finished status command...
[tungsten@localhost ]$ trepctl -host 192.168.23.109 status
Processing status command...
NAME VALUE
---- -----
appliedLastEventId : mysql-bin.000057:0000000000006076;96
appliedLastSeqno : 26
appliedLatency : 0.778
channels : 1
clusterName : default
currentEventId : NONE
currentTimeMillis : 1397206148280
dataServerHost : 192.168.23.109
extensions :
host : 192.168.23.109
latestEpochNumber : 0
masterConnectUri : thl://192.168.23.108:2112/
masterListenUri : thl://192.168.23.109:2112/
maximumStoredSeqNo : 26
minimumStoredSeqNo : 0
offlineRequests : NONE
pendingError : NONE
pendingErrorCode : NONE
pendingErrorEventId : NONE
pendingErrorSeqno : -1
pendingExceptionMessage: NONE
pipelineSource : thl://192.168.23.108:2112/
relativeLatency : 65.279
resourcePrecedence : 99
rmiPort : 10000
role : slave
seqnoType : java.lang.Long
serviceName : log
serviceType : local
simpleServiceName : log
siteName : default
sourceId : 192.168.23.109
state : ONLINE
timeInStateSeconds : 757.293
transitioningTo :
uptimeSeconds : 835.709
useSSLConnection : false
version : Tungsten Replicator 2.2.0 build 292
Finished status command...
[tungsten@localhost ]$ trepctl -host 192.168.23.108 services
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: 26
appliedLatency : 0.496
role : master
serviceName : log
serviceType : local
started : true
state : ONLINE
Finished services command...
[tungsten@localhost ]$ trepctl -host 192.168.23.109 services
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: 26
appliedLatency : 0.778
role : slave
serviceName : log
serviceType : local
started : true
state : ONLINE
Finished services command...
查看主从数据
Master:
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2621441 |
+----------+
1 row in set (22.27 sec)
Slave:
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2621441 |
+----------+
1 row in set (22.27 sec)
同步成功,下面总结一下在安装过程中需要注意的问题
1、如果MySQL是自己编译安装的,需要在/etc/profile指定PATH环境变量,否则tungsten认不出来报如下错误:192.168.23.109 Failed: which mysql
2、192.168.23.109 Unable to connect to the MySQL server using tungsten@192.168.23.109:3306 (WITH PASSWORD)
此错误表示tungsten用户无法登陆本地MySQL执行如下命令解决：
create user tungsten@'localhost' identified by 'XXXXX';grant all on *.* to tungsten@'localhost' identified by 'XXXXX' with grant option;flush privileges;
3、 192.168.23.107 The MySQL config file '/etc/my.cnf' does not exist
安装Tungsten的时候使用--datasource-mysql-conf参数指定my.cnf的位置
除了用MySQL自带的replicator之外tungsten-replicator也提供更加灵活复制方式选择.继续挖潜tungsten.今天先到这里吧.^_^
阅读(8919) | 评论(0) | 转发(0) |
