DB admins usually fill in DB tables or columns with unnecessary data.
The easier and the hardest way to reduce MySQL size is by deleting all the unwanted data. | database_name | table_name | index_size | SELECT table_schema as database_name,table_name,round(index_length/1024/1024,2) as index_sizeĪnd table_schema not in ('information_schema', 'sys','performance_schema', 'mysql') Query below returns index size ordered from the ones using the most. LIMIT 5 - adjust it according to your needs ORDER BY (data_length + index_length) DESC SELECT table_schema as `Database`,table_name AS `Table`,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` Query below returns the size of each Table in MB. MariaDB > SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" Query below returns the size of each Database in MB. Use the following queries to monitor and evaluation table and index size. $ mariabackup -backup -target-dir=/var/mariadb/backup/ -user=myuser -password=mypasswordĬheck more MySQL commands on MySQL Cheat Sheet.
#MYSQL SHOW DATABASES WITH SIZE FULL#
If you are using MariaDB, you have the option of full or incremental backup via mariabackup utility. Mysqldump -all-databases -single-transaction -quick -lock-tables=false > full-backup-$(date +%F).sql -u root -p MySQL and MariaDB include the mysqldump utility to simplify the process to create a backup of a database or system of databases. Backup, first but not leastīest practices suggest to backup your database before take any dangerous action. This article provides easy tips to reduce the size of MySQL database. The bigger the database the more it costs for support and maintenance. Sooner or later all database administrators face up the problem of huge database disk usage.
0 kommentar(er)
