While MySQL typically requires password authentication for security reasons, there are valid use cases for passwordless access, particularly in development environments or automated scripts. This guide covers multiple approaches to achieve this on Ubuntu servers.

The most secure way to implement passwordless access is through MySQL's native auth_socket plugin, which authenticates users based on their system credentials:

# Login to MySQL as root
sudo mysql -u root

# Create a new user or modify existing one to use socket auth
CREATE USER 'devuser'@'localhost' IDENTIFIED WITH auth_socket;
GRANT ALL PRIVILEGES ON *.* TO 'devuser'@'localhost';
FLUSH PRIVILEGES;

For temporary development setups, you can set an empty password (not recommended for production):

ALTER USER 'username'@'localhost' IDENTIFIED BY '';

Then modify your MySQL client configuration (~/.my.cnf):

[client]
user=username
password=

Important security measures when implementing passwordless access:

• Never use this for remote connections
• Restrict to localhost only (127.0.0.1)
• Limit privileges to specific databases
• Consider using .my.cnf with strict permissions (600)

For scripts requiring passwordless access, here's a Python example using the auth_socket method:

import mysql.connector

config = {
  'user': 'devuser',
  'host': 'localhost',
  'auth_plugin': 'mysql_native_password', # or 'auth_socket' if supported
  'database': 'test_db'
}

connection = mysql.connector.connect(**config)

If you encounter problems, check:

# Verify user privileges
SELECT user, host, plugin FROM mysql.user;

# Check authentication method
SHOW VARIABLES LIKE 'default_authentication_plugin';

---

Many developers prefer passwordless authentication for MySQL when working on local development environments or trusted networks. This approach eliminates the need to repeatedly enter credentials while maintaining security through other means like Unix socket authentication.

Before proceeding, ensure you have:

• MySQL server installed on Ubuntu
• sudo privileges
• Basic understanding of MySQL configuration

1. Edit MySQL Configuration File

First, open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or modify these lines under the [mysqld] section:

[mysqld]
skip-grant-tables

2. Restart MySQL Service

Apply the changes by restarting MySQL:

sudo systemctl restart mysql

3. Create a Passwordless User (Optional)

For more granular control, you can create a specific user with no password requirement:

mysql -u root
CREATE USER 'devuser'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'devuser'@'localhost';
FLUSH PRIVILEGES;

For better security while maintaining convenience, consider using Unix socket authentication:

sudo mysql -u root
CREATE USER 'devuser'@'localhost' IDENTIFIED WITH auth_socket;
GRANT ALL PRIVILEGES ON *.* TO 'devuser'@'localhost';
FLUSH PRIVILEGES;

While convenient, passwordless authentication has security implications:

• Only use this in development environments
• Never expose a passwordless MySQL instance to the internet
• Consider using .my.cnf file for storing credentials securely

After configuration, you can connect simply with:

mysql -u devuser

Or for root access:

sudo mysql

If you encounter problems:

1. Check MySQL error logs: sudo tail -f /var/log/mysql/error.log
2. Verify the skip-grant-tables option is active
3. Ensure proper user privileges are set