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

 

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

A. Compare Multiple Table 
1. check table server1
 

 
2. check table server2
 

3. run script compare table

sh compare_table.sh.sh teguhth 

[root@teguhth02 compare]# sh compare_table.sh.sh teguhth 
 

4. Script compare 

[root@teguhth02 tbl]# cat compare_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" ]; then
  echo "Usage: $0 <nama_db_suffix>"
  exit 1
fi

dbx0="${1}"


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;")

COUNT1=$(mysql -u$userx -p$pwdx -h$ipx1 -P$portx1 -sNe "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '$dbx0';")

COUNT2=$(mysql -u$userx -p$pwdx -h$ipx2 -P$portx2 -sNe "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '$dbx0';")

table1=$(mysql -u$userx -p$pwdx -h$ipx1 -P$portx1 -sNe "SELECT table_name FROM information_schema.tables WHERE table_schema = '$dbx0';")
table2=$(mysql -u$userx -p$pwdx -h$ipx2 -P$portx2 -sNe "SELECT table_name FROM information_schema.tables WHERE table_schema = '$dbx0';")

echo "$table1" > compare1.txt
echo "$table2" > compare2.txt

Selisih=$((COUNT1 - COUNT2))

if [ "$Selisih" -gt 0 ]; then
    # COUNT1 lebih besar ? cari tabel yang ada di compare1.txt tapi tidak di compare2.txt
    misingx=$(grep -Fxv -f compare2.txt compare1.txt)
elif [ "$Selisih" -lt 0 ]; then
    # COUNT2 lebih besar ? cari tabel yang ada di compare2.txt tapi tidak di compare1.txt
    misingx=$(grep -Fxv -f compare1.txt compare2.txt)
else
    # COUNT1 == COUNT2 ? tetap bandingkan untuk memastikan tidak ada perbedaan konten
    misingx=$(grep -Fxv -f compare2.txt compare1.txt)
fi

misinginclude=$(grep -Fxv -f compare2.txt compare1.txt)
misingexclude=$(grep -Fxv -f compare1.txt compare2.txt)

datex=$(date)
echo ""
echo ".::: Result compare table ${1}' at $datex :::."
echo ".::: Result compare table '${1}' at $datex :::." > log_compare.txt
echo "" >> log_compare.txt
echo ""
echo "Database       : $dbx0"
echo "Database       : $dbx0"  >> 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 "Total Table 1  : $COUNT1 from $dbx0"


echo "Total Table 1  : $COUNT1 from $dbx0" >> log_compare.txt
echo "" >> log_compare.txt
echo ""
echo "IP Server 2    : $ipx2"
echo "IP Server 2    : $ipx2" >> log_compare.txt

echo "Total Table 2  : $COUNT2 from $dbx0"
echo "Total Table 2  : $COUNT2 from $dbx0" >> 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 "" >> log_compare.txt
echo ""
echo "Different      : $Selisih "
echo "Different      : $Selisih " >> log_compare.txt

echo "Table 1 EXCEPT Table 2 : "
echo "Table 1 EXCEPT 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 "Table 2 EXCEPT Table 1 : "
echo "Table 2 EXCEPT Table 1 : "  >> log_compare.txt

echo "$misingexclude" | tail -n +0 | sed 's/^/                /'
echo "$misingexclude" | tail -n +0 | sed 's/^/                /' >> log_compare.txt
echo ""

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

B. Compare Single Database 

 

1. put script single db 

 

[root@teguhth compare]# cat script_compare_table_singledb.sh

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

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

dbx0="${1}"


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

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

COUNT1=$(mysql -u$userx -p$pwdx -h$ipx1 -P$portx1 -sNe "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '$dbx0';")

table1=$(mysql -u$userx -p$pwdx -h$ipx1 -P$portx1 -sNe "SELECT table_name FROM information_schema.tables WHERE table_schema = '$dbx0';")

echo "$table1" > compare_$dbx0.txt


datex=$(date)
echo ""
echo ".::: Result compare table '${1}' at $datex :::."
echo ".::: Result compare table '${1}' at $datex :::." > log_compare.txt
echo "" >> log_compare.txt
echo ""
echo "Database       : $dbx0"
echo "Database       : $dbx0"  >> log_compare.txt
echo "" >> log_compare.txt
echo "IP Server     : $ipx1"
echo "IP Server     : $ipx1" >> log_compare.txt

echo "Hostname      : $host1"
echo "Hostname      : $host1" >> log_compare.txt
echo "DB Version    : $ver1"
echo "DB Version    : $ver1" >> log_compare.txt


echo "Total Table   : $COUNT1 from $dbx0"

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

2.  put script compare table from 2 database in file

 

[root@teguhth compare]# cat script_compare_table_file.sh

file1=${1}
file2=${2}

COUNT1=$(wc -l < compare_${file1}.txt)
COUNT2=$(wc -l < compare_${file2}.txt)

Selisih=$((COUNT1 - COUNT2))

if [ "$Selisih" -gt 0 ]; then
    # COUNT1 lebih besar ? cari tabel yang ada di compare_${file1}.txt tapi tidak di compare_${file2}
    misingx=$(grep -Fxv -f compare_${file2}.txt compare_${file1}.txt)
elif [ "$Selisih" -lt 0 ]; then
    # COUNT2 lebih besar ? cari tabel yang ada di compare_${file2}.txt tapi tidak di compare_${file1}
    misingx=$(grep -Fxv -f compare_${file1}.txt compare_${file2}.txt)
else
    # COUNT1 == COUNT2 ? tetap bandingkan untuk memastikan tidak ada perbedaan konten
    misingx=$(grep -Fxv -f compare_${file2}.txt compare_${file1}.txt)
fi

misinginclude=$(grep -Fxv -f compare_${file2}.txt compare_${file1}.txt)
misingexclude=$(grep -Fxv -f compare_${file1}.txt compare_${file2}.txt)

datex=$(date)
echo ""
echo ".::: Result compare table in db '${1}' vs table in db '${2}' at $datex :::."
echo ".::: Result compare table in db '${1}' vs table in db '${2}' at $datex :::." > log_compare.txt
echo "" >> log_compare.txt
echo ""

echo "DB Name 1      : $file1"
echo "DB Name 1      : $file1" >> log_compare.txt


echo "Total Table 1  : $COUNT1 from $file1"
echo "Total Table 1  : $COUNT1 from $file1" >> log_compare.txt
echo "" >> log_compare.txt
echo ""
echo "DB Name 2      : $file2"
echo "DB Name 2      : $file2" >> log_compare.txt
echo "Total Table 2  : $COUNT2 from $file2"
echo "Total Table 2  : $COUNT2 from $file2" >> log_compare.txt

echo "" >> log_compare.txt
echo ""
echo "Different      : $Selisih "
echo "Different      : $Selisih " >> log_compare.txt

echo "Table 1 EXCEPT Table 2 : "
echo "Table 1 EXCEPT 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 "Table 2 EXCEPT Table 1 : "
echo "Table 2 EXCEPT Table 1 : "  >> log_compare.txt

echo "$misingexclude" | tail -n +0 | sed 's/^/                /'
echo "$misingexclude" | tail -n +0 | sed 's/^/                /' >> log_compare.txt
echo ""

echo "-- ============================================="
echo "-- Author: Teguh Triharto"
echo "-- Create date:"
echo "-- Description: Script Backup Database"
echo "-- Website : https://www.linkedin.com/in/teguhth"
echo "-- ============================================="
echo "" >> log_compare.txt


[root@teguhth compare]#
  

3. script manual localhost

 

[root@teguhth compare]# mysql -uroot -pxxx -sNe "SELECT table_name FROM information_schema.tables WHERE table_schema='teguhth'" > compare_teguhth.txt;

[root@teguhth compare]# mysql -uroot -pxxx -sNe "SELECT table_name FROM information_schema.tables WHERE table_schema='erpdb'" > compare_erpdb.txt;
[root@teguhth compare]#