Как узнать размер базы данных mysql

Sorry, you have been blocked

This website is using a security service to protect itself from online attacks. The action you just performed triggered the security solution. There are several actions that could trigger this block including submitting a certain word or phrase, a SQL command or malformed data.

What can I do to resolve this?

You can email the site owner to let them know you were blocked. Please include what you were doing when this page came up and the Cloudflare Ray ID found at the bottom of this page.

Cloudflare Ray ID: 7a750f03eebd77b9 • Your IP: Click to reveal 88.135.219.175 • Performance & security by Cloudflare

How to Check MySQL Database and Table Size

MySQL provides valuable metadata about databases and tables. For example, if a database’s system memory runs out, checking the database or table size helps identify where the storage is particularly overwhelmed.

This article provides three methods to check the size for all MySQL databases, a single database, or a table within a database.

• A MySQL database with populated tables.
• Access to the command line/terminal. or MySQL workbench installed and configured (optional).

Note: For storage-heavy workloads and overwhelmed databases, we recommend you deploy a memory-optimized Bare Metal Cloud server. The server instance comes with up to 768GB of RAM and scalable processors to ensure stability during peak times.

How to Check MySQL Database and Table Size

There are three ways to check MySQL database and table sizes:

1. Using phpMyAdmin.

2. Using the SELECT statement.

3. Using MySQL workbench.

All methods provide ways to check the size for:

• A single database.
• All databases.
• Table size for a single database.
• Table size for all databases.

Choose a method that best fits your configuration and follow the step-by-step instructions below.

Method 1: Using phpMyAdmin

Use the phpMyAdmin web interface to access information about MySQL databases and tables, including their sizes. Start by logging into your phpMyAdmin administration page.

Get the Size for a Single Database

Follow these steps to check the size for a single database using phpMyAdmin:

1. Select the database name on the left pane.

2. Locate the Size column. The individual table sizes display in the output.

Scroll to the bottom to see the total size for the selected database, which is the total sum.

Get the Size for all Databases

To find the size of all databases in phpMyAdmin:

1. On the index page, locate and select the Databases tab in the top navbar.

2. Below the table, select Enable statistics.

Enabling statistics can cause high traffic between MySQL and web servers. Use this method when there’s low traffic to minimize server overload.

3. The Database statistics page displays general statistics about all the databases, including the size. The column Total sums the Data and Indexes columns.

The last row shows the sizes summed up.

Get Table Size for a Single Database

To check the size for a single database table:

1. Click a database name in the left pane to select a database.

2. Use the search bar to filter tables by name.

Alternatively, locate the table manually in the list below the search bar.

3. Find the Size column and check the table size.

If not immediately visible, scroll the table to the right until the column is visible.

Get Table Size for All Databases

Check all database table sizes in phpMyAdmin using the SELECT query.

1. On the index page, select the SQL tab.

2. Enter the following query to display the table size for all databases:

Divide twice by 1024 to get the size in MB and once in KB. Press Go to run the query and fetch the result.

3. The output shows the table sizes for all databases in the last column.

4. Drag and drop the columns to manage the order, or click on the column name to sort the output.

Method 2: Using the SELECT MySQL Command-Line Statement

The database and tables sizes are available through the MySQL command-line interface.

1. Open the terminal (CTRL+ALT+T) and start the MySQL monitor with:

2. Type the password when prompted and press Enter. The terminal shows the mysql> prompt.

Note: If you’re getting an access denied error, follow our troubleshooting guide: Access denied for user [email protected] .

Below are several example queries using the SELECT statement. All the outputs show the size in MB with two decimal places.

Get the Size for a Single Database

Use the SELECT statement to get the size of a single database:

Change the <database name> to the database you’d like to check.

Note: Use SHOW DATABASES; to list all the databases and find the exact name. For other useful MySQL commands, grab our free MySQL cheat sheet.

Get the Size for all Databases

View the size of all databases with the following query:

The output shows database sizes sorted in descending order in MB.

Get Table Size for a Single Database

List table sizes for a single database by using:

Exchange <database name> for the actual database name. The output sorts the tables by size in descending order in MB.

Get Table Size for All Databases

Show table sizes for all databases with:

The query sorts the output by database size. Change the last line to sort by a different parameter.

Method 3: Using MySQL Workbench

The SELECT query methods work equally well for MySQL Workbench. However, the program provides two additional ways to quickly check the size of a single database and the tables inside a database.

Start by opening MySQL Workbench and establish a connection.

Get the Size for a Single Database

1. To get the size of a single database in MySQL Workbench, right-click on the schema you want to check in the left navigation pane.

2. Select Schema Inspector from the list. On the right pane, under the Index tab, check the database size.

The size is a rough estimate displayed in binary bytes.

Get Table Size for a Single Database

To get the size of a table of a specific database:

1. Open the Schema Inspector for the database where the table(s) reside.

2. Navigate to the Tables tab.

3. Check the size of a specific table in the Data Length column or add them together to get the total amount. Alternatively, use the SELECT statement in a query.

Table Size Limits

MyISAM tables have a default limit set to 256TB for data and index files, which you can change to 65,536TB maximum.

InnoDB maximum size for tables is 256TB, which corresponds to the full tablespace size. Generally, the best practice is to divide an InnoDB table into multiple tablespaces.

Note: Check out our detailed comparison: MyISAM vs. InnoDB

Depending on the specifications, some operating systems limit the file size. Although MySQL has some table size limits, a full table error is most likely due to the operating system. Alternatively, the storage where the database resides is possibly at full capacity.

You should now know several methods to get database and table sizes after following this tutorial. Next, read how to optimize MySQL tables and other get other MySQL Performance Tuning and Optimization Tips.

Размер базы данных MySQL

В наше время базы данных используются практически везде, я уже не говорю про крупные компании и команды разработчиков приложений, даже веб-мастера, которые делают свои сайты используют базы данных для хранения динамически изменяющейся информации. Обычно это одна из самых популярных баз данных — MariaDB или MySQL.

Но сколько занимает ваша база данных? А каждая таблица? Для больших проектов эти показатели могут достигать очень больших значений. В сегодняшней статье мы поговорим о том, как узнать размер базы данных MySQL через консоль и другими способами.

Размер базы данных MySQL

Я предполагаю, что ваша база данных установлена в Linux, но большинство методов будут работать и для других операционных систем. Самый простой способ прикинуть сколько места занимают все базы данных — это посмотреть размер папки с файлами базы данных в системе — /var/lib/mysql:

du -h /var/lib/mysql

Утилита позволяет узнать размер базы Mysql в мегабайтах. Но будет гораздо более правильно смотреть информацию о базе данных с помощью ее встроенных инструментов. Для в MySQL есть специальная таблица — Information_schema. Из нее мы и будем брать данные. Но сначала подключитесь к базе данных:

SELECT table_schema AS «Имя базы данных», ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS «Размер в Мб» FROM information_schema.TABLES GROUP BY table_schema;

Запрос выведет точный размер базы MySQL для каждой из существующих баз, теперь вы можете ориентироваться что и сколько занимает. Если у вас очень большой проект и большая база данных, то вы можете выводить информацию в гигабайтах:

SELECT table_schema AS «Имя базы данных», ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS «Размер в Гб» FROM information_schema.TABLES GROUP BY table_schema;

Но это еще не все. Возможно, в базе данных есть ненужная информация, которая только замедляет работу системы. Мы можем посмотреть размер таблиц для каждой из баз данных. Запрос для этого будет выглядеть следующим образом:

SELECT table_name AS «Имя таблицы», ROUND(((data_length + index_length) / 1024 / 1024), 2) AS «Size in (MB)» FROM information_schema.TABLES WHERE table_schema = «wpfc_options» ORDER BY (data_length + index_length) DESC;

Данная информация помогает понять какая таблица сколько занимает и это уже может навести на интересные мысли.

Размер базы данных в Phpmyadmin

Если вы не очень любите работать в терминале, то вы всегда можете использовать Phpmyadmin для просмотра размера базы данных. Размер каждой из таблиц базы данных вы можете посмотреть на вкладке «Структура», когда выберите базу данных для работы:

Что касается размера самой базы данных, то придется выполнить тот же запрос в интерфейсе программы на вкладке «SQL»:

Выводы

В этой небольшой статье мы рассмотрели как узнать размер базы данных MySQL несколькими способами. Если у вас остались вопросы, спрашивайте в комментариях!

How to Check the Size of a Database in MySQL

In MySQL, you can query the information_schema.tables table to return information about the tables in a database. This table includes information about the data length, index length, as well as other details such as collation, creation time, etc. You can use the information in this table to find the size of a given database or all databases on the server.

You can also use the MySQL Workbench GUI to find details about the database (including its size).

This article provides a quick overview of both methods.

Code Example

Here’s an example of finding the size of each database by running a query against the information_schema.tables table:

In this example I’ve listed the size in bytes and in mebibytes (MiB), but you can choose how you want to present it.

Of course, you can always narrow it down to a specific database if you need to. Simply add a WHERE clause with the name of the database:

The FORMAT_BYTES() Function

You can use the FORMAT_BYTES() function to save yourself converting the size into mebibytes, kibibytes, or whatever. This function takes a value, converts it to human-readable format and returns a string consisting of a value and a units indicator. The converted value will depend on the size of the value (so the result could be in bytes , KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), PiB (pebibytes), or EiB (exbibytes).

Here’s an example of rewriting the previous example to use the FORMAT_BYTES() function:

MySQL Workbench

Another way of finding the database size is to use the MySQL Workbench GUI. Here’s how: