MySQL – Restore Server After Deleting ibdata1

MySQL

ON an Ubuntu Server 12.10 machine, I had attempted to delete the ibdata1 file before reading Rolando's explanation about how exactly to do so. Specifically, I had not set innodb_fast_shutdown = 0 before shutting MySQL down.

How might I start MySQL, now that ibdata1 has been deleted but innodb_fast_shutdown had not been set?

I have backups of all the databases, but I cannot reinstall MySQL since the Ubuntu version is no longer supported. I cannot update Ubuntu to a supported version due to an accessibility issue in the current versions, nor can I downgrade to 12.04 LTS (still supported) since Ubuntu was never designed to be downgradable and some applications that I need won't run well on that older version if I do a complete reinstall as they depend on a string of dependencies not in 12.04 but in 12.10.

Best Answer

If you got a mysqldump export (.sql) of your databases, you do not need to have stopped mysql in any special way.

  1. Make sure mysql is down

    pgrep mysqld
    (no output)
    
  2. Delete everything on your datadir (by default, /var/lib/mysql)

    mkdir /tmp/mysql.bak && sudo mv /var/lib/mysql/* /tmp/mysql.bak
    
  3. Run:

    sudo mysql_install_db
    
  4. Change the permissions od your datadir to the user mysql:

    sudo chown -R mysql:mysql /var/lib/mysql
    
  5. Start mysql:

    sudo service mysql start
    
  6. Import your databases back:

    mysql -u root < your_dump.sql
    

You will have your databases back on a fresh installation of mysql, with a just created ibdata1.