Performance_schema recreation

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hello,

I had some issue on my two node percona 5.7 cluster and after the restart I started to get issues like performance_schema.variables has structure issue. I tried to recreate the the performance schema by disabling and enabling performance_schema in my.cnf and remove the folder performance_schema but it did not work. Now i want to recreate performance_schema to make sure the structure in performance_schema DB is recreated without issues. Please give your suggestion.

Thanks,
Shahid.

2

quick thinking… did you recently upgrade and missed mysql_upgrade?

3

Kedarpercona,

I think your are right… there was a minor update on the system that yum initiated. Can you tell me will my tables be locked if I run mysql_upgrade ? I have large database and cant keep it down for long time.

Thanks.

4

I’d not really run other things while i’m performing mysql_upgrdae. Perform that and restart as it will change P_S / I_S tables

5

I will need to run mysql_upgrade on one of my data nodes or I will need to run it on both nodes ? and do i have to stop one node while i am running mysql_upgrade on other machine ?

6

@shahidbashir7861,
You can run mysql_upgrade on both nodes, it will not hurt anything if it runs twice. You do not need to stop any nodes, but you will need to restart each node after running mysql_upgrade.

1 Like
7

Mattthewb, Thanks for the response. I try plan accordingly.

I am trying to take the dump of all the DBs before I do mysql_upgrade but I am getting the following error.

mysqldump: Couldn’t execute ‘SELECT COUNT(*) FROM performance_schema.session_variables WHERE VARIABLE_NAME LIKE ‘rocksdb_skip_fill_cache’’: Native table ‘performance_schema’.‘session_variables’ has the wrong structure (1682)

Is there some workaround for this ? because i need to do backups before I could run mysql_upgrade.

Thanks.

8

Hi @shahidbashir7861

You don’t need to dump those databases… I_S/P_S/sys…

Thanks,
K

9

Hello Kedar,

I am not taking dump of information_schema or performance_schema. The error shows up when I try to take dump of any database on the server using mysqldump command. Also, the file level back using innobackupex is also failing as it also tries to read some variables from peformance_schema before it starts the backup.

Thanks,
Shahid.

10

@shahidbashir7861,
You need to run mysql_upgrade first. You can’t do anything else, backup included, until you do this. mysql_upgrade does not modify any data or touch any non-system tables. You are safe to run this command now.

1 Like
11

Hello Matthewb,

mysql_upgrade worked without any issue on my system and all the operations were recovered. Thanks for the help

One thing, I noticed that mysql_upgrade kind of checking all the tables in all the DBs when i was testing the command on some of our Dev servers. As you said it wont touch any non system tables, I just wanted to let you know and check why it would do so.

Thanks,
Shahid.

12

Yes, it checks non-system tables for issues but it does not rebuild them. That’s what I meant. System tables will automatically get rebuilt/fixed, but non-system tables will still get checked. It is a non-locking/non-blocking check; nothing to worry about.

1 Like