Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the process of incremental synchronization of database tables with shell script?

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >

Share

Shulou(Shulou.com)06/03 Report--

What is the process of shell script to achieve incremental synchronization of database tables? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Demand:

Change the zxxt_class table of the source database study_plan library at a fixed time every day

Incremental synchronization to the zxxt_class table of the target database axt_statistics library

Prerequisites:

The zxxt_class table structure in the two libraries is the same.

Ask the developer which field to use as an incremental reference. Here the development is given to the id field.

Process:

Gets the maximum id value of the id field in the zxxt_class table of the axt_statistics library

Use this id value to back up data in the zxxt_class table of the study_plan library that is larger than this id

Import data

Script:

#! / bin/bash # General variable MySql_Comm='/usr/local/mysql/bin/mysql'MySqldump_Comm='/usr/local/mysql/bin/mysqldump'DateTime= `date +% Y-%m-%d-%H:%M:% S`date-e "\ n\ n$ {DateTime}-script starts execution -" > > / tmp/sourcedb.log # Source database information Source_MySql_User='root'Source_MySql_Pass='123456' Source_MySql_Port='3306'Source_MySql_DB='study_plan'Source_MySql_Table='zxxt_class'Source_Host_IP='192.168.0.100' # Native database information Mysql_User='root'MySql_Pass='12345678'MySql_Port='3306'MySql_DB='axt_statistics'MySql_Table='zxxt_class'MySql_Bak_Dir= "/ tmp/ `date +% YMY% MMI% dMY% HMI% M`" # create backup directory mkdir ${MySql_Bak_Dir} # backup native table if [- d ${MySql_Bak_Dir}] Then ${MySqldump_Comm}\-u$ {Mysql_User}\-p$ {MySql_Pass}\-h 127.0.0.1\-P$ {MySql_Port}\ ${MySql_DB} ${MySql_Table} > ${MySql_Bak_Dir} / ${MySql_DB}-${MySql_Table} .sqlelse echo "${DateTime} ERROR: ${MySql_Bak_Dir} directory does not exist" > / tmp/sourcedb.log echo " ${DateTime}-script execution complete! -"> > / tmp/sourcedb.log exit 1fi # get the maximum ID$ {MySql_Comm}\-u$ {Mysql_User}\-p$ {MySql_Pass}\-h 127.0.0.1\-P$ {MySql_Port}\-compress ${MySql_DB}-e" select max (id) from ${MySql_Table} "> / tmp/tmp.txt ID_Num= `tail-1 / tmp/ tmp.txt`echo $ID_Num # backup source The table is larger than the data if of id obtained locally [[${ID_Num}-gt 0]] Then if [- d ${MySql_Bak_Dir}] Then echo "${DateTime} starts backing up data of the original host ${Source_MySql_DB} ${Source_MySql_Table} ID greater than ${ID_Num}." > / tmp/sourcedb.log ${MySqldump_Comm}-t\-u$ {Source_MySql_User}\-p$ {Source_MySql_Pass}\-h$ {Source_Host_IP}\-P$ {Source_MySql_Port}\ -- single-transaction-- compress ${Source_MySql_DB} ${Source_MySql_Table}-- where= "id > '`tail-1 / tmp/ tmp.txt`" > ${MySql_Bak_Dir} / ${Source_MySql_DB}-${Source_MySql_Table} .SQL echo "${DateTime} data backup completed ${MySql_Bak_Dir} / ${Source_MySql_DB}-${Source_MySql_Table} .sql" > > / tmp/sourcedb .log # Import data if [- f ${MySql_Bak_Dir} / ${Source_MySql_DB}-${Source_MySql_Table} .sql] Then echo "${DateTime} starts importing data." > > / tmp/sourcedb.log ${MySql_Comm}\-u$ {Mysql_User}\-p$ {MySql_Pass}\-h 127.0.0.1\-P$ {MySql_Port}\ ${MySql_DB}-e "source ${MySql_Bak_Dir} / ${Source_MySql_DB}-${ Source_MySql_Table} .sql "echo" ${DateTime} data import completed ${MySql_Bak_Dir} / ${Source_MySql_DB}-${Source_MySql_Table} .sql. "> / tmp/sourcedb.log echo" ${DateTime}-script execution completed! -"> > / tmp/sourcedb.log else echo" ${DateTime} ERROR: sql file ${MySql_Bak_Dir} / ${Source_MySql_DB}-${Source_MySql_Table} .sql does not exist! " Echo "${DateTime}-script execution complete! -"> > / tmp/sourcedb.log exit 1 fi else echo" ${DateTime} ERROR: ${MySql_Bak_Dir} directory does not exist "> > / tmp/sourcedb.log echo" ${DateTime}-script execution completed! -"> > / tmp/sourcedb.log exit 1 fielse echo" ${DateTime} ERROR: ID equals NULL "> > / tmp/sourcedb.log echo" ${DateTime}-script execution complete! -"> > / tmp/sourcedb.log exit 1fi

Be careful! It should be noted in the script that the parameter-t must be added when using mysqldump from the source database, and-t means backup insert data. If you do not add-t, then the data imported into the target database will replace the source data.

Test:

As you can see in the above two figures, there is one more data in the source table than in the target table.

After executing the script

The data has been synchronized.

Log:

Take a look at the imported sql script.

You can see that only the newly added data was backed up and imported.

The answer to the question about the process of shell script to achieve incremental synchronization of database tables is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Development

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report