Error while importing large .sql file into MySQL

Question

I am trying to import my backup .sql file into MySQL. I've tried using phpmyadmin, but that didn't allow my large sized file.

My file size is 243 MB. I've made changes in php.ini. The problem is out of 1.1M records, its importing only 600k records. I've tried with MySQL console also using command. Same thing happened with that also. I am using MySQL Workbench, and it showed me below error.

ERROR at line 695259: Unknown command '\a'.
Finished executing script
Operation failed with exitcode 1 

What does this mean? How do I import my full data?

Answer 1

It seems dump was not properly created. There are few errors in you sql file.

Answer 2

The presence of the <br/> tag at the tail of your dump file indicates that you have probably used phpmyadmin to create it and the the page timed out while the dump was in progress. Thus the error is not in your import process but in your dump process.

How can this be fixed? A global solution is to increase the php script execution time with set_time_limit an alternative is to use the mysql console client to create the dump by passing phpmyadmin.

A somewhat tedius but workable solution is to dump one table at a time. This doesn't always work (for example if one table makes up most of the size of the database it might still cause a timeout)