Versions Compared

Old Version 1

changes.mady.by.user Paul Ruvin

Saved on

compared with

New Version Current

changes.mady.by.user Paul Ruvin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Introduction and Prechecks

If there is a request to move DualShield Replication cluster to a new set of servers the best approach would be to Clone just one dualshield/mysql node in the cluster using the instructions as written here...

...

Ideally, you should have a few..

..buthowever, if you have a large amount, dating back a long time, together with a large audit log table, cleaning cloning will take too long, or may even fail.  Therefore, Please do the following to ensure a more efficient and quicker way of moving Duslashield from and existing replication cluster...


Expand
titleReduce Log Table Size

The easiest way to check the size of your audit log table is to go to C:\Program Files\Deepnet DualShield\mysql\data\dualshield and search for the log.idb file

In the screenshot posted above 3Gb is acceptable and should not hamper the cloning, but if you have 10, 20, or 30 Gbs or more it is advisable to cut this down.

If you are not concerned with keeping the audit logs, then the fastest approach is to stop DualShield service, log into your Mysql console via command prompt or MySQL Workbench and drop the log table..

Code Block
themeEmacs
drop table log;

Please note that for DualShield v5 you also need check the size of the log_field.idb and drop this table first.

If you are not confident with MySQL queries you can use the Truncate audit trail task in the admin console to do the same thing.

Truncate audit trail

If you would prefer to keep some of the records you can set the parameters of the days you want to keep and run the Purge old audit trail records task,

Purge old audit trail records

If you wish to backup whilst purging then you can run the Export/Purge old audit trail records task.

Export and Purge old audit trail records

Please note that Purging is a longer process than truncating, and export/purge will take even longer.

Once you have completed the trucate or purge tasks it is recommended to then optimize the table size to ensure the file size of the table reduces

Code Block
themeEmacs
optimize table log;

Do the same for the table log_Field if required (for DualShield v5)




Expand
titleRemove Superflous Bin Log Files...

Binary log files (often referred to as binlogs) in MySQL are a record of all changes made to the database. They are essential for replication, backups, and recovery purposes. Binlogs track events such as:

  • Data modifications (e.g., INSERT, UPDATE, DELETE statements)
  • Table and database schema changes (e.g., CREATE, ALTER statements)

MySQL uses binlogs to propagate changes from a master database to one or more slave databases in a replication setup. The slaves read and apply the binlogs from the master to stay synchronized.

Over time the the amount of bin log files increase and therefore so does the size of the file they are stored in.  This will take up hard drive space plus, this will significantly slow the process if you are cloning the system as the more bin log files there are, the more that have to be copied during the cloning process.

Therefore there is some house keeping that needs to be done to ensure the older bin log files get deleted.


Expand
titleModify the das.ini file to housekeep automatically.......
  • expire_logs_days
    • You can control how long binary logs are kept by setting the expire_logs_days parameter in the MySQL configuration file
    • To do this go to  C:\Program Files\Deepnet DualShield\mysql
    • Edit the das.ini file in elevated Notepad or Notepad++
    • Add an entry under [mysqld] for example expire_logs_days = 30

Image Added

    • This will ensure that logs over 30 days old will be deleted.  You can set a different amount of days, but we recommend at least 30 to ensure no loss of data.
    • Remeber to Save the das.ini file
    • Restart the MySQL(Dual) service

Image Added

Expand
titleUse a MySQL query to manually purge binlogs...
    •  Log into MySQL Console in command prompt (refer to Login to MySql console)
    • Use the following query... show binary logs;

Image Added

    • Use the following query... PURGE BINARY LOGS BEFORE NOW() - INTERVAL 30 DAY;Image Added
    • Logs over 30 days old will be deleted right away.  You can set a different amount of days, but we recommend at least 30 to ensure no loss of data.
    • Use the following query... show binary logs;

Image Added

    • You can see that the number of bin log files has been greatly reduced. * 
    • *Please note the screenshots were taken from a test server with a low amount of bin log files to start with so in this example it was reduced to 7 days, but most likely you will see more bin log files if reduced to 30 days as recommended.