msgbartop
MAC OS X, Linux, Windows and other IT Tips and Tricks
msgbarbottom

19 Dec 16 Display MySQL databases types in bash

Based on the site: http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table

Here is a bash command that will display the databases types(innoDB or MyISAM) of all the MySQL databases except for the defaults ones(mysql, etc)
for i in $(mysql -u root -p -e "show databases;" | egrep -v 'Database|information_schema|mysql|performance_schema'); do echo "--------------------$i--------------------"; mysql -u root -p -e "use $i; show table status;"; done | awk '{print $1" "$2}' | column -t

You will need to type as many times as there are databases the MySQL root password.

Alternative:
Create a mysql user and use it’s password on the command line as follows:
for i in $(mysql -u <user> -p --password='secret' -e "show databases;" | egrep -v 'Database|mysql|performance_schema'); do echo "--------------------$i--------------------"; mysql -u <user> -p --password='secret' -e "use $i; show table status;"; done | awk '{print $1" "$2}' | column -t

Leave a Reply

%d bloggers like this: