Mysql8
Salut à tou s!
J'ai installé MySQL 8.0-community-server sous CentOS 7, j'arrive à me connecter avec le terminal à MySQL en étant sous le serveur ou à distance.
Mais dès que je tente de me connecter avec
De même, avec une application web connecté à une base de données, je n'arrive pas à me connecté à mysql et ca m'affiche :
Prière m'aider.
J'ai installé MySQL 8.0-community-server sous CentOS 7, j'arrive à me connecter avec le terminal à MySQL en étant sous le serveur ou à distance.
Mais dès que je tente de me connecter avec
phpmyadmin, ça m'affiche impossible de me connecter à MySQL.
De même, avec une application web connecté à une base de données, je n'arrive pas à me connecté à mysql et ca m'affiche :
ERREUR : Impossible de se connecter. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory.
Prière m'aider.
3 réponses
Merci bien. J'ai essayé ça en faisant :
ça m'affiche :
et :
Merci.
default_authentication_plugin = mysql_native_password dans le fichier /etc/my.cnf
ça m'affiche :
ERREUR : Impossible de se connecter. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory pour l'application web connécté à mysql
et :
connexion au serveur Mysql non permise pour phpmyadmin.
Merci.
Bonsoir,
Depuis la version 8.0, Mysql a changé le mode de chiffrement de la connexion au serveur et demande un plugin spécifique pour identifier un utilisateur.
l existe une commande SQL pour changer la méthode de chiffrement de l'utilisateur, quel que soit le système que vous utilisez. Vous devez pour cela vous connecter en tant que root au serveur Mysql avec l'interface en ligne de commande.
ALTER USER 'utilisateur'@'adresse_ip_du_serveur' IDENTIFIED WITH mysql_native_password BY 'mot_de_passe';
Si vous travaillez avec Windows ou Linux, il est possible de changer les paramètres du serveur Mysql en modifiant directement le fichier de configuration. Sous Windows, le fichier se nomme "my.ini" alors que sous Linux il s'agit du fichier "my.cnf". Modifiez le paramètre "default_authentication_plugin" en lui indiquant d'utiliser la méthode d'authentification native de Mysql :
default_authentication_plugin=mysql_native_password
Redémarrez ensuite le serveur. Si le message d'erreur apparaît toujours, alors vous devez créer un nouvel utilisateur. Voici la commande à utiliser en tant que root pour créer un utilisateur disposant de tous les privilèges :
GRANT ALL PRIVILEGES ON *.* TO 'utilisateur'@'adresse_ip_du_serveur' IDENTIFIED BY 'mot_de_passe';
Si il fait de la résistance
apt install mariadb-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
mariadb-server is already the newest version (1:10.3.22-0+deb10u1).
The following packages were automatically installed and are no longer required:
libmecab2 libnuma1 mecab-ipadic mecab-ipadic-utf8 mecab-utils
Use 'sudo apt autoremove' to remove them.
0 upgraded, 0 newly installed, 0 to remove and 2 not upgraded.
ok, donc il est bien installé..
ARRET RELANCE
systemctl restart mariadb
systemctl status mariadb
sudo tail -n 50 /var/log/mysql/error.log
On devrait avoir
2020-06-16T05:54:27.318461Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.20) initializing of server in progress as process 31950
2020-06-16T05:54:27.325890Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T05:54:27.904590Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T05:54:29.337889Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-06-16T05:54:32.439070Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 31998
2020-06-16T05:54:32.454682Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T05:54:33.178283Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T05:54:33.360250Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T05:54:33.563830Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T05:54:33.566136Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2020-06-16T05:54:33.592160Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.20).
2020-06-16T05:54:35.104444Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-06-16T05:54:36.008613Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 32129
2020-06-16T05:54:36.017437Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T05:54:36.269448Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T05:54:36.397070Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T05:54:36.473057Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T05:54:36.494292Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-06-16T06:34:49.403507Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-06-16T06:35:01.968856Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 499
2020-06-16T06:35:02.005266Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T06:35:02.417797Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T06:35:02.583117Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T06:35:02.651417Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T06:35:02.685007Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-06-16T06:56:35.536466Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-06-16T06:56:48.038908Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 498
2020-06-16T06:56:48.070692Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T06:56:48.462540Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T06:56:48.623927Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T06:56:48.692696Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T06:56:48.722794Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-06-16T09:50:47.341957Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-06-16T09:51:00.845871Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 503
2020-06-16T09:51:00.877653Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T09:51:01.345536Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T09:51:01.570741Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T09:51:01.691328Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T09:51:01.726018Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-06-16T11:41:07.178224Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
OUF TERMINE
Depuis la version 8.0, Mysql a changé le mode de chiffrement de la connexion au serveur et demande un plugin spécifique pour identifier un utilisateur.
l existe une commande SQL pour changer la méthode de chiffrement de l'utilisateur, quel que soit le système que vous utilisez. Vous devez pour cela vous connecter en tant que root au serveur Mysql avec l'interface en ligne de commande.
ALTER USER 'utilisateur'@'adresse_ip_du_serveur' IDENTIFIED WITH mysql_native_password BY 'mot_de_passe';
Si vous travaillez avec Windows ou Linux, il est possible de changer les paramètres du serveur Mysql en modifiant directement le fichier de configuration. Sous Windows, le fichier se nomme "my.ini" alors que sous Linux il s'agit du fichier "my.cnf". Modifiez le paramètre "default_authentication_plugin" en lui indiquant d'utiliser la méthode d'authentification native de Mysql :
default_authentication_plugin=mysql_native_password
Redémarrez ensuite le serveur. Si le message d'erreur apparaît toujours, alors vous devez créer un nouvel utilisateur. Voici la commande à utiliser en tant que root pour créer un utilisateur disposant de tous les privilèges :
GRANT ALL PRIVILEGES ON *.* TO 'utilisateur'@'adresse_ip_du_serveur' IDENTIFIED BY 'mot_de_passe';
Si il fait de la résistance
apt install mariadb-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
mariadb-server is already the newest version (1:10.3.22-0+deb10u1).
The following packages were automatically installed and are no longer required:
libmecab2 libnuma1 mecab-ipadic mecab-ipadic-utf8 mecab-utils
Use 'sudo apt autoremove' to remove them.
0 upgraded, 0 newly installed, 0 to remove and 2 not upgraded.
ok, donc il est bien installé..
ARRET RELANCE
systemctl restart mariadb
systemctl status mariadb
sudo tail -n 50 /var/log/mysql/error.log
On devrait avoir
2020-06-16T05:54:27.318461Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.20) initializing of server in progress as process 31950
2020-06-16T05:54:27.325890Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T05:54:27.904590Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T05:54:29.337889Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-06-16T05:54:32.439070Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 31998
2020-06-16T05:54:32.454682Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T05:54:33.178283Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T05:54:33.360250Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T05:54:33.563830Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T05:54:33.566136Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2020-06-16T05:54:33.592160Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.20).
2020-06-16T05:54:35.104444Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-06-16T05:54:36.008613Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 32129
2020-06-16T05:54:36.017437Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T05:54:36.269448Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T05:54:36.397070Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T05:54:36.473057Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T05:54:36.494292Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-06-16T06:34:49.403507Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-06-16T06:35:01.968856Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 499
2020-06-16T06:35:02.005266Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T06:35:02.417797Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T06:35:02.583117Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T06:35:02.651417Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T06:35:02.685007Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-06-16T06:56:35.536466Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-06-16T06:56:48.038908Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 498
2020-06-16T06:56:48.070692Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T06:56:48.462540Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T06:56:48.623927Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T06:56:48.692696Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T06:56:48.722794Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-06-16T09:50:47.341957Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-06-16T09:51:00.845871Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 503
2020-06-16T09:51:00.877653Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-16T09:51:01.345536Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T09:51:01.570741Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-06-16T09:51:01.691328Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-16T09:51:01.726018Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-06-16T11:41:07.178224Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
OUF TERMINE
