Script to find the table with missing primary key in MySQL

Sometimes a primary key may be missing from an earlier table, which can easily lead to a slow query or master-slave replication.

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

#!/bin/bash

# Find a table without a primary key

# 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_sche MA = ' $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

This article is from the "Vegetable Chicken" blog, please be sure to keep this source http://lee90.blog.51cto.com/10414478/1932495

Script to find the table with missing primary key in MySQL