MySQL on Ubuntu

I was installing MySQL on a Ubuntu VM to do some testing. Being lazy, I was following the directions posted on the How-To Geek blog which was a breeze. Until I actually tried to create my database.

$ mysqladmin create <databasename>
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Oops…

$ sudo !!
sudo mysqladmin create <databasename>
[sudo] password for testuser:
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Hmmm… oh yeah, password…

$ sudo mysqladmin -u root -p create <databasename>
Enter password:
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Ummm…. ok…. So after messing around for a while and Googling a lot, it seems the setup script that is run by Aptitude to create the installation doesn’t actually set the root password correctly. I found I had to do the following:

$ sudo stop mysqld
mysql stop/waiting
$ sudo mysqld_safe --skip-grant-tables &
110613 17:36:18 mysqld_safe Logging to syslog.
110613 17:36:19 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
$ mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> update mysql.user set password=PASSWORD('password') where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 3  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
$ sudo mysqladmin -u root -p shutdown
Enter password:
110613 21:21:49 mysqld_safe mysqld from pid file /var/lib/mysql/Test-ubuntu.pid ended
[1]+  Done                    sudo mysqld_safe --skip-grant-tables
$ sudo start mysql
mysql start/running, process 22598
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 34
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> exit
Bye
$ mysqladmin -u root -p create test
Enter password:
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 36
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql>

Woo Hoo! Working now! What a PITA!]]>

Leave a Reply Cancel reply