mysql replication 1x master, 1x slave

Question

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] => 
)

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 01 '12 at 11:04

the-wabbit · Accepted Answer · 2012-07-01T21:22:52.273

4

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.

edited Jul 01 '12 at 21:22

answered Jul 01 '12 at 11:03

the-wabbit

40,319
13
105
169

ok.. but is the slave not replicated as soon as data is written to the master? – clarkk Jul 01 '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](http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/). 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 01 '12 at 16:55
I was just about to post an answer to my own question.. found a link which describes the "problem" http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/ – clarkk Jul 01 '12 at 18:48
@clarkk i've linked the URL you are providing into my [previous comment](http://serverfault.com/questions/403850/mysql-replication-1x-master-1x-slave/403853#comment422267_403853). 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 01 '12 at 21:07
funny coincidence :) ok, but thanks anyway.. Now I understand how my replication setup works – clarkk Jul 01 '12 at 21:14
maybe you should put the link in the answer :) – clarkk Jul 01 '12 at 21:15
@clarkk See? Does not help even a bit :) – the-wabbit Jul 01 '12 at 21:28
I got one question about Percona.. I need to install it on the slave right? If I want to use it to verify the replicated data? – clarkk Jul 02 '12 at 20:42
@clarkk it does not matter where you install pt-table-checksum. You just run against the master, it figures out the slave config automatically. Check the docs at http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html for further information. – the-wabbit Jul 03 '12 at 22:49

score 2 · Answer 2 · answered Jul 01 '12 at 11:06

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.

mysql replication 1x master, 1x slave

2 Answers2