5

I have set up mysql replication with single slave. Whenever i am adding data to master database, it is automatically copied to slave database. But if I add data to slave database it is not available in master database. why?

If it is wrong to add slave database, how I can tell to the slave database to redirect writes to master? is it possible?

My application is designed as normal application not for replicated database. Should I change my application code for working with replicated database?

Improve this question
2
  • Yes, it's incorrect to write to slaves (check this article for details on how replication works). You can try to use such things as MySQL Proxy as intermediate layer separating application and db. But in our case we actually modified database-access layer so that all write ops are done at master only.
    – raina77ow
    Commented Apr 18, 2013 at 11:41
  • Habeebperwad, what you want is not the point of a Master-Slave replication. For what you want you should try a Master-Master Replication which you can find how to do so in this link howtoforge.com/mysql_master_master_replication or even looking up on the internet
    – medina
    Commented Apr 18, 2013 at 11:46

2 Answers 2

Reset to default
5

Data should never be directly inserted into a MySQL slave [except for edge cases ignored here]. To prevent this accidentally happening you should set read_only in your my.ini. Details on this option are here: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_only

MySQL Proxy might be an option for read/write splitting, but the last time I checked there were bugs that may mean this is currently unworkable. (MySQL Proxy: http://dev.mysql.com/downloads/mysql-proxy/, R/W split: https://wikis.oracle.com/display/mysql/MySQL+Proxy+RW+Splitting)

A third option (if you are looking for more HA than performance, and wish to avoid rewriting your application) would be to configure Master Master replication and put a loadbalancer such as haproxy in front of it - there are some excellent tutorials on the web on configuring haproxy in this kind of environment. (HAProxy: http://haproxy.1wt.eu)

(edited for spelling/links)

Improve this answer
4

MySQL master-slave replication only replicates from the master to the slave. It's okay to do read-only queries (e.g. SELECT) on either server, but you'll have to adjust your code so that all queries which modify the tables (e.g. INSERT, UPDATE) always connect to the master.

The other option is to use master-master replication, but it's significantly more complex.

Improve this answer
2
  • All the tables' have id with AUTO_INCREMENT. If I am using master-master replication, i will get dubliate entry error, right? How to solve the issue?
    – habeebperwad
    Commented Apr 18, 2013 at 11:57
  • You need to set auto_increment_increment and auto_increment_offset to differening values. Details on this are listed on the MySQL website as well as the comment on this article: howtoforge.com/mysql_master_master_replication. Be aware that if it breaks, master/master replication can be a pain to fix, so ensure you have good monitoring set up for this.
    – Chris
    Commented Apr 18, 2013 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.