☁️ Introduction

When working with MariaDB/MySQL inside XAMPP on Ubuntu, many developers face confusing issues related to root password behavior, localhost vs 127.0.0.1 access, and authentication plugins.
This tutorial provides a comprehensive step-by-step guide to understand, troubleshoot, and secure your MariaDB setup.

✅ Learn why password prompts behave differently,
✅ How to fix broken authentication (even after using --skip-grant-tables),
✅ And how to set up proper security best practices for production or local development.

🛑 Problem: Different Behavior Between localhost and 127.0.0.1

You may notice:

• When connecting via localhost, MariaDB allows login without password.
• When connecting via 127.0.0.1, it asks for root password.

✅ This is not a bug — it’s due to different connection methods.

In MariaDB, different plugins can authenticate based on how you connect.

🔍 How to Check Current User Authentication Plugins

Login to MariaDB (even without password if allowed):

sudo mysql -u root

Run:

SELECT user, host, plugin FROM mysql.user;
Code language: CSS (css)

Typical result:

🛠 How to Fix: Make Root Always Require Password

By default, in Ubuntu XAMPP, root@localhost may use auth_socket, meaning system login, not password login.
To fix and enforce password login, change the authentication plugin.

✅ Correct command for MariaDB:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourStrongPassword';
FLUSH PRIVILEGES;
Code language: JavaScript (javascript)

✅ After that:

• Connecting via localhost or 127.0.0.1 both require password.
• No automatic system user trust.

🛠 What If You See --skip-grant-tables Error?

If you get:

ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option
Code language: JavaScript (javascript)

It means:

• Grant tables are disabled,
• You cannot ALTER USER or SET PASSWORD.

✅ Solution:

1. Stop MariaDB:

sudo /opt/lampp/lampp stopmysql

2. Start MariaDB normally:

sudo /opt/lampp/lampp startmysql

3. Then login and fix password properly.

📦 How to Check and Remove Permanent --skip-grant-tables

Sometimes, --skip-grant-tables is accidentally set in XAMPP’s MariaDB configuration.

Check file:

sudo nano /opt/lampp/etc/my.cnf

Look for:

skip-grant-tables

✅ If found, comment it out:

#skip-grant-tables
Code language: CSS (css)

✅ Then restart MariaDB.

🔥 Best Practices to Secure MariaDB on Ubuntu (especially inside XAMPP)

🛠 How to Create a New Admin User (Recommended)

1. Login:

mysql -u root -p

2. Run:

CREATE USER 'adminuser'@'localhost' IDENTIFIED BY 'yourSecurePassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Code language: JavaScript (javascript)

✅ Now you can use adminuser safely for all database operations.

📋 Summary Checklist

🚀 Final Notes

• When using MariaDB with XAMPP in Ubuntu, always verify connection method differences (localhost vs 127.0.0.1).
• Ensure MariaDB server runs without –skip-grant-tables for full security.
• Don’t use root for web apps — create a limited user.
• Always restart XAMPP cleanly after major database config changes.

🧠 Pro Tip

If you’re planning to expose XAMPP or MariaDB over the internet (even in test environments):

• Enable SSL/TLS for MySQL connections.
• Use firewall rules to restrict IP access.
• Prefer SSH tunnels for remote database connections instead of direct port opening.

📢 Conclusion

“A secure database setup begins with understanding the basics: authentication, connection types, and privilege management.”

Following the steps in this tutorial will ensure you have a properly secured and consistent MariaDB environment inside XAMPP, ready for both development and production testing.

Stay secure. Happy coding! 🚀

✅ Ready-To-Use Final Command Set (Summary)

sudo /opt/lampp/lampp stopmysql
sudo nano /opt/lampp/etc/my.cnf   # Comment out skip-grant-tables if exists
sudo /opt/lampp/lampp startmysql

mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourStrongPassword!';
FLUSH PRIVILEGES;

CREATE USER 'adminuser'@'localhost' IDENTIFIED BY 'yourSecurePassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;
Code language: PHP (php)

✅ Done!

Rajesh Kumar

I’m a DevOps/SRE/DevSecOps/Cloud Expert passionate about sharing knowledge and experiences. I have worked at Cotocus. I share tech blog at DevOps School, travel stories at Holiday Landmark, stock market tips at Stocks Mantra, health and fitness guidance at My Medic Plus, product reviews at TrueReviewNow , and SEO strategies at Wizbrand.

Do you want to learn Quantum Computing?

Please find my social handles as below;

Rajesh Kumar Personal Website
Rajesh Kumar at YOUTUBE
Rajesh Kumar at INSTAGRAM
Rajesh Kumar at X
Rajesh Kumar at FACEBOOK
Rajesh Kumar at LINKEDIN
Rajesh Kumar at WIZBRAND

Rajesh Kumar DailyLogs