Forum Stats

  • 3,780,534 Users
  • 2,254,407 Discussions
  • 7,879,374 Comments

Discussions

How do you find out the size of a database on mysql v 4.1 without information_schema?

3363898
3363898 Member Posts: 23
edited Dec 16, 2016 10:25AM in MySQL Community Space

Hi,

Is there a way to find the database size on MySQL v4.1 without information_schema? Getting the same result as the following query without using information_schema:

mysql> SELECT ENGINE, COUNT(*), SUM(DATA_LENGTH), SUM(INDEX_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA='TABLE_SCHEMA_NAME' GROUP BY ENGINE;

Thank you.

Best Answer

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Dec 15, 2016 3:53PM Accepted Answer

    I see. Follow the steps below.

    We can determine the file that your database is using by seeing what's referenced in your my.cnf file.

    [[email protected] mysql]$ locate -n 4 my.cnf

    /etc/my.cnf

    [[email protected] mysql]$ cat /etc/my.cnf

    [mysqld]

    The location below is the real location of your ibdata1 file. The other one might be a backup.

    datadir=/var/lib/mysql

    socket=/var/lib/mysql/mysql.sock

    user=mysql

    # Disabling symbolic-links is recommended to prevent assorted security risks

    symbolic-links=0

    [mysqld_safe]

    log-error=/var/log/mysqld.log

    pid-file=/var/run/mysqld/mysqld.pid

    [[email protected] mysql]$

    Once you get the real location. Cd into that location and run the query below:

    ls -lrth
«1

Answers

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Dec 15, 2016 3:18PM

    Are you on Windows or Linux?

  • 3363898
    3363898 Member Posts: 23
    edited Dec 15, 2016 3:30PM

    I am on Linux.

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Dec 15, 2016 3:42PM

    If you're using Innodb, you can alternatively find the size by locating your MySQL store data file: ibdata1 and optionally, you might also have ibdata2.

    Locate those files and you can get the size of your database.

    Linux:

    Run the command below to find it:

    Locate -n 10 ibdata1  or

    Locate -n 10 ibdata2

    The file should be located here:

    /var/lib/mysql

    Make sure that your database is running. Run the command below while using root or sudo priv.

    $ service mysqld status

    If it's running, then cd into:

    $ cd /var/lib/mysql

    Then run:

    ls -lrth

    The size of the ibdata1 should give you the size.

  • 3363898
    3363898 Member Posts: 23
    edited Dec 15, 2016 3:45PM

    I get 2 results:

    mysql> locate -n 10 ibdata1

    /var/lib/mysql/data/ibdata1

    /var/lib/mysql/ibdata1

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Dec 15, 2016 3:53PM Accepted Answer

    I see. Follow the steps below.

    We can determine the file that your database is using by seeing what's referenced in your my.cnf file.

    [[email protected] mysql]$ locate -n 4 my.cnf

    /etc/my.cnf

    [[email protected] mysql]$ cat /etc/my.cnf

    [mysqld]

    The location below is the real location of your ibdata1 file. The other one might be a backup.

    datadir=/var/lib/mysql

    socket=/var/lib/mysql/mysql.sock

    user=mysql

    # Disabling symbolic-links is recommended to prevent assorted security risks

    symbolic-links=0

    [mysqld_safe]

    log-error=/var/log/mysqld.log

    pid-file=/var/run/mysqld/mysqld.pid

    [[email protected] mysql]$

    Once you get the real location. Cd into that location and run the query below:

    ls -lrth
  • 3363898
    3363898 Member Posts: 23
    edited Dec 15, 2016 4:48PM

    After running the command: ls -lrth i got the following result:

    mysql>  ls -lrth

    total 21M

    ...

    -rw-rw----  1 mysql mysql 2.6K Oct 30  2014 filename.index

    -rw-rw----  1 mysql mysql 5.0M May 22  2016 ib_logfile0

    -rw-rw----  1 mysql mysql  10M May 22  2016 ibdata1

    So that means that total DB size is 10M, right?

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Dec 15, 2016 4:56PM

    Great. Yes.

    I'll still suggest your run the script version as well. Can you run this and post back the result.

    SELECT sum(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) "Size in MB" FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = "TABLE_SCHEMA_NAME" GROUP BY ENGINE;

  • 3363898
    3363898 Member Posts: 23
    edited Dec 15, 2016 5:00PM

    Thanks, here is the result:

    mysql> SELECT sum(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) "Size in MB" FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = "TABLE_SCHEMA_NAME" GROUP BY ENGINE;

    ERROR 1146 (42S02): Table 'INFORMATION_SCHEMA.TABLES' doesn't exist

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Dec 16, 2016 8:17AM

    Are you sure that your server have information_schema?

    Can you run the command below and check to make sure you have the INFORMATION_SCHEMA.

    Then run the query below in this forum:

    MySQL :: GET THE DATABASE SIZE FROM THE MYSQL QUERY BROWSER

  • 3363898
    3363898 Member Posts: 23
    edited Dec 16, 2016 10:03AM

    Hi,

    I don't see the INFORMATION_SCHEMA database when i run the command:

    mysql> SHOW databases;

    And also when i run:

    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 ; 

    I get the following error message:

    ERROR 1146 (42S02): Table 'information_schema.TABLES' doesn't exist

    So i believe that INFORMATION_SCHEMA doesn't exist...