FN13763
(Created page with "{{DISPLAYTITLE:FN13763 - MySQL binary log files accumulate and fill the /var partition}} = Affected Products = * TMG800, TMG3200, TMG7800-CTRL * Solution Developer (Tdev) Lin...")
Newer edit →
Revision as of 11:43, 20 July 2015
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.
Verify if this field notice apply to your system This fix is applicable if the /etc/my.cnf does not contain the parameter 'expire_logs_days=60'.
- 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 'mgnt0' 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.cnf.bak.cnf; cat /etc/my.cnf.bak | sed -r 's/(\[mysqld\].*)$/\1\nexpire_logs_days=60/g' > /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=60" 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=60"
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=60 [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 | 60 | +------------------+-------+
References
MySQL PURGE BINARY LOGS Syntax
https://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
