Php mysql database size

PHP + MiSqli узнать размер базы данных

Часто при разработке приложений для взаимодействия с базой данных MySql возникает необходимость опепративно узнать её размер не через интерфейс админки, а средствами скрипта на php что бы в случае превышения лимита выполнить необходимые деёствия на сайте или сигнализировать администратору.

Здесь я привожу небольшой класс для работы с базой данных через функционал php mysqli который позволит соединится с базой данных, узнать её размер в байтах и отформатировать результат для удобочитаемости в Кб, Мб, Гб.

class bazaInfo< public function connect()< $db_host = 'localhost'; $db_user = 'LOGIN'; $db_password = 'PASS'; $db_name = 'BAZA NAME'; $link = new MySQLi($db_host, $db_user, $db_password, $db_name); if ($link->connect_error) < die('
'.$link->connect_errno.' - '.$link->connect_error.'
'); > $link->query("SET NAMES 'cp1251'"); return $link; //echo "
Вы подключились к MySQL!
"; > public function formatfilesize($data) < // bytes if( $data < 1024 ) < return $data . " bytes"; >// kilobytes else if( $data < 1024000 ) < return round( ( $data / 1024 ), 1 ) . "k"; >// megabytes else < return round( ( $data / 1024000 ), 1 ) . " MB"; >> public function bazaSize()< $mysqli=self::connect(); $sql = "SHOW TABLE STATUS"; $result = $mysqli->query($sql); $dbsize = 0; while($R = mysqli_fetch_object($result)) < $dbsize += $R->Data_length + $R->Index_length; > return self::formatfilesize($dbsize); > > // Выводим данные $bz=new bazaInfo; $size = $bz->bazaSize(); echo $size;

Функция connect() устанавливает соединение с базой данных и оперделяет кодировку.

Функция formatfilesize( $data ) форматирует числовое представление размера базы данных.

И ключевая функция bazaSize() собственно вычисляет и возвращает размер базы данных, с помощью внутренних вызовов пердыдущих функций осуществляет соединение и форматирование результата.

Подсчёт размера базы данных осуществляется суммарным значением веса каждой таблицы + индексной составляющей.

Следующий код более универсален и позволяет:

Узнать размер базы данных в одном запросе:

class bazaInfo< public function connect()< $db_host = 'localhost'; $db_user = 'LOGIN'; $db_password = 'PASS'; $db_name = 'BAZA NAME'; $link = new MySQLi($db_host, $db_user, $db_password, $db_name); if ($link->connect_error) < die('
'.$link->connect_errno.' - '.$link->connect_error.'
');> $link->query("SET NAMES 'utf8'"); $baza['link'] = $link; $baza['db_name'] = $db_name; return $baza; //echo "
Вы подключились к MySQL!
"; > public function bazaSize() < $baza=self::connect(); $mysqli=$baza['link']; $db_name = $baza['db_name'] $sql = "SELECT CONCAT(round(sum(data_length + index_length) / 1024 / 1024, 2), ' MB') AS size FROM information_schema.TABLES WHERE table_schema = '$db_name'"; // выполнение запроса и получение результата $result = mysqli_query($mysqli, $sql); $row = mysqli_fetch_assoc($result); // закрытие подключения к базе данных mysqli_close($mysqli); //отправляем результат return $row['size']; >> // Выводим данные $bz=new bazaInfo; $size = $bz->bazaSize(); echo echo 'Размер базы данных: '.$size.'Mb';

Дата публикации: 2020-05-01

Источник

How to get size of mysql database?

Solution 4: Alternatively you can directly jump into data directory and check for combined size of v3.myd, v3. How to display the size in megabytes entire database?

How to get size of mysql database?

How to get size of a mysql database?
Suppose the target database is called «v3».

Run this query and you’ll probably get what you’re looking for:

SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; 

This query comes from the mysql forums, where there are more comprehensive instructions available.

It can be determined by using following MySQL command

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema 

Database Size (MB) db1 11.75678253 db2 9.53125000 test 50.78547382 

Get result in GB

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema 

Alternatively, if you are using phpMyAdmin , you can take a look at the sum of the table sizes in the footer of your database structure tab. The actual database size may be slightly over this size, however it appears to be consistent with the table_schema method mentioned above.

Screen-shot :

Alternatively you can directly jump into data directory and check for combined size of v3.myd, v3. myi and v3. frm files (for myisam) or v3.idb & v3.frm (for innodb).

How to get size of mysql database?, It can be determined by using following MySQL command SELECT table_schema AS «Database», SUM (data_length + index_length) / 1024 / 1024 AS «Size (MB)» FROM information_schema.TABLES GROUP BY table_schema Result Database Size (MB) db1 11.75678253 db2 9.53125000 test 50.78547382 Get result in GB Code sampleSELECT table_schema «DB Name»,ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) «DB Size in MB»FROM information_schema.tablesGROUP BY table_schema;Feedback

MySQL : How to Check MySQL database and

MySQL Query database size

Is there a query or function that I can use to determine the Size of a Database in MySQL? If not what is the typical way to find the size of database in MySQL?

I was googling and found SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH — DATA_FREE) / 1024 / 1024),2)),» MB») AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like ‘%YOUR_DB_NAME%’ ;

And it returns a database that I know is 400MB to be 474989023196466.25 MB !

SELECT table_schema AS "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 AS "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ; 

Or with this, if you want to ROUND :

SELECT table_schema AS "Data Base Name", ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ; 

SELECT table_schema, sum(data_length + index_length) FROM information_schema.TABLES GROUP BY table_schema; 

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; 

Sql — MySQL Query database size, SELECT table_schema AS «Data Base Name», sum ( data_length + index_length ) / 1024 / 1024 AS «Data Base Size in MB» FROM information_schema.TABLES GROUP BY table_schema ; …

How to Get True Size of MySQL Database?

I would like to know how much space does my MySQL database use, in order to select a web host. I found the command SHOW TABLE STATUS LIKE ‘table_name’ so when I do the query, I get something like this:

Name | Rows | Avg. Row Length | Data_Length | Index Length ---------- ---- --------------- ----------- ------------ table_name 400 55 362000 66560 

So do I have 362000 or 400*362000 = 144800000 bytes of data for this table? And what does Index Length mean? Thanks !

From S. Prakash, found at the MySQL forum:

SELECT table_schema "database name", sum( data_length + index_length ) / 1024 / 1024 "database size in MB", sum( data_free )/ 1024 / 1024 "free space in MB" FROM information_schema.TABLES GROUP BY table_schema; 

Or in a single line for easier copy-pasting:

SELECT table_schema "database name", sum( data_length + index_length ) / 1024 / 1024 "database size in MB", sum( data_free )/ 1024 / 1024 "free space in MB" FROM information_schema.TABLES GROUP BY table_schema; 

You can get the size of your Mysql database by running the following command in Mysql client

SELECT sum(round(((data_length + index_length) / 1024 / 1024 / 1024), 2)) as "Size in GB" FROM information_schema.TABLES WHERE table_schema = "" 

If you use phpMyAdmin, it can tell you this information.

Just go to «Databases» (menu on top) and click «Enable Statistics».

You will see something like this:

This will probably lose some accuracy as the sizes go up, but it should be accurate enough for your purposes.

if you want to find it in MB do this

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; 

Size of database . PHP, I want to know the size of my database using php. How to display the size in megabytes entire database? Size in megabytes a specific request?

Size of database . PHP

I want to know the size of my database using php. How to display the size in megabytes entire database? Size in megabytes a specific request?

Try this to get the size in bytes:

mysql_select_db("yourdatabase"); $q = mysql_query("SHOW TABLE STATUS"); $size = 0; while($row = mysql_fetch_array($q))

then to convert in megabytes:

$decimals = 2; $mbytes = number_format($size/(1024*1024),$decimals); 

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema ; 

I try to modify from internet source for this case, just try it please.

This SQL give you useful data about your DB:

SELECT TABLE_SCHEMA AS DB_Name, count(TABLE_SCHEMA) AS Total_Tables, SUM(TABLE_ROWS) AS Total_Tables_Row, ROUND(sum(data_length + index_length)/1024/1024) AS "DB Size (MB)", ROUND(sum( data_free )/ 1024 / 1024) AS "Free Space (MB)" FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DATABASE NAME' GROUP BY TABLE_SCHEMA ; 

Sql — Query to determine the size of tables in a database?, Determine size of all tables in a database 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»; The first query works correctly, but the second query doesn’t …

Источник

How to get the size of a MySQL database using PHP

This is a simple tutorial that shows how to get the size of the MySQL database using php. This works in all latest PHP versions. We are using mysqli_connect function.

The query we are running to find the size of the database is SHOW TABLE STATUS
We are getting the size from Data_length and Index_length columns from the tables inside the database and adding them up.

All you have to do is update these lines in the main script.

1] Credentials to connect to database

$objConnect = mysqli_connect("localhost","","") or die("Error Connecting to Database, 
Known issues:Database may be down or need to contact admin");

2] Name of the database you would like to check the size

Complete code

Known issues:Database may be down or need to contact admin"); $dbname = 'mytestdatabase'; function getdbsize( $data ) < // bytes if( $data < 1024 ) < return $data . " bytes"; >// kilobytes else if( $data // megabytes else < return round( ( $data / 1024000 ), 2 ) . " MB"; >> mysqli_select_db($objConnect,$dbname); $result = mysqli_query($objConnect,"SHOW TABLE STATUS"); $dbsize = 0; while( $row = mysqli_fetch_array($result ) ) < $dbsize += $row[ "Data_length" ] + $row[ "Index_length" ]; >echo "The size of the database is ".getdbsize($dbsize)."
This is a size of a sample database we have"; ?>

In the demo we have a small database that we are using to show you the size. It is the same size that phpmyadmin report for the database we are connecting see the screenshot below

Related Tutorials:

Источник

Calculate MySQL database size with PHP (off the old shelf)

Calculate the MySQL database size in PHP. Sometimes you’d be amazed what you find when cleaning out your old script archives. I found an old PHP script to calculate the size of a MySQL database. I post it here just for the fun of it.

All this piece of PHP code needs is your MySQL database credentials.

 /** * Function to calculate the size, in bytes, of a MySQL database * Needs $dbhostname, $db, $dbusername, $dbpassword * - Jan Reilink @saotn.nl>, Twitter: @Jan_Reilink */ $dbhostname = "hostname"; $db = "database name"; $dbusername = "user name"; $dbpassword = "password"; $link = mysql_connect( $dbhostname,$dbusername,$dbpassword ); if( !$link ) < die( mysql_error() ); > $db_selected = mysql_select_db( $db, $link ); if ( !$db_selected ) < die( mysql_error() ); > function get_dbsize( $db ) < $query = "SHOW TABLE STATUS FROM `".$db."`"; if ( $result = mysql_query( $query ) ) < $tables = 0; $rows = 0; $size = 0; while ( $row = mysql_fetch_array( $result,MYSQL_ASSOC ) ) < $rows += $row["Rows"]; $size += $row["Data_length"]; $size += $row["Index_length"]; $tables++; > > $data[0] = $size; $data[1] = $tables; $data[2] = $rows; return $data; > $result = get_dbsize( $db ); $megabytes = $result[0] / 1024 / 1024; /* http://www.php.net/manual/en/function.number-format.php */ $megabytes = number_format( round( $megabytes, 3 ), 2, ',', '.' ); ?> Code language: PHP (php)

For MySQL database size, the array $data[] holds all the information in its keys:

Use with caution! And it shouldn’t be too difficult to rewrite this to MySQLi.

Calculate the MySQL database size by querying MySQL information_scheme database

You can use the following MySQL query, as a pure MySQL solution, to get the MySQL database size. It queries the information_scheme database:

SELECT SUM( data_length + index_length ) FROM information_schema.tables WHERE table_schema = '[db-name]';Code language: SQL (Structured Query Language) (sql)

Related Posts

• Convert PHP ext/mysql to MySQLi Code base
• PHP with WinCache on IIS Windows Server
• Connect to MS SQL Server with PHP 5.3+ Code base
• How to send authenticated SMTP over a TLS encrypted… Code base
• Huge increase in WordPress xmlrpc.php POST requests WordPress
• How to filter web traffic with blocklists Web application security

Источник