How to make an SQL file smaller

Why is the file so big?

A database is where your site keeps its data. An SQL file is a saved copy of it, written as text. Big databases make big files. Many tools, such as phpMyAdmin, only accept files up to a limit. A smaller file also uploads faster.

Way 1: compress the file

Compressing squeezes a file, like packing clothes in a vacuum bag. SQL text shrinks a lot, often to a small part of the original size.

On Windows:

1. Right-click the .sql file.
2. Choose Send to, then Compressed (zipped) folder.

On a Linux server, with SSH:

gzip backup.sql

This turns backup.sql into backup.sql.gz. phpMyAdmin can import .zip and .gz files directly.

Way 2: export it compressed in the first place

1. In phpMyAdmin, choose your database and click the Export tab.
2. Choose Custom as the export method.
3. Find Compression and choose gzipped or zipped.
4. Click Go.

With SSH you can do it in one command:

mysqldump -u username -p dbname | gzip > backup.sql.gz

This exports the database and compresses it as it goes.

Way 3: leave out data you do not need

Large tables often hold junk, like old logs, spam comments, or cache data. Clean these first.

1. Take a full backup first.
2. Find the biggest tables. In phpMyAdmin, click the database and sort by the Size column.
3. Empty tables you do not need, such as cache or log tables.
4. In WordPress, delete old post revisions and spam comments. A cleanup plugin can help.
5. Export again.

Warning: Deleting data cannot be undone. Only remove tables you are sure about. Check with the app's guide first.

Way 4: split the file

Export the database in parts, one table or a few tables at a time. In the Custom export, untick tables you do not want in this part. Import the parts one by one.

Still too large? Open a support ticket.

Quick recap

• Zip or gzip the file for the biggest saving.
• Export compressed with the Custom option.
• Clean out logs, cache and spam first, after a backup.
• Split big exports into parts.