☁️ 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 Rajesh Kumar, a DevOps, SRE, DevSecOps, Cloud, and Platform Engineering expert passionate about sharing practical knowledge, real-world experiences, and industry best practices. I have worked at Cotocus and regularly write about technology, travel, investing, health, product reviews, and digital marketing through my various platforms.

I publish technical articles at DevOps School, travel stories at Holiday Landmark, stock market insights at Stocks Mantra, health and fitness guidance at My Medic Plus, product reviews at TrueReviewNow, and SEO and digital marketing strategies at Wizbrand.