Restore mysql db | Sololearn: Learn to code for FREE!
New course! Every coder should learn Generative AI!
Try a free lesson
+ 1

Restore mysql db

I am restoring mysql db which has more then 300000 records it toke a lot of time when using import option mysqldump is there any fastest way to restore mysql db? Please help me solve the pronlem. Archana AJ Anant S.Adil 🇦🇫 r1c5 david chongo David F. Rose JΞΜΔ 🇨🇩👑 MCGAmedCoder gamerArtist

27th Jun 2020, 5:40 PM
Moh Waleed Sharifi
5 Answers
+ 6
You definitely need to change the following innodb_buffer_pool_size = 4G innodb_log_buffer_size = 256M innodb_log_file_size = 1G innodb_write_io_threads = 16 innodb_flush_log_at_trx_commit = 0 Why these settings ? innodb_buffer_pool_size will cache frequently read data innodb_log_buffer_size : Larger buffer reduces write I/O to Transaction Logs innodb_log_file_size : Larger log file reduces checkpointing and write I/O innodb_write_io_threads : Service Write Operations to .ibd files. According to MySQL Documentation on Configuring the Number of Background InnoDB I/O Threads, each thread can handle up to 256 pending I/O requests. Default for MySQL is 4, 8 for Percona Server. Max is 64. innodb_flush_log_at_trx_commit In the event of a crash, both 0 and 2 can lose once second of data. The tradeoff is that both 0 and 2 increase write performance. I choose 0 over 2 because 0 flushes the InnoDB Log Buffer to the Transaction Logs (ib_logfile0, ib_logfile1) once per second, with or without a commit. Setting 2 flushe
27th Jun 2020, 5:59 PM
Shahghasi Adil
Shahghasi Adil - avatar
+ 4
Hadia 🇦🇫 30000 is not much data. Btw how much time it is taking?
27th Jun 2020, 6:01 PM
A͢J - avatar
+ 2
Hadia 🇦🇫find your mysql cnf file - Most probably located in /etc/mysql/
28th Jun 2020, 2:59 AM
Shahghasi Adil
Shahghasi Adil - avatar
+ 1
Thanks AJ Anant sorry the number of rows is 300000 and it takes up to 40 minutes to complete
28th Jun 2020, 2:51 AM
Moh Waleed Sharifi
Mr S.Adil 🇦🇫 where can I change these values?
28th Jun 2020, 2:54 AM
Moh Waleed Sharifi