Mariadb Error: Attempted to open a previously opened tablespace

Mariadb 因为服务器异常关机导致文件损坏而无法启动，也导致了HDP Ambari无法启动服务。

错误信息如下：

[root@hadoop1 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB 10.1.38 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: activating (start) since Sat 2019-05-11 09:50:20 CST; 431ms ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 12559 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 12556 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 12592 (mysqld)
   Status: "InnoDB: Error: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: ./mysql/innodb_table_stats.ibd. Cannot open tablespace ambari/ClusterHostMapping which uses space ID: 1 at filepath: ./ambari/ClusterHostMapping.ibd"
   CGroup: /system.slice/mariadb.service
           ├─12592 /usr/sbin/mysqld
           └─12606 addr2line -C -f -e /usr/sbin/mysqld

解决办法如下：

• 1. 修改配置
  vi /etc/my.cnf 设置 ：
  [mysqld]
  innodb_force_recovery = 6
• 2. 启动服务
  systemctl restart mysqld
• 3. 备份数据库
  

 mysqldump -uroot ambari > ambari.sql
 mysqldump -uroot druid > druid.sql
 mysqldump -uroot hive > hive.sql
 mysqldump -uroot ranger > ranger.sql
 mysqldump -uroot rangerkms > rangerkms.sql

• 4. cd /var/lib/mysql/ 删除文件ibdata1，ib_logfile0，ib_logfile1
• 5. 修改数据库配置
  vi /etc/my.cnf 设置 ：
  [mysqld]
  innodb_force_recovery = 6
• 6. 重启服务
  systemctl restart mysqld
• 7. 删除数据库目录文件

[root@hadoop1 mysql]# rm -f ambari/*
[root@hadoop1 mysql]# rm -f druid/*
[root@hadoop1 mysql]# rm -f hive/*
[root@hadoop1 mysql]# rm -f ranger/*
[root@hadoop1 mysql]# rm -f rangerkms/*

• 8.还原数据库

mysql -uroot ambari < ambari.sql
mysql -uroot druid < druid.sql
mysql -uroot hive < hive.sql
mysql -uroot ranger < ranger.sql
mysql -uroot rangerkms < rangerkms.sql

• 重启服务
  systemctl restart mysqld
  

完整自动处理脚本如下：

#! /bin/bash
current=`date "+%Y-%m-%d_%H:%M:%S"`

sed -i "s/innodb_force_recovery = 0/innodb_force_recovery = 6/g" /etc/my.cnf
systemctl restart mysqld
mkdir -p /root/mysql_bak/$current
mv /root/mysql_bak/*.sql /root/mysql_bak/$current
echo "finished update mysql setting"

mysqldump -uroot ambari > /root/mysql_bak/ambari.sql
mysqldump -uroot druid > /root/mysql_bak/druid.sql
mysqldump -uroot hive > /root/mysql_bak/hive.sql
mysqldump -uroot ranger > /root/mysql_bak/ranger.sql
mysqldump -uroot rangerkms > /root/mysql_bak/rangerkms.sql
echo "finished backup mysql"

rm -f /var/lib/mysql/ibdata1
rm -f /var/lib/mysql/ib_logfile0
rm -f /var/lib/mysql/ib_logfile1
echo "finished rm mysql file"

sed -i "s/innodb_force_recovery = 6/innodb_force_recovery = 0/g" /etc/my.cnf
systemctl restart mysqld
echo "finished restore mysql setting"

rm -rf /var/lib/mysql/ambari/*
rm -rf /var/lib/mysql/druid/*
rm -rf /var/lib/mysql/hive/*
rm -rf /var/lib/mysql/ranger/*
rm -rf /var/lib/mysql/rangerkms/*
echo "finished rm mysql db files"

mysql -uroot -e "drop database ambari"
mysql -uroot -e "drop database druid"
mysql -uroot -e "drop database hive"
mysql -uroot -e "drop database ranger"
mysql -uroot -e "drop database rangerkms"
echo "finished drop databases"

mysql -uroot -e "create database ambari DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"
mysql -uroot -e "create database druid DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"
mysql -uroot -e "create database hive DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"
mysql -uroot -e "create database ranger DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"
mysql -uroot -e "create database rangerkms DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"
echo "finished created databases"

mysql -uroot ambari < /root/mysql_bak/ambari.sql
mysql -uroot druid < /root/mysql_bak/druid.sql
mysql -uroot hive < /root/mysql_bak/hive.sql
mysql -uroot ranger < /root/mysql_bak/ranger.sql
mysql -uroot rangerkms < /root/mysql_bak/rangerkms.sql
echo "finished restore databases"

systemctl restart mysqld
systemctl restart ambari-server
echo "finished restart mysql and ambari service"