.::: Shell Script to compare value table a Database MariaDB MySQL :::.

  

correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. check value table barang server1
 

 
2. check value table barang server2
 

3. run script compare value table

sh compare_value_table.sh teguhth barang

[root@teguhth02 compare]# sh compare_value_table.sh teguhth barang
 

4. Script compare 

[root@teguhth02 compare]# cat compare_value_table.sh

ipx1='10.10.10.31'
portx1='3306'
#dbx1='dbxx'
#dbx0="db_${1}"
userx='admin'
pwdx='admin'

ipx2='10.10.10.32'
portx2='3306'
#dbx2='dbxx'

if [ -z "$1" ] || [ -z "$2" ]; then
  echo "Usage: $0 <nama_db_suffix> <table_name>"
  exit 1
fi

dbx0="${1}"
tablex="${2}"


host1=$(mysql -u$userx -p$pwdx -h$ipx1 -P$portx1 -sNe "SELECT @@hostname;")
host2=$(mysql -u$userx -p$pwdx -h$ipx2 -P$portx2 -sNe "SELECT @@hostname;")

ver1=$(mysql -u$userx -p$pwdx -h$ipx1 -P$portx1 -sNe "SELECT @@version;")
ver2=$(mysql -u$userx -p$pwdx -h$ipx2 -P$portx2 -sNe "SELECT @@version;")


table1=$(mysql -u$userx -p$pwdx -h$ipx1 -P$portx1 $dbx0 -sNe "SELECT count(*) from $tablex;")
table2=$(mysql -u$userx -p$pwdx -h$ipx2 -P$portx2 $dbx0 -sNe "SELECT count(*) from $tablex;")

isi1=$(mysql -u$userx -p$pwdx -h$ipx1 -P$portx1 $dbx0 -sNe "SELECT * from $tablex;")
isi2=$(mysql -u$userx -p$pwdx -h$ipx2 -P$portx2 $dbx0 -sNe "SELECT * from $tablex;")

echo "$isi1" > value1.txt
echo "$isi2" > value2.txt

Selisih=$((table1 - table2))


misinginclude=$(grep -Fxv -f value2.txt value1.txt)
misingexclude=$(grep -Fxv -f value1.txt value2.txt)

datex=$(date)
echo ""
echo ".::: Result compare Value Table '${2}' in Database '${1}' at $datex :::."
echo ".::: Result compare Value Table '${2}' in Database '${1}' at $datex :::." > log_compare.txt
echo "" >> log_compare.txt
echo ""
echo "Database       : $dbx0"
echo "Database       : $dbx0"  >> log_compare.txt
echo "Table          : ${2}"
echo "Table          : ${2}"  >> log_compare.txt

echo "" >> log_compare.txt
echo ""
echo "IP Server 1    : $ipx1"
echo "IP Server 1    : $ipx1" >> log_compare.txt
echo "Hostname 1     : $host1"
echo "Hostname 1     : $host1" >> log_compare.txt
echo "DB Version 1   : $ver1"
echo "DB Version 1   : $ver1" >> log_compare.txt
echo "Count Table1   : $table1 from database '$dbx0'"
echo "Count Table 1  : $table1 from database '$dbx0'" >> log_compare.txt
echo "" >> log_compare.txt
echo ""
echo "IP Server 2    : $ipx2"
echo "IP Server 2    : $ipx2" >> log_compare.txt
echo "Hostname 2     : $host2"
echo "Hostname 2     : $host2" >> log_compare.txt
echo "DB Version 2   : $ver2"
echo "DB Version 2   : $ver2" >> log_compare.txt
echo "Count Table 2  : $table2 from database '$dbx0'"
echo "Count Table 2  : $table2 from database '$dbx0'" >> log_compare.txt
echo "" >> log_compare.txt
echo ""
echo "Different      : $Selisih "
echo "Different      : $Selisih " >> log_compare.txt

echo "Value table 1 EXCEPT Value Table 2 : "
echo "Value table 1 EXCEPT Value Table 2 : "  >> log_compare.txt
echo "$misinginclude" | tail -n +0 | sed 's/^/                /'
echo "$misinginclude" | tail -n +0 | sed 's/^/                /' >> log_compare.txt
echo ""
echo "" >> log_compare.txt
echo "Value table 2 EXCEPT Value Table 1 : "
echo "Value table 2 EXCEPT Value Table 1 : " >> log_compare.txt
echo "$misingexclude" | tail -n +0 | sed 's/^/                /'
echo "$misingexclude" | tail -n +0 | sed 's/^/                /' >> log_compare.txt

echo "-- ============================================="
echo "-- Author: Teguh Triharto"
echo "-- Create date:"
echo "-- Description: Script Backup Database"
echo "-- Website : https://www.linkedin.com/in/teguhth"
echo "-- ============================================="
[root@teguhth02 compare]#