Month: September 2012

Resetting a forgotten MySQL root password

mysql

When you have forgotten you MySQL root password, with root access it is easy enough to get back into the database and reset the password.

Firstly, bring down your MySQL database. This can be as simple as running:

/etc/init.d/mysqld stop

Then once you have confirmed your MySQL instance is no longer running:

ps -ef | grep -i mysql

You are free to restart the MySQL instance again without using the user tables:

mysqld_safe –skip-grant-tables &

Confirm that the MySQL instance is running again, and it is likely that you will have received a warning had it have not started properly anyway:

ps -ef | grep -i mysql

Then you are free to login to MySQL again:

mysql -uroot

Then once you are successfully back in the database, you can choose whether or not you want to reset the password again:

UPDATE mysql.user SET Password=PASSWORD(‘some_password_here’) WHERE User=’root’;
FLUSH PRIVILEGES; 

Dabbling with iptables to ensure remote MySQL connection works properly

iptables, mysql

I found in a cluster of machines that I couldn’t access MySQL remotely anymore. After some playing around with GRANT PRIVILEGES in MySQL, I realised that the issue wasn’t actually because of the MySQL settings at all.

So, was it the port number that I was connecting to that happened to be wrong?

netstat -tln | grep 3306

Showed that MySQL is running on port 3306 as intended.

telnet xx.xx.xx.xx 3306

From machine A to machine B refused to accept connections, so I knew that we had an issue somewhere else – probably on the firewall – stopping me from connecting remotely.

I didn’t really have much experience with iptables, so had to read a little bit more into it. Firstly, I needed to display all the rules on the firewall with this command:

/sbin/iptables -L -v -n

…under the Chain RH-Firewall-1-INPUT I saw no mention of the open port 3306 for MySQL.  We needed to add two rules, for tcp and udp connections.

/sbin/iptables -I RH-Firewall-1-INPUT -p tcp –dport 3306 -j ACCEPT

/sbin/iptables -I RH-Firewall-1-INPUT -p udp –dport 3306 -j ACCEPT

Afterwards, I was able to connect remotely to the MySQL machine again.