Ubuntu 20.04, MySQL 10.7.3-MariaDB.
Good afternoon, I ran into a problem, if you disable anonymous access, it is impossible to change the password of the databases. At what creation of databases works.
After installation I tried to connect via SSH to mariadb
mysql -u root -p
Driving in absolutely any password, I was connected.
Next, I set a password for root, as advised on the Internet.
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('ROOTPASSWORD');
UPDATE mysql.user SET plugin = '' WHERE user = 'root' AND host = 'localhost';
FLUSH PRIVILEGES;
now using mysql -u root -p connects only after entering the correct root password.
After that I tried to change the password for different databases and I get
2023-06-28 12:31:21 53 [Warning] Aborted connection 53 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)
But creating databases works if aapanel has the correct root password from mariadb. It is also impossible to change the root password through the aapanel panel.
How can this be fixed so that the password changes in the same way as the creation of databases through root and password?
I also encountered the fact that if you put skip-name-resolve, then it will not connect due to the fact that it does not resolve "localhost". I don’t know how important this is, but to increase performance, it is advised to add skip-name-resolve to the config. Maybe it makes sense to replace the localhost with 127.0.0.1?
Strange, but CMS Opencart refuses to connect to the database on 127.0.0.1, only on localhost. Maybe I'm wrong.
Also, when creating a database, 2 users are created, for 127.0.0.1 and for localhost, is that how it should be?
By default in mariadb config max_allowed_packet = 100G
Obviously errors are flying into the log. Maybe it's worth fixing it too, to some smaller default values for everyone?
Immediately after installing mariadb in ubuntu 20 or debian 11, start, stop, restart mariadb does not work. By errors from the log, deleting files from the /www/server/data folder
aria_log.00000001
aria_log_control
ib_logfile0
Next, find out the pid by port
sudo ss -tulpn | grep ':3306'
kill -9 pid.
After that start, stop, restart works fine. Perhaps this, too, can be somehow fixed for everyone?