I have a MySQL 5.5 server with a server-id of 20. I want to execute a few queries on that server, but have them write to the binlog as a different server-id than 20. Is this possible?

I am aware set sql_log_bin is commonly used to turn binlogging on/off at-will, but I actually do want to execute these queries and have them written to the binlog, just as another server-id.

set server_id=21 errors saying it needs to be global, not a session variable, so I may already have my answer, but still, I have a feeling it is possible somehow. I mean, the mysql replication slave component can do it.

  • You cannot control your server-id on the session level; you did indeed have your answer. The mysql replication slave component does not do it as you describe. You could possibly get away with a complext topology where slaves replicating certain databases turn to pass only certain queries. But off my sleeve this doesn't sound like something you would want. – Shlomi Noach May 26 '13 at 16:59
  • The mysql replication slave component does do what I want. dev.mysql.com/doc/refman/5.5/en/… The log-slave-updates when enabled, will tell that host when it executes queries received from a master through replication, to write those queries to its own binlog file. It does so with the source's server-id. That's what I want. To do an update SQL command like coming in from a master, but not actually through replication, but as a an actual query on my SQL session. I want to do replication without using built-in replication. – Crash Override May 26 '13 at 18:21

server_id is a dynamic variable, so you can change it while the server is running. To change the global variable, do:

SET GLOBAL server_id=21

More info at http://dev.mysql.com/doc/refman/5.5/en/replication-options.html#option_mysqld_server-id

|improve this answer|||||
  • Thanks. However I am wondering if possible to change it for a session (not globally). – Crash Override May 26 '13 at 19:14
  • @CrashOverride no, it's a global variable only. – ESG May 26 '13 at 20:18

To make the change permanent you need to modify: /etc/my.cnf

Just change the following value:

|improve this answer|||||

Basically, there can be two solution :

Solution 1 :

If you having MySQL Instance enabled with GTID, then you can use gtid_purged variable to exclude transaction which you don't want to replicate it to slave.

The global value of the gtid_purged system variable (@@GLOBAL.gtid_purged) is a GTID set consisting of the GTIDs of all the transactions that have been committed on the server, but do not exist in any binary log file on the server. gtid_purged is a subset of gtid_executed.

That GTID(s) of transactions will be committed in binary logging but will not execute on Slave.

Solution 2 :

If you instance is not enabled with GTID, then there is no option other than setting sql_log_bin because setting server_id on session based is not possible.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.