Shulou(Shulou.com)06/01 Report--

Write at the front:

The author uses the traditional master-slave environment, after the master database updates the records, the slave database can not synchronize the data. When checking the master-slave replication status from the slave database, Read_Master_Log_Pos and Exec_Master_Log_Pos are the same, the binlog O and SQL threads are normal, there is no master-slave delay, and there is no artificial delay update parameter. Both the master library binlog and the slave database relay log have corresponding update records. There is no replication-related error information from the library error log. If you are in the same situation as the author, then you may have encountered the "pit" of copying and filtering rules like the author.

Environment:

MySQL5.6 (MySQL5.7,MySQL8 has not personally tested it)

Scene reproduction:

Master configuration:

[mysqld]

Datadir = / home/data/mysql3306/

Port = 3306

Server_id = 1

Binlog_format = row

Log_bin = / home/data/mysql3306/binlog

SLave configuration:

[mysqld]

Datadir = / home/data/mysql3306/

Port = 3306

Binlog_format = row

Server_id=2

Relay_log = / home/data/mysql3306/relaylog

Replicate_do_db=edusoho_e,statis

Master authorized replication connection user:

Mysql > grant replication slave on *. * to repliter@'192.168.32.2' identified by PASSWORD'* 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

Query OK, 0 rows affected (0.01 sec)

Mysql > flush logs

Query OK, 0 rows affected (0.03 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | binlog.000004 | 120 | |

+-+

1 row in set (0.00 sec)

Slave enables data replication:

CHANGE MASTER TO MASTER_HOST='192.168.32.3',MASTER_USER='repliter',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=120

Query OK, 0 rows affected, 2 warnings (0.05 sec)

Mysql > start slave

Query OK, 0 rows affected (0.03 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: edusoho_e,statis

Master-slave replication status is normal!

Master changed the data:

Mysql > create database edusoho_e

Query OK, 1 row affected (0.00 sec)

Mysql > use edusoho_e

Database changed

CREATE TABLE `t1` (

`id`INT (11) UNSIGNED NOT NULL AUTO_INCREMENT

`xname`VARCHAR (20) NOT NULL DEFAULT''

`address`CHAR (20) NOT NULL DEFAULT''

`sex` TINYINT (1) NOT NULL DEFAULT'1'

`hobby` VARCHAR (30) NOT NULL DEFAULT''

`age`TINYINT (2) DEFAULT '18'

PRIMARY KEY (`id`)

KEY `idx_ name` (`xname`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

Mysql > INSERT INTO `edusoho_ e`.`t1` (`xname`, `address`, `hobby`) VALUES ('edusoho_e',' ldl', 'dba')

Query OK, 1 row affected (0.01sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | binlog.000004 | 882 | |

+-+

1 row in set (0.00 sec)

Master's binlog log is normal.

However, you can't see the newly created table and its data on the Slave host.

Slave:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

View the master-slave replication status:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.32.3

Master_User: repliter

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000004

Read_Master_Log_Pos: 882

Relay_Log_File: relaylog.000002

Relay_Log_Pos: 1042

Relay_Master_Log_File: binlog.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: edusoho_e,statis

Exec_Master_Log_Pos: 882

Seconds_Behind_Master: 0

SQL_Delay: 0

You will find that the Read_Master_Log_Pos O and SQL threads are normal; the values of Read_Master_Log_Pos and Exec_Master_Log_Pos are the same; the value of Seconds_Behind_Master is 0, which means there is no master-slave delay; the value of SQL_Delay is 0, which means there is no subjective setting for delay insertion; although the master-slave filtering rule is set, it is only copying the library. Is there something wrong with Slave's relay log and does not log the Master?

If you go to Slave to analyze the relay log log, you will find that there are corresponding Master logs.

[root@slave mysql3306] # mysqlbinlog-v-- base64-output=decode relaylog.000002

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/

/ *! 40019 SET @ @ session.max_insert_delayed_threads=0*/

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 190530 9:32:17 server id 2 end_log_pos 120 CRC32 0x35d47ba3 Start: binlog v 4, server v 5.6.16-log created 190530 9:32:17

# at 120

# 700101 8:00:00 server id 1 end_log_pos 0 CRC32 0x0166516e Rotate to binlog.000004 pos: 120

# at 164

# 190530 9:29:02 server id 1 end_log_pos 0 CRC32 0xfea4f75a Start: binlog v 4, server v 5.6.16-log created 190530 9:29:02

# at 280

# 190530 9:35:18 server id 1 end_log_pos 229 CRC32 0x6b0d2047 Query thread_id=2 exec_time=0 error_code=0

SET timestamp 1559180118Compact /

SET @ @ session.pseudoplastics thread readreadable cards idler / session.

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards modewords 1073741824Universe

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C utf8 * /! * /

SET @ @ session. Session. Session setting setting clientmakers 33 minutes. Session. Collationalization connections 33 minutes.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

Create database edusoho_e

/ *! * /

# at 389

# 190530 9:35:31 server id 1 end_log_pos 653 CRC32 0x1268f754 Query thread_id=2 exec_time=0 error_code=0

Use `edusoho_ e` / *! * /

SET timestamp 1559180131

CREATE TABLE `t1` (

`id`INT (11) UNSIGNED NOT NULL AUTO_INCREMENT

`xname`VARCHAR (20) NOT NULL DEFAULT''

`address`CHAR (20) NOT NULL DEFAULT''

`sex` TINYINT (1) NOT NULL DEFAULT'1'

`hobby` VARCHAR (30) NOT NULL DEFAULT''

`age`TINYINT (2) DEFAULT '18'

PRIMARY KEY (`id`)

KEY `idx_ name` (`xname`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

/ *! * /

# at 813

# 190530 9:35:41 server id 1 end_log_pos 730 CRC32 0x20610ab1 Query thread_id=2 exec_time=0 error_code=0

SET timestamp 1559180141

BEGIN

/ *! * /

# at 890

# 190530 9:35:41 server id 1 end_log_pos 791CRC32 0xc2edbad8 Table_map: `edusoho_ e`.`t1` mapped to number 540

# at 951

# 190530 9:35:41 server id 1 end_log_pos 851 CRC32 0xaa57d74f Write_rows: table id 540 flags: STMT_END_F

# INSERT INTO `edusoho_ e`.`t1`

# SET

# @ 1room1

# @ 2roomedusohooroome'

# @ 3 roommates ldl'

# @ 4room1

# @ 5roomdba'

# @ 6x18

# at 1011

# 190530 9:35:41 server id 1 end_log_pos 882 CRC32 0x7de64644 Xid = 1350

Com _ MIT _ blank /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

Check the error log of Slave and do not see any information related to replication error

So here's the problem, this may be an encounter with BUG! The author also forgot whether some big shot said or read in a blog that if Slave is configured with replicate_do_db filtering rules, if it is written in the following form:

Replicate_do_db=edusoho_e,statis may encounter BUG, which needs to be written separately

Replicate_do_db=edusoho_e

Replicate_do_db=statis

Restart Slave to verify the conjecture

Master:

Mysql > flush logs

Query OK, 0 rows affected (0.44 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | binlog.000005 | 120 | |

+-+

1 row in set (0.00 sec)

SET @ @ session.sql_log_bin=0

DROP DATABASE `edusoho_ e`

Mysql > create database edusoho_e

Query OK, 1 row affected (0.00 sec)

Mysql > use edusoho_e

Database changed

CREATE TABLE `t1` (

`id`INT (11) UNSIGNED NOT NULL AUTO_INCREMENT

`xname`VARCHAR (20) NOT NULL DEFAULT''

`address`CHAR (20) NOT NULL DEFAULT''

`sex` TINYINT (1) NOT NULL DEFAULT'1'

`hobby` VARCHAR (30) NOT NULL DEFAULT''

`age`TINYINT (2) DEFAULT '18'

PRIMARY KEY (`id`)

KEY `idx_ name` (`xname`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

Mysql > INSERT INTO `edusoho_ e`.`t1` (`xname`, `address`, `hobby`) VALUES ('edusoho_e',' ldl', 'dba')

Query OK, 1 row affected (0.01sec)

Slave:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | edusoho_e |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > select * from edusoho_e.t1

+-+ +

| | id | xname | address | sex | hobby | age | |

+-+ +

| | 1 | edusoho_e | ldl | 1 | dba | 18 |

+-+ +

1 row in set (0.00 sec)

You will find that the newly created tables and data have been synchronized, indicating that it is indeed a "pit" of replicate_do_db filtering rules.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.