I dropped MySQL's `performance_schema` database, how can I create it?

Question

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?

Answer 1

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

Answer 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.