Mysql Replication Assistance

[cbtshares]

Good day,

Hello, I have a serious issue.I have configure mysql Mater /slave replication and it works fine.Just that if the main DB server goes everything goes.So I configured Master /Master(Or hoped to) , so I started.

On the slave server, I in put this in my.cnf

mysql> grant replication slave, replication client on *.* to 'slave_user'@'10.0.0.1'

identified by '[password removed by TMN Admin]';


FLUSH PRIVILEGES;


//////EDIT YOUR MY.CNF FILE ON THE MAIN SERVER TO LOOK LIKE THIS


[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

old_passwords=1

server-id=1


master-host = 10.0.0.1

master-port = 3306

master-user = slave_user

master-password = [password removed by TMN Admin]

master-connect-retry=60

slave-skip-errors = 1062


[mysqld_safe]

log-bin = /var/log/mysql/mysql-bin.log

log-slave-updates

log-bin-index = /var/log/mysql/log-bin.index

log-error = /var/log/mysqld.log 


relay-log = /var/log/mysql/relay.log

relay-log-info-file = /var/log/mysql/relay-log.info

relay-log-index = /var/log/mysql/relay-log.index

master-info-file = /var/log/mysql/master.info


auto_increment_increment = 10

auto_increment_offset = 1

replicate-same-server-id = 0


pid-file=/var/run/mysqld/mysqld.pid

Restarted and all was well....

Then on my Master I in put the same values but changed the server id and ip address, then I restarted.

Then all hell broke loose, It dropped all my tables, and deleted my entire database and my site was down...I managed to make the slave the master and am now in the process of configuring back the M/S replication , and then the M/M but dont want this to happen again.Can anyone tell me what I did wrong.? The logs just say error in dropping tables, as does the "Show slave StaTUS"

Edited July 24, 2010 by CA3LE
removed password from quote for users safety

[CA3LE]

I have pretty extensive knowledge of MySQL. But I've never dealt with replication. We rarely get MySQL questions here so I'm not too sure if your question will be answered here.

Can you do me a favor though, if you find the solution can you please post it to this thread. It may help someone else in the future.

MySQL has driven me crazy in the past. And that's why you ALWAYS backup your databases before doing anything major... one wrong step can land your ass on the keyboard for a looooong time.

[mudmanc4]

You'll need to make sure you have set in your config.php (?) file , database persistence set to 'off '

It may have tried to re write the tables with each other yes ?

I should ask what version mysql your running

error in dropping tables tells us that the path to the slave is not set properly , in one fashion or another , or that f your using an older version of mysql , that the log is not set on the slave

" replicate-same-server-id = 0 " actually = " 1 " ? or the unique slave Id is not set properly on the slave db