How to connect and create a database in MySQL?

Question

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?

Answer 1

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!

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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).

Answer 6

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.