Whitelist fro remote mysql

Dev Shed Forums Sponsor:

February 3rd, 2012, 02:24 PM

zodehala

Contributing User

Join Date: Oct 2006

Posts: 265

Time spent in forums: 1 Day 15 h 53 m 5 sec
Reputation Power: 0

Whitelist fro remote mysql

in order to fetch data from remote mysql i added my ip to whitelist using cpanel

how can i do it mysql server which have no cpanel or plesk or any ...?

(for example i can not fetch data from other pc(mysql server) in local

i am in 192.168.2.2 mysql is in 192.168.2.10

February 3rd, 2012, 02:58 PM

fubes2000

manwich

Join Date: Oct 2003

Location: Canadanistan

Posts: 575

Folding Points: 388781 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 1 Week 10 h 47 m
Reputation Power: 382

http://dev.mysql.com/doc/refman/5.5/en/create-user.html

Every user is specified as user@host, ie: you will have user1@localhost and user1@remote.host.net. Both of these users have separately managed passwords and permissions even though the control panel likely obscures this.

So on the mysql server there will be 3 or more users created in something similar to the following way:

Code:

CREATE USER 'zodehala'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'zodehala'@'192.168.1.%' IDENTIFIED BY 'mypass';
CREATE USER 'zodehala'@'212.156.127.%' IDENTIFIED BY 'mypass';

__________________

February 4th, 2012, 03:40 AM

zodehala

Contributing User

Join Date: Oct 2006

Posts: 265

Time spent in forums: 1 Day 15 h 53 m 5 sec
Reputation Power: 0

Quote:

Originally Posted by fubes2000

Code:

CREATE USER 'zodehala'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'zodehala'@'192.168.1.%' IDENTIFIED BY 'mypass';
CREATE USER 'zodehala'@'212.156.127.%' IDENTIFIED BY 'mypass';

for example our mysql server is in 78.188.198.115

for wan ?
1-i want to access to it from 5 different ip. how can i do it
2- i want to access to it from 78.188.0/24

for local
1- i want to access it from all local IP how can i do it

February 4th, 2012, 06:29 PM

fubes2000

manwich

Join Date: Oct 2003

Location: Canadanistan

Posts: 575

Time spent in forums: 1 Week 10 h 47 m
Reputation Power: 382

1. If you want 5 different IPs you'll need 5 different user entries. Since % is a wildcard you can also use 'user'@'%' to allow connections from anywhere, but this is super insecure if you're not using a firewall in front of your mySQL box.

2. 192.168.1.% is essentially equivalent to a /24. According to the mySQL docs you can also specify IPs with the full netmask such as 192.168.1.0/255.255.255.0 if you need something other than a /8, /16, or /24.

3. See 1 and 2. If you have a local domain you can specify the host part as something like '%.mydomain.local'.