数据库同步的方式有设置标志位同步方式、以时间戳同步的方式,对于一对一的同步这两种方式都满足,但是对于一对多的同步,则只能选择时间戳的同步方式了,但是已时间戳同步的方式的一个问题是如何可靠的保证数据可以不丢失的同步到数据库中。下面有两种方式
数据库同步的方式有设置标志位同步方式、以时间戳同步的方式,对于一对一的同步这两种方式都满足,但是对于一对多的同步,则只能选择时间戳的同步方式了,但是已时间戳同步的方式的一个问题是如何可靠的保证数据可以不丢失的同步到数据库中。下面有两种方式来保证以时间戳同步的可靠性。
1、计算服务器与客户端两台电脑的时间差,将该时间差记入在同步时间的比对当中,下图是使用kettle做的一个计算同步的时间差的思路:
该思路的xml代码为:
<transformation> <info> <name>getLeadValue</name> <description/> <extended_description/> <trans_version/> <trans_type>Normal</trans_type> <trans_status>0</trans_status> <directory>/downloadServerData/commonData</directory> <parameters> </parameters> <log> <trans-log-table><connection/> <schema/> <table/> <size_limit_lines/> <interval/> <timeout_days/> <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>CHANNEL_ID</id><enabled>Y</enabled><name>CHANNEL_ID</name></field><field><id>TRANSNAME</id><enabled>Y</enabled><name>TRANSNAME</name></field><field><id>STATUS</id><enabled>Y</enabled><name>STATUS</name></field><field><id>LINES_READ</id><enabled>Y</enabled><name>LINES_READ</name><subject/></field><field><id>LINES_WRITTEN</id><enabled>Y</enabled><name>LINES_WRITTEN</name><subject/></field><field><id>LINES_UPDATED</id><enabled>Y</enabled><name>LINES_UPDATED</name><subject/></field><field><id>LINES_INPUT</id><enabled>Y</enabled><name>LINES_INPUT</name><subject/></field><field><id>LINES_OUTPUT</id><enabled>Y</enabled><name>LINES_OUTPUT</name><subject/></field><field><id>LINES_REJECTED</id><enabled>Y</enabled><name>LINES_REJECTED</name><subject/></field><field><id>ERRORS</id><enabled>Y</enabled><name>ERRORS</name></field><field><id>STARTDATE</id><enabled>Y</enabled><name>STARTDATE</name></field><field><id>ENDDATE</id><enabled>Y</enabled><name>ENDDATE</name></field><field><id>LOGDATE</id><enabled>Y</enabled><name>LOGDATE</name></field><field><id>DEPDATE</id><enabled>Y</enabled><name>DEPDATE</name></field><field><id>REPLAYDATE</id><enabled>Y</enabled><name>REPLAYDATE</name></field><field><id>LOG_FIELD</id><enabled>Y</enabled><name>LOG_FIELD</name></field></trans-log-table> <perf-log-table><connection/> <schema/> <table/> <interval/> <timeout_days/> <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>SEQ_NR</id><enabled>Y</enabled><name>SEQ_NR</name></field><field><id>LOGDATE</id><enabled>Y</enabled><name>LOGDATE</name></field><field><id>TRANSNAME</id><enabled>Y</enabled><name>TRANSNAME</name></field><field><id>STEPNAME</id><enabled>Y</enabled><name>STEPNAME</name></field><field><id>STEP_COPY</id><enabled>Y</enabled><name>STEP_COPY</name></field><field><id>LINES_READ</id><enabled>Y</enabled><name>LINES_READ</name></field><field><id>LINES_WRITTEN</id><enabled>Y</enabled><name>LINES_WRITTEN</name></field><field><id>LINES_UPDATED</id><enabled>Y</enabled><name>LINES_UPDATED</name></field><field><id>LINES_INPUT</id><enabled>Y</enabled><name>LINES_INPUT</name></field><field><id>LINES_OUTPUT</id><enabled>Y</enabled><name>LINES_OUTPUT</name></field><field><id>LINES_REJECTED</id><enabled>Y</enabled><name>LINES_REJECTED</name></field><field><id>ERRORS</id><enabled>Y</enabled><name>ERRORS</name></field><field><id>INPUT_BUFFER_ROWS</id><enabled>Y</enabled><name>INPUT_BUFFER_ROWS</name></field><field><id>OUTPUT_BUFFER_ROWS</id><enabled>Y</enabled><name>OUTPUT_BUFFER_ROWS</name></field></perf-log-table> <channel-log-table><connection/> <schema/> <table/> <timeout_days/> <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>CHANNEL_ID</id><enabled>Y</enabled><name>CHANNEL_ID</name></field><field><id>LOG_DATE</id><enabled>Y</enabled><name>LOG_DATE</name></field><field><id>LOGGING_OBJECT_TYPE</id><enabled>Y</enabled><name>LOGGING_OBJECT_TYPE</name></field><field><id>OBJECT_NAME</id><enabled>Y</enabled><name>OBJECT_NAME</name></field><field><id>OBJECT_COPY</id><enabled>Y</enabled><name>OBJECT_COPY</name></field><field><id>REPOSITORY_DIRECTORY</id><enabled>Y</enabled><name>REPOSITORY_DIRECTORY</name></field><field><id>FILENAME</id><enabled>Y</enabled><name>FILENAME</name></field><field><id>OBJECT_ID</id><enabled>Y</enabled><name>OBJECT_ID</name></field><field><id>OBJECT_REVISION</id><enabled>Y</enabled><name>OBJECT_REVISION</name></field><field><id>PARENT_CHANNEL_ID</id><enabled>Y</enabled><name>PARENT_CHANNEL_ID</name></field><field><id>ROOT_CHANNEL_ID</id><enabled>Y</enabled><name>ROOT_CHANNEL_ID</name></field></channel-log-table> <step-log-table><connection/> <schema/> <table/> <timeout_days/> <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>CHANNEL_ID</id><enabled>Y</enabled><name>CHANNEL_ID</name></field><field><id>LOG_DATE</id><enabled>Y</enabled><name>LOG_DATE</name></field><field><id>TRANSNAME</id><enabled>Y</enabled><name>TRANSNAME</name></field><field><id>STEPNAME</id><enabled>Y</enabled><name>STEPNAME</name></field><field><id>STEP_COPY</id><enabled>Y</enabled><name>STEP_COPY</name></field><field><id>LINES_READ</id><enabled>Y</enabled><name>LINES_READ</name></field><field><id>LINES_WRITTEN</id><enabled>Y</enabled><name>LINES_WRITTEN</name></field><field><id>LINES_UPDATED</id><enabled>Y</enabled><name>LINES_UPDATED</name></field><field><id>LINES_INPUT</id><enabled>Y</enabled><name>LINES_INPUT</name></field><field><id>LINES_OUTPUT</id><enabled>Y</enabled><name>LINES_OUTPUT</name></field><field><id>LINES_REJECTED</id><enabled>Y</enabled><name>LINES_REJECTED</name></field><field><id>ERRORS</id><enabled>Y</enabled><name>ERRORS</name></field><field><id>LOG_FIELD</id><enabled>N</enabled><name>LOG_FIELD</name></field></step-log-table> </log> <maxdate> <connection/> <table/> <field/> <offset>0.0</offset> <maxdiff>0.0</maxdiff> </maxdate> <size_rowset>10000</size_rowset> <sleep_time_empty>50</sleep_time_empty> <sleep_time_full>50</sleep_time_full> <unique_connections>N</unique_connections> <feedback_shown>Y</feedback_shown> <feedback_size>50000</feedback_size> <using_thread_priorities>Y</using_thread_priorities> <shared_objects_file/> <capture_step_performance>N</capture_step_performance> <step_performance_capturing_delay>1000</step_performance_capturing_delay> <step_performance_capturing_size_limit>100</step_performance_capturing_size_limit> <dependencies> </dependencies> <partitionschemas> </partitionschemas> <slaveservers> </slaveservers> <clusterschemas> </clusterschemas> <created_user>-</created_user> <created_date>2012/11/16 13:59:51.117</created_date> <modified_user>-</modified_user> <modified_date>2014/05/12 15:22:12.008</modified_date> </info> <notepads> </notepads> <connection> <name>backupConn</name> <server>${CLIENT_DATABASE_IP}</server> <type>ORACLE</type> <access>Native</access> <database>${CLIENT_DATABASE_NAME}</database> <port>${CLIENT_DATABASE_PORT}</port> <username>${CLIENT_DATABASE_USERNAME}</username> <password>${CLIENT_DATABASE_PASSWORD}</password> <servername/> <data_tablespace/> <index_tablespace/> <attributes> <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute> <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute> <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute> <attribute><code>PORT_NUMBER</code><attribute>${CLIENT_DATABASE_PORT}</attribute></attribute> <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute> <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>N</attribute></attribute> <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute> </attributes> </connection> <connection> <name>serverConn</name> <server>${SERVER_DATABASE_IP}</server> <type>ORACLE</type> <access>Native</access> <database>${SERVER_DATABASE_NAME}</database> <port>${SERVER_DATABASE_PORT}</port> <username>${SERVER_DATABASE_USERNAME}</username> <password>${SERVER_DATABASE_PASSWORD}</password> <servername/> <data_tablespace/> <index_tablespace/> <attributes> <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute> <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute> <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute> <attribute><code>PORT_NUMBER</code><attribute>${SERVER_DATABASE_PORT}</attribute></attribute> <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute> <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>N</attribute></attribute> <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute> </attributes> </connection> <connection> <name>wardConn</name> <server>${CLIENT_DATABASE_IP}</server> <type>ORACLE</type> <access>Native</access> <database>${CLIENT_DATABASE_NAME}</database> <port>${CLIENT_DATABASE_PORT}</port> <username>${CLIENT_DATABASE_USERNAME}</username> <password>${CLIENT_DATABASE_PASSWORD}</password> <servername/> <data_tablespace/> <index_tablespace/> <attributes> <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute> <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute> <attribute><code>INITIAL_POOL_SIZE</code><attribute>50</attribute></attribute> <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute> <attribute><code>MAXIMUM_POOL_SIZE</code><attribute>500</attribute></attribute> <attribute><code>PORT_NUMBER</code><attribute>${CLIENT_DATABASE_PORT}</attribute></attribute> <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute> <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>N</attribute></attribute> <attribute><code>USE_POOLING</code><attribute>Y</attribute></attribute> </attributes> </connection> <order> <hop> <from>增加常量</from><to>增加常量 2</to><enabled>Y</enabled> </hop> <hop> <from>计算器</from><to>Set Variables 2</to><enabled>Y</enabled> </hop> <hop> <from>调用DB存储过程</from><to>调用DB存储过程 2</to><enabled>Y</enabled> </hop> <hop> <from>调用DB存储过程 2</from><to>增加常量</to><enabled>Y</enabled> </hop> <hop> <from>增加常量 2</from><to>计算器</to><enabled>Y</enabled> </hop> </order> <step> <name>Set Variables 2</name> <type>SetVariable</type> <description/> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name/> </partitioning> <fields> <field> <field_name>TIMEDIFF</field_name> <variable_name>DOWNLOAD_SERVER_COMMON_DATA_LEAD</variable_name> <variable_type>JVM</variable_type> <default_value/> </field> </fields> <use_formatting>Y</use_formatting> <cluster_schema/> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>118</xloc> <yloc>273</yloc> <draw>Y</draw> </GUI> </step> <step> <name>增加常量</name> <type>Constant</type> <description/> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name/> </partitioning> <fields> <field> <name>DIVIDEND</name> <type>Integer</type> <format/> <currency/> <decimal/> <group/> <nullif>86400000</nullif> <length>-1</length> <precision>-1</precision> </field> </fields> <cluster_schema/> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>388</xloc> <yloc>241</yloc> <draw>Y</draw> </GUI> </step> <step> <name>增加常量 2</name> <type>Constant</type> <description/> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name/> </partitioning> <fields> <field> <name>DEVIATION</name> <type>Integer</type> <format/> <currency/> <decimal/> <group/> <nullif>10000</nullif> <length>-1</length> <precision>-1</precision> </field> </fields> <cluster_schema/> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>364</xloc> <yloc>362</yloc> <draw>Y</draw> </GUI> </step> <step> <name>计算器</name> <type>Calculator</type> <description/> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name/> </partitioning> <calculation><field_name>TEMP</field_name> <calc_type>SUBTRACT</calc_type> <field_a>LOCAL_DATE</field_a> <field_b>SERVER_DATE</field_b> <field_c/> <value_type>BigNumber</value_type> <value_length>-1</value_length> <value_precision>-1</value_precision> <remove>N</remove> <conversion_mask/> <decimal_symbol/> <grouping_symbol/> <currency_symbol/> </calculation> <calculation><field_name>TEMP2</field_name> <calc_type>ADD</calc_type> <field_a>TEMP</field_a> <field_b>DEVIATION</field_b> <field_c/> <value_type>BigNumber</value_type> <value_length>-1</value_length> <value_precision>-1</value_precision> <remove>N</remove> <conversion_mask/> <decimal_symbol/> <grouping_symbol/> <currency_symbol/> </calculation> <calculation><field_name>TIMEDIFF</field_name> <calc_type>DIVIDE</calc_type> <field_a>TEMP2</field_a> <field_b>DIVIDEND</field_b> <field_c/> <value_type>BigNumber</value_type> <value_length>-1</value_length> <value_precision>-1</value_precision> <remove>N</remove> <conversion_mask/> <decimal_symbol/> <grouping_symbol/> <currency_symbol/> </calculation> <cluster_schema/> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>251</xloc> <yloc>272</yloc> <draw>Y</draw> </GUI> </step> <step> <name>调用DB存储过程</name> <type>DBProc</type> <description/> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name/> </partitioning> <connection>serverConn</connection> <procedure>F_GET_SYSDATE2INT</procedure> <lookup> </lookup> <result> <name>SERVER_DATE</name> <type>BigNumber</type> </result> <auto_commit>Y</auto_commit> <cluster_schema/> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>140</xloc> <yloc>134</yloc> <draw>Y</draw> </GUI> </step> <step> <name>调用DB存储过程 2</name> <type>DBProc</type> <description/> <distribute>Y</distribute> <copies>1</copies> <partitioning> <method>none</method> <schema_name/> </partitioning> <connection>wardConn</connection> <procedure>F_GET_SYSDATE2INT</procedure> <lookup> </lookup> <result> <name>LOCAL_DATE</name> <type>BigNumber</type> </result> <auto_commit>Y</auto_commit> <cluster_schema/> <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI> <xloc>353</xloc> <yloc>132</yloc> <draw>Y</draw> </GUI> </step> <step_error_handling> </step_error_handling> <slave-step-copy-partition-distribution> </slave-step-copy-partition-distribution> <slave_transformation>N</slave_transformation> </transformation>
1)如果可以连接到Internet网中,则可以采用国际上的校时服务器去校对时间
2)如果在内网中且不能上网,则可以自己定义一台都可访问到的台式机作为NTP服务器,其他机子与该计算机教士即可,下面是window NTP校时服务器的设置。
可以参考文章:NTP服务同步时间方案