MySQL master-master replication and autoincrement indexes setup /etc/my.cnf

MySQL master-master replication and autoincrement indexes

If you are using master-slave replication, than most likely you will design your application the way to write to master and read from slave or several slaves. But when you are using master-master replication you are going to read and write to any of master servers. So, in this case the problem with autoincremental indexes will raise. When both servers will have to add a record (different one each server simultaneously) to the same table. Each one will assign them the same index and will try to replicate to the salve, this will create a collision. Simple trick will allow to avoid such collisions on MySQL server.
On the Master 1/Slave 2 add to /etc/my.cnf:

auto_increment_increment= 2

auto_increment_offset   = 1

On the Master 2/Slave 1 add to /etc/my.cnf:

auto_increment_increment= 2

auto_increment_offset   = 2

MySQL Replication Error 1236

MySQL error 1236: Client requested master to start replication from impossible position

Symptoms:
[1236] Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'

110902 16:47:08 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
110902 16:47:08 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
110902 16:47:08 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000033', position 4621679

on the master
root@dimko:/var/lib/mysql# ls -la mysql-bin.000033
-rw-rw---- 1 mysql mysql 4620018 2011-09-01 13:45 mysql-bin.000033

4620018 is less than 4621679, therefore it's an invalid position.


Causes:
Master server has crashed and the binlog cache has not been flushed to disk. Slave has recieved a new position, did not recieve data, and data gets lost in a crash (however it might have been written to table, but not in binlog).

Solution:
Use this CHANGE MASTER statement on the slave.
CHANGE MASTER TO MASTER_LOG_FILE=[NEXT FILE], MASTER_LOG_POS=4;
SLAVE START;

in my case that would be

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=4;
SLAVE START;

I don't know why the master log position needs to be 4 for the new file.

What happens:
When the master server restarts it logs binary changes to a new binlog file, so that we minimize data loss by skipping to the next file (everything from the previous file was written already).

Prevention:
Add this line to my.cnf:

sync_binlog = 1

 With this setting the master server flushes cache in the binlog after every write, so that in case of a crash you can lose one statement at most.