How to resize InnoDB logs ?

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 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:

Step 1 – Check existing logs and their size:

[root@localhost ~]# lsof -c mysqld | grep ib_logfile
mysqld  1018 mysql    5uW     REG              253,0  50331648   180228 /var/lib/mysql/ib_logfile0
mysqld  1018 mysql   11uW     REG              253,0  50331648   180229 /var/lib/mysql/ib_logfile1

Step 2 – Shutdown MySQL

[root@localhost ~]# systemctl stop mysqld 
[root@localhost ~]# systemctl status mysqld 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Sat 2018-05-12 16:33:51 IST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 5848 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 5831 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 5848 (code=exited, status=0/SUCCESS)
   Status: "SERVER_SHUTTING_DOWN"

May 12 16:33:43 localhost.localdomain systemd[1]: Starting MySQL Server...
May 12 16:33:44 localhost.localdomain systemd[1]: Started MySQL Server.
May 12 16:33:49 localhost.localdomain systemd[1]: Stopping MySQL Server...
May 12 16:33:51 localhost.localdomain systemd[1]: Stopped MySQL Server.
[root@localhost ~]# 

Step 3 – 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

[root@localhost ~]# find /var/lib/mysql -type f -name "ib_logfile?" -exec mv {} {}_OLD \;
[root@localhost ~]# ls /var/lib/mysql
auto.cnf       ca-key.pem       db1              ib_logfile1_OLD     private_key.pem  sys
binlog.000008  ca.pem           ib_buffer_pool   mysql               public_key.pem   undo_001
binlog.000009  client-cert.pem  ibdata1          mysql.ibd           server-cert.pem  undo_002
binlog.index   client-key.pem   ib_logfile0_OLD  performance_schema  server-key.pem
[root@localhost ~]#

Step 4 –  Resize 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)

[root@localhost ~]# grep 'innodb_log_file_size' /etc/my.cnf
innodb_log_file_size=64M

Step 5 – Restart MySQL instance

[root@localhost ~]# systemctl start mysqld 
[root@localhost ~]# systemctl status mysqld 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2018-05-12 16:44:30 IST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 5938 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 5955 (mysqld)
   Status: "SERVER_OPERATING"
   CGroup: /system.slice/mysqld.service
           └─5955 /usr/sbin/mysqld

May 12 16:44:29 localhost.localdomain systemd[1]: Starting MySQL Server...
May 12 16:44:30 localhost.localdomain systemd[1]: Started MySQL Server.

You have successfully resized InnoDB log files size 😊 👍

[root@localhost ~]# lsof -c mysqld | grep ib_logfile
mysqld  5955 mysql   10uW     REG              253,0  67108864   180206 /var/lib/mysql/ib_logfile0
mysqld  5955 mysql   11uW     REG              253,0  67108864   180230 /var/lib/mysql/ib_logfile1
[root@localhost ~]#