Automatic Database Maintenance (Mandatory)

Introduction

This topic describes how to delete old binary files and configure MySQL  and maintenance plan.

Maintenance plan do recurring tasks every day during the night :

•clear old unused items

•backup database

•clear old backups

•...

Deletion of old binary files

The binary log is a set of log files that contain information about data modifications made to a MySQL server instance. To limit number of binary logs files it is possible to set a variable that determine number of days after which old binary log files are deleted (The default is 0, which means “no automatic removal.” ). If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master. To remove binary log files manually, use the PURGE BINARY LOGS statement.

•Open MySQL Workbench

•Open MySQL Connection to connect to.

•Select INSTANCE > Options File option > select Logging tab > set expire_logs_days = 15

•Press Apply... button

Maintenance plan : Clear Old Items

This task clear items (events, terminal sessions, session cards, calllog entries, audits) that are too old. It will be executed every day at 1:00 am.

•Open MySQL Workbench

•Paste following code into new query :

DROP EVENT IF EXISTS EvtClearOldItems;

DELIMITER |

CREATE EVENT IF NOT EXISTS EvtClearOldItems

ON SCHEDULE EVERY 1 DAY STARTS '-01-01 01:00:00'

ON COMPLETION PRESERVE

ENABLE

DO BEGIN

   DECLARE _delay INT;

   DECLARE _reporting_delay INT;

   DECLARE _log_delay INT;

   DECLARE _audit_delay INT;

   UPDATE APP_GLOBALSETTING SET SettingValue = UTC_TIMESTAMP() WHERE SettingKey = 'sql.maintenanceplan.startexecution';

      SET _delay = ( SELECT SettingValue

                     FROM `<database_name>`.APP_GLOBALSETTING

                     WHERE SettingKey = 'database.purge.delay' );

   IF _delay IS NULL THEN

       SET _delay = 6;

   END IF;

   DELETE FROM `<database_name>`.SYS_EVENT

      WHERE (DateTime < DATE_ADD(UTC_TIMESTAMP(),INTERVAL -_delay DAY));

   DELETE FROM `<database_name>`.SYS_TERMINALSESSION

      WHERE DATE_ADD(LastUpdateDateTime,INTERVAL _delay DAY) < UTC_TIMESTAMP(); 

   DELETE FROM `<database_name>`.ACS_LICENSEOVERFLOW

     WHERE EXISTS (

          SELECT * 

          FROM `<database_name>`.SYS_USERSESSION

          WHERE DATE_ADD(LastUpdateDateTime,INTERVAL _delay DAY) < UTC_TIMESTAMP()

            AND `<database_name>`.ACS_LICENSEOVERFLOW.UserUId = `<database_name>`.SYS_USERSESSION.UserUId

            AND `<database_name>`.ACS_LICENSEOVERFLOW.TSSId = `<database_name>`.SYS_USERSESSION.TSSId

      );

   DELETE FROM `<database_name>`.SYS_USERSESSION

      WHERE DATE_ADD(LastUpdateDateTime,INTERVAL _delay DAY) < UTC_TIMESTAMP();

      SET _reporting_delay = ( SELECT SettingValue

                     FROM `<database_name>`.APP_GLOBALSETTING

                     WHERE SettingKey = 'database.purge.delay' );

   IF _reporting_delay IS NULL THEN

       SET _reporting_delay = 8;

   END IF;

   DELETE FROM `<database_name>`.USR_CALLLOGEVENT

      WHERE CallLogEntryUId in ( SELECT UId

                                 FROM `<database_name>`.USR_CALLLOGENTRY

                                 WHERE ConsoleDateTime < DATE_ADD(UTC_TIMESTAMP(),INTERVAL -_reporting_delay DAY) )

      OR ConsoleDateTime < DATE_ADD(UTC_TIMESTAMP(),INTERVAL -_reporting_delay DAY);

   DELETE FROM `<database_name>`.USR_CALLLOGENTRY

      WHERE ConsoleDateTime < DATE_ADD(UTC_TIMESTAMP(),INTERVAL -_reporting_delay DAY)

      AND Version != '000000000';

   DELETE FROM `<database_name>`.USR_LINECOVERAGE

      WHERE (ConsoleDateTime < DATE_ADD(UTC_TIMESTAMP(),INTERVAL -_reporting_delay DAY));

      SET _log_delay = (  SELECT SettingValue

                          FROM `<database_name>`.APP_GLOBALSETTING

                          WHERE SettingKey = 'calllogentry.purge.delay' );

   IF _log_delay IS NULL THEN

       SET _log_delay = 15;    

   END IF;

   IF _log_delay <= _reporting_delay THEN

       SET _log_delay = _reporting_delay + 1;

   END IF;

   DELETE FROM `<database_name>`.USR_CALLLOGENTRY

      WHERE (ConsoleDateTime < DATE_ADD(UTC_TIMESTAMP(),INTERVAL -_log_delay DAY));

      SET _audit_delay = ( SELECT SettingValue

                           FROM `<database_name>`.APP_GLOBALSETTING

                           WHERE SettingKey = 'audit.purge.delay' );

   IF _audit_delay < 30 THEN

       SET _audit_delay = 30;

   END IF;

   IF _audit_delay IS NULL THEN

       SET _audit_delay = 30;

   END IF;

   DELETE FROM `<database_name>`.SYS_AUDIT

      WHERE (DateTime < DATE_ADD(UTC_TIMESTAMP(),INTERVAL -_audit_delay DAY));

   UPDATE APP_GLOBALSETTING SET SettingValue = UTC_TIMESTAMP() WHERE SettingKey = 'sql.maintenanceplan.lastexecution';

END|

DELIMITER ;

•Replace all occurrence of <database_name> by the name of your database

•Right click on database name in Object Browser and click on Select as Default Schema

•Press Execute button to execute code

Maintenance plan : Backup

This task backs up databases and delete old backup files. It will be executed every day at 1:30 am.

Create batch

•Create new folder : C:\mysql\backup

•Create new folder : C:\mysql\task

•Create text file : C:\mysql\task\task_backup.bat

•Paste following code in this file :

@echo off

for /F "usebackq tokens=1,2 delims==" %%i in (`wmic os get LocalDateTime /VALUE 2^>NUL`) do if '.%%i.'=='.LocalDateTime.' set ldt=%%j

set mydate=%ldt:~0,4%%ldt:~4,2%%ldt:~6,2%

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" <database_name> --opt --user=<user> --password=<password> > C:\mysql\backup\backup_<database_name>_%mydate%.sql        

forfiles /p C:\mysql\backup /s /m *.sql /d -14 /c "cmd /c del @file"

•Replace all occurrences of <database_name> by the name of your database (ie: dbipt_rcXX_YYYY)

•Replace all occurrences of <user> by administrator user of MySQL (ie: root)

•Replace all occurrences of <password> by password of this user

•Save file on disk

Additional information

•To back up several database : duplicate the line containing mysqldump command and change <database_name> by the proper database name.

•Take care that <database_name> is also used in the filename of backup. If  <database_name> contains illegals filename characters ('\ / : * ? " < > |') please replace all these characters by legal characters (ie: '_' )

•Deletion of old backup file is 14 days by default. Replace all occurrences of 14 in the last line by the proper number of days you want.

Create schedule task

•Go to Start > Programs > Accessories > System Tools > Task Scheduler

•Press Action > Create Task...

•Set 'IPTrade MySQL Backup' as Name

•Set 'Perform backup of database on MySQL' as Description

•Select proper user to run the task. Take care password policy must not expire or the task will not run anymore and user must have sufficient privilege to run schedule task.

•Select option Run whether user is logged on or not

•Select tab Triggers

•Press New... button

•Select option 'On a schedule' as Begin the task

•Select option 'Daily' as Settings

•Select '1/01/200 1:30:00' as Start date

•Check flag 'Enabled' in Advanced settings

•Press OK button

•Select tab Actions

•Press New... button

•Select 'Start a program' as Action

•Press Browse... button

•Select file 'C:\mysql\task\task_backup.bat' on disk

•Press Open button

•Press OK button

•Press OK button to validate creation of task

•Enter password of selected user if necessary