Restoring MySQL database - Ubuntu 16.04 LTS

Forums › Linux › Restoring MySQL database - Ubuntu 16.04 LTS

StevieT

702 posts

Ultimate Geek

#198140 28-Jun-2016 08:40

Hi there

Before reinstalling Ubuntu 16.04 on my other laptop, I logged into phpmyadmin in hope that I would export (and then import after the reinstall) a database. However, that did not go to plan as when I tried to import the database it appears the file I saved is a html file, even though previous times I have done the same procedure and got a file which is importable.

I did have CrashPlan running on the laptop, and have the most recent files from /var/lib/mysql These include: ib_logfile0, ib_logfile1, ibdata1 and ibtmp1. I believe the databases tables and data are stored in .frm and .ibd files (and in addition a db.opt file).

Is there anyway to restore the database from the .frm and .ibd files (and perhaps copy over the ib_logfile etc)?

Any help much appreciated!

Stevie

darylblake

1162 posts

Uber Geek

Trusted

#1581708 28-Jun-2016 09:21

Yes,

You can install mysql (preferablty the same version)

Then copy the entire /var/lib/mysql folder from the crashplan backup over the top of your new /var/lib/mysql.

The thing is, is the crashplan might not have backed it up properly as the ibdata file will probably have been open at the time the crashplan backup ran, so you might get lucky, you might not.

Also check that HTML file that you made, are you sure it doesnt have any SQL inside it?

StevieT

702 posts

Ultimate Geek

#1583165 30-Jun-2016 10:02

Thanks for that! It seems I have another .sql file (saved three months ago) which is larger (3MB) than the 'backup' I took last Friday (48kb).

I've seen on the web that stopping the MySQL service is a good way forward to integrate those .frm and .ibd files. Now the question is, how do I do stop MySQL on Ubuntu 16.04 LTS? Googling gives me some results, but the commands suggested don't work.

darylblake

1162 posts

Uber Geek

Trusted

#1583225 30-Jun-2016 10:54

sudo  systemctl restart mysqld

sudo  systemctl stop mysqld

sudo  systemctl start mysqld



or it might be mysql... not 100% sure, i think its mysqld.

michaelmurfy

meow
13271 posts

Uber Geek

Moderator

ID Verified

Trusted

Lifetime subscriber

#1583270 30-Jun-2016 12:17

@darylblake should use mariadb instead bro.

Michael Murphy | https://murfy.nz
Referral Links: Quic Broadband (use R122101E7CV7Q for free setup)

Are you happy with what you get from Geekzone? Please consider supporting us by subscribing.
Opinions are my own and not the views of my employer.

SumnerBoy

2076 posts

Uber Geek

ID Verified

Lifetime subscriber

#1583325 30-Jun-2016 13:41

Apologies for OT question, but @michaelmurfy why is mariadb so much better? Never heard of it till today!

mikespook

12 posts

Geek

#1583330 30-Jun-2016 13:50

StevieT:

Thanks for that! It seems I have another .sql file (saved three months ago) which is larger (3MB) than the 'backup' I took last Friday (48kb).

I've seen on the web that stopping the MySQL service is a good way forward to integrate those .frm and .ibd files. Now the question is, how do I do stop MySQL on Ubuntu 16.04 LTS? Googling gives me some results, but the commands suggested don't work.

You can send a signal to the process of mysql, or just use another way: mysqladmin shutdown.

See the document: http://dev.mysql.com/doc/refman/5.7/en/server-shutdown.html

michaelmurfy

meow
13271 posts

Uber Geek

Moderator

ID Verified

Trusted

Lifetime subscriber

#1583364 30-Jun-2016 14:06

SumnerBoy:

Apologies for OT question, but @michaelmurfy why is mariadb so much better? Never heard of it till today!

MariaDB is the Opensource fork of MySQL and is designed as a drop-in replacement. Since Oracle are proven to be a bunch of *insert word here* on security and the general Opensource spirit some devs have forked the last "good" release of MySQL and started packing it with more features etc. MariaDB is now the default MySQL server for Fedora, Debian 8, Ubuntu and CentOS 7 (+ RHEL).

If you're running MySQL then switch to MariaDB, it is faster and run by a large Opensource community instead of Oracle. This is kinda the same thing that happened to Openoffice forking off to Libreoffice.

Quic Broadband

Move to New Zealand's best fibre broadband service (affiliate link). Free setup code: R587125ERQ6VE. Note that to use Quic Broadband you must be comfortable with configuring your own router.

SumnerBoy

2076 posts

Uber Geek

ID Verified

Lifetime subscriber

#1583382 30-Jun-2016 14:31

michaelmurfy:

If you're running MySQL then switch to MariaDB, it is faster and run by a large Opensource community instead of Oracle. This is kinda the same thing that happened to Openoffice forking off to Libreoffice.

Cheers, will add it to the list of things to tinker with!

michaelmurfy

meow
13271 posts

Uber Geek

Moderator

ID Verified

Trusted

Lifetime subscriber

#1583407 30-Jun-2016 15:14

SumnerBoy:

michaelmurfy:

If you're running MySQL then switch to MariaDB, it is faster and run by a large Opensource community instead of Oracle. This is kinda the same thing that happened to Openoffice forking off to Libreoffice.

Cheers, will add it to the list of things to tinker with!

It is an one-liner to switch to MariaDB:

sudo apt-get install mariadb-server

From here, the installer will remove MySQL and install MariaDB, you're done, no messing around at all :) It is literally a drop-in replacement.

StevieT

702 posts

Ultimate Geek

#1584247 1-Jul-2016 17:21

darylblake:

sudo  systemctl restart mysqld

sudo  systemctl stop mysqld

sudo  systemctl start mysqld



or it might be mysql... not 100% sure, i think its mysqld.

sudo systemctl stop mysql did the trick. But now a problem has arisen when I try to start MySQL up again (and rebooting doesn't start it, either. Perhaps because of the error?).

I use sudo systemctl start mysql but get the following: "Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

This is what I get when running sudo systemctl status mysql.service:

I've probably stuffed things up big time, unless this is a semi-easy fix?

Many thanks

Stevie

mikespook

12 posts

Geek

#1584260 1-Jul-2016 17:41

How's the error log? Show the full error log of the fault start: /var/log/mysql/error.log

Abo

78 posts

Master Geek

#1615241 21-Aug-2016 19:53

This is one of the annoying things about systemd - trying to hide success/failure messages is really a hinderance.

Agree with checking the mysql log - another log to check is /var/log/messages

The other option is to try run it manually and see what it outputs to the screen.