Friday, April 27, 2012

MySQL Tips

1) Restore DB
a) mysql --user=root --password=root maheshsql_db < "C:\mydbbkp.sql"


2) MYSQL Get all table rows and size of each table in DB
a) SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
b) SELECT TABLE_NAME, table_rows, data_length, index_length, 
round
(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "schema_name";
c) SELECT SUM(round(((data_length + index_length) / 1024 / 1024),2)) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "schema_name";


3) Count no of tables in DB
a) SELECT count(*) FROM information_schema.tables WHERE table_schema  = "schema_name";


4)  Can not load from mysql.proc. The table is probably corrupted
a) Run this Command
           a.1) mysql_upgrade 
                 OR
           a.1) mysql_upgrade --user=my_username --password=my_password

5) ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10061)
        step 1) Open your my.cnf file OR my.ini   file in mysql/bin folder
         step 2) Comment the bind-address
              #bind-address="127.0.0.1"