Recently I was attempting to import a large (136mb) SQL backup for a new client.<\/p>\n
I first created a database and db user in PHPMYADMIN (most current version).<\/p>\n
Then using my Windows workstation, I launched MySQL Workbench 6.1 (my favorite tool for importing\/exporting) and tried to do a Server–>Data Import.<\/p>\n
Pointed it to the 136mb file and first had a problem:<\/p>\n
17:45:03 Restoring Z:\\whateverdatabase.sql Running: mysql.exe --defaults-file=\"c:\\users\\aaa\\appdata\\local\\temp\\tmp8pbfwe.cnf\"\u00a0 --host=192.168.x.x --user=root --port=3306 --default-character-set=utf8 --comments --database=btb-old < \"Z:\\\\whateverdatabase.sql\" ERROR 2006 (HY000) at line 44: MySQL server has gone away\r\nOperation failed with exitcode 1\r\n17:45:04 Import of Z:\\whateverdatabase.sql has finished with 1 errors\r\n<\/pre>\nWant an easy solution for this?\u00a0 It turns out there is a limit in the MySQL database for the size of imports…<\/p>\n
Run this SQL statement against the MySQL database:<\/p>\n
SET<\/span> GLOBAL max_allowed_packet<\/span>=<\/span>250x1024x1024<\/span>;<\/span><\/code><\/pre>\nIn my case, I picked a size (250mb) (above) that is larger than my SQL import.<\/p>\n
Execute that script.<\/p>\n
Re-run the import.\u00a0 Works perfectly!<\/p>\n
17:47:42 Restoring Z:\\whateverdatabase.sql\r\nRunning: mysql.exe --defaults-file=\"c:\\users\\aaa\\appdata\\local\\temp\\tmpwpesfe.cnf\"\u00a0 --host=192.168.x.x --user=root --port=3306 --default-character-set=utf8 --comments --database=btb-old < \"Z:\\\\whateverdatabase.sql\"\r\n17:50:18 Import of Z:\\whateverdatabase.sql has finished<\/pre>\n<\/p>\n
<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Recently I was attempting to import a large (136mb) SQL backup for a new client. I first created a database and db user in PHPMYADMIN (most current version). Then using my Windows workstation, I launched MySQL Workbench 6.1 (my favorite tool for importing\/exporting) and tried to do a Server–>Data Import. Pointed it to the 136mb file and first had a<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61],"tags":[104],"class_list":["post-340","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/posts\/340","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/comments?post=340"}],"version-history":[{"count":3,"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/posts\/340\/revisions"}],"predecessor-version":[{"id":343,"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/posts\/340\/revisions\/343"}],"wp:attachment":[{"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/media?parent=340"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/categories?post=340"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.amixa.com\/blog\/wp-json\/wp\/v2\/tags?post=340"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}