Archive for December 2007

mysql stuff

I did a pretty silly thing just now as I was trying to decide how to best back up the mysql database files for this blog. I followed the scripts from wordpress.org to create a backup file containing the mysql database for thefowlerfamily.net. No problems with that, but I noticed that when I installed mysql, I had neglected to set a password for the root account. I proceeded to do that with

>mysqladmin -u root password NEWPASSWORD

I then tried to check the root password by logging in with

>mysql -u root -p

After entering the password I had set above (which is not really NEWPASSWORD, by the way), I got

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Doh!!!!!!!

I had set the global root password, but my installation of mysql wants to differentiate passwords between domains, such as ‘thefowlerfamily.net’ and ‘localhost’. So now I was screwed. Time to Google.

The mysql.com web site has a page for “access denied” errors, but none of the solutions on that page worked. They had stuff like try, “mysqladmin –no-defaults -u root -p” and then reset the password. Still got the error, above.

After searching around some more, I found a less secure way on their web site that allowed me to reset the password. The full instructions are at

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

First I had to kill the mysqld process with a simple ‘kill’ command on the running process. Then restarted it with

>/usr/bin/mysqld_safe –skip-grant-tables –user=root &

This allows anyone to log in without passwords, so it should not be done while connected to the internet.

Then I logged in as root and reset the password

>mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD(‘NEWPASSWORD’)
-> WHERE User=’root’;
mysql> FLUSH PRIVILEGES;
mysql> exit

Killed the mysqld process again, and then restarted the daemon correctly

>service mysqld start

I tested the root login and got what I expected.

>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.

mysql> exit
Bye

So, that is now correct. I feel like such a noob. Oh, and as far as backups, I decided to just add the entire mysql database directory into my rsnapshot.conf file so that all databases get backed up every night. I may write about my use of rsnapshot later. It is a pretty awesome tool for making current and archival backups in the least amount of disk space.