Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Apr 18 '13 at 15:46

This question came from our site for professional and enthusiast programmers.

    
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 Apr 18 '13 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 Apr 18 '13 at 11:46

2 Answers 2

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)

share|improve this answer

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.

share|improve this answer
    
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 Apr 18 '13 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 Apr 18 '13 at 12:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.