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;”
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>