Jump to content

Mysql Replication Assistance


Recommended Posts

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 by CA3LE
removed password from quote for users safety
Link to post
Share on other sites

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.

Link to post
Share on other sites

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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...