1

I need to automate master binary log purging to occur so that purging never goes back further than the last slave has the updated info for.

These slaves are mobile and there are 3 of them, and sync whenever the users get around to it.

I can do this manually but how to set this up in a script eludes me.

Any ideas?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Eric
  • 19
  • 1

3 Answers3

3

DISCLAIMER : Not a Python Programmer

I can give you a generic algorithm

Let's say you have 4 DB Servers

  • replicator@10.1.2.% is the Replication User
  • 1 Master (10.1.2.10)
  • 3 Slaves (IP Addresses : 10.1.2.20,10.1.2.30,10.1.2.40)

STEP 01) Run this query

SELECT host FROM information_schema.processlist WHERE user = 'replicator';

Collect host columns into a list.

STEP 02) For each host collected into the list, run

SHOW SLAVE STATUS;

and retrieve the Relay_Master_Log_File column. Collect these in an array.

I wrote about why to pick that field and not Read_Master_Log_File

STEP 03) Sort the array of Relay_Master_Log_File names. Select the first entry.

Suppose you collected

  • mysql-bin.00124
  • mysql-bin.00123
  • mysql-bin.00125

After sorting, you would pick mysql-bin.00123

STEP 04) Purge binary logs on the Master using the select binary log

PURGE BINARY LOGS TO 'mysql-bin.00123';

That's it.

Give it a Try !!!

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

In addition to Rolando's general description I would recommend your script maintains and expected configuration definition to compare against the active process list. Rolando's steps only very connected slaves. A likely scenario a slave would fall so far behind many bin logs is replication broken or was administratively stopped and not in the masters process list at all.

Treat an event of a missing slave as an error state in which no binary logs are purged from the master.

This does add the additional admin overhead of needing to keep that config list in sync when adding/removing slaves but required for your script to work as desired.

Further consider your retention policy. If you want to spin up a new slave you might do that by restoring a recent backup and restarting replication from when the backup was taken. For the scope of this discussion we'll assume you have daily backups and are certain the binlog position stored with the backups have been proven to be working correctly.

If you have daily backups you'd probably want to keep at least 2 days of bin logs.

Edit: After rereading your post it sounds like your question is more a long the lines of "how to write scripts". Python is certainly a viable option suited for the task. Whether it's perl, python, php or other you'll need to look up how to work with their MySQL API clients. More specific questions of how-to-code in these languages are probably better placed in stackoverflow.com after googling some basic intro's to the language to gain that literacy.

atxdba
  • 5,293
  • 5
  • 41
  • 62
0

draw backs of this script. 1.you don't have chance to recover db when ever crash.if you maintain incremental backups in a backup server than it is ok).

you can better to enable expire_logs. or you purge the bin-logs using date time in the script.

ashuthosh
  • 707
  • 1
  • 9
  • 15