Tag Archives: Large

Import a large sql dump file

1) Set network buffer length to a large byte number. The default value may throw errors for such large data files

set global net_buffer_length=1000000;

2) Set maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.

set global max_allowed_packet=1000000000;

3) Disable foreign key checking to avoid delays,errors and unwanted behaviour

SET AUTOCOMMIT = 0;
SET UNIQUE_CHECKS = 0;
SET foreign_key_checks = 0;

4) Import your sql dump file

source FILEPATH/FILE_NAME

5) Enable foreign key checks when import is done.

SET AUTOCOMMIT = 1;
SET UNIQUE_CHECKS = 1;
SET foreign_key_checks = 1;
innodb_buffer_pool_size    = 1G
innodb_log_buffer_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_file_size = 1G