---

☁️ 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.

Connection Type	Behavior
localhost	Connects via Unix Socket (auth_socket plugin)
127.0.0.1	Connects via TCP/IP (password-based plugin)

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;

Typical result:

user	host	plugin
root	localhost	auth_socket
root	127.0.0.1	mysql_native_password

---

🛠 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;

✅ 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

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

✅ Then restart MariaDB.

---

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

Practice	Why It Matters
Always set a strong password for root	Prevent unauthorized access
Disable root remote login	root should only login from localhost
Create a new admin user	Use adminuser@localhost for your apps instead of root
Keep your MariaDB updated	Get latest security patches
Secure XAMPP itself	Password-protect XAMPP dashboard and phpMyAdmin

---

🛠 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;

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

---

📋 Summary Checklist

Action	Done?
Fix root password with ALTER USER	✅
Restart MariaDB without --skip-grant-tables	✅
Remove skip-grant-tables from my.cnf	✅
Disable root remote access (optional)	✅
Create separate admin user for apps	✅

---

🚀 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;

✅ Done!