[译] MySQL-恢复被删除的Performance Schema 数据库

原创晨辉 2022-03-15

4809

原文地址：https://blog.sqlauthority.com/2021/12/23/mysql-recover-dropped-performance-schema-database/
原文作者：Pinal Dave
复制

今天早些时候我在一个在线论坛上看到有用户在找如何恢复被删除的Performance Schema 数据库的方法。

老实说，恢复删掉的performance schema数据库非常容易，下面就是操作命令：
打开操作系统命令行，执行如下命令：

mysql_upgrade --user=root --password=password --force
复制

一旦你的操作成功了，就需要重启MySQL 服务。重启完成，你就有新的Performance Schema 数据库了。不用担心，你的数据是不会存在这个新的数据库的（译者注：这里指Performance Schema 数据库），MySQL服务只是用它来存放性能相关数据。

译者注

既然这么简单，那就给大家实操一把，如下操作：

删除前检查下performance_schema 是否正常

[root@3306][(none)]>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bp_metadata        |
| db_gbk             |
| dbaas              |
| ggmgr              |
| mysql              |
| performance_schema |
| pythonDB           |
| sqm                |
| sys                |
| sysbench           |
| ticle              |
| xhy                |
| zoramon            |
+--------------------+
14 rows in set (0.13 sec)
[root@3306][(none)]>>use performance_schema
Database changed
[root@3306][performance_schema]>>show tables;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
| events_stages_current                                |
| events_stages_history                                |
| events_stages_history_long                           |
| events_stages_summary_by_account_by_event_name       |
| events_stages_summary_by_host_by_event_name          |
| events_stages_summary_by_thread_by_event_name        |
| events_stages_summary_by_user_by_event_name          |
。。。
[root@3306][performance_schema]>>select count(*) from accounts;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.10 sec)

[root@3306][performance_schema]>>select count(*) from threads;
+----------+
| count(*) |
+----------+
|       51 |
+----------+
1 row in set (0.03 sec)

[root@3306][performance_schema]>>show variables like 'datadir';
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| datadir       | /u01/mysql/mysql3306/data/ |
+---------------+----------------------------+
1 row in set (0.18 sec)

复制

模拟删除（将移动performance_schema 到/tmp下）

[root@ora11g1 ~]# cd  /u01/mysql/mysql3306/data
[root@ora11g1 data]# mv performance_schema /tmp/
[root@ora11g1 data]# ls performance_schema
ls: cannot access performance_schema: No such file or directory
复制

再次查看数据库信息，performance_schema数据库已经不见了

[root@3306][(none)]>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bp_metadata        |
| db_gbk             |
| dbaas              |
| ggmgr              |
| mysql              |
| pythonDB           |
| sqm                |
| sys                |
| sysbench           |
| ticle              |
| xhy                |
| zoramon            |
+--------------------+
13 rows in set (0.03 sec)

[root@3306][(none)]>>use [performance_schema;
ERROR 1049 (42000): Unknown database '[performance_schema'
复制

进行恢复操作：
4.1）执行升级：mysql_upgrade --user=root --password=123456 --force

[root@ora11g1 data]#  mysql_upgrade --user=root --password=123456 --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
...
zoramon.tbl_tmp_sql_text                           OK
Upgrade process completed successfully.
Checking if update is needed.

复制

4.2）重启数据库，恢复完成

可以看到即使不重启数据库performance_schema也已经存在了，但查询具体表时提示 1682错误，表结构发生了改变。
[root@3306][(none)]>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
...
| mysql              |
| performance_schema |
| pythonDB           |
| sqm                |
| sys                |
| sysbench           |
| ticle              |
| xhy                |
| zoramon            |
+--------------------+
14 rows in set (0.04 sec)

[root@3306][(none)]>>use performance_schema
Database changed
[root@3306][performance_schema]>>show tables;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
...
| users                                                |
| variables_by_thread                                  |
+------------------------------------------------------+
87 rows in set (0.00 sec)

[root@3306][performance_schema]>>select count(*) from threads;
ERROR 1682 (HY000): Native table 'performance_schema'.'threads' has the wrong structure

进行重启操作：可以看到已经可以正常查询performance_schema下的表了
[root@3306][performance_schema]>>exit
Bye
(base) [root@ora11g1 ~]# service mysqld restart
Shutting down MySQL....                                    [  OK  ]
Starting MySQL...                                          [  OK  ]
(base) [root@ora11g1 ~]# mysql -uroot -p123456 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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.

[root@3306][(none)]>>use performance_schema
Database changed
[root@3306][performance_schema]>>select count(*) from threads;
+----------+
| count(*) |
+----------+
|       51 |
+----------+
1 row in set (0.00 sec)

[root@3306][performance_schema]>>
复制