So you don’t know your root password for mysql. Getting root control of your mysql server back without having to reset the password.

[singlepic id=57]

 

This little tidbit is written for Redhat / CentOS, but with a little bit of work, the concepts and commands are easily modified for any flavor of linux.

If you’re sitting on a LAMP stack and you’ve had a defection or just haven’t documented your passwords in a safe place, the easiest way to make this happen is to simply look at your webserver files.  Chances are if you haven’t bothered to document anything (such a common mistake that could’ve been resolved in 3 min.), your developers (or you) are using the root user to access the mysql instance.

You’re going to need a little bit of technical expertise, but not much.  You will need to find out where your files are located for your website.

Log into your server using an ssh client.  I use putty.  Once there,  you’ll want to become the root user.

Type:   su –

Enter the root password and now you’re the root user.

go to the webroot of your machine (where your files are located) and just type the following:  grep ‘root’ ./*/*

This will give you a list of files with the word “root” in it.  Now, just go through those files until you find a mysql login that says root.  Somewhere below that will be your password.

If you have several sites, check all of the webroots for the sites.  If not, just look for a mysql user.  Anyone will be fine.

Write down the mysql username and password for that user.

now, shut down your mysql server.  On RedHat / CentOS it’s as simple as ‘service mysqld stop’

Type ‘which mysqld_safe’ to get the path to mysqld_safe and just simply type ‘mysqld_safe –skip-grant-tables &’ and hit return twice or three times.  This should return you to the shell with mysql running without any permissions.. lovely, huh?

Type mysql -u <whatever username you wrote down>

you should get a mysql prompt

The first thing you’re going to type is “Flush Privileges;”

hit enter.

Now just simply type the following:

1. “use mysql;” <hit enter>

2. “GRANT ALL PRIVILEGES ON * . * TO ‘the <username you wrote down> ‘@ ‘localhost’ IDENTIFIED BY ‘<password you wrote down>’ WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;”     <hit enter>

3. “Flush Privileges;”

4. type exit and hit enter

5. You’ve now granted that user the abilities of root.

type ‘ps aux|grep mysql’ and hit enter

you’ll get something that looks like this.

root      2283  0.0  0.0   4632  1220 ?        S    Feb29   0:00 /bin/sh /usr/bin/mysqld_safe –datadir=/var/lib/mysql –socket=/var/lib/mysql/mysql.sock –log-error=/var/log/mysqld.log –pid-file=/var/run/mysqld/mysqld.pid –user=mysql
mysql     2333  0.0  1.4 186656 35916 ?        Sl   Feb29   0:05 /usr/libexec/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –socket=/var/lib/mysql/mysql.sock
root     24161  0.0  0.0   4016   704 pts/0    S+   09:38   0:00 grep mysql

You see the numbers next to each line?  type the following:

kill 2283 2333 24161 <hit enter>

now type service mysqld start     <hit enter>

You’re now running mysql again safely, but you also have a user with the power of root.  Get it?  You can do anything you want with that user that root can do.

I would suggest editing the files for each website and creating a user that only has access to each database respectively.

then change the password for root.

Good luck and don’t forget to donate!! <grin>