Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have just setup one master and one slave server, but its not working..

On my website I connect to the slave server and I insert some rows, but they do not appear on the master and vice versa.. What is wrong?

This is what I did:

Master:

-> /etc/mysql/my.cnf

[mysqld]
log-bin         = mysql-master-bin
server-id=1
# bind-address      = 127.0.0.1
binlog-do-db        = test_db


Slave:

-> /etc/mysql/my.cnf

[mysqld]
log-bin         = mysql-slave-bin
server-id=2
# bind-address      = 127.0.0.1
replicate-do-db     = test_db


Slave:

terminal 0 >
mysql> STOP SLAVE; // and drop tables


Master:

terminal 1 >
mysql> CREATE USER 'repl_slave'@'slave_ip' IDENTIFIED BY 'repl_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_slave'@'slave_ip';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
-- leave terminal open

terminal 2 >
shell> mysqldump -u root -pPASSWORD test_db --lock-all-tables > dump.sql

mysql> SHOW MASTER STATUS;


Slave:

terminal 3 >
shell> mysql -u root -pPASSWORD test_db < dump.sql

terminal 0 >
mysql> CHANGE MASTER TO
mysql> MASTER_HOST='master_ip',
mysql> MASTER_USER='repl_slave',
mysql> MASTER_PASSWORD='repl_pass',
mysql> MASTER_PORT=3306,
mysql> MASTER_LOG_FILE='mysql-master-bin.000003',   // terminal 2 > SHOW MASTER STATUS
mysql> MASTER_LOG_POS=4,                        // terminal 2 > SHOW MASTER STATUS
mysql> MASTER_CONNECT_RETRY=10;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS;

Here is the slave status:

Array
(
    [Slave_IO_State] => Waiting for master to send event
    [Master_Host] => xx.xx.xx.xx
    [Master_User] => repl_slave
    [Master_Port] => 3306
    [Connect_Retry] => 10
    [Master_Log_File] => mysql-master-bin.000003
    [Read_Master_Log_Pos] => 106
    [Relay_Log_File] => mysqld-relay-bin.000002
    [Relay_Log_Pos] => 258
    [Relay_Master_Log_File] => mysql-master-bin.000003
    [Slave_IO_Running] => Yes
    [Slave_SQL_Running] => Yes
    [Replicate_Do_DB] => test_db
    [Replicate_Ignore_DB] => 
    [Replicate_Do_Table] => 
    [Replicate_Ignore_Table] => 
    [Replicate_Wild_Do_Table] => 
    [Replicate_Wild_Ignore_Table] => 
    [Last_Errno] => 0
    [Last_Error] => 
    [Skip_Counter] => 0
    [Exec_Master_Log_Pos] => 106
    [Relay_Log_Space] => 414
    [Until_Condition] => None
    [Until_Log_File] => 
    [Until_Log_Pos] => 0
    [Master_SSL_Allowed] => No
    [Master_SSL_CA_File] => 
    [Master_SSL_CA_Path] => 
    [Master_SSL_Cert] => 
    [Master_SSL_Cipher] => 
    [Master_SSL_Key] => 
    [Seconds_Behind_Master] => 0
    [Master_SSL_Verify_Server_Cert] => No
    [Last_IO_Errno] => 0
    [Last_IO_Error] => 
    [Last_SQL_Errno] => 0
    [Last_SQL_Error] => 
)
share|improve this question
3  
You have published your replication user's password within your question. As the REPLICATION SLAVE privilege allows access to your database data (the binlolgs), you should change this password ASAP. –  the-wabbit Jul 1 '12 at 11:04
    
thanks.. I missed it :) –  clarkk Jul 1 '12 at 12:06
add comment

2 Answers 2

up vote 4 down vote accepted

According to your slave status, replication is up and running correctly. What is likely to be broken are your expectations. MySQL replication is a one-way replication from master to slave without the verification of consistency on the slave. Rows changed on the slave by a third party will not get replicated back to the master but will obviously affect consistency of the slave's data.

In the most cases, you will not notice changed data rows on the slave without performing additional, manual checks - take a look at the Percona Toolkit (formerly Maatkit) for tools do facilitate this task.

You also should remove replicate_do_db from your configuration as this filtering option is probably not doing what you think it is. Other than that, it should work out of the box. If it doesn't, please post your SQL statements used to verify data replication.

share|improve this answer
    
ok.. but is the slave not replicated as soon as data is written to the master? –  clarkk Jul 1 '12 at 11:56
    
@clarkk You should remove replicate_do_db from your configuration as this filtering option is probably not doing what you think it is. Other than that, it should work out of the box. If it doesn't, please post your SQL statements used to verify data replication. –  the-wabbit Jul 1 '12 at 16:55
    
I was just about to post an answer to my own question.. found a link which describes the "problem" mysqlperformanceblog.com/2009/05/14/… –  clarkk Jul 1 '12 at 18:48
    
@clarkk i've linked the URL you are providing into my previous comment. Unfortunately, the Serverfault coloring template does not let links stand out enough so they go unnoticed sometimes. Aside from that, I have found the behavior described in the German localization of the MySQL documentation as well - unfortunately, the appropriate passage of text is not present in the English version. –  the-wabbit Jul 1 '12 at 21:07
    
funny coincidence :) ok, but thanks anyway.. Now I understand how my replication setup works –  clarkk Jul 1 '12 at 21:14
show 4 more comments

On my website I connect to the slave server and I insert some rows, but they do not appear on the master...

This will only happen with multi-master replication. In this kind of setup, both servers are masters. This is complicated and it's easy to make mistakes. You won't want to do multi-master for your first time.

So, with a normal master -> slave setup, you can't insert rows into the slave. The slave is read-only for you. The only way new data gets into the slave is via replicating it from the master.

... and vice versa.

If you inserted rows on the master and they didn't appear on the slave there is a problem. The slave status looks fine to me and there's no delay. I don't see anything that would cause it not to work unless you inserted into some other database than test_db.

share|improve this answer
add comment

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.