mysql replication 1x master, 1x slave
- by clarkk
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] =>
)
