| Bug #78772 | Gtids-based replication, all tables of performance_schema will disappear | ||
|---|---|---|---|
| Submitted: | 9 Oct 2015 7:28 | Modified: | 14 Oct 2015 9:30 |
| Reporter: | feijie li | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | mysql5.7.8-rc | OS: | Red Hat |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | replication performance_schema gtids | ||
[9 Oct 2015 7:49]
feijie li
When create a user for replication,you must add a select privilege on performance_schema. For example, mysql> CREATE USER 'repl'@'192.168.%' IDENTIFIED BY 'slavepass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%'; mysql> GRANT SELECT ON PERFORMANCE_SCHEMA.* TO 'repl'@'192.168.%'; Otherwise ,replication will fails with a error. mysql> show slave status\G Last_IO_Errno: 1142 Last_IO_Error: The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master. Error: SELECT command denied to user 'rsandbox'@'localhost' for table 'global_variables' For details see the following link. http://planet.mysql.com/entry/?id=5991630
[13 Oct 2015 11:14]
MySQL Verification Team
Hello feijie li, Thank you for the report. I'm not seeing this issue anymore on latest 5.7 builds(used 5.7.10 daily build - with master and slave using 5.7.10 build) and this is most likely fixed after Bug #77732. Thank you for your interest in MySQL. ### // 5.7.10 - slave without gtid etc [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S run/slave.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.10-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(1) from information_schema.tables where table_schema='performance_schema'; +----------+ | count(1) | +----------+ | 87 | +----------+ 1 row in set (0.01 sec) - slave with gtid enabled -- enable gtid - follow https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-howto.html mysql> CHANGE MASTER TO -> MASTER_HOST='127.0.0.1', -> MASTER_PORT=15000, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 762 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 417 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_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: 762 Relay_Log_Space: 664 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 7f1da03f-7193-11e5-9a42-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/mysql-advanced-5.7.10/slave/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 7f1da03f-7193-11e5-9a42-0010e05f3e06:1-4, b048034a-6e50-11e5-8f9f-0010e05f3e06:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: 1 row in set (0.00 sec) mysql> select count(1) from information_schema.tables where table_schema='performance_schema'; +----------+ | count(1) | +----------+ | 87 | +----------+ 1 row in set (0.00 sec) Thanks, Umesh
[14 Oct 2015 9:30]
feijie li
Hello Umesh, Thanks for your reply! I recreate two new port（3306、3309）,and 3309 be master.Problems still exist. When i check the binary log,i found that there are some sql in master's binary log,such as"ROP DATABASE IF EXISTS performance_schema","CREATE DATABASE performance_schema character set utf8","CREATE TABLE performance_schema.cond_instances(NAME VARCHAR(128) not null,OBJECT_INSTANCE_BEGIN BIGINT unsigned not null)ENGINE=PERFORMANCE_SCHEMA",and so on. But there is no create table sql in slave's binary log after slave execute "start slave" to build master-slave. So ，i think this why mysql slave will loss tables of performance_schema database. As you said,this issue may fixed after Bug #77732. So,you do not seeing this issue anymore on 5.7.10. Now i can only download mysql5.7.8. So i will wait for the next version. Here are some logs. port:3309 role:master binnary log : ######################################################################## SET @@SESSION.GTID_NEXT= '5f01a734-7249-11e5-bd35-005056ab35ee:40'/*!*/; # at 30508 #151014 15:58:38 server id 51143309 end_log_pos 30631 CRC32 0x5055ad0c Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1444809518/*!*/; DROP DATABASE IF EXISTS performance_schema /*!*/; # at 30631 #151014 15:58:38 server id 51143309 end_log_pos 30696 CRC32 0xa45df45e GTID last_committed=40 sequence_number=41 SET @@SESSION.GTID_NEXT= '5f01a734-7249-11e5-bd35-005056ab35ee:41'/*!*/; # at 30696 #151014 15:58:38 server id 51143309 end_log_pos 30851 CRC32 0xccc1b7e8 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1444809518/*!*/; CREATE DATABASE performance_schema character set utf8 /*!*/; # at 30851 #151014 15:58:38 server id 51143309 end_log_pos 30916 CRC32 0x92a3e4fa GTID last_committed=41 sequence_number=42 SET @@SESSION.GTID_NEXT= '5f01a734-7249-11e5-bd35-005056ab35ee:42'/*!*/; # at 30916 #151014 15:58:38 server id 51143309 end_log_pos 31151 CRC32 0x907ef718 Query thread_id=1 exec_time=0 error_code=0 use `mysql`/*!*/; SET TIMESTAMP=1444809518/*!*/; CREATE TABLE performance_schema.cond_instances(NAME VARCHAR(128) not null,OBJECT_INSTANCE_BEGIN BIGINT unsigned not null)ENGINE=PERFORMANCE_SCHEMA /*!*/; # at 31151 #151014 15:58:38 server id 51143309 end_log_pos 31216 CRC32 0xfbf13b4b GTID last_committed=42 sequence_number=43 SET @@SESSION.GTID_NEXT= '5f01a734-7249-11e5-bd35-005056ab35ee:43'/*!*/; # at 31216 #151014 15:58:38 server id 51143309 end_log_pos 31961 CRC32 0xf7a5140f Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1444809518/*!*/; CREATE TABLE performance_schema.events_waits_current(THREAD_ID BIGINT unsigned not null,EVENT_ID BIGINT unsigned not null,END_EVENT_ID BIGINT unsigned,EVENT_NAME VARCHAR(128) not null,SOURCE VARCHAR(64),TIMER_START BIGINT unsigned,TIMER_END BIGINT unsigned,TIMER_WAIT BIGINT unsigned,SPINS INTEGER unsigned,OBJECT_SCHEMA VARCHAR(64),OBJECT_NAME VARCHAR(512),INDEX_NAME VARCHAR(64),OBJECT_TYPE VARCHAR(64),OBJECT_INSTANCE_BEGIN BIGINT unsigned not null,NESTING_EVENT_ID BIGINT unsigned,NESTING_EVENT_TYPE ENUM('TRANSACTION', 'STATEMENT', 'STAGE', 'WAIT'),OPERATION VARCHAR(32) not null,NUMBER_OF_BYTES BIGINT,FLAGS INTEGER unsigned)ENGINE=PERFORMANCE_SCHEMA /*!*/; ######################################################################## port:3306 role:slave binnary log : ######################################################################## # at 9975 #151014 15:58:38 server id 51143309 end_log_pos 10119 CRC32 0x5de7d747 Query thread_id=1 exec_time=579 error_code=0 SET TIMESTAMP=1444809518/*!*/; DROP DATABASE IF EXISTS performance_schema /*!*/; # at 10119 #151014 15:58:38 server id 51143309 end_log_pos 10184 CRC32 0x441aa9ee GTID last_committed=47 sequence_number=48 SET @@SESSION.GTID_NEXT= '5f01a734-7249-11e5-bd35-005056ab35ee:41'/*!*/; # at 10184 #151014 15:58:38 server id 51143309 end_log_pos 10339 CRC32 0x3df82d2a Query thread_id=1 exec_time=579 error_code=0 SET TIMESTAMP=1444809518/*!*/; CREATE DATABASE performance_schema character set utf8 /*!*/; # at 10339 #151014 15:58:38 server id 51143309 end_log_pos 10404 CRC32 0x9ae6754b GTID last_committed=48 sequence_number=49 SET @@SESSION.GTID_NEXT= '5f01a734-7249-11e5-bd35-005056ab35ee:42'/*!*/; # at 10404 #151014 15:58:38 server id 51143309 end_log_pos 10477 CRC32 0x4d3bfe75 Query thread_id=1 exec_time=579 error_code=0 SET TIMESTAMP=1444809518/*!*/; BEGIN /*!*/; # at 10477 #151014 15:58:38 server id 51143309 end_log_pos 10551 CRC32 0x79518ea7 Query thread_id=1 exec_time=579 error_code=0 SET TIMESTAMP=1444809518/*!*/; COMMIT /*!*/; # at 10551 #151014 15:58:38 server id 51143309 end_log_pos 10616 CRC32 0x6eb06f1a GTID last_committed=49 sequence_number=50 SET @@SESSION.GTID_NEXT= '5f01a734-7249-11e5-bd35-005056ab35ee:43'/*!*/; # at 10616 #151014 15:58:38 server id 51143309 end_log_pos 10689 CRC32 0xf184eae3 Query thread_id=1 exec_time=579 error_code=0 SET TIMESTAMP=1444809518/*!*/; BEGIN /*!*/; # at 10689 #151014 15:58:38 server id 51143309 end_log_pos 10763 CRC32 0x0a77006d Query thread_id=1 exec_time=579 error_code=0 SET TIMESTAMP=1444809518/*!*/; COMMIT /*!*/; ######################################################################## Thanks, feijie
Description: I built three new ports(3301,3302,3303) in the same server,and port 3301 be master,others be slave. Port 3302 use gtids-based replication to built master-slave to port 3301.All tables of performance_schema will disappear when use "start slave" to make master-slave replication work. Port 3303 use file-based replication to built master-slave to port 3301.Don't have the above problem. How to repeat: mysql> select @@version; +--------------+ | @@version | +--------------+ | 5.7.8-rc-log | +--------------+ 1 row in set (0.00 sec) 1） port 3302 mysql> select count(1) from information_schema.tables where table_schema='performance_schema'; +----------+ | count(1) | +----------+ | 87 | +----------+ 1 row in set (0.02 sec) 2） Gtid-based replication mysql> change master to -> master_host = '192.168.5.114', -> master_port = 3301, -> master_user = 'repl', -> master_password = 'slavepass', -> master_auto_position = 1; Query OK, 0 rows affected, 2 warnings (0.24 sec) mysql> select count(1) from information_schema.tables where table_schema='performance_schema'; +----------+ | count(1) | +----------+ | 87 | +----------+ 1 row in set (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> select count(1) from information_schema.tables where table_schema='performance_schema'; +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.5.114 Master_User: repl Master_Port: 3301 Connect_Retry: 60 Master_Log_File: mysql3301.000002 Read_Master_Log_Pos: 1746 Relay_Log_File: mysql3302-relay-bin.000003 Relay_Log_Pos: 1959 Relay_Master_Log_File: mysql3301.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,test 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: 1746 Relay_Log_Space: 102589 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 51143301 Master_UUID: b27a9fec-6e55-11e5-a57c-005056ab35ee Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: b27a9fec-6e55-11e5-a57c-005056ab35ee:1-138 Executed_Gtid_Set: 133a1315-6e56-11e5-afb5-005056ab35ee:1-138, b27a9fec-6e55-11e5-a57c-005056ab35ee:1-138 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: 1 row in set (0.00 sec) ERROR: No query specified 3） file-based replication port 3301 mysql> show master status\G; *************************** 1. row *************************** File: mysql3301.000002 Position: 1746 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: b27a9fec-6e55-11e5-a57c-005056ab35ee:1-138 1 row in set (0.02 sec) ERROR: No query specified mysql> port 3303 mysql> select count(1) from information_schema.tables where table_schema='performance_schema'; +----------+ | count(1) | +----------+ | 87 | +----------+ 1 row in set (0.00 sec) mysql> change master to -> master_host = '192.168.5.114', -> master_port = 3301, -> master_user = 'repl', -> master_password = 'slavepass', -> master_log_file = 'mysql3301.000002', -> master_log_pos = 1746; Query OK, 0 rows affected, 2 warnings (0.26 sec) mysql> select count(1) from information_schema.tables where table_schema='performance_schema'; +----------+ | count(1) | +----------+ | 87 | +----------+ 1 row in set (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> select count(1) from information_schema.tables where table_schema='performance_schema'; +----------+ | count(1) | +----------+ | 87 | +----------+ 1 row in set (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.5.114 Master_User: repl Master_Port: 3301 Connect_Retry: 60 Master_Log_File: mysql3301.000002 Read_Master_Log_Pos: 1746 Relay_Log_File: mysql3303-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql3301.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,test 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: 1746 Relay_Log_Space: 531 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 51143301 Master_UUID: b27a9fec-6e55-11e5-a57c-005056ab35ee Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 57734620-6e4c-11e5-8e99-005056ab35ee:1-136 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 1 row in set (0.00 sec) ERROR: No query specified Suggested fix: please check