All frm files in the instance disappear.

Today, the frm file in the MySQL instance disappears for no reason, while the ibd file still exists. Finally, the problem was located at night. The culprit was pt-stalk. to put it short, when using pt-stalk, it is very important to have an option. do not specify dest as the MySQL data directory or another important directory, because the files in the specified directory

Today, the frm file in the MySQL instance disappears for no reason, while the ibd file still exists. Finally, the problem was located at night. The culprit was pt-stalk. to put it short, when using pt-stalk, it is very important to have an option. do not specify dest as the MySQL data directory or another important directory, because the files in the specified directory

Today, the frm file in the MySQL instance disappears for no reason, while the ibd file still exists. Finally, the problem was located at night. The culprit was pt-stalk.

To put it short, when using pt-stalk, it is very important to have an option. do not specify-dest as the MySQL data directory or another important directory, because the files in the specified directory may be deleted.

The pt-stalk command I used today runs on three instances. The command for the first instance is:

pt-stalk --log /u01/mysql/pt-stalk.log --dest /u01/mysql --pid /u01/mysql/pt-stalk.pid --daemonize --collect-gdb --sleep 1 --iterations 20 --function status --variable Slave_trx_pending --threshold 100 -- --user=root --port=3306 --host=127.0.0.1

The sad discovery of replication is interrupted because no table is found. Log on and check that there is only a database and no tables!
Let's take a look at the/u01/mysql/data/db. There are only ibd files, and all frm files are gone!

In the afternoon, I only upgraded mysqld and restarted the first instance. I felt that it had nothing to do with the patches in the afternoon.
In this case, we suspect that the script is used, because thousands of frm files, including mysql/bin and mysql/scripts, are all gone.

Instead, you have to redo it. Copy the frm file and the corresponding bin from the master database to the corresponding directory and restart it.

At more than 10 o'clock in the evening, I found that all my colleagues in instance 2 and instance 3 were killed and found the same problem. Think about it first.

Think carefully. In addition to upgrading mysqld in the afternoon, pt-stalk is used. Pt-stalk has been used before, and there is no problem.
All three instances are monitored, but the fourth instance is not monitored and running properly. Therefore, I can't help but doubt pt-stalk.

First, find the suspicious code in pt-stalk:

# Delete collect files which more than --retention-time days old.
find "$dir" -type f -mtime +$retention_time -exec rm -f '{}' \;

Corresponds to the pseudo-code of pt-stalk To clean up old data:

while true; do
if --variable from --function is greater than --threshold; then
observations++
if observations is greater than --cycles; then
capture diagnostics for --run-time seconds
exit if --iterations is exceeded
sleep for --sleep seconds
done
done
clean up data that's older than --retention-time
sleep for --interval seconds
done

The default retention-time is 30 days. That is to say, data from the past 30 days may be cleared.

Find the corresponding code, change rm to echo, and run it in your testing environment. This is indeed the problem:

### Not running sudo For The First Time ###
Find: '/u01/ps5518/mysql-test/var/install. db/mtr': Permission denied
Find: '/u01/ps5518/mysql-test/var/install. db/performance_schema': Permission denied
Rm: cannot remove '/u01/ps5518/mysql-test/var/install. db/ibdata1': Permission denied
Rm: cannot remove '/u01/ps5518/mysql-test/var/install. db/ib_logfile1': Permission denied
Rm: cannot remove '/u01/ps5518/mysql-test/var/install. db/ib_logfile0': Permission denied
Rm: cannot remove '/u01/ps5518/mysql-test/var/install. db/mysql/time_zone_transition.myd': Permission denied
Rm: cannot remove '/u01/ps5518/mysql-test/var/install. db/mysql/user. myi': Permission denied
Rm: cannot remove '/u01/ps5518/mysql-test/var/install. db/mysql/time_zone.myi': Permission denied
Rm: cannot remove '/u01/ps5518/mysql-test/var/install. db/mysql/help_relation.frm': Permission denied

### Replace rm with long echo for the second sudo run ###
Find: '/u01/ps5518/data/sbtest': Permission denied
Find: '/u01/ps5518/data/test': Permission denied

The problem is verified here.

Fortunately, this slave database is the core transaction database. Fortunately, service access is not provided, otherwise it is definitely a P0 fault.

Original article address: All frm files in the instance are behind the scenes and thanks to the original author for sharing them.