Received a friend’s request to delete the datadir directory of the mysql database. The database is currently running, but many operations can no longer be performed normally.
The database can log in, but no business database can be seen, you can query it in conjunction with the table name
[root@hy-db-xff-s-110 mysql3306]# mysql -uroot -ptSQghoV^J1GE^U8*wPElImv5 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 443214 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> select count(1) from xifenfei.orders; +----------+ | count(1) | +----------+ | 16451326 | +----------+ 1 row in set (4.17 sec)
Data cannot be exported (into outfile does not work due to the default of the secure-file-priv parameter)
mysql> select * from xifenfei.orders into outfile '/bakcup/orders_new.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement [root@hy-db-cps-s-110 fd]# mysqldump -uroot -pwww.xifenfei.com xifenfei orders >/linshi/1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. mysqldump: Got error: 1049: Unknown database 'xifenfei' when selecting the database
Because mysql has no crash, the related files already exist (not really deleted)
Use this method to restore the relevant data files to the new server, and then try to start the database. It is found that the database cannot be started normally. Some files are lost. Finally, the individual ibds are processed separately.[MySQL Recovery]mysql ibd file recoveryTo achieve the recovery of most data, for some data that cannot be recovered by this method, if the disk level is not covered, you can first recover according to the os level method, refer to:extundeleteRecover Linux deleted files,If this method also fails to recover normally, you can try database disk fragmentation level recovery: MySQL drop database recovery (recovery method also applies to MySQL drop table, delete, truncate table)
