for a long time I am figuring out how to set up an MySQL Master and Slave replication.
I followed the instructions from MySQL itself (
http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html ) and several other resources. I've also tried to use the `INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';` thing but doesnt work either.
_ALL_ solutions ending up with this error (not with the same key but same error code) `Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '881' for key 'PRIMARY''`
It is driving me so crazy I cant find a solution that blows this issue away!
I am running MySQL Server 5.5and want to replicate Typo3.
Basically I set the servers up like this:
########### On master ###########:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
rpl_semi_sync_master_enabled=1
SET GLOBAL rpl_semi_sync_master_timeout = 10000;
CREATE USER 'replicant'@'masterip' IDENTIFIED BY 'pw';
GRANT REPLICATION SLAVE, SUPER, RELOAD, SELECT ON *.* TO 'replicant'@'masterip' IDENTIFIED BY 'pw';
--- lock tables ----
FLUSH TABLES WITH READ LOCK;
tar -cvf /tmp/typo3db.tar /opt/mysql/server-5.5/data/typo3
scp /tmp/typo3db.tar transfer@slaveip:/tmp/ # Slave IP
#####################################
########### On slave ###########:
tar -xvf /tmp/typo3db.tar
cp -r typo3 /opt/mysql/server-5.5/data/
chown -R mysql.mysql /opt/mysql/server-5.5/data/typo3
chgrp -R mysql /opt/mysql/server-5.5/data/typo3
#####################################
########### On master ###########:
SHOW MASTER STATUS;
--- note File and Position. ---
bind-address = masterip in /etc/my.cnf
UNLOCK TABLES;
restart
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
rpl_semi_sync_master_enabled=1
#####################################
########### On slave ###########:
in /etc/my.cnf below [mysqld] < server-id = 2
mysqld restart
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1
CHANGE MASTER TO MASTER_HOST='masterip', MASTER_PORT=3306, MASTER_USER='replicant', MASTER_PASSWORD='masterip', MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=153989, MASTER_CONNECT_RETRY=10;
STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
#####################################
In the beginning, it works beautifully. After some changes in Typo3 itself it bricks with the 1062 (duplicate) error.
I know the trick with `SET GLOBAL sql_slave_skip_counter = N;` but this isnt a solution!
I hope somebody can help me! Maybe there is another option to replicate a database? Maybe on filesystem level?
- Timo