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’ve been doing some investigation into properly configuring MySQL replication.

From what I understand after reading the documentation, you can only replicate specific databases, and each database name must be specified: In the master configuration, in order for the relevant database writes to be written to the binary logs, and in the slave configuration, in order to replay those logs onto the slave.

Is this correct, or is it possible to configure MySQL to replicate any and all databases?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

To answer your question about how MySQL replicates all databases, here is how it works...

  • Master has Binary Logging enabled
  • Master records completed SQL Statements
  • Master will inject 'USE dbname;' among the SQL Statements it records
  • Slave connects to Master (IO Thread)
  • Slave IO Thread requests next SQL statement from the Binary Logs
  • Slave IO Thread copies it to its Relay Logs
  • Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Once USE dbname; is issued out of the relay logs, multiple SQL statements will be processes chronologically until another USE dbname; (a different database) is issued from the Relay Logs.

To see this, pick any Binary Log (except the Current One) on the Master, and run this:

mysqlbinlog binarylogname > SQLStatements.sql

To see all the binary logs and the current binary log on the Master, run these:

SHOW BINARY LOGS;
SHOW MASTER STATUS;

Long story short: By default, all DBs are replicated due to the USE dbname; command within the binary logs on the master and the relay logs on the slave.

Abdul's answer shows how to filter in or filter out databases from being processed by the slave.

share|improve this answer

Yes,You can replicate all DB's from Master to slave.

But sometime we don't prefer that Mysql database shuld be replicated due to security reason because all the user related informations will be also be available to slave.that can cause security breach.

you can set which database should be replicate and which one should not by using options in configuration file..

   replicate-ignore-db=db_name  and replicate-do-db=db_name

It also depend on that which type of replication you are using like statement based and row based.please see

http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html

share|improve this answer
    
This partially answers my question, but leads to the obvious next question: Okay, so how do I indicate to MySQL that it should replicate every database, with the exception of the mysql database, without manually specifying the name of every database? –  Abraham Vegh Dec 8 '11 at 7:37
1  
If don't specify database name it will itself replicate all the database including Mysql. –  Abdul Manaf Dec 8 '11 at 8:36

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.