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.