mysql -u root -p: What the Flags Mean and Why You Should Stop Using Root

Q: How do I avoid typing the MySQL password every time?

Put it in ~/.my.cnf under a client section and chmod the file to 600. The mysql client picks it up automatically. Never pass the password as a command-line argument - it leaks through shell history and the process list.

mysql -u root -p means: connect as the user root, and prompt me for a password. The -p deliberately takes no argument - if you type -pmypassword it works, but the password is now in your shell history and visible to every user on the box via ps. The bigger point is the one nobody makes: this command is fine for administering the server and completely wrong for running an application. If your app’s connection string says root, you have given a SQL injection bug the ability to drop your database.

What each flag actually does
Never put the password on the command line
Why your application must not connect as root
The auth_socket surprise on Ubuntu
The commands you will actually run as root
How this fits the rest of the stack
FAQ

What each flag actually does

mysql -u root -p

-u root - connect as the MySQL user named root. This is a MySQL account, entirely unrelated to the Linux root user, despite the shared name. That confusion is the source of a surprising number of support threads.

-p - prompt for a password. Note the space that is not there: -p on its own means ask me, while -pSecret123 means the password is Secret123. There is no space between -p and the value, which is unlike almost every other command-line tool and catches everyone once.

Other flags worth knowing:

mysql -u appuser -p -h db.internal -P 3306 appdb

-h - the host (defaults to localhost). -P - the port, capital P, because lowercase is taken. And a bare word at the end is the database to USE on connect, which saves you a step.

On a local connection, -h localhost and -h 127.0.0.1 are not the same thing. localhost makes MySQL use a Unix socket; 127.0.0.1 makes it use TCP. They can hit different authentication rules, which is why a connection sometimes works one way and fails the other with identical credentials.

Never put the password on the command line

This works and you should not do it:

mysql -u root -pSuperSecret123

Two problems, both real:

It goes into your shell history. ~/.bash_history now contains your production database password in plaintext, and it will still be there in three years.

It is visible to every user on the machine. Command-line arguments are not private. Anyone who runs ps aux while your command executes sees the password. On a shared box, that is a credential leak that requires no skill at all to exploit.

MySQL will actually warn you about this - Warning: Using a password on the command line interface can be insecure - and the warning is routinely ignored.

For scripts and automation, use a config file instead:

# ~/.my.cnf
[client]
user=backup
password=whatever
host=db.internal

chmod 600 ~/.my.cnf

Then mysql with no flags at all picks it up. The permissions matter - MySQL will complain if the file is world-readable, and it is right to.

Why your application must not connect as root

This is the part that matters more than the syntax.

The MySQL root user can do anything: read every database, drop every table, create users, grant privileges, read files from disk with LOAD DATA INFILE. When your application connects as root, every one of those capabilities is one SQL injection bug away from being available to an attacker.

With a properly scoped user, the same injection bug is contained. An attacker who finds injection in a SELECT against a user with only SELECT, INSERT, UPDATE, and DELETE on one database can do damage to that database - but cannot drop it, cannot read your other databases, cannot create a new admin user, and cannot read /etc/passwd.

Create a real application user:

CREATE USER 'appuser'@'10.0.%' IDENTIFIED BY 'a-long-random-string';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'10.0.%';
FLUSH PRIVILEGES;

Note what is not granted: no DROP, no CREATE, no GRANT, no FILE. Migrations need more - give the migration job a separate, more privileged user, and run it as a distinct step rather than granting ALTER to the process serving web traffic.

The host part of the username is a real security control, not decoration. 'appuser'@'10.0.%' can only connect from your private network. 'appuser'@'%' can connect from anywhere in the world.

The auth_socket surprise on Ubuntu

Install MySQL on Ubuntu or Debian, try to log in as root with the password you set, and get denied. Then discover that sudo mysql with no password at all works fine.

This is not a bug. Modern MySQL on Debian-family systems configures the root account with the auth_socket plugin, which authenticates by checking your operating system user rather than a password. If you are Linux root, you are MySQL root. If you are not, no password will help you.

Check what a user is using:

SELECT user, host, plugin FROM mysql.user;

If root shows auth_socket, that is your answer. This is a genuinely good default - it means there is no root password to leak or brute-force - and you should generally leave it alone, administering the server with sudo mysql.

If you truly need password login for root, switch the plugin:

ALTER USER 'root'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'a-strong-password';
FLUSH PRIVILEGES;

But ask why first. Wanting a root password usually means an application is about to connect as root, which is the thing to avoid.

The commands you will actually run as root

Root exists for administration. This is what that legitimately looks like:

SHOW DATABASES;
SHOW PROCESSLIST;                    -- what is running right now
SELECT user, host, plugin FROM mysql.user;
SHOW GRANTS FOR 'appuser'@'10.0.%';

SHOW PROCESSLIST is the one to reach for when the database is slow and you need to know why now. It shows every live connection and what it is executing. A query that has been running for 400 seconds and is blocking everything else will be sitting right there, and you can end it:

KILL 12345;

And the shape of a day-to-day admin session:

sudo mysql                # auth_socket, no password needed
USE appdb;
SHOW TABLES;
SHOW CREATE TABLE orders\G

The \G terminator instead of ; prints results vertically, one field per line, which is the difference between a readable schema and a wall of wrapped text.

The division to hold onto: root is for humans doing administration, interactively. Applications get scoped users. Anything else is handing an injection bug a loaded weapon.

How this fits the rest of the stack

Whatever you decide here, the cost of the decision only shows up as a bill. The RunxBuild hosting calculator is the right place to model that before committing: the compute, the database, the storage, the bandwidth, the worker - each one is a separate line item, and the real cost of a platform is the sum, not the headline number. The RunxBuild dashboard is where the team sees the actual usage once it is running.

Useful related references:

FAQ

What does mysql -u root -p mean?

Connect to MySQL as the user root, and prompt for the password. The -p deliberately takes no value - typing -pMyPassword works but puts the password in your shell history and makes it visible to anyone running ps on the machine.

Why does MySQL say access denied for root even with the right password?

On Ubuntu and Debian, the root account usually uses the auth_socket plugin, which authenticates on your operating system user rather than a password. Run sudo mysql with no password. Check with SELECT user, host, plugin FROM mysql.user.

Should my application connect as root?

No. Root can drop any database, create users, and read files from disk, so any SQL injection bug in your app inherits all of that. Create a user with only SELECT, INSERT, UPDATE and DELETE on the one database it needs, and restrict its host to your private network.

How do I avoid typing the MySQL password every time?

Put it in ~/.my.cnf under a [client] section and chmod the file to 600. The mysql client picks it up automatically. Never pass the password as a command-line argument - it leaks through shell history and the process list.

What is the difference between -h localhost and -h 127.0.0.1?

localhost makes the MySQL client use a Unix socket; 127.0.0.1 makes it use TCP. They can match different authentication rules, which is why the same credentials sometimes work one way and fail the other.