MySQL – Resolving error 1153 when exporting database

When it comes to importing & exporting MySQL database using command-line, we use to think that it’s pretty hard to do. Reason is because of GUI (graphical user interface), where most of us use to interact all the time but not the command line. I’d say it is slightly complicated to remember the commands and execute it one by one one rather than just hitting on a button in GUI. Nevertheless practically in real-time, commands plays the major role transparently whilst whatever we execute in GUI. I couldn’t think of a good example than Filezilla, an FTP program. Because Filezilla actually displays its users to see what commands are sent/received through while executing each buttons via GUI.

Having that said; we’ll move on to what we were discussing about. Well, I’ve been assigned to a task where I needed to dump an MySQL database by importing from one place & exporting the same into another. At first I thought it will be pretty easy as I just need to backup and restore it into the new place. Guess I was wrong.!!!

The database itself is 9GB & I cannot imagine how much texts that are stuffed into this. So I had to do few searches and reads to make this a success without loosing any of the dumped DATA(s). For that I will need to depend on command lines rather than using a GUI. Major reason is that I’m about to dump a database from an online server to a local server. Therefore, I need to make sure that the connection is stable and I can work without any interruptions. Well, obviously; everyone knows dumping into a local established server makes it faster comparing to online due to the internet speed being placed. So to speak, I manage to dump the database using command line by SSH’ing to my VPS server using below command.

mysqldump -u root -p database_name > dumping_database_name.sql

Mysqldump is the default and powerful command which does its work perfectly without any interruption. Below is an explanation about the command which will educate you more; (refer this document to learn more)

• mysqldump – command which will dump the said database to the said destination.
• u – defines the database user (you will need to give the user who has full privilege to the said database).
• p – defines the database user password (I prefer not to mention the password as it will prompt to type while executing).
• database_name – defines the database name which we will need to dump.
• > – defines as append. it will print whatever the information from on to another (in this case, from left database_name to right dumping_database_name.sql).
• dumping_database_name.sql – this is the dump file where the database will be dumped to.

Once done, I downloaded the dump file to my local computer and tried exporting it to the localhost sql server using the same command but slight modification where the append faced the opposite. Because its an upload, not a download.

mysqldump -u root -p database_name < dumping_database_name.sql

It wall went got until an error message appears. Dumping stopped at a certain table returning continuous errors. I tried various ways to dump the database such as using MySQL dumper, Splitting MySQL database tables… etc which didn’t help either. All these methods helped me to backup from the original source (online server) but not to restore into the local database (local pc). I had to say; this gave me a good experience about learning and understanding the database structures. Somehow, I managed to find out which tables were causing problems and the errors. Below are the errors which gave me while dumping;

ERROR 2006 (HY000) at line 5855099: MySQL server has gone away
ERROR 1153 (08S01) at line 4215: Got a packet bigger than
'max_allowed_packet' bytes

So to speak, “max_allowed_packet” was the problem which was causing while dumping the database. Anyhow, few more hours of search lead me to link which heavenly worked like a charm exporting the whole database without any errors. And this is how I achieved it;

• Logged into the mysql server using command “mysql -u root -p” & entered the specified database user password.
• Once logged in, I issued below commands;

set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;

• Thereafter, I opened a new command prompt whilst leaving the existing one, then typed below to execute

mysql --max_allowed_packet=100M -u root -p local_database_name < downloaded_database_dump.sql

It worked like a charm!! What this does is that it will use a large packet size for dumping which gives us more time and space to dump. Hope this helps!

I’ll come back with another useful article soon… Until then adios! 🙂