How to Import Large Databases Into MySQL

Recently, I work on a woo-commerce site that stores a large database. Since I need to audit the site and do some customization on site. I want to migrate the site to my local host.

What I do, I export the database(SQL file) via PHPMyAdmin from the cPanel and try to import the database(SQL file) via PHPMyAdmin on my localhost.

Problems

• Import isn’t complete and shows the error message on the PHPMyAdmin. The error can be “Unrecognized keyword.”
• Some tables don’t have the primary key and foreign keys. You will see the error message when you are trying to update the site settings, plugin settings or theme settings. The error will say about the duplicate key on the specific table. In my case, it was a wp_options table. So navigate to that table and check the table structure. You will see no primary key and foreign key are set.
• Some tables have the primary key as zero value. You will see the error message when you are trying to update the page content, post content or other post type content. When you check the related table, you will see the field value of the primary key is zero.

Solutions

• Import data using MySQL Workbench
  • Before importing the data, you must change the “max_allowed_packet” value in my.ini (or my.cnf). You should change the value that is enough for importing the large database such as 1024M. After changing the value, you MUST restart your web server to effect the changes.
• Import data using PHPMyADMIN
  • Before importing the data, you must change the “max_allowed_packet” value in my.ini (or my.cnf). You should change the value that is enough for importing the large database such as 1024M. After changing the value, you MUST restart your web server to see the changes.
  • Apart from edit the my.ini, you will change the PHP value in php.ini as well. So change the PHP value as below. You can add the bigger value if it needs. After changing the PHP value, you MUST restart your web server to see the changes.

max_execution_time = 600
max_input_time = 600
memory_limit = 1024M
post_max_size = 1024M

That’s it. You should be able to import the large database into your localhost now and Merry Christmas everyone!