Those that I work with know that my first, and primary, job is as a MySQL DBA. Unfortunately, cause I love MySQL, I haven’t been doing as much with it lately because of all the virtualization work going on.
Today I’m going to post about MySQL. Occasionally you may encounter a MySQL server that has been around for a while, and no one knows who set it up, where it came from, or who owns it. Those wonderfully inaccessible databases are still someone’s responsibility. So, what do you do if you don’t know the root password? Well, it’s actually not all that difficult, assuming you can start and stop the instance a few times.
First we need to stop the mysql instance. I’m a *nix admin, so I’m presenting those commands. If you’re responsible for a windows server (sorry for that), you’ll have to figure out how to stop and start the instance using the service control panel.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
service mysqld stop # or if you aren't using RHEL or one of it's ilk: # /etc/init.d/mysqld stop # now we restart mysql, but we tell it to not use the grant tables # this means that there is no user access controls...everyone has # access to everything. Since this, quite obviously, presents a # security risk, we also tell it to not use networking. The only way # to connect is to use a named pipe, shared memory, or a socket. /path/to/mysql/bin/mysqld --skip-grant-tables --skip-networking & # on my Fedora laptop, the mysqld binary is at /usr/libexec # now we connect to the mysql instance using the socket on # localhost. The username you connect with doesn't really matter... /path/to/mysql/bin/mysql --socket=/path/to/mysql.socket --user=root # on my system, the socket is at /var/lib/mysql/mysql.sock. You can # find out where it is by doing "grep socket /etc/my.conf", substituting # the path to your my.cnf file # you should be at a mysql command line... mysql> use mysql; mysql> UPDATE user SET password = PASSWORD('new_password') WHERE user = 'root'; mysql> exit # now we stop the unprotected mysql instance and restart the normal /path/to/mysql/bin/mysqladmin --user=root shutdown service mysqld start # or "/etc/init.d/mysqld start" |
You should now be able to connect in the normal way using the newly set root password.