DevChunks

Control remote access

On a standard MySQL installation, remote access is disabled by default. This is great if you are sat next to your server, but inconvenient if you are not. With the advent of modern support teams spanning the globe, it is entirely possible to administer a server that is in a different country or continent and in that case you need to enable remote access.

The first thing you need to do is enable remote access and then restrict it to prevent unauthorized access. Start with a ssh connection to the remote MySQL database server.

{code type=php}
ssh user@mysql.mydomain.com
{/code}

After login, edit the my.cnf configuration file with a text editor, locate the [mysqld] section and make the following changes:

{code type=php}
[mysqld]
#skip-networking Can delete this line or comment it
bind-address = Enter your server ip address
{/code}

Save the file and restart the MySQL server.

{code type=php}
$ /etc/init.d/mysql restart
{/code}

Now you need to grant remote access to the databases for each remote computer.

{code type=php}
$ mysql –u root –p password
mysql> CREATE DATABASE mydb;
mysql> GRANT ALL ON mydb.* to user@ipaddress IDENTIFIED BY Password;
mysql> UPDATE DB SET Host=ipaddress where DB=’mydb’;
mysql> UPDATE USER SET HOST=ipaddress WHERE USER=username;
mysql> exit
{/code}

The first two MySQL statements create a new database called mydb and grants remote access to the user at ipaddress who supplies the correct password. The second set of two MySQL statements do the same action but for an existing database.

The next step is to open TCP port 3306 using iptables.

{code type=php}
/sbin/iptables –A INPUT –i eth0 –p tcp –destination-port 3306 –j ACCEPT
$ service iptables save
{/code}

You can use the –s switch to specify connections from an ipaddress or a subnet.

Once you configuration is complete the last stage is to test it from your remote workstations. Once it is working, you have a secure method for remote access to your MySQL servers no matter how far away they are physically located from you.

Share:

Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,

Iteration

If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the