In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.