A colleague reported that a certain MySQL database under test accidentally deleted the ibdata1 file, causing the database to fail to start and no backup was made. Can it be restored?
If a file is accidentally deleted but the database has not been restarted, it can be restored as long as the deleted file handle is still in the system. You can refer to “Linux Operation to Recover Accidentally Deleted Files”. However, in this environment, the database process has been deleted, and lsof did not find the accidentally deleted file.
And the server does not have any backup, so this road is closed.
After confirmation, you don’t need these data, as long as it can be started.
If you start the database directly regardless of ibdata1, the following error will be prompted:
2023-11-01T05:16:55.058805Z mysqld_safe Logging to '/mysql/3306/log/mysql-error.log'. 2023-11-01T05:16:55.082918Z mysqld_safe Starting mysqld daemon with databases from /mysql/3306/data 2023-11-01T05:16:55.088486Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2023-11-01T05:16:55.088539Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 2023-11-01T13:16:55.237280 + 08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled 2023-11-01T13:16:55.237309 + 08:00 0 [Note] /usr/local/mysql7/bin/mysqld (mysqld 5.7.32-log) starting as process 46374 ... 2023-11-01T13:16:55.250775 + 08:00 0 [Note] InnoDB: PUNCH HOLE support available 2023-11-01T13:16:55.250793 + 08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2023-11-01T13:16:55.250798 + 08:00 0 [Note] InnoDB: Uses event mutexes 2023-11-01T13:16:55.250801 + 08:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2023-11-01T13:16:55.250804 + 08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2023-11-01T13:16:55.250807 + 08:00 0 [Note] InnoDB: Using Linux native AIO 2023-11-01T13:16:55.250975 + 08:00 0 [Note] InnoDB: Number of pools: 1 2023-11-01T13:16:55.251057 + 08:00 0 [Note] InnoDB: Using CPU crc32 instructions 2023-11-01T13:16:55.252307 + 08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2023-11-01T13:16:55.258902 + 08:00 0 [Note] InnoDB: Completed initialization of buffer pool 2023-11-01T13:16:55.262430 + 08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2023-11-01T13:16:55.276886 + 08:00 0 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 0 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages! 2023-11-01T13:16:55.276904 + 08:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 2023-11-01T13:16:55.888909 + 08:00 0 [ERROR] Plugin 'InnoDB' init function returned error. 2023-11-01T13:16:55.888944 + 08:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2023-11-01T13:16:55.888949 + 08:00 0 [ERROR] Failed to initialize builtin plugins. 2023-11-01T13:16:55.888952 + 08:00 0 [ERROR] Aborting 2023-11-01T13:16:55.888967 + 08:00 0 [Note] Binlog end 2023-11-01T13:16:55.889025 + 08:00 0 [Note] Shutting down plugin 'CSV' 2023-11-01T13:16:55.889192 + 08:00 0 [Note] /usr/local/mysql7/bin/mysqld: Shutdown complete
MySQL 5.7, if you delete ib_logfile0 and ib_logfile1 at the same time and start the database, these three files can be automatically created.
2023-11-01T09:13:52.873672Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2023-11-01T09:13:52.873731Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 2023-11-01T17:13:53.011913 + 08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled 2023-11-01T17:13:53.011946 + 08:00 0 [Note] /usr/local/mysql7/bin/mysqld (mysqld 5.7.32-log) starting as process 95232 ... 2023-11-01T17:13:53.014739 + 08:00 0 [Note] InnoDB: PUNCH HOLE support available 2023-11-01T17:13:53.014758 + 08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2023-11-01T17:13:53.014762 + 08:00 0 [Note] InnoDB: Uses event mutexes 2023-11-01T17:13:53.014766 + 08:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2023-11-01T17:13:53.014768 + 08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2023-11-01T17:13:53.014771 + 08:00 0 [Note] InnoDB: Using Linux native AIO 2023-11-01T17:13:53.014911 + 08:00 0 [Note] InnoDB: Number of pools: 1 2023-11-01T17:13:53.015005 + 08:00 0 [Note] InnoDB: Using CPU crc32 instructions 2023-11-01T17:13:53.015890 + 08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2023-11-01T17:13:53.020309 + 08:00 0 [Note] InnoDB: Completed initialization of buffer pool 2023-11-01T17:13:53.021508 + 08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2023-11-01T17:13:53.032014 + 08:00 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created! 2023-11-01T17:13:53.032158 + 08:00 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ... 2023-11-01T17:13:53.107490 + 08:00 0 [Note] InnoDB: File './ibdata1' size is now 12 MB. 2023-11-01T17:13:53.107760 + 08:00 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2023-11-01T17:13:53.480921 + 08:00 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2023-11-01T17:13:54.438187 + 08:00 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2023-11-01T17:13:54.438251 + 08:00 0 [Warning] InnoDB: New log files created, LSN=45790 2023-11-01T17:13:54.438264 + 08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2023-11-01T17:13:54.438329 + 08:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2023-11-01T17:13:54.505869 + 08:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2023-11-01T17:13:54.506059 + 08:00 0 [Note] InnoDB: Doublewrite buffer not found: creating new 2023-11-01T17:13:54.527522 + 08:00 0 [Note] InnoDB: Doublewrite buffer created 2023-11-01T17:13:54.531060 + 08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2023-11-01T17:13:54.531078 + 08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2023-11-01T17:13:54.531169 + 08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2023-11-01T17:13:54.541415 + 08:00 0 [Note] InnoDB: Foreign key constraint system tables created 2023-11-01T17:13:54.541468 + 08:00 0 [Note] InnoDB: Creating tablespace and datafile system tables. 2023-11-01T17:13:54.542864 + 08:00 0 [Note] InnoDB: Tablespace and datafile system tables created. 2023-11-01T17:13:54.542889 + 08:00 0 [Note] InnoDB: Creating sys_virtual system tables. 2023-11-01T17:13:54.544077 + 08:00 0 [Note] InnoDB: sys_virtual table created 2023-11-01T17:13:54.544168 + 08:00 0 [Note] InnoDB: Waiting for purge to start 2023-11-01T17:13:54.594720 + 08:00 0 [Note] InnoDB: 5.7.32 started; log sequence number 0 2023-11-01T17:13:54.596887 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. mysqld: Table 'mysql.plugin' doesn't exist 2023-11-01T17:13:54.596922 + 08:00 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 2023-11-01T17:13:54.600988 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. mysqld: Table 'mysql.gtid_executed' doesn't exist 2023-11-01T17:13:54.601016 + 08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2023-11-01T17:13:54.602556 + 08:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2023-11-01T17:13:54.602568 + 08:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory. 2023-11-01T17:13:54.603431 + 08:00 0 [Warning] CA certificate ca.pem is self signed. 2023-11-01T17:13:54.603465 + 08:00 0 [Note] Skipping generation of RSA key pair as key files are present in data directory. 2023-11-01T17:13:54.603546 + 08:00 0 [Note] Server hostname (bind-address): '*'; port: 3306 2023-11-01T17:13:54.603630 + 08:00 0 [Note] IPv6 is available. 2023-11-01T17:13:54.603640 + 08:00 0 [Note] - '::' resolves to '::'; 2023-11-01T17:13:54.603677 + 08:00 0 [Note] Server socket created on IP: '::'. 2023-11-01T17:13:54.604700 + 08:00 0 [Warning] Insecure configuration for --pid-file: Location '/mysql' in the path is accessible to all OS users. Consider choosing a different directory. 2023-11-01T17:13:54.604900 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-11-01T17:13:54.604919 + 08:00 0 [Warning] Failed to open optimizer cost constant tables 2023-11-01T17:13:54.605366 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-11-01T17:13:54.605380 + 08:00 0 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them 2023-11-01T17:13:54.605806 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-11-01T17:13:54.605824 + 08:00 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 2023-11-01T17:13:54.605991 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-11-01T17:13:54.606061 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-11-01T17:13:54.606113 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-11-01T17:13:54.606122 + 08:00 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened. 2023-11-01T17:13:54.606181 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-11-01T17:13:54.606230 + 08:00 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev .mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2023-11-01T17:13:54.606242 + 08:00 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened. 2023-11-01T17:13:54.606263 + 08:00 0 [Note] Failed to start slave threads for channel '' 2023-11-01T17:13:54.609674 + 08:00 0 [Note] Event Scheduler: Loaded 0 events 2023-11-01T17:13:54.618992 + 08:00 0 [Note] /usr/local/mysql7/bin/mysqld: ready for connections. Version: '5.7.32-log' socket: '/mysql/3306/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
Therefore, for MySQL 5.7, if you delete ibdata1 by mistake, you can delete ib_logfile0 and ib_logfile1 if the data is not needed, so that MySQL can automatically create these files and bring up the database.
But the MariaDB used by my colleagues deleted these files and executed the database startup instructions. Although these files can be automatically created, they cannot be started, and the following error is displayed.
systemctl status mariadb.service ● mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled) Active: failed (Result: exit-code) since Wednesday 2023-11-01 13:30:55 CST; 12s ago Process: 10099 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE) Process: 10098 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS) Process: 10067 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS) Main PID: 10098 (code=exited, status=0/SUCCESS) November 01 13:30:49 localhost.localdomain systemd[1]: Starting MariaDB database server... November 01 13:30:49 localhost.localdomain mariadb-prepare-db-dir[10067]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done. November 01 13:30:49 localhost.localdomain mariadb-prepare-db-dir[10067]: If this is not the case, make sure the /var/lib/mysql is empty before running ...b-dir. November 01 13:30:49 localhost.localdomain mysqld_safe[10098]: 231101 13:30:49 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. November 01 13:30:49 localhost.localdomain mysqld_safe[10098]: 231101 13:30:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql November 01 13:30:55 localhost.localdomain systemd[1]: mariadb.service: control process exited, code=exited status=1 November 01 13:30:55 localhost.localdomain systemd[1]: Failed to start MariaDB database server. November 01 13:30:55 localhost.localdomain systemd[1]: Unit mariadb.service entered failed state. November 01 13:30:55 localhost.localdomain systemd[1]: mariadb.service failed. Hint: Some lines were ellipsized, use -l to show in full.
When logging into the database, it says that sock cannot be found, but actually there is this file.
[root@localhost ~]# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
I think I should be closer to the answer, but due to the progress, I still reinstalled.
The most important thing derived from this is that no matter what the library is, it is recommended to make a backup. A library without backup is like running naked, and there are various risks.
If you think this article is helpful, please feel free to click “Like” and “Reading” at the end of the article, or forward it directly to pyq,
Recently updated articles:
“Oracle talks about digital transformation”
“Exploring the understanding of several lines of defense in network information security”
“The Changing Impact of the Unveiling of the National Data Agency on Us”
“Popular introduction to upper-level and lower-level methods”
“Solutions to Extracurricular Mathematics Problems for Second Grade”
Recent hot articles:
“Recommend a classic paper on Oracle RAC Cache Fusion”
“The shock that the open source code of the “Red Alert” game brings to us”
Article classification and indexing:
“Classification and Indexing of 1,300 Public Account Articles”
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 77456 people are learning the system