How to fix ERROR 1185: Storage engine doesn't support binary dump in MySQL

Run this query to see what engine your table uses:

SHOW TABLE STATUS WHERE Name = 'your_table_name';

Look at the Engine column. Common values: InnoDB, MyISAM, MEMORY, ARCHIVE, MERGE, FEDERATED.

For all tables in your database:

SHOW TABLE STATUS;

The recommended solution is to use mysqldump, which works with all storage engines:

mysqldump -u username -p database_name > backup.sql

For a specific table:

mysqldump -u username -p database_name table_name > table_backup.sql

This creates a logical SQL dump that can be restored to any MySQL instance, regardless of storage engine.

If you specifically need binary dump support, convert your table to InnoDB:

ALTER TABLE table_name ENGINE=InnoDB;

Verify the change:

SHOW TABLE STATUS WHERE Name = 'table_name';

Note: This can be slow on large tables. Plan the conversion during low-traffic periods.

For production environments:

For InnoDB tables:
- MySQL Enterprise Backup (official)
- Percona XtraBackup (open source, highly recommended)
- mysqldump with --single-transaction for consistency

For mixed storage engines:
- mysqldump (works with everything)
- Logical backups via your application layer

Example with mysqldump and gzip compression:

mysqldump -u username -p --all-databases | gzip > full_backup.sql.gz