Find missing .ibd files

Hi,

I have mysql file that extension is .frm and .ibd file.
I am trying the to get command line utility to get missing .ibd file.

for example:
Input :

$ ls -al
-rw-rw---- 1 mysql mysql       8684 Dec 22 12:22 a.frm
-rw-rw---- 1 mysql mysql     114688 Feb  5 16:01 a.ibd
-rw-rw---- 1 mysql mysql       8638 Dec 22 12:22 b.frm
-rw-rw---- 1 mysql mysql     114688 Feb  5 16:01 b.ibd
-rw-rw---- 1 mysql mysql       8638 Dec 22 12:22 c.frm
-rw-rw---- 1 mysql mysql       8638 Dec 22 12:22 d.frm

From the above list c.ibd & d.ibd file only missing. so i am expecting the list of files that not the having .ibd files.

Output :

-rw-rw---- 1 mysql mysql       8638 Dec 22 12:22 c.frm
-rw-rw---- 1 mysql mysql       8638 Dec 22 12:22 d.frm

Thanks in advance.

Hello k_manimuthu,

As you have mentioned that only .frm and .ibd files only be present in your directory, then following may help you in same.

ls -lhtr | awk '{A=$NF;sub(/\..*/,X,A);if($NF ~ /\.frm/ || $0 ~ /\.ibd/){B[A]++}} END{for(i in B){if(B<2){print "ls -ltr " i"*"}}}'  | sh

Thanks,
R. Singh

$ ls -1
a.frm
a.ibd
b.frm
b.ibd
c.frm
d.frm

$ ls | uniq -u -w1
c.frm
d.frm

If you want a long list, afterward:

ls | uniq -u -w1 | xargs ls -l

Also, you can count the fields produced by ls -l and skip with the -f

 ls -l | uniq -u -f8 -w2

Hi.

Using shell constructs:

#!/usr/bin/env bash

# @(#) s1       Demonstrate finding missing files based on prefix-suffix.

# Utility functions: print-as-echo, print-line-with-visual-space, debug.
# export PATH="/usr/local/bin:/usr/bin:/bin"
LC_ALL=C ; LANG=C ; export LC_ALL LANG
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }
db() { ( printf " db, ";for _i;do printf "%s" "$_i";done;printf "\n" ) >&2 ; }
db() { : ; }
C=$HOME/bin/context && [ -f $C ] && $C

pl " Current content of frm and ibd files:"
ls *.frm *.ibd

pl " Results:"
for i in *.frm
do
  [ ! -f ${i%%.frm}.ibd ] && printf " Missing ${i%%.frm}.ibd\n"
done

exit 0

producing:

$ ./s1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 3.16.0-4-amd64, x86_64
Distribution        : Debian 8.3 (jessie) 
bash GNU bash 4.3.30

-----
 Current content of frm and ibd files:
a.frm  a.ibd  b.frm  b.ibd  c.frm  d.frm

-----
 Results:
 Missing c.ibd
 Missing d.ibd

See man bash for details, search for %% in parameter section.

Best wishes ... cheers, drl