I’m new to MySQL and installed MySQL in Ubuntu 16.04. At the time of installation I have given password for root.
In the terminal I can access only root user and created user only.
bharath@bharath-desktop:~$ mysql -uroot -pbharath
mysql: [Warning] Using a password on the command line interface can be
insecure. Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 7 Server version: 5.7.16-0ubuntu0.16.04.1
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input
But I am not able to access the default MySQL user in the terminal. It shows like this:
ERROR 1045 (28000): Access denied for user ‘bharath’@’localhost’
(using password: NO)**
If I hit enter after MySQL, it’s not authenticated. How to access this?
As far as I can tell, the default users for a fresh mysql server is
mysql.sys@localhost, you’ll need to create a
bharath@localhost user and create a .my.cnf under your
$HOME folder with the following contents:
[mysql] user=bharath password=<yourpreferredpassword> host=localhost
If there is a default MySQL user named
mysql then you would start a MySQL prompt with the following command:
shell> mysql -umysql
This would grant you access if the mysql user doesn’t have a password.
Otherwise you can specify the -p parameter and be prompted for the password:
shell> mysql -umysql -p
However, according to the default documentation 3.4 Securing the Initial MySQL Accounts, there is either the
root user or the anonymous account
The mysql.user grant table defines the initial MySQL user accounts and their access privileges. Current versions of MySQL 5.7 create only a ‘root’@’localhost’ account, but for earlier versions, there might be multiple accounts such as described here:
Some accounts have the user name root. These are superuser accounts that have all privileges and can do anything. If these root accounts have empty passwords, anyone can connect to the MySQL server as root without a password and be granted all privileges.
◾ On Windows, root accounts are created that permit connections from the local host only. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, or the IPv6 address ::1. If the user selects the Enable root access from remote machines option during installation, the Windows installer creates another root account that permits connections from any host.
◾ On Unix, each root account permits connections from the local host. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, the IPv6 address ::1, or the actual host name or IP address.
◾ The ‘root’@’localhost’ account also has a row in the mysql.proxies_priv table that enables granting the PROXY privilege for ”@”, that is, for all users and all hosts. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. See Section 5.9, “Proxy Users”.
• If accounts for anonymous users were created, these have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.
◾ On Windows, there is one anonymous account that permits connections from the local host. Connections can be made by specifying a host name of localhost.
◾ On Unix, each anonymous account permits connections from the local host. Connections can be made by specifying a host name of localhost for one of the accounts, or the actual host name or IP address for the other.
You can verify the account by executing the following statement on your MySQL instance after connecting with your root account:
SELECT User, Host, Password FROM mysql.user;
5.7.6 and later:
SELECT User, Host, HEX(authentication_string) FROM mysql.user;
The following table will be displayed:
+------+--------------------+----------+ | User | Host | Password | +------+--------------------+----------+ | root | localhost | | | root | myhost.example.com | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | myhost.example.com | | +------+--------------------+----------+
In your example there might be password entries for
root and maybe even for anonymous
If you want to change the password for anonymous then execute the following commands:
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR ''@'myhost.example.com' = PASSWORD('new_password');
You should then be able to connect to localhost like this:
shell> mysql -h localhost -pnewpassword