mysql replication 1x master, 1x slave

Posted by clarkk on Server Fault See other posts from Server Fault or by clarkk
Published on 2012-07-01T10:33:24Z Indexed on 2012/07/01 15:18 UTC
Read the original article Hit count: 416

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

© Server Fault or respective owner

Related posts about mysql

Related posts about replication