This post is about a very simple approach \/ step-by-step InnoDB log (aka transaction logs)<\/em>resize, We don’t do this activity regularly but when we have to resize InnoDB log files, there will be a MySQL downtime. This post will be a like a checklist for anyone who want to resize InnoDB log files without any mistakes, We made this task in multiple steps so that you can follow much better:<\/p>\n

Step 1<\/strong> – Check existing logs and their size:<\/p>\n

[root@localhost ~]# lsof -c mysqld | grep ib_logfile\r\nmysqld  1018 mysql    5uW     REG              253,0  50331648   180228 \/var\/lib\/mysql\/ib_logfile0\r\nmysqld  1018 mysql   11uW     REG              253,0  50331648   180229 \/var\/lib\/mysql\/ib_logfile1<\/pre>\n
Step 2<\/strong> – Shutdown MySQL<\/p>\n
[root@localhost ~]# systemctl stop mysqld \r\n[root@localhost ~]# systemctl status mysqld \r\n\u25cf mysqld.service - MySQL Server\r\n   Loaded: loaded (\/usr\/lib\/systemd\/system\/mysqld.service; enabled; vendor preset: disabled)\r\n   Active: inactive (dead) since Sat 2018-05-12 16:33:51 IST; 5s ago\r\n     Docs: man:mysqld(8)\r\n           http:\/\/dev.mysql.com\/doc\/refman\/en\/using-systemd.html\r\n  Process: 5848 ExecStart=\/usr\/sbin\/mysqld $MYSQLD_OPTS (code=exited, status=0\/SUCCESS)\r\n  Process: 5831 ExecStartPre=\/usr\/bin\/mysqld_pre_systemd (code=exited, status=0\/SUCCESS)\r\n Main PID: 5848 (code=exited, status=0\/SUCCESS)\r\n   Status: \"SERVER_SHUTTING_DOWN\"\r\n\r\nMay 12 16:33:43 localhost.localdomain systemd[1]: Starting MySQL Server...\r\nMay 12 16:33:44 localhost.localdomain systemd[1]: Started MySQL Server.\r\nMay 12 16:33:49 localhost.localdomain systemd[1]: Stopping MySQL Server...\r\nMay 12 16:33:51 localhost.localdomain systemd[1]: Stopped MySQL Server.\r\n[root@localhost ~]# \r\n<\/pre>\n
Step 3<\/strong> – From reliability \/ safety perspective, We don’t recommend you remove the existing log files. You may need these files to restore database if anything goes wrong unfortunately<\/p>\n
[root@localhost ~]# find \/var\/lib\/mysql -type f -name \"ib_logfile?\" -exec mv {} {}_OLD \\;\r\n[root@localhost ~]# ls \/var\/lib\/mysql\r\nauto.cnf       ca-key.pem       db1              ib_logfile1_OLD     private_key.pem  sys\r\nbinlog.000008  ca.pem           ib_buffer_pool   mysql               public_key.pem   undo_001\r\nbinlog.000009  client-cert.pem  ibdata1          mysql.ibd           server-cert.pem  undo_002\r\nbinlog.index   client-key.pem   ib_logfile0_OLD  performance_schema  server-key.pem\r\n[root@localhost ~]#<\/pre>\n
Step 4<\/strong> – \u00a0Resize innodb_log_file_size system variable in my.cnf using you favorite editor of choice, In this post we have used 64M (which actually is a good value for many mid sized systems, bigger values are always a concern under some situations)<\/p>\n
[root@localhost ~]# grep 'innodb_log_file_size' \/etc\/my.cnf\r\ninnodb_log_file_size=64M\r\n<\/pre>\n
Step 5<\/strong> – Restart MySQL instance<\/p>\n
[root@localhost ~]# systemctl start mysqld \r\n[root@localhost ~]# systemctl status mysqld \r\n\u25cf mysqld.service - MySQL Server\r\n   Loaded: loaded (\/usr\/lib\/systemd\/system\/mysqld.service; enabled; vendor preset: disabled)\r\n   Active: active (running) since Sat 2018-05-12 16:44:30 IST; 7s ago\r\n     Docs: man:mysqld(8)\r\n           http:\/\/dev.mysql.com\/doc\/refman\/en\/using-systemd.html\r\n  Process: 5938 ExecStartPre=\/usr\/bin\/mysqld_pre_systemd (code=exited, status=0\/SUCCESS)\r\n Main PID: 5955 (mysqld)\r\n   Status: \"SERVER_OPERATING\"\r\n   CGroup: \/system.slice\/mysqld.service\r\n           \u2514\u25005955 \/usr\/sbin\/mysqld\r\n\r\nMay 12 16:44:29 localhost.localdomain systemd[1]: Starting MySQL Server...\r\nMay 12 16:44:30 localhost.localdomain systemd[1]: Started MySQL Server.<\/pre>\n
You have successfully resized InnoDB log files size \ud83d\ude0a\u00a0\ud83d\udc4d<\/p>\n
[root@localhost ~]# lsof -c mysqld | grep ib_logfile\r\nmysqld  5955 mysql   10uW     REG              253,0  67108864   180206 \/var\/lib\/mysql\/ib_logfile0\r\nmysqld  5955 mysql   11uW     REG              253,0  67108864   180230 \/var\/lib\/mysql\/ib_logfile1\r\n[root@localhost ~]# \r\n<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
This post is about a very simple approach \/ step-by-step InnoDB log (aka transaction logs)resize, We don’t do this activity regularly but when we have to resize InnoDB log files, there will be a MySQL […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,21,2,3,15,118],"tags":[24,16,129],"yoast_head":"\n