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?
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
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:
Open mysql from terminal:
mysql -u root -p
Enter the password created before.
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!
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
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
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.
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).
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.
