[ Note to list admin, you are blocking messages from gmail.com ]
MySQL List,
I have a large MySQL database (around 20 gigs in total, some tables
>=3gigs. All tables are MyISAM. We have replication setup to a
number of slaves including one dedicated for backups.
All are running some semi-recent version of 4.1.
As we know:
Slaves are fine for distributing SELECT load.
Slaves are fine for failing over.
Slaves are fine for making backups.
Slaves do nothing to help after a destructive query like "delete from
important_table;" This should never happen but developers aren't
perfect and don't always test enough in their dev environment so I
want a solution for this type of catastrophe. Here's what I've
thought up. I wanted the lists thoughts before I code it up. google
was of no help.
Setup:
PRE) Setup a slave and configure "skip-slave-start" in the my.cnf.
Configure the slave with CHANGE MASTER command and bring it up to
sync w/ master. STOP SLAVE. System is now ready.
1) start mysql (skip-slave-start)
2) start mysql-slave-keepalive script on local machine.
mysql-slave-keepalive script would be (more or less, I'd have more
checks/auditing):
1) lynx --source http://private.internal.server/operations/
replication-status [contents $master_log_file:$master_log_pos]
2a) if (values == previous_values || values == 'HALT'); do stop
slave; page_operations_team; sleep 30 mins; done;
2b) otherwise...
3) issue 'START SLAVE UNTIL MASTER_LOG_FILE='$master_log_file',
MASTER_LOG_POS=$master_log_pos. sleep 30; goto 1.
In our internal admin system there would be an interface for pressing
"the big red button" to HALT slaving which could be issued by any
team member at any time. This would, hopefully, give anyone 30
minutes to ensure access to a currently running database that hasn't
been destroyed by the nefarious query.
Some of my questions:
1) What are the benefits to using relay_log_file and relay_log_pos
instead of master_log_file and master_log_pos? that the slave
binlogs would already exist locally? Perhaps that's good or bad?
thoughts?
2) Has anyone done something like this?
3) If I made it robust and flexible would people be interested in it?
4) Is there a better way?
I've tested this all by hand and it seems to work extremely well and
be well suited to scripting... In fact, I was surprised I wasn't able
to google anything about it.
Thanks for all feedback!
David Ulevitch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
