mysql 8 cannot startup after remove ibdata files

Products

• CA API Gateway
• API SECURITY
• CA Mobile API Gateway
• CA Rapid App Security
• STARTER PACK-7

Issue/Introduction

Not like the mysql 5.x, after remove ib* files, the mysql 8 will not re-generate the ibdata file if ibdata file is missing, so it won't be able to startup.

The errors in /var/lib/mysqld.log indicates it cannot find the ibdata file,

[ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.

[ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.

[ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.

[ERROR] [MY-012646] [InnoDB] File ./ibdata: 'open' returned OS error 71.

[ERROR] [MY-012981] [InnoDB] Cannot continue operation.  

 

 

It also indicates the KB article about "Shrink ibdata File" is not working anymore for mysql 8.

 

In fact, the mysql 8 doesn't need to shrink ibdata file, as it uses innodb file for each table -- with option innodb_file_per_table=ON (by default), disk space will be returned to the operating system after truncating or dropping a table

Environment

Release : 10.0

Component : API GATEWAY

Resolution

If the ib* files are removed, and there is no snapshot/backup, then re-init mysql would be needed,

0. take snapshot before any change

1. if already mysqldump all databases, jump to step 2
mkdir /tmp/mysql
vi /etc/my.cnf (delete following variables if exist: innodb_file_per_table,  innodb_force_recovery)

mysqld --console --user=mysql --initialize --datadir=/tmp/mysql
cp -p /tmp/mysql/ib* /var/lib/mysql
vi /etc/my.cnf
  add one line: innodb_force_recovery=6
systemctl start mysql
mysqldump --all-databases > alldb.sql

2. initialize mysql
rm -Rf /var/lib/mysql/*
vi /etc/my.cnf (delete variables if exist: innodb_file_per_table, innodb_force_recovery)
mysqld --console --user=mysql --initialize --datadir=/var/lib/mysql
(the output will show temp password, for example, "A temporary password is generated for root@localhost: MWq.j0>RMdV>" -- if there is no output, you can find the temp password in /var/log/mysqld.log)
systemctl start mysql

mysql -u root -p

(login with the temp password, and set password for root -- replace '7layer' with the default password you currently have in ~/.my.cnf)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '7layer';
mysql> exit;

3. restore data
mysql < alldb.sql

(replace the sql file name if needed)

4. restore user privileges
mysql> flush privileges;