Admin Linux ... MySQL DBA

Verify 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 = 'forum'; 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

Few MySQL Tips: a. calculate databases size : -> It will show database size SELECT s.schema_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in("mysql","information_schema","test") GROUP BY s.schema_name order by Data_size DESC; -> It will show database size along with table size. SELECT s.schema_name,t.table_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") data_size,CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") index_size, t.ENGINE ENGINE, t.table_rows TABLE_ROWS,t.row_format TABLE_ROW_FORMAT,date(t.update_time) FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema

Some time we have requirement that, particular job should stop for certain period of time and start again. Most of us familiar with KILL command, but here is another feature of KILL command , which saves your life :- #kill -STOP 10067 ( where 10067 is process id ) #kill -CONT 10067 ( where 10067 is process id ) have a fun :)