导入数据磁盘满重启失败解决过程

导入数据磁盘满重启失败解决过程

    # mysqlimport 导入数据磁盘满，登录 mysql 想执行 purge binary logs，但执行 show binary logs 已卡死
    systemctl stop mysql

    # stop mysql 后错误日志如下
    tail -f /var/log/mysql/error.log
        2022-02-08T01:29:23.513307Z 135 [ERROR] [MY-010907] [Server] Error writing file 'binlog' (errno: 28 - No space left on device)

    # 禁用 bin log 后重启
    vi /etc/mysql/mysql.conf.d/mysqld.cnf
        disable_log_bin
    systemctl start mysql

    # 启动几个小时也没有成功，一直卡在下面

    tail -f /var/log/mysql/error.log
        2022-02-08T01:34:52.792314Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
        2022-02-08T02:04:40.048902Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...

    # 正常 stop mysql 无法成功，直接强杀

    systemctl stop mysql
    kill -9 $mysql_pid

    # 设置 force recovery 为 6，人工不执行进程崩溃前的未提交事务回滚操作
    vi /etc/mysql/mysql.conf.d/mysqld.cnf
        innodb_force_recovery=6
    systemctl start mysql

    # mysql 能正常启动，但无法删除导入失败的表
    mysql
        drop table error_table;
            error

    # 手工删除 binlog 文件，恢复正常启动模式，无法启动报错
    rm /var/lib/mysql/binlog.00*
    vi /etc/mysql/mysql.conf.d/mysqld.cnf
        innodb_force_recovery=0
    systemctl stop mysql
    systemctl start mysql
    tail -f /var/log/mysql/error.log
        2022-02-08T04:20:43.697017Z 1 [ERROR] [MY-012179] [InnoDB] Could not find any file associated with the tablespace ID: 481        

    # 正常 stop 失败，强杀，然后设置 force_recovery=1 重启，然后删除错误表
    kill -9 $mysql_pid
    vi /etc/mysql/mysql.conf.d/mysqld.cnf
        innodb_force_recovery=1
    systemctl start mysql
    mysql
        drop table error_table;

    # 恢复正常启动模式，启动成功
    systemctl stop mysql
    sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
        innodb_force_recovery=0
    systemctl start mysql