MySQL’ed Out
Filed Under: Technical
So eventually I end up screwing around with things long enough to screw them up. Today I managed to munge the root account on a MySQL installation and wasn’t able to log back in to fix it. It took me some time to figure my way this mess. There’s no easy to use guide (that I can find) so here’s one.
Recreating the MySQL ‘root’ account on Linux (Fedora Core 4):
Note: The command line prompt on my installation looks like this [root@hostname ~]& and commands that should be typed in are shown on bold. The MySQL client command line propmt looks like this mysql>
- Stop the MySQL server:
- Find the MySQL daemon, mysqld, process ID file.
[root@hostname ~]& locate mysql.pid - Kill the mysqld process.
[root@hostname ~]& kill `cat /var/run/mysql/mysql.pid`
- Find the MySQL daemon, mysqld, process ID file.
- Start the MySQL server without using the permission tables:
[root@hostname ~]& /usr/libexec/mysqld –user=root –skip-grant-tables –skip-networking
The “–skip-networking” option is a minor security precaution to prevent users connecting to the server whilst it’s running in an unsecure mode, ie. not checking permissions.
Check this for a more secure method of starting MySQL. - Start the mysql client:
[root@hostname ~]& mysql -u root -p - Correct the “broken” ‘root’ account as appropriate:
- Reset the password:
mysql> UPDATE user SET password=PASSWORD(‘new_password’) WHERE user=’root’;
OR - Change the allowed location(s):
mysql> UPDATE user SET host=’correct_host’ WHERE user=’root’;
OR - Create a new ‘root’ account:
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY ‘password’;
- Reset the password:
- Always remember to reload the provileges when you’re done:
mysql> FLUSH PRIVILEGES; - Stop the MySQL server:
- Restart the MySQL the normal way. I use Webmin (using system/boot up & shutdown/mysqld) but you can also use:
- Directly running the command
[root@hostname ~]& /usr/bin/safe_mysqld &
The ampersand (&) detaches the command from the current terminal, allowing the process to run on it’s own. - System init method (how it normally starts when Linux boots:
[root@hostname ~]& /etc/init.d/mysqld start
- Directly running the command
That’s it. You’re done. After this I go back to admin’ing MySQL with a combination of Webmin (minimal use) and PHPMyAdmin (most stuff).
Comments
No Comments so far
Leave a comment
Line and paragraph breaks automatic, HTML allowed:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
Apologies for the CAPTCHA but the SPAM was killing the site. I was getting hundreds each day.
This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.