MySQL blocked because of Too Many connections

MySQL server will block connections in case of too many connections. After solving the problem creating too many connections from web server, in some cases still we can see that MySQL blocks the webhosts.

We had to run the following command in mysql server, at the command prompt:

mysqladmin flush-hosts -uroot -pxxxx

To display all users Grants

To display all users as "show grants for..."

mysql -Bse "SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user" | mysql -Bs | sed 's/$/;/g'

MySQL Tips to Verify database Objects

Verifing database Objects:

1. Verify table.
a. SHOW TABLE STATUS LIKE '%user%'\G
b. SHOW TABLE STATUS from forum LIKE '%user%' \G

2. It will show you all tables have MyISAM engine.
a. SELECT TABLE_SCHEMA, table_name, table_type, engine From information_schema.tables where engine='MyISAM';

3. To know rows per table, you can use a query like this:
a. SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'xyz';

4. Verify you procedure / function.
a. SHOW procedure/FUNCTION STATUS LIKE ´hello´G
b. show create procedure/function hello\G

5. Routines
a. select routine_name from information_schema.routines; // list of all the routines in the system
b. select routine_name, routine_schema, routine_type from information_schema.routines; // This lists all of the routines in the system. With additional Information like the database the routines belongs too and also distinct between the different routines.
c. select routine_name, routine_schema, routine_type from information_schema.routines where routine_schema = database() // This time we can see just the routines for the currently selected database.


6. Triggers
a. select TRIGGER_SCHEMA,TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS;
b. select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = database();