How to log in to MySQL and query the database from Linux terminal?

Question

I am using Debian Linux on which MySQL is installed. I can log in to my Linux machine using root user as well as other user. I can connect to MySQL database on Linux machine from Windows machine using SQLyog. Now I want to execute queries on Linux machine only using Linux terminal command line.

I tried some following things on terminal

I went to root directory then I went to /var/lib directory

I run following commands on terminal

mysqladmin -u root -p
mysqladmin -u root -ppassword

everytime I have tried, I get the following error message:

ERROR 1045 (28000) Access denied for user 'root'@'localhost' (Using password NO)

How can I accomplish the following?:

1. How do I get MySQL prompt in Linux terminal?
2. How I stop the MySQL server from Linux terminal?
3. How I start the MySQL server from Linux terminal?
4. How do I get MySQL prompt in Linux terminal?
5. How do I login to MySQL server from Linux terminal?
6. How do I solve following error?

ERROR 1045 (28000) Access denied for user 'root'@'localhost' (Using password NO)

Answer 1

If you would like to make it easier (if you have sudo access) is to just use the command: sudo mysql this command logs into the MySQL with sudo permissions, if you would like to make a user that you can remember for admin commands use this:

1. Make a new user (edit this out to your server configs and your liking)

   CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

2. Grant Admin Permissions (once again change to server configs and personalized settings)

   GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;

OPTIONAL: Allow the user to connect from any host:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

3. Flush Privileges (set the new changes and set them live)

   FLUSH PRIVILEGES;

Conclusion: Try for yourself! Use this command to login to the new account after you use EXIT; to logout of the current one: mysql -u newuser -p

Answer 2

MySQL allows several different ways to authenticate users. These are called "authentication plugins," and each user can specify its own plugin. To see which plugin is assigned to a user, you can use the plugin value in the mysql.user table. On Ubuntu systems (and possibly other Linux systems), the default settings for a new MySQL installation give the MySQL root user a different plugin from standard users, which explains the discrepancy you've noticed.

Specifically, the MySQL root user is assigned auth_socket authentication by default, while standard users are assigned either mysql_native_password (pre-MySQL 8) or caching_sha2_password (MySQL 8+). That is, you would see something like

# Prior to MySQL 8

mysql> USE mysql;
mysql> SELECT plugin FROM mysql.user WHERE user='root';
+-------------+
| plugin      |
+-------------+
| auth_socket |
+-------------+

mysql> SELECT plugin FROM mysql.user WHERE user='basic_db_user';
+-----------------------+
| plugin                |
+-----------------------+
| mysql_native_password |
+-----------------------+

or

# MySQL 8 and above

mysql> USE mysql;
mysql> SELECT plugin FROM mysql.user WHERE user='root';
+-------------+
| plugin      |
+-------------+
| auth_socket |
+-------------+

mysql> SELECT plugin FROM mysql.user WHERE user='basic_db_user';
+-----------------------+
| plugin                |
+-----------------------+
| caching_sha2_password |
+-----------------------+

auth_socket is socket authentication, which works by matching the connecting Unix user to the MySQL user of the same name, if there is one. This only works when connecting locally, which is one reason it's good for the privileged MySQL root user; under the auth_socket plugin, it isn't possible for someone to access your database as its root user remotely (unless they also have OS-level root access, in which case they already own your machine anyway).

Both mysql_native_password and caching_sha2_password are forms of password authentication. To understand the difference you see between accessing MySQL as its root user and as a standard user, we'll compare the login process between password authentication and socket authentication.

Password authentication

Let's say you attempt to access a local MySQL server using the command that's typically recommended. Your Unix/Linux OS user is joeuser, and you want to connect to the MySQL server as the MySQL user joesql. You type

joeuser@localhost:~$ mysql -u joesql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

The connection is successful. What has happened here is that the mysql program (use $ which mysql to find it on your system) is invoked with the command-line arguments -u, joesql, -p. From these, MySQL understands that you're trying to connect as the MySQL user joesql. It checks the plugin value of mysql.user for joesql and finds, let's say, caching_sha2_password. Thus, it knows you need a password to connect. From the -p command-line argument, it knows that you want it to ask you to type in your password, and it does so. You enter your password, and MySQL hashes it and compares that hash to what it has stored as the password for joesql. It matches, so you are allowed to connect as MySQL user joesql.

Socket Authentication

Now, you want to access the local MySQL server as the MySQL root user. If you naively attempt the same command you used for the standard user joesql, you run into the problem you've noted:

joeuser@localhost:~$ mysql -u root -p     
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

This happens even if you have a password defined for the MySQL root user and enter that when prompted. So what happened? As before, you've invoked the mysql program with command-line arguments -u, root, -p, which tells MySQL that you'd like to connect as the MySQL root user. Now, however, when it checks the plugin value of mysql.user for root, it finds auth_socket instead of one of the password authentication values.

So, instead of asking for a password, it checks a specified socket file that your Unix/Linux system wrote expressly for this purpose, and which includes the name of the OS user that you're acting as. Instead of matching passwords, auth_socket matches usernames. Thus, noting that the OS username you're using, joeuser, is not equal to the MySQL username you're attempting to connect as, root, it forbids the connection with the seriously useless error message Access denied for user 'root'@'localhost'.

So, irritated, you do the standard Linux move of adding 'sudo' to make it do what you want:

joeuser@localhost:~$ sudo mysql -u root -p
[sudo] password for joeuser: 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

This works. Why did this work? When the shell interprets your command, the first thing it finds is that you're using sudo to act as the Unix root user, so it asks you for your sudo password. Once given, the remainder of the command is executed as that user. Thus, the socket file created when connecting to the MySQL server is identified with the Unix root, not joeuser, and MySQL's socket authentication matches this to the MySQL user of the same name. Authentication is passed at this point, but you did specifically request to be asked for your password with the -p option, so MySQL dutifully obliges by asking for the MySQL root user password. After entering it, you are successfully connected to the local MySQL server.

An obvious question once you understand this is, if socket authentication is based on matching usernames (Unix username to MySQL username), is the password even necessary? It isn't! You can leave off the -p and do this:

joeuser@localhost:~$ sudo mysql -u root
[sudo] password for joeuser: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

and skip entering your MySQL root password. This works only for the MySQL root user, and only if that user is set to use auth_socket as their authentication plugin.

Summary

Under the standard defaults for MySQL on recent versions of Ubuntu (at least 16.04-20.04, probably others), these are the commands to most easily connect to the mysql shell:

• MySQL root user: $ sudo mysql -u root

  (enter your sudo password)

• Other MySQL user: $ mysql -u <username> -p

  (enter the <username> MySQL password)

Of course, you've asked about Debian Linux, not Ubuntu. Ubuntu is derived directly from Debian, and I assume it's similar to what I've said here. Comments about how the above explanation varies among various distributions and versions of Linux are welcome.

Answer 3

mysql -u username -p password

as admin

sudo mysql -u username - p password

Answer 4

Firstly Run terminal in root directory

sudo su

hit enter then type your root password then run your mysql using this command

mysql -u root -p

hit enter then enter your mysql password

Good Luck.!

Answer 5

In Ubuntu, all I have to do ran is sudo mysql in GNOME terminal and that's it. I can start making databases or query a data in table, etc.

Answer 6

use this "mysql -uroot -pPassword"

Answer 7

Try "sudo mysql -u root -p" please.

Answer 8

At the command prompt try:

mysql -u root -p

give the password when prompted.

Answer 9

if you're already logged in as root just

mysql -u root

prompting the password will otherwise return as error

Answer 10

if u got still no access to db, 1. in ur error message is set no password right? then first do mysqlpasswd 'username' after that enter and then give it a password type again as requested and then try to access again with mysql -p if you are root

Answer 11

I had the same exact issue on my ArchLinux VPS today.

mysql -u root -p just didn't work, whereas mysql -u root -pmypassword did.

It turned out I had a broken /dev/tty device file (most likely after a udev upgrade), so mysql couldn't use it for an interactive login.

I ended up removing /dev/tty and recreating it with mknod /dev/tty c 5 1 and chmod 666 /dev/tty. That solved the mysql problem and some other issues too.

Answer 12

To your first question:

mysql -u root -p

or

mysqladmin -u root -p "your_command"

depending on what you want to do. The password will be asked of you once you hit enter! I'm guessing you really want to use mysql and not mysqladmin.

For restarting or stopping the MySQL-server on linux, it depends on your installation, but in the common debian derivatives this will work for starting, stopping and restarting the service:

sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql restart
sudo /etc/init.d/mysql status

In some newer distros this might work as well if MySQL is set up as a deamon/service.

sudo service mysql start
sudo service mysql stop
sudo service mysql restart
sudo service mysql status

But the question is really impossible to answer without knowing your particular setup.

Answer 13

1.- How do I get mysql prompt in linux terminal?

mysql -u root -p

At the Enter password: prompt, well, enter root's password :)

You can find further reference by typing mysql --help or at the online manual.

2. How I stop the mysql server from linux terminal?

It depends. Red Hat based distros have the service command:

service mysqld stop

Other distros require to call the init script directly:

/etc/init.d/mysqld stop

3. How I start the mysql server from linux terminal?

Same as #2, but with start.

4. How do I get mysql prompt in linux terminal?

Same as #1.

5. How do I login to mysql server from linux terminal?

Same as #1.

6. How do I solve following error?

Same as #1.

Answer 14

1. you should use mysql command. It's a command line client for mysql RDBMS, and comes with most mysql installations: http://dev.mysql.com/doc/refman/5.1/en/mysql.html

2. To stop or start mysql database (you rarely should need doing that 'by hand'), use proper init script with stop or start parameter, usually /etc/init.d/mysql stop. This, however depends on your linux distribution. Some new distributions encourage service mysql start style.

3. You're logging in by using mysql sql shell.

4. The error comes probably because double '-p' parameter. You can provide -ppassword or just -p and you'll be asked for password interactively. Also note, that some instalations might use mysql (not root) user as an administrative user. Check your sqlyog configuration to obtain working connection parameters.

Answer 15

To stop or start mysql on most linux systems the following should work:

/etc/init.d/mysqld stop

/etc/init.d/mysqld start

The other answers look good for accessing the mysql client from the command line.

Good luck!

Answer 16

I assume you are looking to use mysql client, which is a good thing and much more efficient to use than any phpMyAdmin alternatives.

The proper way to log in with the commandline client is by typing:

mysql -u username -p

Notice I did not type the password. Doing so would of made the password visible on screen, that is not good in multi-user environnment!

After typing this hit enter key, mysql will ask you for your password.

Once logged in, of course you will need:

use databaseName;

to do anything.

Good-luck.