FN13763 - MySQL binary log files accumulate and fill the /var partition

Affected Products

• TMG800, TMG3200, TMG7800-CTRL
• Solution Developer (Tdev) Linux server with (CentOS, RedHat, etc) running Toolpack software

Problem Symptoms

• The web portal become unaccessible or Toolpack application loose connection to the MySQL database.
• ssh is still accessible and /var is at 100% :

[root@TB00xxxx]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda5             3.9G  1.1G  2.7G  29% /
/dev/sda7              21G  3.7G   17G  19% /mnt/.0
/dev/sda6             2.0G   36M  1.9G   2% /tmp
/dev/sda2             4.9G  4.6G  5.3M 100% /var
/dev/sda1             494M   16M  453M   4% /boot
tmpfs                 997M     0  997M   0% /dev/shm

Problem Description

The MySQL database binary log is a set of files that contain information about data modifications made by the MySQL server. The log consists of a set of binary log files, plus an index file. The log max files size is set by default to 1G. When it reaches that limit, another file is created. If those files are not purged from the system, if fill up the /var partition.

• System which are shipped before January 2013 requires this fix
• System installed by a solution developper following TelcoBridges wiki for MySQL server configuration (my.cnf) requires this fix
• This fix is applicable if the /etc/my.cnf does not contain the parameter 'expire_logs_days=30'

To verify if this field notice apply to your system

• ssh and login with root account

grep 'expire_logs_days' /etc/my.cnf

• If grep does not return a value, apply the solution below.

Solution

For the following procedure, you must first make sure that the management interface is named 'mgmt0' on your system. Some Solution Developer systems or old version of TMG might have another name for the management interface.

• ssh and login with root account

[root@TB00xxxx]# ifconfig | grep mgmt0 -A 1
mgmt0     Link encap:Ethernet  HWaddr 4C:72:B9:7B:AE:C0  
          inet addr:192.168.3.180  Bcast:192.168.3.255  Mask:255.255.255.0

• If there is no output, please contact TelcoBridges support.
• Follow the procedure bellow

Step 1: Purge unneeded MySQL binary logs from /var

Procedure for TMG800, TMG3200, TMG7800-CTRL standalone or Toolpack running on 1 server

Find latest log file

• ssh and login with root account

[root@TB00xxxx]# mysql -h `ifconfig | grep mgmt0 -A 1 | grep "inet addr" | sed 's/\s\+/:/g' | cut -d':' -f4` -u tbdb -ptbdbpw -e"show binary logs\G" | tail -n 2
 Log_name: mysqld-bin.000081
File_size: 25334528

Purge the logs

• In the command bellow, change 'mysqld-bin.xxxxxx' for the value returned by the previous command (ie. mysqld-bin.000081)

[root@TB00xxxx]# mysql -h `ifconfig | grep mgmt0 -A 1 | grep "inet addr" | sed 's/\s\+/:/g' | cut -d':' -f4` -u tbdb -ptbdbpw -e"PURGE BINARY LOGS TO 'mysqld-bin.xxxxxx';"

Procedure TMG800 1+1, TMG3200 1+1, TMG7800-CTRL redundant or Toolpack running HA on 2 servers

Identify the slave and the master database unit

To find which unit is the slave database, run the following commands on both 1+1 units, TMG7800-CTRLs or Toolpack HA servers:

• ssh and login with root account

mysql -h `ifconfig | grep mgmt0 -A 1 | grep "inet addr" | sed 's/\s\+/:/g' | cut -d':' -f4` -u tbdb -ptbdbpw -e"show slave status\G" | grep Master_Log_File

The Master database unit returns no value and the slave database unit returns a value for Master_Log_File parameter.

For example:

Unit A output returns nothing

Unit B output returns:

              Master_Log_File: mysqld-bin.000012
       Relay_Master_Log_File: mysqld-bin.000012

=> A is the master database unit
=> B is the slave database unit

Purge the master database unit logs

• ssh to the master database unit and login with root account
• In the command bellow, change 'mysqld-bin.xxxxx' for the 'Master_Log_File' value returned by the slave database unit (ie. mysqld-bin.000012)

mysql -h `ifconfig | grep mgmt0 -A 1 | grep "inet addr" | sed 's/\s\+/:/g' | cut -d':' -f4` -u tbdb -ptbdbpw -e"PURGE BINARY LOGS TO 'mysqld-bin.xxxxx';"

Purge the slave database unit logs

• ssh to the slave database unit and login with root account
• Execute the following commands to show the latest log:

[root@TB00xxxx]# mysql -h `ifconfig | grep mgmt0 -A 1 | grep "inet addr" | sed 's/\s\+/:/g' | cut -d':' -f4` -u tbdb -ptbdbpw -e"show binary logs\G" | tail -n 2
 Log_name: mysqld-bin.000010
File_size: 43248618

• In the command bellow, change 'mysqld-bin.xxxxx' for the value returned by the previous command (ie. mysqld-bin.000010)

[root@TB00xxxx]# mysql -h `ifconfig | grep mgmt0 -A 1 | grep "inet addr" | sed 's/\s\+/:/g' | cut -d':' -f4` -u tbdb -ptbdbpw -e"PURGE BINARY LOGS TO 'mysqld-bin.xxxxx';"

Step 2: Modify /etc/my.cnf

• ssh to the units and login with root account

cp /etc/my.cnf /etc/my.bak.cnf
cat /etc/my.bak.cnf | sed -r 's/(\[mysqld\].*)$/\1\nexpire_logs_days=30/g' > /etc/my.new.cnf
cp /etc/my.new.cnf /etc/my.cnf

Note: must be executed on both units in case of TMG800 1+1, TMG3200 1+1, TMG7800-CTRL redundant or Toolpack running HA on 2 servers.

Step 3: Change "expire_logs_days=30" on current running instance

• ssh to the units and login with root account

mysql -h `ifconfig | grep mgmt0 -A 1 | grep "inet addr" | sed 's/\s\+/:/g' | cut -d':' -f4` -u tbdb -ptbdbpw -e"SET GLOBAL expire_logs_days=30"

Note: must be executed on both units in case of TMG800 1+1, TMG3200 1+1, TMG7800-CTRL redundant or Toolpack running HA on 2 servers.

Validate

• ssh to the units and login with root account

[root@TB00xxxx]# grep 'expire_logs_days' /etc/my.cnf
expire_logs_days=30

[root@TB00xxxx]# mysql -h `ifconfig | grep mgmt0 -A 1 | grep "inet addr" | sed 's/\s\+/:/g' | cut -d':' -f4` -u tbdb -ptbdbpw -e"SHOW VARIABLES LIKE 'expire_logs_days'"
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 30    |
+------------------+-------+

References

MySQL PURGE BINARY LOGS Syntax

https://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html