[CentOS] mysql.users deleting

Wed Aug 8 10:22:59 UTC 2007
Johnny Hughes <johnny at centos.org>

Jordi Espasa Clofent wrote:
> Hello,
> 
> Today I was working with MySQL in a test box (CentOS 5.0 x86_64) using
> phpMyAdmin interface. Accidentaly I've removed user table in mysql
> database (so, mysql.users)...
> 
> :S
> 
> I've two interesting questions:
> 
> 1) Despite of it, ddbb server seems to work perfectly. I use 3/4
> diferent databases with their mysql users... and it works yet! I don't
> understand, because mysql.users is the reference that MySQL server uses
> to admin their users and permissions access.
> 
> ¿?¿?¿?¿?¿ Maybe a kind of memory caché is active... but I think this
> have not any sense...
> 

If you restarted mysql it would probably fail to start ...

> I've suposed the mysql.user was totally _required_.

It is

> 
> 2) ¿How I can reisert mysql.user table without reinstall entire package?
> I've got only a few mysql users only and I can recreate their
> permissions access in short time.
> 

This worked for me, but is risky ... you should do it on another machine
and copy it over if possible.  However ... I did test it on a live
machine and it worked for me.

###---Commands---###

cd /var/lib/

mv mysql mysql.old

mkdir mysql

chown mysql.mysql mysql

###---End Commands---###

(OK ... at this point, make SURE you have a mysql.old dir with all your
current data in it and a mysql dir that is blank and both directories
should be owned by mysql.mysql ... VERY IMPORTANT)

###---Commands---###

/usr/bin/mysql_install_db

cp -a mysql/mysql/user.* mysql.old/mysql/

mv mysql mysql.bak

mv mysql.old mysql

###---End Commands---###

(OK at this point, you should have the default mysql user tables and
indexes in your /var/lib/mysql/mysql directory and you SHOULD be able to
 restart mysql and then login as root without a password from the
command line with the mysql command ... then assign a root password AND
then assign your user permissions to the databases)

Please be careful if this machine is important ... of course if it was
important then you would have had a backup from last night :D

You also now have a mysql.bak directory that are all the initial
database files for a blank install in case this happens again.

Thanks,
Johnny Hughes

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 252 bytes
Desc: OpenPGP digital signature
URL: <http://lists.centos.org/pipermail/centos/attachments/20070808/fe1c3b41/attachment-0005.sig>