MySQL Data File Size Decrease

If ibdata1 data file size is too big, you can decrease this file. My scenario is that my database have a table and size is 100 GB, I want to take backup and delete all data with decrease datafile size. If I use truncate command, this is not decrease ibdata1 size. I can use backup and drop scenario. I am using MySQL 5.x on Windows Server.


1-Take Backup With Data


Open Command Prompt (cmd)
> mysqldump.exe -u root -p db_name > E:\db_backup.sql

2-Take Metadata Backup


Open Command Prompt
> mysqldump.exe -u root -p –no-data db_name > E:\db_backupmetadata.sql
or
> mysqldump.exe -u root -p -d db_name > E:\db_backupmetadata.sql

3-Drop Database


Open MySQL Client Console
mysql> show databases;
mysql> drop database db_name;

4-Stop MySQL Service


Open Services.msc
MySQL servise has to do stop

5-Delete Data Files


Open Data Directory
You can delete ibdata1, ib_logfile0, ib_logfile1 … start with ib..
This files will be recreated as automatic by MySQL service when MySQL service start

6-Start MySQL Service


Open Services.msc
MySQL servise has to do start

7-Create Database


Open MySQL Client Console
mysql> create database db_name;

8-Restore Metadata From Backup


Open Command Prompt (cmd)
> mysql.exe -u root -p db_name < E:\db_backupmetadata.sql