Accidently removed "all privledges" from root@ user

[mopacfan]

I've allways used MSsql and we're moving several databases to MYsql to save the license fees. However, knowing very little about mysql and setting it up for the first time on our web server, I accidently removed the privledges from the root account and now there are no accounts with sufficient rights to do much of anything. Is there any way to restore the rights or do I need to uninstall and reinstall the program? Any help will be greatly appreciated.

 

[KarveR]

Is it your webserver and do you have root privileges?

If so, you can copy in the mysql database files from a new install over th eold ones and you will have al default access reset.

Theres another way of doing this too, but it escapes me atthe moment. - try a search here, its been discussed a few times

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[mopacfan]

The root has only select privledges. That's my problem. Now I'm stuck. I'll have to do some searching and see what I find.

 

[itsgsd]

Here's a quote from the MYSQL documentation:

" To re-create the grant tables completely, remove all the `.frm', `.MYI', and `.MYD' files in the directory containing the mysql database. (This is the directory named `mysql' under the database directory, which is listed when you run mysqld --help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.

Note: for MySQL versions older than Version 3.22.10, you should not delete the `.frm' files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db."

See:

http://www.mysql.com/documentation/...tabase_Administration.html#Default_privileges

Good Luck.

 

[flugh]

How about checking out:

http://www.mysql.com/doc/en/Resetting_permissions.html

Start mysqld with the --skip-grant-tables option. Make your changes, restart mysqld. Should be good to go.

----
JBR

 

[mopacfan]

flugh,

I read that one already but that's just to reset a p/w.

Itsgsd,

I'll give that a try. That sounds like the gem I've been looking for.

Thanks.

 

[tshot]

login as mysql user. most likely it is mysql. then start ur mysql server as
mysqld --skip-grant-tables

The --skip-grant-tables option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)

This is also from on-line manual

For testing, start the mysqld daemon with the --skip-grant-tables option. Then you can change the MySQL grant tables and use the mysqlaccess script to check whether your modifications have the desired effect. When you are satisfied with your changes, execute mysqladmin flush-privileges to tell the mysqld server to start using the new grant tables.




----------------
ur feedback is a very welcome desire