On a Monday morning, going to my office normally but I got that my server was shutdown due to broken power. When I reboot the server, it could not boot as normal because of missing Grub. Taking two days to recover it but it was no lucky. I decided to install a fresh server and restore Mysql database from physical files after mounting the hard disk of old server to new one.
If you fall in that case, your hard disk is work but server cannot boot up, you can follow this post as a reference.
ref: http://stackoverflow.com/a/484773/1920536 but you don’t need to shutdown your server.
Step 1. Shutdown Mysql server
Step 2. Copy database in your database folder (in linux, the default location is /var/lib/mysql). Keep same name of the database, and same name of database in mysql mode.
sudo cp -rf /mnt/ubuntu_426/var/lib/mysql/database1 /var/lib/mysql/
Step 3: Change own and change mode the folder:
sudo chown -R mysql:mysql /var/lib/mysql/database1 sudo chmod -R 660 /var/lib/mysql/database1
sudo chown mysql:mysql /var/lib/mysql/database1 sudo chmod 700 /var/lib/mysql/database1
Step 4: Copy ibdata1 in your database folder
sudo cp /mnt/ubuntu_426/var/lib/mysql/ibdata1 /var/lib/mysql/ sudo chown mysql.mysql /var/lib/mysql/ibdata1
Step 5: copy ib_logfile0 and ib_logfile1 files in your database folder.
sudo cp /mnt/ubuntu_426/var/lib/mysql/ib_logfile0 /var/lib/mysql/ sudo cp /mnt/ubuntu_426/var/lib/mysql/ib_logfile1 /var/lib/mysql/
Remember change own and change root of those files:
sudo chown -R mysql.mysql /var/log/mysql/ib_logfile0 sudo chown -R mysql.mysql /var/log/mysql/ib_logfile1
sudo chown -R mysql.mysql /var/log/mysql
Step 6 (Optional): My site has configuration to store files in a specific location, then I copy those to corresponding location, exactly.
Step 7: Start your Mysql server. Everything come back and enjoy it.
Note: if you doesn’t do step 4, you can get an error:
InnoDB: The log sequence in ibdata files is higher InnoDB: than the log sequence number in the ib_logfiles! Are database? InnoDB: you are using the right ib_logfiles to start up the database? InnoDB: Log sequence number in ib_logfiles in 6316712, log InnoDB: sequence numbers stamped to ibdata file headers are between InnoDB: 24916633497 and 24916633497
to resolved it, you can do as step 4 above
or remove your log files: mv /var/lib/mysql/ib_logfile* /tmp/. Then Start MySQL server. This will create new logs;