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.
dbtestthe default database when you did theINSERT? Avoid replication filters unless necessary, and if not, read the documentation very carefully.dbtestis created. And I have a small question in master processlist. Why do master connect to slave in port45148. And this port always change when I check. Something wrongs in it?INSERT INTO dbtest.table..., anddbtestisn'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.usecomment, 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 ;))