I am receiving .SQL(500mb) backup file and restoring to MYSQL (8.0.13) database.
Currently, restore is taking 5 hrs to complete. I would like to speedup the process and started working on it.
Here are the things I tried
Changed innodb_flush_log_at_trx_commit=2 from 1.
Changed expire_log_days from 0 to 2. My log was filling up and storing file for everyday.
My .sql file already have Unique key,Foreign key checks to zero.AutoCommit=0 like below
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /; /!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /; /!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
Here are my my.ini file settings
max_connections=151
table_open_cache=2000
tmp_table_size=172M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=334M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
Innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300 i
nnodb_stats_on_
metadata=0
innodb_file_per_table=1
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
frustrating part is sometimes restore is stuck for hours and I have to kill the connection.
Please let me know what I am doing wrong here.
Appreciate your help.