Little details first. What is a database and mysql ?
Databases are collections of data. Mysql is one of the available programs which lets you store and retrieve data efficiently. Mysql servers can be accessed by programming languages like php,perl,python,java to display the content in a fashioned way at the userend as well store data entered by users(eg: registration) in databases.
If you have a Lamp server running on your vps or dedicated server then it includes mysql-server. You can access a mysql-server from your local computer(using xampp,wamp) or from another remote client( vps or dedi server with mysql-client installed). If you have shared hosting then it can act as a mysql client to connect to server database but cannot be used as a mysql server as it lacks root privileges of the system. xampp or wamp are great environments to connect to your server database from your computer and test your work before it is uploaded on a server.
When you try connecting to mysql-server(crybit.com) from a client machine(eg: 200.200.200.200) located elsewhere, this is the error you will see.
$ mysql -h crybit.com -u root -p
Enter password:
ERROR 1130: Host '200.200.200.200' is not allowed to connect to this MySQL server
One thing you must understand is that mysql server only allow localhost connections by default. So what does that mean ? Before going deep let me put up an example mysql-server. (1)
” Consider crybit.com (5.5.5.5) is our mysql-server and is hosted on a VPS machine. We have setup a lamp environment on 5.5.5.5 and everything is ready to go. You can install mysql-server without apache and php by the way if you dont want a lamp setup. At this point i will guess you have mysql-server up and running. ”
(1) Back to the localhost part, if you access mysql-server from within the server crybit.com (5.5.5.5) by ssh into it then you are accessing mysql-server as localhost. If you access mysql database with root privileges then it is root@localhost.
Example, here you are in crybit server with shell access.
You used putty to “ssh [email protected]”
root@crybit [~]# mysql -u root -p
Enter password:
mysql>
Now check how are you logged in as
Select all mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+
The above command shows that the access to the mysql-server is from within the crybit.com server(so its shows localhost) and by user root, which is being shown as root@localhost.
Checking who have access to mysql-server
By using this below command you can find out which users and hosts are allowed access to mysql server. You can see that currently localhost , its IPV4 and IPV6 are allowed by default.
Select all mysql> select user,host from mysql.user; +----------------+--------------------+ | user | host | +----------------+--------------------+ | root | 127.0.0.1 | | root | localhost | | root | ::1 | +----------------+--------------------+
This means remote users cannot have access to the server. So we have to allow them manually. Everyone other than crybit.com(5.5.5.5) are remote users and thus without any entry of those clients in mysql.user table they won’t have access.
Allowing access to a remote client machine
Suppose your client is 24psd.com(7.7.7.7) you can allow access by using this command in server(crybit.com).
root@crybit [~] mysql -u root -p
Enter password:
Select all mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'24psd.com' IDENTIFIED BY 'your-password'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'7.7.7.7' IDENTIFIED BY 'your-password'; Query OK, 0 rows affected (0.00 sec)
With this you are telling the crybit.com server that client 24psd.com(7.7.7.7) can access it.
Recheck and now you can see an entry for client 24psd.com along with its ip 7.7.7.7 which is for redundancy in the mysql.user table.
Select all mysql> select user,host from mysql.user; +----------------+--------------------+ | user | host | +----------------+--------------------+ | root | 127.0.0.1 | | root | localhost | | root | ::1 | | root | 24psd.com | | root | 7.7.7.7 | +----------------+--------------------+
Checking access to server from client side
Login or access from client 24psd.com using a PHP program or through mysql client shell.
root@24psd [~] # mysql -h crybit.com -u root -p
Enter password:
Success it now shows the prompt.
mysql>
If you are accessing mysql-server and its database with php page then the hostname will be like below.
$hostname=crybit.com
or
$hostname=5.5.5.5
Access to mysql server from local machine with xampp
Considering you have already setup xampp environment on your computer.
Do the following at crybit.com (mysql-server) to allow access to xampp client.
root@crybit [~] mysql -u root -p
Enter password:
Select all mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'200.200.200.200' IDENTIFIED BY 'your-password'; Query OK, 0 rows affected (0.00 sec)
200.200.200.200 is your ip and can be found by googling ‘my ip’. This option might be better suited for static ips but dynamic ip will work too. Make sure to remove the ip if it is dynamic after you have finished the development.
Check the entry we just added
Select all mysql> select user,host from mysql.user; +----------------+--------------------+ | user | host | +----------------+--------------------+ | root | 127.0.0.1 | | root | localhost | | root | ::1 | | root | 24psd.com | | root | 7.7.7.7 | | root | 200.200.200.200 | +----------------+--------------------+
Now Access using Xampp shell
Setting environment for using XAMPP for Windows.
user@user-PC c:\xampp
# mysql -h crytek.org -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 356
Server version: 5.5.32-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>
No more Error 1130: “Host is not allowed to connect to this MySQL server”
Is this article informative for you ? Let us know by commenting below.
