A. Activity test compare
1. prepare file
[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# ls -lh
total 296M
-rw-r--r-- 1 root root 1023 Sep 22 14:53 cek_compare_backup_error_basic.sh
-rw-r--r-- 1 root root 1.4K Sep 22 14:50 cek_compare_backup_error_detail.sh
-rw-r--r-- 1 root root 2.0K Sep 22 14:50 cek_compare_backup_error_detail_compres.sh
-rw-r--r-- 1 root root 2.5K Sep 22 14:50 cek_compare_backup_error_detail_compres_enc.sh
-rw-r--r-- 1 root root 973 Sep 22 15:58 restore_error.log
-rw-r--r-- 1 root root 139M Sep 22 14:53 teguhth_ori.sql
-rw-r--r-- 1 root root 19M Sep 22 14:51 teguhth_ori.sql.gz
-rw-r--r-- 1 root root 139M Sep 22 14:52 teguhthenc.sql.enc
[root@teguhth10x mirortest]#
2. check restore_error.log
[root@teguhth10x mirortest]# cat restore_error.log
--------------
CREATE TABLE `barang_maria_federated_srcdef` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------
ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
--------------
CREATE TABLE `table_edb_barang` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=edbdb' `TABLE_TYPE`=ODBC `TABNAME`='barang'
--------------
ERROR 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'
[root@teguhth10x mirortest]#
3. check basic
sh cek_compare_backup_error_basic.sh
[root@teguhth10x mirortest]# sh cek_compare_backup_error_basic.sh
ERROR LINE : 887445
DATABASE : dblink
TABLE : barang_maria_federated_srcdef
ERROR : Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
ERROR LINE : 887879
DATABASE : edb
TABLE : table_edb_barang
ERROR : Unknown option 'TABLE_TYPE'
[root@teguhth10x mirortest]#
4. check detail
sh cek_compare_backup_error_detail.sh
[root@teguhth10x mirortest]# sh cek_compare_backup_error_detail.sh
============================================================
ERROR LINE : 887445
DATABASE : dblink
TABLE : barang_maria_federated_srcdef
ERROR : Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
CREATE TABLE `barang_maria_federated_srcdef` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------
ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
============================================================
ERROR LINE : 887879
DATABASE : edb
TABLE : table_edb_barang
ERROR : Unknown option 'TABLE_TYPE'
CREATE TABLE `table_edb_barang` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=edbdb' `TABLE_TYPE`=ODBC `TABNAME`='barang'
--------------
ERROR 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'
[root@teguhth10x mirortest]#
5. cek detail from .gz
sh cek_compare_backup_error_detail_compres.sh
[root@teguhth10x mirortest]# sh cek_compare_backup_error_detail_compres.sh
============================================================
ERROR LINE : 887445
DATABASE : dblink
TABLE : barang_maria_federated_srcdef
ERROR : Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
CREATE TABLE `barang_maria_federated_srcdef` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------
ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
============================================================
ERROR LINE : 887879
DATABASE : edb
TABLE : table_edb_barang
ERROR : Unknown option 'TABLE_TYPE'
CREATE TABLE `table_edb_barang` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=edbdb' `TABLE_TYPE`=ODBC `TABNAME`='barang'
--------------
ERROR 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'
[root@teguhth10x mirortest]#
6. cek detail from .gz n encrypted
[root@teguhth10x mirortest]# sh cek_compare_backup_error_detail_compres_enc.sh
*** WARNING : deprecated key derivation used.
Using -iter or -pbkdf2 would be better.
============================================================
ERROR LINE : 887445
DATABASE : dblink
TABLE : barang_maria_federated_srcdef
ERROR : Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
CREATE TABLE `barang_maria_federated_srcdef` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------
ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
============================================================
ERROR LINE : 887879
DATABASE : edb
TABLE : table_edb_barang
ERROR : Unknown option 'TABLE_TYPE'
CREATE TABLE `table_edb_barang` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=edbdb' `TABLE_TYPE`=ODBC `TABNAME`='barang'
--------------
ERROR 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'
[root@teguhth10x mirortest]#
B. script error log mariadb using simple or resume
1. run script
sh read_log_maria_resume.sh restore_error_sample.log
[root@teguhth10x onlylog]# pwd
/data/mirror/onlylog
[root@teguhth10x onlylog]# sh read_log_maria_resume.sh restore_error_sample.log
# Error 1
ERROR LINE : 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
ERROR : '10.10.10.8'
--------------
CREATE TABLE `barang_maria_federated_srcdef` (
`KODE_BARANG` char(6) NOT NULL,
******cut******
# Error 2
ERROR LINE : 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'
ERROR : Unknown option 'TABLE_TYPE'
--------------
CREATE TABLE `table_edb_barang` (
`KODE_BARANG` char(6) NOT NULL,
******cut******
[root@teguhth10x onlylog]#
2. sample script read_log_maria_resume.sh
[root@teguhth10x onlylog]# pwd
/data/mirror/onlylog
[root@teguhth10x onlylog]# cat read_log_maria_resume.sh
#!/bin/bash
LOG="$1"
if [ -z "$LOG" ]; then
echo "Usage: sh $0 restore_error.log"
exit 1
fi
awk '
/^--------------$/ {
if (in_query) {
in_query=0
} else {
in_query=1
query=""
}
next
}
# Simpan semua isi query
in_query {
query=query $0 "\n"
next
}
# ERROR
/^ERROR [0-9]+ \(HY000\) at line [0-9]+:/ {
error_no++
# Ambil error code
match($0, /^ERROR [0-9]+/)
error_code=substr($0, 7)
# Ambil pesan error
msg=$0
sub(/^.*: /, "", msg)
if (error_no > 1)
print ""
print "# Error " error_no
print ""
print "ERROR LINE : " error_code
print "ERROR : " msg
print "--------------"
# Tampilkan hanya 2 baris pertama query
n=split(query, lines, "\n")
if (n >= 1)
print lines[1]
if (n >= 2)
print lines[2]
print "******cut******"
query=""
}
' "$LOG"
[root@teguhth10x onlylog]#
C. Script checkiing
1. script cek_compare_backup_error_basic.sh
sh cek_compare_backup_error_basic.sh
[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# cat cek_compare_backup_error_basic.sh
#!/bin/bash
dump="teguhth_ori.sql"
err="restore_error.log"
grep -oE 'line [0-9]+' "$err" | awk '{print $2}' | while read line
do
# DATABASE dari dump
db=$(awk -v target="$line" '
NR > target { exit }
/^USE `/ {
db=$0
sub(/^USE `/, "", db)
sub(/`;$/, "", db)
}
END {
print db
}
' "$dump")
# TABLE dari restore_error.log
table=$(awk -v target="$line" '
/^CREATE TABLE `/ {
table=$0
sub(/^CREATE TABLE `/, "", table)
sub(/`.*/, "", table)
}
/at line / {
if ($0 ~ "at line " target ":") {
print table
exit
}
}
' "$err")
# ERROR
error=$(grep "at line $line:" "$err" |
sed "s/.*at line $line: //")
echo "ERROR LINE : $line"
echo "DATABASE : ${db:-UNKNOWN}"
echo "TABLE : ${table:-UNKNOWN}"
echo "ERROR : ${error:-UNKNOWN}"
echo
done
[root@teguhth10x mirortest]#
2. script cek_compare_backup_error_detail.sh
sh cek_compare_backup_error_detail.sh
[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# cat cek_compare_backup_error_detail.sh
#!/bin/bash
#dump="oridbsf.sql"
dump="teguhth_ori.sql"
err="restore_error.log"
grep -oE 'line [0-9]+' "$err" | awk '{print $2}' | while read line
do
# DATABASE
db=$(awk -v target="$line" '
NR > target { exit }
/^USE `/ {
db=$0
sub(/^USE `/, "", db)
sub(/`;$/, "", db)
}
END {
print db
}
' "$dump")
# TABLE
table=$(awk -v target="$line" '
/^CREATE TABLE `/ {
t=$0
sub(/^CREATE TABLE `/, "", t)
sub(/`.*/, "", t)
}
$0 ~ "at line " target ":" {
print t
exit
}
' "$err")
# ERROR
error=$(grep "at line $line:" "$err" |
sed "s/.*at line $line: //")
# CREATE TABLE
create_table=$(awk -v target="$line" '
/^CREATE TABLE `/ {
capture=1
block=$0
next
}
capture {
block=block "\n" $0
}
$0 ~ "ERROR .* at line " target ":" {
print block
exit
}
' "$err")
echo "============================================================"
echo "ERROR LINE : $line"
echo "DATABASE : ${db:-UNKNOWN}"
echo "TABLE : ${table:-UNKNOWN}"
echo "ERROR : ${error:-UNKNOWN}"
echo
echo "$create_table"
echo
done
[root@teguhth10x mirortest]#
3. script cek_compare_backup_error_detail_compres.sh
sh cek_compare_backup_error_detail_compres.sh
[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# cat cek_compare_backup_error_detail_compres.sh
#!/bin/bash
#dump="oridbsf.sql.gz"
dump="teguhth_ori.sql.gz"
err="restore_error.log"
grep -oE 'line [0-9]+' "$err" |
awk '{print $2}' |
sort -n |
uniq |
while read line
do
# ============================================================
# DATABASE
# ============================================================
db=$(zcat "$dump" 2>/dev/null |
awk -v target="$line" '
NR > target {
exit
}
/^USE `/ {
db=$0
sub(/^USE `/, "", db)
sub(/`;$/, "", db)
}
END {
print db
}
')
# ============================================================
# TABLE
# ============================================================
table=$(awk -v target="$line" '
/^CREATE TABLE `/ {
t=$0
sub(/^CREATE TABLE `/, "", t)
sub(/`.*/, "", t)
}
$0 ~ "at line " target ":" {
print t
exit
}
' "$err")
# ============================================================
# ERROR
# ============================================================
error=$(grep "at line $line:" "$err" |
sed "s/.*at line $line: //")
# ============================================================
# CREATE TABLE
# ============================================================
create_table=$(awk -v target="$line" '
/^CREATE TABLE `/ {
capture=1
block=$0
next
}
capture {
block=block "\n" $0
}
$0 ~ "ERROR .* at line " target ":" {
print block
exit
}
' "$err")
echo "============================================================"
echo "ERROR LINE : $line"
echo "DATABASE : ${db:-UNKNOWN}"
echo "TABLE : ${table:-UNKNOWN}"
echo "ERROR : ${error:-UNKNOWN}"
echo
echo "$create_table"
echo
done
[root@teguhth10x mirortest]#
4. script cek_compare_backup_error_detail_compres_enc.sh
sh cek_compare_backup_error_detail_compres_enc.sh
[root@teguhth10x mirortest]# cat cek_compare_backup_error_detail_compres_enc.sh
#!/bin/bash
dumpenc="teguhthenc.sql.enc"
tmpdump="/tmp/teguhthenc_$$.sql"
err="restore_error.log"
# ============================================================
# DECRYPT DUMP KE TEMPORARY FILE
# ============================================================
openssl enc -d -aes-256-cbc -md sha1 \
-in "$dumpenc" \
-out "$tmpdump" \
-pass pass:"$dumpenc"
if [ $? -ne 0 ]; then
echo "ERROR: decrypt gagal"
rm -f "$tmpdump"
exit 1
fi
trap 'rm -f "$tmpdump"' EXIT
# ============================================================
# CEK ERROR LINE
# ============================================================
grep -oE 'line [0-9]+' "$err" |
awk '{print $2}' |
sort -n |
uniq |
while read line
do
# ========================================================
# DATABASE
# ========================================================
db=$(awk -v target="$line" '
NR > target {
exit
}
/^USE `/ {
db=$0
sub(/^USE `/, "", db)
sub(/`;$/, "", db)
}
END {
print db
}
' "$tmpdump")
# ========================================================
# TABLE
# ========================================================
table=$(awk -v target="$line" '
NR > target {
exit
}
/^CREATE TABLE `/ {
t=$0
sub(/^CREATE TABLE `/, "", t)
sub(/`.*/, "", t)
}
END {
print t
}
' "$tmpdump")
# ========================================================
# ERROR
# ========================================================
error=$(grep "at line $line:" "$err" |
sed "s/.*at line $line: //" |
head -1)
# ========================================================
# CREATE TABLE DARI ERROR LOG
# ========================================================
create_table=$(awk -v target="$line" '
/^CREATE TABLE `/ {
capture=1
block=$0
next
}
capture {
block=block "\n" $0
}
$0 ~ "ERROR .* at line " target ":" {
print block
exit
}
' "$err")
echo "============================================================"
echo "ERROR LINE : $line"
echo "DATABASE : ${db:-UNKNOWN}"
echo "TABLE : ${table:-UNKNOWN}"
echo "ERROR : ${error:-UNKNOWN}"
echo
echo "$create_table"
echo
done
[root@teguhth10x mirortest]#
[root@teguhth10x mirortest]# cat restore_error.log
--------------
CREATE TABLE `barang_maria_federated_srcdef` (
`KODE_BARANG` char(6) NOT NULL,
`NAMA_BARANG` varchar(25) DEFAULT NULL,
`SATUAN_BARANG` varchar(20) DEFAULT NULL,
`STOK_BARANG` decimal(4,0) DEFAULT NULL,
PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------
ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
biar outputnya.bgmna scrip sheelnya
ERROR LINE : 887445
DATABASE : dblink
TABLE : barang_maria_federated_srcdef
ERROR : Can't create federated table. Foreign data src error: database: 'teguhth' username: 'admin' hostname: '10.10.10.8'
CREATE TABLE `barang_maria_federated_srcdef` (
`KODE_BARANG` char(6) NOT NULL,
#!/bin/bash
LOG="$1"
if [ -z "$LOG" ]; then
echo "Usage: $0 restore_error.log"
exit 1
fi
awk '
/^CREATE TABLE `/ {
create=$0
# Ambil nama table
table=$0
sub(/^CREATE TABLE `/, "", table)
sub(/`.*/, "", table)
next
}
/^ERROR [0-9]+/ {
error_line=""
error_msg=$0
# ERROR LINE
if (match($0, /at line [0-9]+/)) {
tmp=substr($0, RSTART, RLENGTH)
sub(/^at line /, "", tmp)
error_line=tmp
}
# ERROR message
sub(/^ERROR [0-9]+ \([^)]*\): /, "", error_msg)
next
}
/Foreign data src error:/ {
db=""
user=""
host=""
# database
if (match($0, /database: '\''[^'\'']+'\''/)) {
tmp=substr($0, RSTART, RLENGTH)
sub(/^database: '\''/, "", tmp)
sub(/'\''$/, "", tmp)
db=tmp
}
# username
if (match($0, /username: '\''[^'\'']+'\''/)) {
tmp=substr($0, RSTART, RLENGTH)
sub(/^username: '\''/, "", tmp)
sub(/'\''$/, "", tmp)
user=tmp
}
# hostname
if (match($0, /hostname: '\''[^'\'']+'\''/)) {
tmp=substr($0, RSTART, RLENGTH)
sub(/^hostname: '\''/, "", tmp)
sub(/'\''$/, "", tmp)
host=tmp
}
print "ERROR LINE : " error_line
print "DATABASE : dblink"
print "TABLE : " table
print "ERROR : " error_msg
print ""
print create
print ""
print "FOREIGN DB : " db
print "USERNAME : " user
print "HOSTNAME : " host
print ""
print "----------------------------------------"
print ""
create=""
table=""
}
' "$LOG"
No comments:
Post a Comment