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