1

I have recently ran into a problem, I was trying to change root password, which I forgot, but it seems that I somehow managed to completely break the mysql service itself and due to the fact many of my other services had problems connecting to the server even prior to my stupidity striking, I decided that it would be the best to just do a full reisntall. To remove all the components I used apt-get purge mysql*, however when I executed the command, the command mysql seemed to be uneffected. After a few attempts and a reboot, when I finally seemed to have purged mysql, when I attempted to install, it installed, but I wasnt shown any configuration, when using the mysql command it didnt even ask me for a user, when I tried to change the root password it said ok, but the password didnt work. Im very confused and frustrated, this hasnt happened for the first time, reinstalling mysql is literally my number one nightmare. Please help me, Im absolutely clueless about what should I do.

EDIT: I actually got to set the password, but... It lets me to log in with ANY password, moreover, no remote connection actually works even after using mysql_secure_installation. I dont know what the actual f is going on, but my mysql installation is completely broken

1
  • I think you'll find that the installation comes with a root user authenticated by so_peercred. You shouldn't be trying to use this account remotely.
    – symcbean
    Oct 21, 2023 at 20:42

1 Answer 1

-1

you may have added an 'anonymous user' by accident while trying to set up. You have to delete that user from the mysql database:

>delete from mysql.user where User='';

then create a new 'root' user that can come from any host: >create user 'root'@'%' identified by 'your_new_password_here';

next give yourself full powers:

>grant all on *.* to 'root'@'%'; flush privileges;

(if im not mistaken, the grant privileges must be done by the server, no remote client allowed.)

this will make it so only your root and password will work. The secure_installation option has messed me up before. Manually is better for understanding anyways.

As for your connection problem, you probably need to change the bind address. mysql/mariadb comes with a default of bind_address=127.0.0.1., which is basically localhost. So only that machine. To allow other machines, change that, on the server, to: bind_address=0.0.0.0

you can determine your current bind_address from mysql. Sign-in to the server using your new root and password, then: >show variables like 'bind_address'; you will see something like this:

| Variable_name | Value   |
+---------------+---------+
| bind_address  | 0.0.0.0 |
+---------------+---------+

if it isn't 0.0.0.0 you need to change it.

it can be tricky to find the .cnf file. for instance, mine is in: /etc/mysql/mariadb.conf.d/50-server.cnf but I think that varies quite a bit with version/distro(?) as many people have trouble finding it.

What you are doing here is opening it up to any IP. If you need to be more secure, you can add your client-side IP or add mulitple IPs. But then it will be limited to just those. (I always use 0.0.0.0 with a good password and its all i need.)

3
  • I've not encountered a default anonymous user with mysqld on rhel, centos nor Debian.
    – symcbean
    Oct 21, 2023 at 20:34
  • @symcbean ok, i edited my answer. From what the OP described, it sounds like he has anonymous user set up in the server.
    – bungee1980
    Oct 21, 2023 at 21:14
  • so_peercred authentication is the default method for root on most mysql variants
    – symcbean
    Oct 26, 2023 at 15:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .