====== MariaDB ======

Après installation
  # mysql_secure_installation

Pour autoriser à se connecter via adminer
<code>
t# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO root@'localhost' IDENTIFIED BY 'mon_mot_de_passe';
Query OK, 0 rows affected (0.00 sec)

</code>

===== Commandes =====

Voir la liste des users

<code>
mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | toto             |
| 127.0.0.1 | root             |
| ::1       | root             |
| debian    | root             |
| localhost | api              |
| localhost | debian-sys-maint |
| localhost | root             |
+-----------+------------------+
</code>



Voir les privilèges

<code>
mysql> SELECT * FROM information_schema.user_privileges;
+--------------------------------+---------------+-------------------------+--------------+
| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+--------------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'             | def           | SELECT                  | YES          |
| 'root'@'localhost'             | def           | INSERT                  | YES          |
| 'root'@'localhost'             | def           | UPDATE                  | YES          |
| 'root'@'localhost'             | def           | DELETE                  | YES          |
| 'root'@'localhost'             | def           | CREATE                  | YES          |
| 'root'@'localhost'             | def           | DROP                    | YES          |
| 'root'@'localhost'             | def           | RELOAD                  | YES          |
| 'root'@'localhost'             | def           | SHUTDOWN                | YES          |
| 'root'@'localhost'             | def           | PROCESS                 | YES          |
...
</code>


<code>
mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXxxx' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
</code>

Penser aussi à modifier le fichier **/etc/mysql/debian.cnf** en lettant à jour les 2 champs password
<code>
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = root
password = XXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = root
password = XXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr
</code>

Ca permettra par exemple à logrotate de générer des erreurs du type
<code>
#007mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
error: error running shared postrotate script for '/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/mariadb-slow.log /var/log/mysql/error.log '
</code>

Vérifiez le bon fonctionnement avec la commande
<code>
# mysqladmin --defaults-file=/etc/mysql/debian.cnf processlist
+----------+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| Id       | User        | Host      | db | Command | Time | State                    | Info             | Progress |
+----------+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| 1        | system user |           |    | Daemon  |      | InnoDB purge coordinator |                  | 0.000    |
| 2        | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 3        | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 4        | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 5        | system user |           |    | Daemon  |      | InnoDB shutdown handler  |                  | 0.000    |
| 14507011 | root        | localhost |    | Query   | 0    | Init                     | show processlist | 0.000    |
+----------+-------------+-----------+----+---------+------+--------------------------+------------------+----------+

</code>


===== mot de passe root perdu =====
Si vous avez perdu votre mot de passe root, pas de panic

source: https://linuxize.com/post/how-to-reset-a-mysql-root-password/

Stoppez mysql ou mariadb
  systemctl stop mysql

Démarrez mysql sans permissions (accès total à tout sans mot de passe !! ATTENTION !!)
  mysqld_safe --skip-grant-tables &

Connectez vous à mysql
  mysql -u root

Modifiez le mot de passe root
  mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
  mysql> FLUSH PRIVILEGES;

Si ça ne marche pas, essayez 
  mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
  mysql> WHERE User = 'root' AND Host = 'localhost';
  mysql> FLUSH PRIVILEGES;

Pour les anciennes versions, essayez
  mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
  mysql> FLUSH PRIVILEGES;

Ensuite, quittez mysql et stoppez le via la commande
  mysqladmin -u root -p shutdown

Démarrez ensuite mysql normalement
  systemctl start mysql
ou
  systemctl start mariadb

vérifiez que tout est ok
  mysql -u root -p