0

I'm practicing mysql replication with 2 Centos 6.9 64bit virtual machine. Setting process was fine but after I insert data to master server, Anythings happens in slave.

MASTER SETTING

mysql.cnf

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0


log-bin=mysql-bin
binlog-do-db=dbtest
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysql> show master status

mysql-bin.000002 |    13069 | dbtest

mysql> show processlist

 145 | root | 192.168.1.11:45148 | NULL       | Binlog Dump |  810 | Has sent all binlog to slave; waiting for binlog to be updated | NULL

SLAVE SETTING

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

#Replication
server-id=2
replicate-do-db=dbtest
master-host=192.168.1.10
master-user=root
master-password=master_password

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysql> show slave status

Slave_IO_State : Waiting for master to send event
Master_Host : 192.168.1.10
Master_User : root
Master_Port : 3306
Connect_Retry : 60
Master_Log_File : mysql-bin.000002
Read_Master_Log_Pos : 12954 
Relay_Log_File : mysqld-relay-bin.000017 
Relay_Log_Pos : 251
Relay_Master_Log_File :  mysql-bin.000002 
Slave_IO_Running : Yes
Slave_SQL_Running : Yes
Replicate_Do_DB : dbtest
Last_Errno : 0
Skip_Counter : 0
Exec_Master_Log_Pos : 12954
Relay_Log_Space : 552
Until_Condition : None
Until_Log_Pos : 0
Master_SSL_Allowed : No
Seconds_Behind_Master : 0
Master_SSL_Verify_Server_Cert : No
Last_IO_Errno : 0
Last_SQL_Errno : 0

mysql> show processlist

 5 | system user |                 | NULL       | Connect | 1251 | Waiting for master to send event | NULL

I follow this guide. Thanks for your help.

Improve this question
4
  • Replication looks fine. Apart from small differences in master log pos. Using replication filters has effects on how you. Is dbtest the default database when you did the INSERT? Avoid replication filters unless necessary, and if not, read the documentation very carefully.
    – danblack
    Sep 29, 2018 at 8:10
  • No. dbtest is created. And I have a small question in master processlist. Why do master connect to slave in port 45148. And this port always change when I check. Something wrongs in it?
    – Thế Hải Nguyễn
    Sep 30, 2018 at 2:47
  • If you INSERT INTO dbtest.table..., and dbtest isn't the default database, it won't be replicationed. The slave connects to the master on destination port 3306. The source port, the 45148 is usually autoallocated. The tcp connection on the master will look like its connected to the port `45148'. This is quite normal.
    – danblack
    Sep 30, 2018 at 2:52
  • Oh, something I don't understand here. I think default database just in a session when I use use comment, when I connect to mysql by myself. But in the process of replicating automatically, how do I configure it to be the default? I'm new here, sorry ;))
    – Thế Hải Nguyễn
    Sep 30, 2018 at 16:43

1 Answer 1

Reset to default
0

The default database is from use db or from the database listed when you connect.

Replication filtering has a large list of rules in the manual: Evaluation of Database-Level Replication and Binary Logging Options is the full text. Only use it when you need it, it will be easier that way.

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .