View original (French)

Access denied for user: 'root'@'localhost'

Solved
LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention   -  
brucine Posted messages 24849 Registration date   Status Member Last intervention   -

Hello,

I'm trying to set up a website with a database. The site is 100% functional locally via Wampserver. However, I would like to host it on my Raspberry Pi so I can access it outside of the local network. I have therefore installed PHP, MySQL, and phpMyAdmin on my RPi. I can access it using my RPi's address followed by /phpmyadmin. However, when I try to import my database, I get this error message (see photo). I have looked online, but I haven't found anything that resolves my issue.


6 answers

  1.
    oakthunder Posted messages 31 Status Member 1
     

    I have hosted several sites on my raspberrypi4 and I have always managed to import databases on phpmyadmin.

    Can you detail your problem and show me the content of the file you want to import into phpmyadmin?

    Have you checked the MySQL usernames and passwords?

    0
    1. LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
       

      Hello, I followed this tutorial to configure (https://raspberry-pi.fr/installer-serveur-web-raspberry-lamp/). I changed the root password and I use it to log in to phpMyAdmin.

      As for the content of my database, it's quite basic. I don't think that is the source of my problem. But I will still share the code:

      -- phpMyAdmin SQL Dump -- version 5.1.1 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1:3306 -- Generated on: Sun, 10 Apr 2022 09:56 -- Server version: 5.7.36 -- PHP version: 7.4.26 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; -- ----------------------------------------------------- -- Schema db_heh_music -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS db_heh_music DEFAULT CHARACTER SET utf8 ; USE db_heh_music ; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `db_heh_music` -- -- -------------------------------------------------------- -- -- Structure of the `client` table -- DROP TABLE IF EXISTS `client`; CREATE TABLE IF NOT EXISTS `client` ( `ncli` int(11) NOT NULL AUTO_INCREMENT, `nom` varchar(255) NOT NULL, `prenom` varchar(255) NOT NULL, `mail` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `admin` int(11) NOT NULL, `visible` tinyint(4) NOT NULL, PRIMARY KEY (`ncli`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; -- -- Data dump of the `client` table -- INSERT INTO `client` (`ncli`, `nom`, `prenom`, `mail`, `password`, `admin`, `visible`) VALUES (1, 'Vandermiers', 'Loïc', '***@***', '$2y$10$.7.8mmKpBx//4lwN0DJmuu6gMUY1n4zKLRGqjUJgjpWV3bcyXEZsS', 1, 1); -- -------------------------------------------------------- -- -- Structure of the `commande` table -- DROP TABLE IF EXISTS `commande`; CREATE TABLE IF NOT EXISTS `commande` ( `ncom` int(11) NOT NULL, `nprod` int(11) NOT NULL, `quantité` int(11) NOT NULL, `date` datetime DEFAULT NULL, `ncli` int(11) DEFAULT NULL, PRIMARY KEY (`ncom`,`nprod`), KEY `ncli_idx` (`ncli`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Structure of the `panier` table -- DROP TABLE IF EXISTS `panier`; CREATE TABLE IF NOT EXISTS `panier` ( `nprod` int(11) NOT NULL, `quantité` int(11) NOT NULL, `ncli` int(11) NOT NULL, PRIMARY KEY (`nprod`,`ncli`), KEY `ncli_idx` (`ncli`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Structure of the `produits` table -- DROP TABLE IF EXISTS `produits`; CREATE TABLE IF NOT EXISTS `produits` ( `nprod` int(11) NOT NULL AUTO_INCREMENT, `nom_produit` varchar(255) NOT NULL, `artiste` varchar(255) NOT NULL, `prix` float NOT NULL, `visible` tinyint(4) NOT NULL, PRIMARY KEY (`nprod`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; -- -- Data dump of the `produits` table -- INSERT INTO `produits` (`nprod`, `nom_produit`, `artiste`, `prix`, `visible`) VALUES (1, 'Batterie faible','Damso', 14.99, 1), (2, 'Ipséité','Damso', 6.99, 1), (3, 'Lithopedion','Damso', 9.99, 1), (4, 'Qalf infinity','Damso', 19.99, 1), (5, 'Civilisation','Orelsan', 14.99, 1), (6, 'La fête est finie','Orelsan', 17.99, 1), (7, 'Étrange histoire de Mr Anderson','Laylow', 11.99, 1), (8, 'Freestyle covid','Menace Santana', 19, 1), (9, '13','Indochine', 8.99, 1), (10, 'La vraie vie','Bigflo et Oli', 4.99, 1), (11, 'Les étoiles vagabondes','Nekfeu', 22.99, 1), (12, 'The 2nd law','Muse', 9.99, 1); -- -- Constraints for the dumped tables -- -- -- Constraints for the `commande` table -- ALTER TABLE `commande` ADD CONSTRAINT `ncli_commande` FOREIGN KEY (`ncli`) REFERENCES `client` (`ncli`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for the `panier` table -- ALTER TABLE `panier` ADD CONSTRAINT `ncli_panier` FOREIGN KEY (`ncli`) REFERENCES `client` (`ncli`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `nprod` FOREIGN KEY (`nprod`) REFERENCES `produits` (`nprod`) ON DELETE NO ACTION ON UPDATE NO ACTION; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
      0
  2. oakthunder Posted messages 31 Status Member 1
     

    I think I found the problem.

    Go to your Pi, turn it on and connect, open a terminal.

    Enter the following commands:

    sudo mysql --user=root

    Once connected do this:

     DROP USER 'root'@'localhost'; CREATE USER 'root'@'localhost' IDENTIFIED BY 'your password'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

    For your next connections with the terminal, use:

    sudo mysql --user=root --password=your password

    For phpmyadmin, I had issues with apt install phpmyadmin.

    I recommend doing this, the comments are in parentheses don't enter them in the terminal:

    cd /var/www/html (to access the server directory) sudo rm -R phpmyadmin (to remove the old phpmyadmin folder) sudo wget https://files.phpmyadmin.net/phpMyAdmin/5.2.0/phpMyAdmin-5.2.0-all-languages.zip (to download phpmyadmin) sudo unzip phpMyAdmin-5.2.0-all-languages.zip (to unzip the file) sudo mv phpMyAdmin-5.2.0-all-languages phpmyadmin (to rename the folder) sudo rm phpMyAdmin-5.2.0-all-languages.zip (to remove the zip)

    Now go to your pi's IP/phpmyadmin and connect.

    0
    1. LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
       

      I had already changed the MySQL password. So here I connected with the

       sudo mysql --user=root --password=your_password

      But when I want to execute the

       GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

      I get this error message

      0
  3. oakthunder Posted messages 31 Status Member 1
     

    This must be a mariadb problem, try uninstalling and then reinstalling mariadb with these commands, then follow the commands from my previous response:

    sudo apt remove mariadb sudo apt update sudo apt upgrade sudo apt install mariadb-server
    0
    1. LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
       

      I uninstalled and then reinstalled mariadb, however I can no longer connect with 

       sudo mysql --user=root

      I get this error message " Access denied for user 'root'@'localhost' (using password: NO)"

      0
  4. oakthunder Posted messages 31 Status Member 1
     

    I think I have finally found the problem using:

     sudo mysql --user=root --password=your password GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'your password'; FLUSH PRIVILEGES; quit
    0
    1. LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
       

      Since I uninstalled and reinstalled MariaDB, my old password is no longer valid. And if I connect with 

       sudo mysql --user=root

      it doesn't work

      0
  6. oakthunder Posted messages 31 Status Member 1
     

    Your .sql file works perfectly when I import it into phpmyadmin.

    For mariadb try :

    sudo mysql --user=root --password=your old password

    or

    sudo mysql

    then :

    DROP USER 'root'@'localhost'; CREATE USER 'root'@'localhost' IDENTIFIED BY 'your password'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'your password'; FLUSH PRIVILEGES; quit
    0
    1. LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
       

      I succeeded in connecting, but the commands still return errors anyway.

      0
      1. brucine Posted messages 24849 Registration date   Status Member Last intervention   4 170 > LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
         

        Hello,

        Don't jump on me, I don't understand much, I just want to submit a functional test configuration for MariaDB/Adminer that I created (not for myself, I don't use it), and where the unlocking is probably due to the fact that we are using not root but a specific user, and we will note that the MariaDB password is not by default the one for the Pi.

         1 Raspberry pi 4 4GB, standard Raspbian Buster installation, Pi address 192.168.1.29 Software aspect Raspbian Buster comes with Apache 2 pre-installed on Pi 4, remaining to install MariaDB and Adminer. Installing MariaDB sudo apt update sudo apt upgrade sudo apt install mariadb-server sudo mysql_secure-installation For all questions asked, left column, user input, right column: Enter current password (leave blank) Set root password Y New password (your choice) Re-enter new password (confirm) Remove anonymous users Y Disallow root login remotely Y Remove the test database Y Reload privileges tables Y MariaDB has its own identifiers and passwords, which are not the ones for the Pi. By default, MariaDB will connect to the local loop (localhost or 127.0.0.1) and will address root@localhost. Conflicts are documented when using the root user sudo mysql -uroot -p password: mypassword CREATE DATABASE test; While we're at it, and for the reasons mentioned earlier, let's create a dedicated user: CREATE USER 'toto' IDENTIFIED BY 'mypassword'; GRANT ALL PRIVILEGES ON test.* TO 'toto'@'localhost'; Exit, check if it works: sudo mysql -utoto -p Installing Adminer. I didn't bother, I went to Add/Remove Software and installed adminer (4.7.1.1.) but nothing prevents: sudo apt install adminer Now let's download our adminer.php file on the Pi in the appropriate folder: sudo wget "http://www.adminer.org/latest.php" -O/var/www/html/adminer.php All that's left is to check locally and then from the Windows client PC. http://192.168.1.29/adminer.php
        0
  7. oakthunder Posted messages 31 Status Member 1
     

    Adminer can work.

    But you need to use Raspberry Pi Imager to burn a new OS onto your SD card, choose Raspberry Pi OS other and then Raspberry Pi OS 64 bit, then burn the micro SD. Then execute these commands once you have connected your Pi to the internet.

    sudo apt update sudo apt upgrade sudo apt update sudo apt install apache2 mariadb-server libapache2-mod-php sudo apt install php-gd php-json php-mysql php-curl php-mbstring php-intl php-imagick php-xml php-zip cd /var/www/html sudo wget https://files.phpmyadmin.net/phpMyAdmin/5.2.0/phpMyAdmin-5.2.0-all-languages.zip sudo unzip phpMyAdmin-5.2.0-all-languages.zip sudo mv phpMyAdmin-5.2.0-all-languages phpmyadmin sudo rm phpMyAdmin-5.2.0-all-languages.zip sudo mysql --user=root DROP USER 'root'@'localhost'; CREATE USER 'root'@'localhost' IDENTIFIED BY 'your password'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'your password'; FLUSH PRIVILEGES; quit

    Now you can use phpMyAdmin

    0
    1. LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
       

      I hoped not to get to that point, but I think that indeed I won't have any other solutions. I will try that tomorrow and I will keep you updated on the progress.

      0
      1. brucine Posted messages 24849 Registration date   Status Member Last intervention   4 170 > LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
         

        For PhpMyAdmin, I don't know, I haven't used the Pi in a while, and we don't know which version LoloLaSaumur has on their Pi.

        But from memory, if you don't want to hassle with making a new SD card with a new version via Raspberry Pi Imager (and especially putting back everything you had on it), the Pi 4s come preloaded with Raspbian/Raspberry Pi OS, where Apache 2 is pre-installed and MariaDB is also readily available via the pre-configured repositories (or through the graphical package manager for those who prefer).

        1
    2. LoloLaSaumur Posted messages 33 Registration date   Status Member Last intervention  
       

      I just redid everything as requested. Everything works perfectly without errors. However, when I try to access phpmyadmin with the address "IP of my raspberry/phpmyadmin", I get a blank screen with no message.

      UPDATE: thanks to this command, the phpmyadmin homepage was displayed

       sudo ln -s /usr/share/phpmyadmin /var/www/html/phpmyadmin

      UPDATE number 2: I successfully imported my database. Thank you very much for your time and help!

      0