How to restore MySQL databases from database files?

Question

Our MySQL on Ubuntu 15.04 stopped working after we moved the computer to a new location and kept giving the missing socket file error. We couldn't really fix it after trying sudo apt-get install -f and there was an error about mysql-server-5.6 being corrupted.

So after a lot of trail and error I was able to remove mysql-server-5.6 following commands here : https://askubuntu.com/questions/172514/how-do-i-uninstall-mysqli

sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql

Afterwards, I reinstalled mysql server by:

sudo apt-get install mysql-client mysql-server

Then when I tried mysql -u root -p and inside mysql, I couldn't see any previous databases..

I now realized that I've removed the /etc/mysql/my.cnf file from the system and my understanding is that mysql doesn't know where the databases files are and they haven't been loaded into the databases yet.

I should also mention that, the /etc/mysql/my.cnf file is basically empty with these two lines:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I have .sql files for each database from a previous logical backup in February. The issue is that I don't know if there were any updates between the February backup and now (as I'm new to manage this database). I also have the path to a directory (let's call it a B directory) that contains what I believe the database files as many directories names match the database names. And in this B directory, many directories seem to be modified after February so it's very likely there were updates.

I'm reading other questions and the B directory contains the following files among others:

ibdata1
ib_logfile0
ib_logfile1
tokudb.directory
tokudb.environment
a bunch of *.tokudb

I understand I could use the following command to restore the February backup.

mysql -u username -p database_name < file.sql

But I'm wondering if there is any possibility to bring those updated database files back to life to MySQL. If so, how to do this? I'm guessing I need to somehow restore the previous /etc/mysql/my.cnf file.. but not sure what are required lines to put from this list I found: http://www.fromdual.com/mysql-configuration-file-sample

I'm not very familiar with MySQL backup and I'm still reading about mysqldump and what it does. I'm wondering if I can use the February .sql files and somehow apply it to the latest mysql database files??? Hopefully this and the title are clear..

Thanks so much for the help!

• Vérace: In your my.cnf, specify the datadir as your "B" directory. Uncomment the line with datadir by removing the #. You can also specify it on startup if you prefer.
• Vérace: BTW, I meant the comment in the fromdual link.
• olala: thanks for the comment. i think i will try this: thegeekstuff.com/2014/04/recover-innodb-mysql what do you mean by "specify it on startup"?
• jerichorivera: you need to make sure you enable tokudb plugin as I see you have tokudb files in there
• olala: how to exactly enable tokudb plugin? Specify something in the my.cnf file? I saw a document that mentioned both InnoDB for one database and TokuDB for another. As I understand, I think we are using MySQL and TokuDB is the storage engine for MariaDB.. can they actually mix these two together?

Answer 1

This is what I do to start the mysql daemon manually.

I've also included my own my.cnf. By specifying the correct corresponding paths in your system, you should be able to do the same thing. I would also point out that this is a source install, but again, it should apply generally.

Start the daemon like this:

./bin/mysqld --defaults-file=./my.cnf

And start the client with:

./bin/mysql -S ./mysql.sock -u root -pdba

Both mysqld and mysql client are launched from the basedir. This can be scripted easily of course.

This is my my.cnf - it's a bit of a mess, but hopefully it should help. I stress that this is not a production server!

[mysqld]
#
# * Basic Settings
#
user = pol
pid-file = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysqld.pid
socket = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.sock
port = 3306
basedir = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64
datadir = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/data
tmpdir = /tmp
pid-file = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.pid
lc-messages-dir = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/share
lc-messages = en_US
general_log = on
general_log_file= /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/logfile.txt
#log_error = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
log-error = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
slow_query_log_file = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/slow_query.log
slow_query_log = 0
explicit-defaults-for-timestamp = TRUE
#language = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/share/english
# bind-address = 0.0.0.0 - may not need c.f. host...
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY
[client]
port = 3306
socket = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysqld.sock
host = 127.0.0.1
user = pol
# or maybe localhost... cf. bind-address above...
# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
#loose-local-infile = 1
local-infile = 1 -- ignore this, it's a setting for abrowse, an external programme.
[mysqld_safe]
#user = linehanp
#socket = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.sock
#err-log = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
#pid-file = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.pid
#log_error = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
#log-error = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log