MariaDB: Access denied for user root

[Michal Švarc]

log databáze mariadb:

Kód: [Vybrat]

2022-12-07 14:42:56 0 [Note] InnoDB: Using Linux native AIO
2022-12-07 14:42:56 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-12-07 14:42:56 0 [Note] InnoDB: Uses event mutexes
2022-12-07 14:42:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-12-07 14:42:56 0 [Note] InnoDB: Number of pools: 1
2022-12-07 14:42:56 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-12-07 14:42:56 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-12-07 14:42:56 0 [Note] InnoDB: Completed initialization of buffer pool
2022-12-07 14:42:56 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-12-07 14:42:57 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-12-07 14:42:57 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-12-07 14:42:57 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-12-07 14:42:57 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-12-07 14:42:57 0 [Note] InnoDB: Waiting for purge to start
2022-12-07 14:42:57 0 [Note] InnoDB: 10.3.37 started; log sequence number 1663097902255; transaction id 75492375
2022-12-07 14:42:57 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-12-07 14:42:57 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-12-07 14:42:57 0 [Note] Server socket created on IP: '127.0.0.1'.
2022-12-07 14:42:57 0 [Note] InnoDB: Buffer pool(s) load completed at 221207 14:42:57
2022-12-07 14:42:57 0 [Note] Reading of all Master_info entries succeeded
2022-12-07 14:42:57 0 [Note] Added new Master_info '' to hash table
2022-12-07 14:42:57 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.37-MariaDB-0ubuntu0.20.04.1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 20.04
2022-12-07 14:42:57 8 [Warning] Access denied for user 'root'@'localhost' to database 'mysql'
Na internetu jsem našel spoustu návodů jak se téhly chyby zbavit. Potíž je že všechny spočívají v přidání práv rootovi.

Mě by spíše zajímalo, kde bych ten příkaz měl hledat, abych ho mohl odmazat/zneaktivnit, protože databáze bez ohledu na to že na něj root nedostane odpověd (prostě k té db nená přístup), šlape.

[Reklama]

[Michal Švarc]

aha, zdá se že

Kód: [Vybrat]

SELECT user,authentication_string,plugin,host FROM mysql.user;což v mém případě dává odpověď

Kód: [Vybrat]

root unix_socket localhost s authentication_string prázdným, takže asi nějak vypnout plugin

Kód: [Vybrat]

unix_socket, nebo změnit na uživatele co má do db přístup (předtím i zkusím zjistit co za plugin to je)

[Michal Švarc]

ok. mám za to, že tento plugin nepotřebuju: https://mariadb.com/kb/en/authentication-plugin-unix-socket/
zkusil jsem ho proto vypnout, pomocí zápisu

Kód: [Vybrat]

[mariadb]
unix_socket=OFF
do

Kód: [Vybrat]

/etc/mysql/mariadb.cnfa restartu mariadb

v logu se to projevilo následovně:

Kód: [Vybrat]

2022-12-07 17:01:18 0 [Note] InnoDB: Using Linux native AIO
2022-12-07 17:01:18 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-12-07 17:01:18 0 [Note] InnoDB: Uses event mutexes
2022-12-07 17:01:18 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-12-07 17:01:18 0 [Note] InnoDB: Number of pools: 1
2022-12-07 17:01:18 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-12-07 17:01:18 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-12-07 17:01:18 0 [Note] InnoDB: Completed initialization of buffer pool
2022-12-07 17:01:18 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-12-07 17:01:18 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-12-07 17:01:18 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-12-07 17:01:18 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-12-07 17:01:18 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-12-07 17:01:18 0 [Note] InnoDB: Waiting for purge to start
2022-12-07 17:01:18 0 [Note] InnoDB: 10.3.37 started; log sequence number 1663315506278; transaction id 75501840
2022-12-07 17:01:18 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-12-07 17:01:18 0 [Note] Plugin 'unix_socket' is disabled.
2022-12-07 17:01:18 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-12-07 17:01:18 0 [Note] InnoDB: Buffer pool(s) load completed at 221207 17:01:18
2022-12-07 17:01:18 0 [Note] Server socket created on IP: '127.0.0.1'.
2022-12-07 17:01:18 0 [Note] Reading of all Master_info entries succeeded
2022-12-07 17:01:18 0 [Note] Added new Master_info '' to hash table
2022-12-07 17:01:18 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.37-MariaDB-0ubuntu0.20.04.1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 20.04

to se zdá ok, ale když zkusím zjistit stav mariadb, pomocí

Kód: [Vybrat]

systemctl status mysqldostanu

Kód: [Vybrat]

root@akichan:/etc/mysql# systemctl status mysql
● mariadb.service - MariaDB 10.3.37 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2022-12-07 17:01:19 CET; 21s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 230207 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 230221 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 230227 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
    Process: 230311 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 230313 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 230280 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 31 (limit: 4611)
     Memory: 133.3M
     CGroup: /system.slice/mariadb.service
             └─230280 /usr/sbin/mysqld

Dec 07 17:01:18 akichan.moe systemd[1]: Starting MariaDB 10.3.37 database server...
Dec 07 17:01:18 akichan.moe mysqld[230280]: 2022-12-07 17:01:18 0 [Note] /usr/sbin/mysqld (mysqld 10.3.37-MariaDB-0ubuntu0.20.04.1) starting as process 230280 ...
Dec 07 17:01:19 akichan.moe systemd[1]: Started MariaDB 10.3.37 database server.
Dec 07 17:01:19 akichan.moe /etc/mysql/debian-start[230315]: Upgrading MySQL tables if necessary.
Dec 07 17:01:19 akichan.moe /etc/mysql/debian-start[230318]: Looking for 'mysql' as: /usr/bin/mysql
Dec 07 17:01:19 akichan.moe /etc/mysql/debian-start[230318]: Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
Dec 07 17:01:19 akichan.moe /etc/mysql/debian-start[230318]: ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded
Dec 07 17:01:19 akichan.moe /etc/mysql/debian-start[230318]: FATAL ERROR: Upgrade failed
Dec 07 17:01:19 akichan.moe debian-start[230327]: ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded
lze se těch chyb nějak zbavit i tam?