It was a fun time to mess around with MySQL in Linux server using terminal lately.
By mistake, I took out all privileges from root user when I tried out MySQL command line in terminal. Then I had some hard time to gain the all root privileges back since different Linux systems have different approaches to handle MySQL through command line.
After tons of google search, I found this way working fine with Linux Centos OS.
First of all, stop MySQL service using “service mysqld stop”.
Secondly, edit my.cnf file to get the root access to MySQL without any permission issues even if root account is changed (with password and privileges). The my.cnf is located under /etc directory. Open it up; and under [MySQLD], add this line “skip-grant-tables” to gain ultimate root access to MySQL.
Thirdly, after editing my.cnf file, restart MySQL service using “service mysqld start”.
After that, get into MySQL command line by just typing “mysql” without using any “-u root -p” to access MySQL command line. And from then on, just try to update mysql.user table to get all privileges back or simply remove current root user and insert a new record.
Lastly, leave MySQL command line, and restart mysqld service. But make sure that my.cnf needs to be edited again to remove “skip-grant-tables”!!!
Anyways, it was all fun to fool around with server by all means necessary.