MySql Database Recovery from Raw Files

---

Disclaimer: This worked under particular case. It may or may not work for everyone.

Scenario:

OS: Ubuntu 12.4 Servedit Edition / x86

MYSQL: Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.2

The OP was running radius for AAA. The disk got faulty for some unknown reasons and were unable to boot from it. There was no database backup [Real example of bad practices] So restoration from mysqldump to new system was not an option there !

Requirements:

We need to restore the Database using mysql raw files. Luckily the faulty disk was able to got attached to other system & we were able to copy the core /var/lib/mysql/ folders (along with all sub folders in it)

---

Quick & Dirty Restoration Step !

Requires some good level of Linux / DB knowledge]

• Setup a test SANDBOX, Install same level of OS along with MYSQL on new system/disk. Create databases / tables as required. Verify all is working by logging to mysql
• Stop the MYSQL service.
• Copy the folder /var/lib/mysql/  [copied from faulty disk] to this new box under /var/lib/mysql/  
• Set the permission on newly copied files/folders
  chown mysql -R /var/lib/mysql/
  

After this point Try to start the MYSQL service , IF it starts successfully & you can see your DATA , then skip below steps , ELSE continue through below steps …

• Edit the /etc/mysql/my.cnf & add following line under [mysqld] section
  innodb_force_recovery = 6
  
• Start MYSQL service & the service will start in Safe Mode with limited working support. Verify if you can able to login to MYSQL service by
  mysql -uroot -pPASS
• If above step works, Export the Database backup using mysqldump cmd e.g:
  mysqldump -uroot -pSQLPASS   radius  >  radius_db_dump_.sql
• Once done, Open the file in nano or any other text editor, & verify if it contains the required data.

Now copy the radius_db_dump_.sql to safe location & you know what to do next 🙂

• Import this mysqldump file to your working radius system !

---

TIPS:

Make sure you have multistage backup strategies in place for any mission critical server.

Example for mysql Database, You can do following

• If your server is VM, then VEEAM B&R will be your best friend & guardian, go for it
• 1st Stage Backup: [Highly recommended for live replication]
  ideally, you should have at least 2 Replica servers & configure either Master-Master or Master-Slave Replication
• 2nd Stage backup:
  Create bash scripts to export DB backup in local folder on a daily basis, (or hourly basis if required]
• 3rd Stage backup:
  Attach external USB disk to the server, and in your backup script, add this usb as additional backup repository
• 4th Stage backup:
  Configure DROPBOX and add it as additional backup repository
• 5th Stage backup:
  The admin should manually copy the backup folders to his desktop so that if all other backups gets failed , this should come in handy.

---

Regard’s
Syed Jahanzaib