Info
Content

MariaDB deny default root@localhost FIX / Allow all IPs / Recreate user

 

Can login as root using mysql -u root but not from PHP on the same host

 

First, login to MySQL

sudo mysql -u root

Check your accounts present in your db

SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| admin            | localhost |
| debian-sys-maint | localhost |
| root             | localhost |
+------------------+-----------+

Delete current 'root'@'localhost' account

DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)

Recreate your user

CREATE USER 'root'@'localhost' IDENTIFIED BY '';
Query OK, 0 rows affected (0,00 sec)

Add password for better security using IDENTIFIED BY 'Passw0rd' instead of IDENTIFIED BY ''

Give permissions to your user (don't forget to flush privileges)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)

FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)

quit for exit

'root'@'%' instead of 'root'@'localhost' for access from everywhere. VERY DANGEROUS

If you want the server to be accessible from everywhere you'll need to comment bind-address = 127.0.0.1 from /etc/mysql/mariadb.conf.d/50-server.cnf using a # or set it to 0.0.0.0. remember to restart the service to apply the config

 

No Comments
Back to top