mysql control and windows
 

Hi,

I'm using the mysql program to access my linux mysql database from windows. when i try to connect i get a:

[custard] ERROR 1130: Host 'w2k.cream-cake.com' is not allowed to connect to this MySQL server

now i see a few posts mentioning GRANT tables and setting permissions but i'm using RH9 and i dont need a password to enter mysql, i just press enter. i tried setting permissions on test databases in the past this way and screw mysql right up! it wouldnt even let me access it from the localhost kept saying access denied ! and this was for root!!!!

i need a way to allow my windows host to access the DB without screwing up mysql?

G

You need to setup a user that allows you to access it remotely. Assuming you can connect to mysql from the command line something like will create a user on the w2k-cream-cake.com host with access to everything.

Note that you really really don't want such user's on a production system. The MySQL docs on http://www.mysql.com has a massive section on the privilege control system.

cheers

Jamie...

ok thanks i'll bear this in mind. i tried setting priv's before and RH9 completely screwed up!

If you do screw the privs in MySQL you can restart it without loading the priviledge tables, allowing you to get in and reset them.

cheers

Jamie...

if you've got any info on that it might be of use! problem is RH allows any user to connect to mysql without a password......so i wouldnt be sure what to amend.

this explains my problem better:

Hi,

I'm using the mysql windows program on windows 2000 to access my linux's mysql database on RH9. when i try to connect i get a:

[custard] ERROR 1130: Host 'w2k.cream-cake.com' is not allowed to connect to this MySQL server

now i see a few posts mentioning GRANT tables and setting permissions but i'm using RH9 and i dont need a password to enter mysql, i just press enter. i tried setting permissions on test databases in the past this way and screwed mysql right up! it wouldnt even let me access it from the localhost (on linux) kept saying access denied ! and this was for root!!!! i dont want to set a password as i'm happy having it open...

i need a way to allow my windows host (w2k.cream-cake.com) to access the DB on RH 9 without screwing up mysql?

thanks

If you want to create a user without a password then use the grant statement I mentioned earlier, but leave out the "identified by 'password'" bit. Note that if you set a user as having a password, you need to get the MySQL command line utility to ask you for it by running "mysql -p" instead of "mysql" on its own.

thanks

Jamie...

ok i'll give it a go over the weekend! cheers

lokking through this further i understand the commaned but the mysql control program i have only specifies the host your connecting too and the password and username allocated to the DB.

so my username on windows 2000 being 'Graeme' would i do:

as the host is w2k.cream-cake.com

G

it hit me earlier on that when creating the DB and user and pass in mysql_setpermission i only specified the localhost as a host to be allowed. so i went back in and added the w2k host and restarted mysql....no luck. tried rebooting the pc, no luck. so i went back in and added the full host name i.e w2k.cream-cake.com and tried again! no luck :( tried flushing all tables also. so looks like allowing a host to a db in setpermission isnt the answer? unless i'm missing something else

i'm reading about checking grant tables? how would i view the grant table and see/amend the host names listed?

as i said yesterday i used mysql_setpermission and selected option 3 to add user privs to an existing db. because it didnt give me the option to edit i input my current user and password and then added the localhost (again) and then the new host i wanted to connect from i.e w2k and w2k.cream-cake.com. on both occasions it brought up a warning stating that the username and localhost aready existed so would be dropped. i agreed with this but assumed the new hosts would be input into the grant tables. as i said in my last post this hasnt worked.

i tried viewing the user table in the mysql db today and see these? no added hosts?

s_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
+-----------+--------+------------------+-------------+-------------+-----------
--+-------------+-------------+-----------+-------------+---------------+-------
-------+-----------+------------+-----------------+------------+------------+
| localhost | root | | Y | Y | Y
| Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y |
| custard | root | | Y | Y | Y
| Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y |
| localhost | | | N | N | N
| N | N | N | N | N | N
| N | N | N | N | N |
| custard | | | N | N | N
| N | N | N | N | N | N
| N | N | N | N | N |
| localhost | graeme | 620544622df99db7 | N | N | N
| N | N | N | N | N | N
| N | N | N | N | N |
+-----------+--------+------------------+-------------+-------------+-----------
--+-------------+-------------+-----------+-------------+---------------+-------
-------+-----------+------------+-----------------+------------+------------+
5 rows in set (0.00 sec)

i guess this means the new hosts i entered havent been added?

Should be To grant user Graeme on host w2k.cream-cake.com all privilieges on all tables in all databases (the *.* bit).

cheers

Jamie...

hahahahaha..............i created a test DB with access from all my hosts and it worked........so i went back and copied all the tables from the creamcake db and then dropped it. recreated it with all the access privs and copied the tables back! and it failed :( reinstalling linux now :( on xmas day as well!!!!!!!!!!!!!!!

jamie, i tried it with the *.* and kept getting errors.....i typed it exactly as you explained.........just gonna reinstall linux and do it all from scratch

Very weird. Good luck with the reinstall, enjoy your Christamas :)

Jamie...

you too! i'll be back if it doesnt work ;)