InnoDB table rebuild

PROBLEM

On a MySQL 5.6 database server, the ibdata1 file includes 5 InnoDB tables in the mysql schema.

mysql> select table_name from information_schema.tables
    -> where table_schema='mysql' and engine='InnoDB';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

In MySQL versions before 5.6, if you shutdown MySQL, delete ibdata1, and start MySQL back up, the ibdata1 gets re-created. However, if you do this with MySQL 5.6, these 5 tables are not recreated. But even if your deleted ibdata1, the following 10 files are still in /var/lib/mysql/mysql:

innodb_index_stats.frm
innodb_index_stats.ibd
innodb_table_stats.frm
innodb_table_stats.ibd
slave_master_info.frm
slave_master_info.ibd
slave_relay_log_info.frm
slave_relay_log_info.ibd
slave_worker_info.frm
slave_worker_info.ibd

I learned this early on. With a newly created or damaged ibdata1, there are no corresponding data dictionary entries for those 5 tables, but the files are still there. In this particular case, I copied ibdata1 from a one server to another one.

SOLUTION

Install MySQL on another server or find another Mysql DB engine installed.
Use mysqldump to extract only those 5 tables.

TABLELIST="innodb_index_stats"
TABLELIST="${TABLELIST} innodb_table_stats"
TABLELIST="${TABLELIST} slave_master_info"
TABLELIST="${TABLELIST} slave_relay_log_info"
TABLELIST="${TABLELIST} slave_worker_info"
mysqldump -uroot -p mysql ${TABLELIST} > mysql_innodb_tables.sql

Copy mysql_innodb_tables.sql to the original DB Server with the corrupted tables.
Execute mysql_innodb_tables.sql to create the missing tables.
Run FLUSH TABLES; Optional

Written on June 20th, 2016 by Samy Gejzenblozen

Tags:

#mysql

Social networks

You may also enjoy:

Last rowcount in MySQL

#mysql

Suppose you want to count the number of lines returned by the last SQL statement issued. For **select** statements you can use the *FOUND_ROWS* construct: ```sql SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS(); ``` This will return the number of rows in the last **select** query (or if the first query ... Read more

20 Sep 2016 - less than 1 minute read

MySQL punch user creation DDL

#mysql

You can get the DDL necessary to duplicate an existing user with the following system command: ``` MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants... Read more

20 Jul 2016 - less than 1 minute read