FN13763 - Revision history

Luc Morissette: mgmt instead of mgnt

2016-01-05T14:17:13Z

mgmt instead of mgnt

Luc Morissette: Changed expire_logs_days to 30 instead of 60

2015-12-03T16:41:00Z

Changed expire_logs_days to 30 instead of 60

Lucas Trottier: /* Step 2: Modify /etc/my.cnf */

2015-09-14T08:15:58Z

Step 2: Modify /etc/my.cnf

Lucas Trottier: /* Problem Description */

2015-07-24T09:38:38Z

Problem Description

Lucas Trottier at 16:51, 20 July 2015

2015-07-20T16:51:28Z

Lucas Trottier at 16:50, 20 July 2015

2015-07-20T16:50:39Z

Lucas Trottier: /* Validate */

2015-07-20T16:43:56Z

Validate

Lucas Trottier: 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..."

2015-07-20T16:43:11Z

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..."

New page

{{DISPLAYTITLE: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.

'''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, [[Support:Contacting TelcoBridges technical support|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

@@ Line 36: / Line 36: @@
 = 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.
+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

@@ Line 24: / Line 24: @@
 * 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=60'
+* This fix is applicable if the /etc/my.cnf '''does not contain''' the parameter 'expire_logs_days=30'
@@ Line 98: / Line 98: @@
 * 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=60/g' > /etc/my.new.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
@@ Line 105: / Line 105: @@
 <br>
-== Step 3: Change "expire_logs_days=60" on current running instance ==
+== 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=60"
+  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.
@@ Line 115: / Line 115: @@
 * ssh to the units and login with root account
   [root@TB00xxxx]# grep 'expire_logs_days' /etc/my.cnf
-  expire_logs_days=60
+  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'"
@@ Line 121: / Line 121: @@
   | Variable_name    | Value |
   +------------------+-------+
-  | expire_logs_days | 60    |
+  | expire_logs_days | 30    |
   +------------------+-------+

@@ Line 22: / Line 22: @@
 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 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.
+* 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'.
+'''To verify if this field notice apply to your system'''
 * ssh and login with root account
   grep 'expire_logs_days' /etc/my.cnf
@@ Line 33: / Line 34: @@
 <br><br>
 = 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.

@@ Line 1: / Line 1: @@
 {{DISPLAYTITLE:FN13763 - MySQL binary log files accumulate and fill the /var partition}}
-<big><big><big>Field Notice</big></big></big>
 = Affected Products =
 * TMG800, TMG3200, TMG7800-CTRL