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:
- 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
- ¿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