MySQL my.cnf tweak for faster importing large database Rumi, March 3, 2018 Few small hack will ease you importing large sized mysql dump in a bit faster process. The trick worked for me for a 70 GB dumped sql, can’t guarantee it’d work for you! Step-1 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_commitIn 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 flushes the InnoDB Log Buffer only on commit. There are other advantages to setting 0 mentioned by @jynus, a former Percona instructor. Restart mysql like this service mysql restart --innodb-doublewrite=0 This disables the InnoDB Double Write Buffer. Import your data. When done, restart mysql normally. This re-enables the InnoDB Double Write Buffer. service mysql restart Step-2 Add these 2 lines- max_allowed_packet=256M wait_timeout=30000 service mysql restart Related MySQL
Installing MySQL on Debian September 26, 2020 Step 1 – Prerequisites Login to your Debian 9 system using shell access. For remote systems connect with SSH. Windows users can use Putty or other alternatives applications for SSH connection. ssh root@debian9 Run below commands to upgrade the current packages to the latest version. sudo apt update sudo apt… Read More
MySQL Tidbits June 27, 2011 Allow Slave to connect master: C:\mysql>create user 'replica'@'192.168.0.110' identified by '<YOUR PASSWORD>'; NOTE: 192.168.0.110 which is the SLAVE machine‟s address. i. mysql>grant create,insert,select,update,delete on ejbca.* to 'replica'@'192.168.0.110'; Checking if Slave can connect to Master: C:\>mysql –u replica –h 192.168.0.100 –p C:\>Enter password: <YOUR VALUES> NOTE: If the mysql prompt opens then the Master… Read More
Install Percona XtraDB Cluster for MySQL 5.7 on Debian 8 February 18, 2019 First of all, why we choose three nodes and not only two? In any cluster, the number of nodes should be odd, so in the case of disconnection of a node, we assume that the highest group of servers has the fresh data, and should be replicated to the down node… Read More