[CS-FSLUG] Anybody run mysql server?

[David McGlone]

On Sunday 07 February 2010 10:49:33 am l4c wrote:
> David McGlone wrote:
> > I'm in a bit of a bind here.
> >
> > Somehow I lost all my mysql database tables and now I cannot log into
> > mysql.
> >
> > my question is, usually when mysql is first installed, it installs IIRC 2
> > tables and I think these 2 tables are required for storing the
> > administrator username and password etc. so I never did touch these
> > tables.
> >
> > Anyway, I tried re-installing everything related to mysql but it does not
> > restore these tables, so I can't log in as root anymore.
> >
> > Does anyone know what I need to uninstall, reinstall to get all the
> > settings back to the same as a fresh install? Short of re-installing the
> > whole Ubuntu distro, which I've had to do before :-( So I'm hoping I
> > don't have to do that this time.
> >
> > I also know I lost my tables I created, but I have backups of those, so
> > I'm not worried 'bout that.
> 
> It installs 3 databases, mysql, information_schema and test.  The test
> database is unimportant, but the other two contain multiple tables that
> need to be there in order to get things going.
> 
> Try doing a full uninstall of mysql-server "sudo aptitude purge
> mysql-server" (all one line there), and then reinstall the same package.
>   This should delete and recreate your default mysql install.  Then you
> can get that running and restore your databases and tables to it.
> 

Thanks Link, it turns out my databases were not gone. Somehow the username and 
password got changed to admin, admin. I found all my databases in 
/var/lib/mysql. I still have no idea why they were missing from webmin.

I don't even know how I managed to fix it, but I figured out that re-installing 
anything mysql doesn't solve the problem.

I removed mysql-server, mysql-client, php5-mysql, phpmyadmin, mysql-common and
 mysql-gui-tools-common and only installed back into the system mysql-server.

At first that didn't sold the problem, but I did get a clue that the databases 
were stored in /var/lib/mysql, so I gave the file a look and sure enough all my 
databases were there. So I immediately copied the folder to my /home so I 
would not lose them.

At this point I knew that somehow the username and password for mysql had been 
changed to admin, admin so I tried to use webmin to change this but that 
required logging in as admin, and it wasn't letting me, I think because those 
tables were missing.

 I also realized that root was still in the list for mysql right under admin, 
so I just deleted the admin user.

Then I renamed the mysql folder in /var/lib and installed mysql-server again. 
It created another mysql folder, I renamed this folder and copied the original 
folder back. When I opened the database server in webmin, all my databases 
were back.

So I went to check all the websited I had built and I got errors that the site 
couldn't connect to the mysql database.

Come to find out, all the permissions on all the folders and files in the mysql 
folder had somehow changed.

So I fixed the permissions and sure enough everything is working again.

Whew! That was a task!

From now on, I'll be backing up /var/lib/mysql right along with regular db 
backups.

-- 
Blessings
David M.
I have been driven to my knees many times by the overwhelming conviction that 
I had nowhere else to go.