{"id":49209,"date":"2025-04-29T06:19:10","date_gmt":"2025-04-29T06:19:10","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=49209"},"modified":"2025-04-29T06:19:10","modified_gmt":"2025-04-29T06:19:10","slug":"managing-mariadb-root-password-and-connection-behavior-in-xampp-ubuntu","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/managing-mariadb-root-password-and-connection-behavior-in-xampp-ubuntu\/","title":{"rendered":"Managing MariaDB Root Password and Connection Behavior in XAMPP (Ubuntu)"},"content":{"rendered":"\n

<\/h1>\n\n\n\n
\n\n\n\n

\u2601\ufe0f Introduction<\/h1>\n\n\n\n

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

\u2705 Learn why password prompts behave differently<\/strong>,
\u2705 How to fix broken authentication<\/strong> (even after using --skip-grant-tables<\/code>),
\u2705 And how to set up proper security best practices<\/strong> for production or local development.<\/p>\n\n\n\n

\n\n\n\n

\ud83d\uded1 Problem: Different Behavior Between localhost<\/code> and 127.0.0.1<\/code><\/h1>\n\n\n\n

You may notice:<\/p>\n\n\n\n

    \n
  • When connecting via localhost<\/code>, MariaDB allows login without password<\/strong>.<\/li>\n\n\n\n
  • When connecting via 127.0.0.1<\/code>, it asks for root password<\/strong>.<\/li>\n<\/ul>\n\n\n\n

    \u2705 This is not a bug<\/strong> \u2014 it’s due to different connection methods<\/strong>.<\/p>\n\n\n\n

    Connection Type<\/th>Behavior<\/th><\/tr><\/thead>
    localhost<\/code><\/td>Connects via Unix Socket<\/strong> (auth_socket plugin)<\/td><\/tr>
    127.0.0.1<\/code><\/td>Connects via TCP\/IP<\/strong> (password-based plugin)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

    In MariaDB, different plugins<\/strong> can authenticate based on how you connect.<\/p>\n\n\n\n

    \n\n\n\n

    \ud83d\udd0d How to Check Current User Authentication Plugins<\/h1>\n\n\n\n

    Login to MariaDB (even without password if allowed):<\/p>\n\n\n

    sudo mysql -u root\n<\/code><\/span><\/pre>\n\n\n
    Run:<\/p>\n\n\n
    SELECT<\/span> user<\/span>, host<\/span>, plugin<\/span> FROM<\/span> mysql<\/span>.user<\/span>;\n<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
    Typical result:<\/p>\n\n\n\n
    user<\/th>host<\/th>plugin<\/th><\/tr><\/thead>
    root<\/td>localhost<\/td>auth_socket<\/td><\/tr>
    root<\/td>127.0.0.1<\/td>mysql_native_password<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n\n\n\n
    \ud83d\udee0 How to Fix: Make Root Always Require Password<\/h1>\n\n\n\n
    By default, in Ubuntu XAMPP, root@localhost<\/code> may use auth_socket<\/code>, meaning system login<\/strong>, not password login<\/strong>.
    To fix and enforce password login<\/strong>, change the authentication plugin.<\/p>\n\n\n\n
    \u2705 Correct command for MariaDB<\/strong>:<\/p>\n\n\n
    ALTER USER 'root'<\/span>@'localhost'<\/span> IDENTIFIED BY 'yourStrongPassword'<\/span>;\nFLUSH PRIVILEGES;\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    \u2705 After that:<\/p>\n\n\n\n
      \n
    • Connecting via localhost<\/code> or 127.0.0.1<\/code> both require password<\/strong>.<\/li>\n\n\n\n
    • No automatic system user trust.<\/li>\n<\/ul>\n\n\n\n
      \n\n\n\n
      \ud83d\udee0 What If You See --skip-grant-tables<\/code> Error?<\/h1>\n\n\n\n
      If you get:<\/p>\n\n\n
      ERROR 1290<\/span> (HY000): The MariaDB server is running with<\/span> the --skip-grant-tables option\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
      It means:<\/p>\n\n\n\n
        \n
      • Grant tables are disabled<\/strong>,<\/li>\n\n\n\n
      • You cannot ALTER USER or SET PASSWORD<\/strong>.<\/li>\n<\/ul>\n\n\n\n
        \u2705 Solution:<\/p>\n\n\n\n
          \n
        1. Stop MariaDB<\/strong>:<\/li>\n<\/ol>\n\n\n
          sudo \/opt\/lampp\/lampp stopmysql\n<\/code><\/span><\/pre>\n\n\n
            \n
          1. Start MariaDB normally<\/strong>:<\/li>\n<\/ol>\n\n\n
            sudo \/opt\/lampp\/lampp startmysql\n<\/code><\/span><\/pre>\n\n\n
              \n
            1. Then login and fix password<\/strong> properly.<\/li>\n<\/ol>\n\n\n\n
              \n\n\n\n
              \ud83d\udce6 How to Check and Remove Permanent --skip-grant-tables<\/code><\/h1>\n\n\n\n
              Sometimes, --skip-grant-tables<\/code> is accidentally set in XAMPP\u2019s MariaDB configuration.<\/p>\n\n\n\n
              Check file:<\/p>\n\n\n
              sudo nano \/opt\/lampp\/etc\/my.cnf\n<\/code><\/span><\/pre>\n\n\n
              Look for:<\/p>\n\n\n
              skip-grant-tables\n<\/code><\/span><\/pre>\n\n\n
              \u2705 If found, comment it out<\/strong>:<\/p>\n\n\n
              #skip-grant-tables<\/span>\n<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
              \u2705 Then restart MariaDB.<\/p>\n\n\n\n
              \n\n\n\n
              \ud83d\udd25 Best Practices to Secure MariaDB on Ubuntu (especially inside XAMPP)<\/h1>\n\n\n\n
              Practice<\/th>Why It Matters<\/th><\/tr><\/thead>
              Always set a strong password for root<\/td>Prevent unauthorized access<\/td><\/tr>
              Disable root remote login<\/td>root<\/code> should only login from localhost<\/code><\/td><\/tr>
              Create a new admin user<\/td>Use adminuser@localhost<\/code> for your apps instead of root<\/td><\/tr>
              Keep your MariaDB updated<\/td>Get latest security patches<\/td><\/tr>
              Secure XAMPP itself<\/td>Password-protect XAMPP dashboard and phpMyAdmin<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
              \n\n\n\n
              \ud83d\udee0 How to Create a New Admin User (Recommended)<\/h1>\n\n\n\n
                \n
              1. Login:<\/li>\n<\/ol>\n\n\n
                mysql -u root -p\n<\/code><\/span><\/pre>\n\n\n
                  \n
                1. Run:<\/li>\n<\/ol>\n\n\n
                  CREATE USER 'adminuser'<\/span>@'localhost'<\/span> IDENTIFIED BY 'yourSecurePassword123!'<\/span>;\nGRANT ALL PRIVILEGES ON *.* TO 'adminuser'<\/span>@'localhost'<\/span> WITH GRANT OPTION;\nFLUSH PRIVILEGES;\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
                  \u2705 Now you can use adminuser<\/code> safely for all database operations.<\/p>\n\n\n\n
                  \n\n\n\n
                  \ud83d\udccb Summary Checklist<\/h1>\n\n\n\n
                  Action<\/th>Done?<\/th><\/tr><\/thead>
                  Fix root password with ALTER USER<\/code><\/td>\u2705<\/td><\/tr>
                  Restart MariaDB without --skip-grant-tables<\/code><\/td>\u2705<\/td><\/tr>
                  Remove skip-grant-tables<\/code> from my.cnf<\/code><\/td>\u2705<\/td><\/tr>
                  Disable root remote access (optional)<\/td>\u2705<\/td><\/tr>
                  Create separate admin user for apps<\/td>\u2705<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                  \n\n\n\n
                  \ud83d\ude80 Final Notes<\/h1>\n\n\n\n
                    \n
                  • When using MariaDB with XAMPP in Ubuntu, always verify connection method differences<\/strong> (localhost<\/code> vs 127.0.0.1<\/code>).<\/li>\n\n\n\n
                  • Ensure MariaDB server runs without –skip-grant-tables<\/strong> for full security.<\/li>\n\n\n\n
                  • Don’t use root<\/code> for web apps<\/strong> \u2014 create a limited user.<\/li>\n\n\n\n
                  • Always restart XAMPP cleanly<\/strong> after major database config changes.<\/li>\n<\/ul>\n\n\n\n
                    \n\n\n\n
                    \ud83e\udde0 Pro Tip<\/h1>\n\n\n\n
                    If you\u2019re planning to expose XAMPP or MariaDB over the internet (even in test environments):<\/p>\n\n\n\n
                      \n
                    • Enable SSL\/TLS for MySQL connections.<\/li>\n\n\n\n
                    • Use firewall rules to restrict IP access.<\/li>\n\n\n\n
                    • Prefer SSH tunnels for remote database connections instead of direct port opening.<\/li>\n<\/ul>\n\n\n\n
                      \n\n\n\n
                      \ud83d\udce2 Conclusion<\/h1>\n\n\n\n
                      \n
                      “A secure database setup begins with understanding the basics: authentication, connection types, and privilege management.”<\/p>\n<\/blockquote>\n\n\n\n
                      Following the steps in this tutorial will ensure you have a properly secured and consistent MariaDB environment inside XAMPP<\/strong>, ready for both development and production testing.<\/p>\n\n\n\n
                      Stay secure. Happy coding! \ud83d\ude80<\/p>\n\n\n\n
                      \n\n\n\n
                      \u2705 Ready-To-Use Final Command Set (Summary)<\/h1>\n\n\n
                      sudo \/opt\/lampp\/lampp stopmysql\nsudo nano \/opt\/lampp\/etc\/my.cnf   # Comment out skip-grant-tables if exists<\/span>\nsudo \/opt\/lampp\/lampp startmysql\n\nmysql -u root -p\n\nALTER USER 'root'<\/span>@'localhost'<\/span> IDENTIFIED BY 'YourStrongPassword!'<\/span>;\nFLUSH PRIVILEGES;\n\nCREATE USER 'adminuser'<\/span>@'localhost'<\/span> IDENTIFIED BY 'yourSecurePassword123!'<\/span>;\nGRANT ALL PRIVILEGES ON *.* TO 'adminuser'<\/span>@'localhost'<\/span> WITH GRANT OPTION;\nFLUSH PRIVILEGES;\nEXIT<\/span>;\n<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
                      \u2705 Done!<\/p>\n","protected":false},"excerpt":{"rendered":"
                      \u2601\ufe0f 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… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-49209","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/49209","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=49209"}],"version-history":[{"count":1,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/49209\/revisions"}],"predecessor-version":[{"id":49210,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/49209\/revisions\/49210"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=49209"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=49209"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=49209"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}