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... I've suposed the mysql.user was totally _required_. 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. -- Thanks, Jordi Espasa Clofent
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-0001.sig>
> If you restarted mysql it would probably fail to start ...Yes; or if you make any FLUSH PRIVILEGES from mysql command interface client.> 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.Great info Johnny. After all I've used a similar method: * copy (with rsync) datadir (I think it is /var/lib/mysql) * Install MySQL-server package in another CentOS box; extract de full mysql table * Deinstall the MySQL-server in affected box. Reinstall it and set up the root password. * Insert the mysq.user table from another CentOS box to the affected box * Recreate de users in mysql.user table of (ex)affected box It has been a tedious process... but a sure process too. A final note: the package removing process respecte de datadir and the copied data with rsync was not needed. -- Thanks, Jordi Espasa Clofent