Swisslinux.org

Didier100 · 24 Aug 2005 20:04:55

Suse Linux 9.3

J'ai un réseau local qui est composé de deux ordinateurs:

1. Ordinateur avec Suse Linux 9.3 et Serveur MySQL Version 4.1.10a. + ....
2. Ordinateur avec Windows XP

Quand j'essaye d'accèder au Serveur MySQL, à partir de mon ordinateur WinXp, je reçoit un message d'erreur disant :

Erreur d'execution SQL (1130). Reponse de la Base de Données:

[Host '192.168.2.100' is not allowed to connect to this MySQL server]

Ma Question:
Comment faire pour resoudre le problème?

Merci d'avance pour votre aide ... ci-joint quelques informations ....

Info: Version Mysql

linux:/home/dieter # mysql -VERSION
mysql Ver 14.7 Distrib 4.1.10a, for suse-linux (i686)

Info Host:

mysql> SELECT User, Host, Password FROM mysql.user;
+------+------------+-------------------------------------------+
| User | Host | Password |
+------+------------+-------------------------------------------+
| root | localhost | *1718172EDB721292F9A32718D9C45EA43C765B8C |
| root | linux.site | *1718172EDB721292F9A32718D9C45EA43C765B8C |
| | linux.site | |
| | localhost | |
+------+------------+-------------------------------------------+
4 rows in set (0,00 sec)

Voici le contenu de mon fichier my.cnf:

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
# log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

# The safe_mysqld script
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Deejayprod · 25 Aug 2005 08:21:25

Ben comme te dis l'erreur, tu n'a pas le droit d'accèder au serveur depuis ton hote, et a voir ta table user :

Code:

| root | localhost | *1718172EDB721292F9A32718D9C45EA43C765B8C |
| root | linux.site | *1718172EDB721292F9A32718D9C45EA43C765B8C |

avec ton root tu a accès au serveur mysql depuis qu'en local ou depuis l'hote linux.site (qui je pense correspond au nom de ton serveur).

alors t'a 2 possibilité:

1)solution bracaille : tu modifies les droits de ton root et comme host tu lui mets "%" ou bien le nom de ta machine XP, car actuellement, ton root ne peut accèder au serveur qu'en local

Code:

UPDATE user SET User = '%' WHERE Host = 'localhost' AND User = 'root';
UPDATE mysql.db SET User = '%' WHERE Host = '%' AND User = 'root';
UPDATE mysql.tables_priv SET User = '%' WHERE Host = '%' AND User = 'root';
UPDATE mysql.columns_priv SET User = '%' WHERE Host = '%' AND User = 'root'

2)bonne solution : tu crées un utilisateur avec les droits dont tu a besoin et qui peut avoir accès depuis une autre machine (tu mets Y ou N pour les droits que tu veux...le privilièges correspondent au requetes que le gars aura le droit d'effectuer):

Code:

INSERT INTO mysql.user 
   SET 
      Host = '%',  
      User = 'NOM_QUE_TU_VEUX', 
      Password = PASSWORD('le mot de passe que tu veux'), 
      Select_priv = 'Y', 
      Insert_priv = 'Y', 
      Update_priv = 'Y', 
      Delete_priv = 'Y', 
      Create_priv = 'Y', 
      Drop_priv = 'Y', 
      Reload_priv = 'Y', 
      Shutdown_priv = 'Y', 
      Process_priv = 'Y', 
      File_priv = 'Y', 
      Grant_priv = 'Y', 
      References_priv = 'Y', 
      Index_priv = 'Y', 
      Alter_priv = 'Y'

ici, ton user aura accès au serveur depuis n'importe quelle machine...

si tu veux restreindre ce droit, tu peux remplacer Host = '%' par Host = 'LE_NOM_DE_TON_HOTE_WINDOWS'.

Voila, normalement ca doit marcher après ca

Didier100 · 25 Aug 2005 17:23:51

Bonjour Deejayprod,

dabord un grand merci pour ton aide.

J'avait choisi la "solution bracaille" ...

Code:

UPDATE user SET User = '%' WHERE Host = 'localhost' AND User = 'root';
UPDATE mysql.db SET User = '%' WHERE Host = '%' AND User = 'root';
UPDATE mysql.tables_priv SET User = '%' WHERE Host = '%' AND User = 'root';
UPDATE mysql.columns_priv SET User = '%' WHERE Host = '%' AND User = 'root'

Après avoir entrée une à une ses 4 instructions (sans retour de message d'erreur) j'ai redammarée mon ordinateur Linux et quand j'essayé d'enter sur le compte root du serveur MySQL je reçoit le message d'erreur suivant:

Code:

dieter@linux:~> mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
dieter@linux:~>

En testant un peut le serveur MySQL aussi à traver de l'interface phpMyAdmin je me suis aperçu que mon môt de passe root (Serveur MySQL) à été efface (non existant).

Quand j'essayé de restaurer le mot de passe administarateur je réçoit le message d'erreur suivant :

Code:

dieter@linux:~> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 18 to server version: 4.1.10a

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> UPDATE mysql.user SET Password = PASSWORD('mot_de_passe_admin') WHERE User = 'root';
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
mysql>

Que je doit faire maintenant ?

D'ailleurs de l'autre côté ( Poste avec WinXP) je reçoit toujour le message d'erreur disant

Code:

[Host '192.168.2.100' is not allowed to connect to this MySQL server]

Info Host :

Code:

mysql> SELECT User, Host, Password FROM mysql.user;
+------+------------+-------------------------------------------+
| User | Host       | Password                                  |
+------+------------+-------------------------------------------+
| %    | localhost  | *1718172EDB721292F9A32718D9C45EA43C765B8C |
| root | linux.site | *1718172EDB721292F9A32718D9C45EA43C765B8C |
|      | linux.site |                                           |
|      | localhost  |                                           |
+------+------------+-------------------------------------------+
4 rows in set (0,00 sec)

Didier100 · 25 Aug 2005 18:08:50

Ca marche ...

J'ai lance entre temps l'instruction ....

Code:

GRANT ALL PRIVILEGES ON *.* TO '%'@'192.168.2.100' IDENTIFIED BY 'mot_de_passe_admin' WITH GRANT OPTION;

Merci encore beaucoup pour ton aide .

Deejayprod · 26 Aug 2005 08:08:34

ah oui, je vois maintenant que j'avais un peu fait faux cette ligne:
UPDATE user SET User = '%' WHERE Host = 'localhost' AND User = 'root';

ca devait etre

UPDATE user SET Host = '%' WHERE Host = 'localhost' AND User = 'root';

.....

j'avais tapé un peu vite...désolé

ah oui j'avais pas pensé au Grant...c'est un peu fait pour ca en fait....

par contre ca aurait été mieux de faire un compte supplémentaire, pcq meme sur mysql...on se logue jamais en root

Didier100 · 26 Aug 2005 09:41:19

Deejayprod,

O.K. Merci.

C'est en faisant des erreurs qu'on aprend ... Grace à ton aide je puis bien avancer ...

Ce Problème est donc résolu.

Entre temps j'ai un nouveau problème mais cela mérite un nouveau Sujet ...
que je vais poster en quelques minutes sur le même Forum.

Un problème viens jamais seul .... Ahhh ses proverbes ....
Plus d'informations ... http://www.swisslinux.org/forums/sutra5359.php#5359

A+

Swisslinux.org

− Le carrefour GNU/Linux en Suisse −

Recherche

Menu

Forum

Identification

Inscription

Langue

Le Forum

#1 24 Aug 2005 20:04:55

[Dev] MySQL : Erreur d'execution SQL : 1130 (résolu)

#2 25 Aug 2005 08:21:25

Re: [Dev] MySQL : Erreur d'execution SQL : 1130 (résolu)

Code:

Code:

Code:

#3 25 Aug 2005 17:23:51

Re: [Dev] MySQL : Erreur d'execution SQL : 1130 (résolu)

Code:

Code:

Code:

Code:

Code:

#4 25 Aug 2005 18:08:50

Re: [Dev] MySQL : Erreur d'execution SQL : 1130 (résolu)

Code:

#5 26 Aug 2005 08:08:34

Re: [Dev] MySQL : Erreur d'execution SQL : 1130 (résolu)

#6 26 Aug 2005 09:41:19

Re: [Dev] MySQL : Erreur d'execution SQL : 1130 (résolu)

Pied de page des forums