The script finds tables in mysql that are missing primary keys

Sometimes early built tables may lack primary keys, which can easily lead to queries or slow master-slave replication.

Here is a small script to find a table without a primary key.

#! / bin/bash

# find tables without primary keys

# Date: 2017-06-05

Source / etc/profile

LOG= "/ tmp/nopk.log_$ (date +% F)"

User='root'

Host='localhost'

Pass='123456'

Sock='/tmp/mysql.sock'

MYSQL_CMD= "mysql-u$user-h$host-p$pass-S$sock"

Dbs=$ ($MYSQL_CMD 2 > / dev/null-BNe "select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME not in ('information_schema','performance_schema')")

For db in $dbs; do

$MYSQL_CMD information_schema 2 > / dev/null-NBe "select distinct TABLE_SCHEMA,table_name from columns where TABLE_SCHEMA ='$db' and table_name not in (select distinct table_name from COLUMNS where TABLE_SCHEMA ='$db' and (column_key = 'PRI' or column_key =' UNI'))" | tee-a $LOG

Done