原文地址: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]>>
最后修改時間:2022-03-16 09:19:42
「喜歡這篇文章,您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容,轉載時必須標注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容,歡迎發送郵件至:contact@modb.pro進行舉報,并提供相關證據,一經查實,墨天輪將立刻刪除相關內容。




