Skip to main content
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer@Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.

added 1 characters in body
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of [@Jonathan's answer][1]@Jonathan's answer. I just close and open binlogs on top of that. He getgets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. [1]: What is the safest way to switch the binlog format at runtime? You may want to apply the binlog_format change in the slave as well.

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of [@Jonathan's answer][1]. I just close and open binlogs on top of that. He get a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs. [1]: What is the safest way to switch the binlog format at runtime?

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.

added 329 characters in body
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer[@Jonathan's answer][1]. I just close and open binlogs on top of that. He get a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs. [1]: What is the safest way to switch the binlog format at runtime?

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He get a +1 for bringing this out first.

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of [@Jonathan's answer][1]. I just close and open binlogs on top of that. He get a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs. [1]: What is the safest way to switch the binlog format at runtime?

added 329 characters in body
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531
Loading
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531
Loading