14

While fixing a ibdata/log problem, I accidentally dropped my performance_schema database, I'd like to create a new one.

mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+---------+
| Variable_name                                     | Value   |
+---------------------------------------------------+---------+
| performance_schema                                | ON      |
| performance_schema_events_waits_history_long_size | 10000   |
| performance_schema_events_waits_history_size      | 10      |
| performance_schema_max_cond_classes               | 80      |
| performance_schema_max_cond_instances             | 1000    |
| performance_schema_max_file_classes               | 50      |
| performance_schema_max_file_handles               | 32768   |
| performance_schema_max_file_instances             | 10000   |
| performance_schema_max_mutex_classes              | 200     |
| performance_schema_max_mutex_instances            | 1000000 |
| performance_schema_max_rwlock_classes             | 30      |
| performance_schema_max_rwlock_instances           | 1000000 |
| performance_schema_max_table_handles              | 100000  |
| performance_schema_max_table_instances            | 50000   |
| performance_schema_max_thread_classes             | 50      |
| performance_schema_max_thread_instances           | 1000    |
+---------------------------------------------------+---------+
16 rows in set (0.06 sec)

Those variables appear to be fine to me.

The following question asks the same thing, however the user concludes that they were able to create it by following documentation that I could not find such instructions in.

mysql: deleted performance_schema, is it a problem?

Any thoughts?

1
  • How did you do that, I want to do exactly that!
    – AaA
    Jun 21, 2020 at 14:23

2 Answers 2

20

Tables in the performance_schema database are a collection of views and temporary tables that do not store data permanently. The mysql_upgrade command will restore the performance_schema database

From the shell

mysql_upgrade --user=root --password=password
2
  • 2
    Restart the mysql service after doing this! It worked only after the restart for me.
    – caesarsol
    Nov 19, 2015 at 19:52
  • And add the --force option
    – Tominator
    Sep 22, 2021 at 9:30
-2

It alludes to DROP DATABASE being recoverable, but only in odd conditions i'm not familiar with http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

According to Docs, binlogs are just a sequence of commands executed based on a given reference point. So that when you did "DROP DATABASE", instead of going "Oh, hes droppping the database, we should back up now just in case" it merely wrote a "DROP DATABASE" to the last binlog. Recovery is not as simple as playing the tape backwards.

What you need to do is recover the database from a last-known-good, and apply the binlogs that happened between that recover point and the DROP command.

http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html

How one determines which binlogs to use tho, unclear.

There is nothing better than having full file system backups. And you should at least have these to fall back to.

2
  • 1
    I'm not interested in recovering the data that was there, only that the performance metrics can be collected/stored again. Craig Efrein's solution worked for this purpose.
    – Juniper X
    Jan 20, 2014 at 15:47
  • -1. The performance schema data is not replicated, there is nothing in the binlog to recover (and it does not exist in 5.0 anyway).
    – Marc Alff
    Aug 17, 2017 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.