20

I want to install MySQL and create a database on it using the following code:

sudo apt-get install mysql-server
mysqladmin -h localhost -u {username} -p create lrs

I get the following message after executing the second line:

    Enter password:
    mysqladmin: connect to server at 'localhost' failed
    error: 'Access denied for user '{username}'@'localhost' (using password: YES)'

What is the problem?

Lucio
  • 18,648
  • 31
  • 107
  • 190
user543489
  • 425
  • 3
  • 7
  • 11
  • 1
    If you actually used these 2 commands where did you GRANT PRIVILIGES for {username} (if not admin/root) to be able to access a database? – Rinzwind Mar 22 '13 at 18:55

6 Answers6

31

After you've installed MySQL, you need to set mysql root password. To do so:

  1. Enter the next command in a terminal:

    mysql -u root

  2. Now it should open the mysql console. And type the following line:

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');

To exit from the mysql console enter exit.

Now you should create the database with the root user. To do so:

  1. Open mysql from terminal:

    mysql -u root -p

  2. Enter the password created before.

  3. Enter the following line:

    CREATE DATABASE yourdatabasename;

If you enter SHOW DATABASES; you should see it in the list. If so, you have a database ready to use!

Lucio
  • 18,648
  • 31
  • 107
  • 190
  • 1
    `mysql -u root` gives me `ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)` – Nicolas Raoul Apr 03 '14 at 07:15
  • That error means that you already have a `root` user and it has a password. If you forgot the `root` password, read [the MySQL documentation](https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix). – Lucio Apr 03 '14 at 17:42
1

You need to connect to MySQL using the root user and associated password. If you need to set them, use the following command: sudo mysqladmin -u root -h localhost password 'mypassword'

From there, you can configure additional accounts by following this document: How to Create a New User and Grant Permissions in MySQL

guntbert
  • 12,914
  • 37
  • 45
  • 86
Bert
  • 3,155
  • 1
  • 13
  • 12
  • 1
    I copied the `sudo mysqladmin -u root -h localhost password 'mypassword'`and pasted it into my terminal and I get the message `Access denied for user 'root'@'localhost' (using password: NO)`. – user543489 Mar 22 '13 at 19:14
  • @Bert This code will not work. The `password 'mypassword'` is wrong, it should be `-password 'mypassword'` – Lucio Mar 22 '13 at 19:30
  • should something be replaced with 'mypassword'? – user543489 Mar 22 '13 at 19:43
  • I executed `sudo mysqladmin -u root -h localhost -password 'mypassword'`, but I get `Access denied for user 'root'@'localhost' (using password: YES)`. – user543489 Mar 22 '13 at 19:44
  • @user543489 What happens if you enter `mysql -u root`? – Lucio Mar 22 '13 at 20:38
1

This is strange because since 12.04 (guessing you're running Kubuntu 12.04), MySQL has been default. Looks like you're missing a few steps in between, so let's look over this:

First, as you mentioned, let's do an installation,

sudo apt-get install mysql-server

After you installed it, let's try a little test,

sudo netstat -tap | grep mysql

When you run this, you should see this reply,

tcp        0      0 localhost:mysql         *:*                LISTEN      2556/mysqld

If this is not running correctly, run this restart command,

sudo service mysql restart

Now to configure the server.

Let's go to /etc/mysql/my.cnf to configure the basic settings. This includes the Log File, Port Number, Etc. For example, to configure MySQL to listen for connections from network hosts, change the bind-address directive to the server's IP address:

bind-address            = 192.168.0.5

After this, restart the MySQL daemon,

sudo service mysql restart

If you want to change the MySQL root password, run this:

sudo dpkg-reconfigure mysql-server-5.5

The daemon will be stopped and you'll be prompted for a new password.

Once you're done, you should be configured and a few google searches will teach you how to create a database

Source: The Ubuntu Server Guide

guntbert
  • 12,914
  • 37
  • 45
  • 86
sharksfan98
  • 294
  • 1
  • 3
  • 13
0

Run this command:

sudo dpkg-reconfigure mysql-server-5.5

(5.5 is the version number, adapt if you have a different version)

This will allow you to set your MySQL root password, that you can then use with mysqladmin and mysql commands.

Nicolas Raoul
  • 11,473
  • 27
  • 93
  • 149
-1

After you've installed MySQL, you need to set mysql root password. To do so:

Enter the next command in a terminal:

mysql -u root -p

Enter Password: (Enter your password here).
Swathi S
  • 1
-1

I know this is 3 years old but I just had this problem as well and wanted to post my answer in case anyone else does the same search I did.

mysqladmin: connect to server at 'localhost' failed

This indicates that your user doesn't have permission to connect to the database itself, before it even gets to logging in; not the database user, your actual linux user. Run everything as super user: sudo mysql -u root -p

Only root (the linux user, or a user with sudo privileges) can log in to mysql as root (the database user). I wasn't able to find anywhere documenting this but experimentation proved it was the case. You can create a new user with all the same rights as the root user and that will not be restricted.

sudo mysql -u root
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Now "newuser" (or whatever name you choose) can be logged-in-as without the need for sudo and you can use it for all your database administrative needs.

WizardStan
  • 1
  • 1