How to import a very large SQL dump file (6 Gb) to a MySQL database using windows command line. If you are using linux it is the same. The process is the following:<\/p>\n
Open a command prompt (or shell in Linux) with administrative privilleges<\/p>\n
Connect to a mysql instance using command line:<\/p>\n
<\/p>\n
# mysql -h 192.168.1.1 --port=3306 -u root -p<\/pre>\n<\/p>\n
if you are in localhost you do not need host and port<\/p>\n
# mysql -u root -p\n\n<\/pre>\nOr if plesk,<\/p>\n
# mysql -uadmin -p`cat \/etc\/psa\/.psa.shadow`\n<\/pre>\nYou are now in mysql shell. Set network buffer length to a large byte number. The default value may throw errors for such large data files<\/p>\n
mysql> set global net_buffer_length=1000000;\n\n<\/pre>\nSet maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.<\/p>\n
mysql> set global max_allowed_packet=1000000000;\n<\/pre>\nDisable foreign key checking to avoid delays,errors and unwanted behaviour<\/p>\n
mysql> SET foreign_key_checks = 0;\nmysql> SET UNIQUE_CHECKS = 0;\nmysql> SET AUTOCOMMIT = 0;\n<\/pre>\nImport your sql dump file<\/p>\n
mysql> use db_name;\nmysql> source backup-file.sql;\n<\/pre>\nRemember to enable foreign key checks when procedure is complete!<\/p>\n
mysql> SET foreign_key_checks = 1;\n mysql> SET UNIQUE_CHECKS = 1;\n mysql> SET AUTOCOMMIT = 1;\n<\/pre>\nIf you are in Linux you can create a Bash script which will do the dirty job and write to stdout start and end time of import:<\/p>\n
#!\/bin\/sh \n\n # store start date to a variable\n imeron=`date`\n\n echo "Import started: OK"\n dumpfile="\/home\/bob\/bobiras.sql"\n\n ddl="set names utf8; "\n ddl="$ddl set global net_buffer_length=1000000;"\n ddl="$ddl set global max_allowed_packet=1000000000; "\n ddl="$ddl SET foreign_key_checks = 0; "\n ddl="$ddl SET UNIQUE_CHECKS = 0; "\n ddl="$ddl SET AUTOCOMMIT = 0; "\n # if your dump file does not create a database, select one\n ddl="$ddl USE jetdb; "\n ddl="$ddl source $dumpfile; "\n ddl="$ddl SET foreign_key_checks = 1; "\n ddl="$ddl SET UNIQUE_CHECKS = 1; "\n ddl="$ddl SET AUTOCOMMIT = 1; "\n ddl="$ddl COMMIT ; "\n\n echo "Import started: OK"\n\n time mysql -h 127.0.0.1 -u root -proot -e "$ddl"\n\n # store end date to a variable\n imeron2=`date`\n\n echo "Start import:$imeron"\n echo "End import:$imeron2"\n\n<\/pre>\nOther solutions:
\nBig Dump
\nhttp:\/\/www.ozerov.de\/bigdump\/ seems good<\/p>\nSplit Files
\nhttp:\/\/www.rusiczki.net\/2007\/01\/24\/sql-dump-file-splitter\/<\/p>\n","protected":false},"excerpt":{"rendered":"How to import a very large SQL dump file (6 Gb) to a MySQL database using windows command line. If you are using linux it is the same. The process is the following: Open a command prompt (or shell in Linux) with administrative privilleges Connect to a mysql instance using command line: # mysql -h … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70],"tags":[],"class_list":["post-4963","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/4963","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=4963"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/4963\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=4963"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=4963"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=4963"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}