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 try to set up mySQL replication, however I have question before I connect 2 servers

Q1. Should I import all data from master into slave before I connect 2 servers?

ex. if My db have millions of rows, should I copy all data to slave first?

Q2. I have read few post about when disaster strikes on master, it switch to slave, so you have time to fix master. What kind of disaster could happen? I have set up mySQL only SELECT, ADD and UPDATE, just wondering what could happen?

share|improve this question

migrated from stackoverflow.com Oct 5 '13 at 13:24

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

1 Answer 1

Q1. Yes, not only "should" you copy the data from the master, you have to.

Replication in MySQL relies on the two servers beginning with an identical set of data, after which, every query that modifies data on the master makes the same changes to the data on the slave.

There are two ways to do this, either by stopping the master completely (shutting down mysqld) and copying the data files... or using mysqldump, as explained in the documentation:

http://dev.mysql.com/doc/refman/5.6/en/replication-howto-existingdata.html

Q2.

when disaster strikes on master, it switch to slave

Well, not exactly. "It" (the master) or "it" (replication) doesn't do anything when there's a failure on the master. You, or your application, must choose if and when to abandon a failed master and start writing to the slave, instead of writing to the master. At this point, the slave becomes master and is the only server in the system, because the data on the old master is now stale and obsolete, and must be resynchronized.

What kind of disaster could happen?

The fact that you ask this question suggests that you are not thinking of the kinds of disasters that replication actually protects you from: hard drive failure, memory corruption, cpu failure, cooling fan failure, power supply failure ... as well as utility power outage, fire, flood, earthquake (assuming the slave server is in a different data center) ... and a pretty-much endless list of the things that can go wrong in or around a server.

Your question suggests that you were thinking along the lines of things that might happen to your data in properly-functioning server... such accidental or malicious altering or deleting or rows, or dropping of tables or databases. Replication doesn't protect against these things, because they'll replicate perfectly.

There was, however, a new feature introduced in MySQL 5.6, "Time Delayed Replication," which allows the slave to lag a fixed period of time behind the master, to give you time to recover from such a change to your data. The slave still "discovers" the changes to the master in near real time, but does not actually apply the changes to its data until the configured amount of time has passed from the time they were originally logged on the master.

http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html

share|improve this answer

Your Answer

 
discard

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