use Google :) On Wed, Aug 11, 2010 at 10:02 PM, PRATIKSHA JAISWAL < pratikshadjayswa...@gmail.com> wrote:
> Hi Prabhat, > > Thanks a ton for sharing your blogs with us. > > I am learning mysql database. I am very good at PostgreSQL. > > Do you have any other material where in i can go for Mysql Certification > and > mainly mysql replication set up material etc.. > > > -- > Thanks > Pratiksha > > > On Wed, Aug 11, 2010 at 5:39 PM, Prabhat Kumar <aim.prab...@gmail.com>wrote: > >> check this, >> >> >> http://adminlinux.blogspot.com/2009/12/mysql-tips-calculate-database-and-table.html >> >> Thx >> >> >> On Wed, Aug 11, 2010 at 5:23 PM, PRATIKSHA JAISWAL < >> pratikshadjayswa...@gmail.com> wrote: >> >>> Hi, >>> >>> Thanks all for your help. >>> >>> >> >>> ---Database & Table wise Size in MB--- >>> SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', >>> CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / >>> 1024),2)," >>> Mb") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = >>> "dbname"; >>> >>> >>> >>---Database & Table wise Size in GB--- >>> SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', >>> CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / >>> 1024),3)," Gb") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA >>> = >>> "dbname"; >>> >>> >>> >>> Result shows the EMPTY SET. >>> >>> >>> >>> >>> On Tue, Aug 10, 2010 at 10:20 AM, Anirudh Sundar >>> <sundar.anir...@gmail.com>wrote: >>> >>> > Hello Pratiksha, >>> > >>> > To get the uptime of the MYSQL instance :- >>> > >>> > mysql>\s >>> > >>> > as given above just give the above command " \s " >>> > >>> > For Total number of users connected to server :- >>> > >>> > mysql>show global status like '%user%'; >>> > >>> > ---Database & Table wise Size in MB--- >>> > SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', >>> > CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / >>> 1024),2)," >>> > Mb") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = >>> "dbname"; >>> > >>> > ---Database & Table wise Size in GB--- >>> > SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', >>> > CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / >>> > 1024),3)," Gb") AS Size FROM INFORMATION_SCHEMA.TABLES where >>> TABLE_SCHEMA = >>> > "dbname"; >>> > >>> > ****Data size, index size & no. of tables, engine type********* >>> > SELECT s.schema_name,t.engine, >>> > 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,t.engine >>> > order by Data_size DESC; >>> > >>> > For top slow queries :- >>> > >>> > mysqldumpslow -s c -a -t 5 mysqlslow.log > top10_slow-count_envr.log >>> > >>> > For Engine Info of a table and other details :- >>> > >>> > Show table status like 'tablename'; >>> > >>> > Cheers, >>> > Anirudh Sundar >>> > >>> > On Mon, Aug 9, 2010 at 9:31 PM, PRATIKSHA JAISWAL < >>> > pratikshadjayswa...@gmail.com> wrote: >>> > >>> >> Hi All, >>> >> >>> >> How can i get following information from database or is there any >>> query >>> >> for >>> >> the same. >>> >> >>> >> (1) mysql server uptime >>> >> (2) Total number of users connected to server >>> >> (3) Data file information / where it is located through mysql prompt / >>> >> size >>> >> of data file >>> >> (4) each Database size >>> >> (5) Database I/O information >>> >> (6) Invalid object in database >>> >> (7) Database performance statistics queries >>> >> (8) Top 5 queries taking more time for executions. >>> >> (9) Engine information. >>> >> >>> >> >>> >> -- >>> >> Thanks >>> >> Pratiksha >>> >> >>> > >>> > >>> >> >> >> >> -- >> Best Regards, >> >> Prabhat Kumar >> MySQL DBA >> >> My Blog: http://adminlinux.blogspot.com >> My LinkedIn: http://www.linkedin.com/in/profileprabhat >> > > -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
