Restoring MySQL database from physical files (Debian/Ubuntu)

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

or

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.

That is.

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;

ref: http://dba.stackexchange.com/a/120673

 

 

 

 

 

 

 

 

 

Advertisements

2 thoughts on “Restoring MySQL database from physical files (Debian/Ubuntu)”

  1. Hey I wanted to thank you for this. I’m creating a web app application for my company, and it’s almost done.

    I’m Cyber Security so I was installing Kali Linux on my XPS 13 Developer Edition. I used the recoverable flash drive to retrieve my website files but I was really stuck on my database.

    I was up till 4 last night and your post saved my ass. I woke up this morning with a .sql file saved and my files backed up, and a fresh installation of Ubuntu and Kali Linux.

    I would have had to do like 20 hours of work over again.

    THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s