MySQL Bugs: #4964: SELECT LOCAL

Bug #4964	SELECT LOCAL
Submitted:	9 Aug 2004 18:20	Modified:	16 Aug 2004 19:11
Reporter:	Dean Richardson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	Any	OS:	Any (Any)
Assigned to:		CPU Architecture:	Any

Description:
Could we have a LOCAL keyword to the SELECT statement?

What I mean by this is that the SELECT statement would be executed on the master, but the "local" keyword would cause the select statement NOT to be written into the master replication logs.

The reason why I ask is that I find a lot of redundant select queries are sent from the master to my slaves (I have 1 master & three slaves).  Not all my selects need to go to my slaves, and it would save on bandwidth ... and $$$ ... if I could insert a LOCAL keyword to stop these selects from being written into the replication log.

Yes, I know I can send SELECTS to the slaves ... and yes I could spend lots of hours on a re-work of our systems ... but a LOCAL keyword would be much easier, give me lots more control and help me cut down on bandwidth and costs if I could stop certain selects from being sent to my slaves.

Thanks,
Dean

How to repeat:
N/A

Suggested fix:
LOCAL keyword added to SELECT statements to stop these statements from being written into the master's binary log

Hi,
SELECT queries are never written to the master's binary log.
The master's binary log contains only queries which update data: UPDATE, INSERT, DELETE, DROP, CREATE etc.
You can see the content of any master's binary log with:
mysqlbinlog <binlog_name>

Yes - correct, I has mis-read the incoming SQL statements.

What I'm seeing in a series of temporary table creations on the master table .... which do not need to be replicated onto the slaves.

Example:-
  Master runs a series of scripts that create some temporary tables (via create X table select ...   and insert into (temporary table) select ...).  These need to be run on the master because the slaves are not 100% reliable.  The temporary tables are created, read then disposed of.

In this example, the temporary table creation statements (create table X select...) do not need to be sent to the slaves.

Therefore a LOCAL parameter in create table, insert into, replace into etc would be an advantage ... unless MYSQL offers another way to avoid these statements being sent to the slaves.

Regards,
Dean

Hi,
yes MySQL provides a way to prevent that a statement goes into the master's binlog: it's the SQL_LOG_BIN variable.
You can also name your temp tables (those which you don't want replicated) tmp<something> and use the replicate-wild-ignore-table option on slaves; it will take bandwidth as the query will be sent to the slave, but then the slave will decide to ignore it.
Or use binlog-ignore-db... Look in the manual for all these.
Good luck.