TurnKey Linux Virtual Appliance Library

How to allow remote access to databases


Configure MySQL to listen for remote connections:

sed -i "s/^bind-address/#bind-address/" /etc/mysql/my.cnf

Configure MySQL to accept remote root connections (from any host ('%')):
(added line breaks for readability)

MYSQL_BATCH="mysql --user=root --password=$MYSQL_PASS --batch"
$MYSQL_BATCH --execute "INSERT INTO mysql.user ( Host , User , Password , Select_priv ,
    Insert_priv , Update_priv , Delete_priv , Create_priv , Drop_priv , Reload_priv , 
    Shutdown_priv , Process_priv , File_priv , Grant_priv , References_priv , 
    Index_priv , Alter_priv , Show_db_priv , Super_priv , Create_tmp_table_priv , 
    Lock_tables_priv , Execute_priv , Repl_slave_priv , Repl_client_priv , 
    Create_view_priv , Show_view_priv , Create_routine_priv , Alter_routine_priv , 
    Create_user_priv , ssl_type , max_questions , max_updates , max_connections , 
    max_user_connections) VALUES ( '%', 'root', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y','Y', 'Y', 'Y', 'Y', 'Y',
    'Y', 'Y', 'Y', 'Y', '', '0', '0', '0', '0');"

Restart MySQL for the changes to take effect:

/etc/init.d/mysql restart



Configure PostgreSQL to listen for remote connections:

sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" $CONF

Configure PostgreSQL to accept remote connections (from any host):

# configure postgres to accept remote connections
cat >> /etc/postgresql/8.3/main/pg_hba.conf <<EOF
# Accept all IPv4 connections - CHANGE THIS!!!
host    all         all             md5

Restart PostgreSQL for the changes to take effect:

/etc/init.d/postgresql-8.3 restart



Simon O's picture

MySQL Alternative

Hi All,

I'm by no means an expert so please bear in mind this is simply my own experience

I found it far easier to run the sed command, then use the web interface to change the host for the user in question to % (aka all/any).

NB: If you do this via the command line, note that the block above after "(added line breaks for readability)" is actually 2 commands - I'm sure this is obvious to most readers but caught me out for a minute or 2

You can do this either through Webmin or phpMyAdmin)

I still had to restart MySQL (I assume for the binding to apply). The restart command listed above barfed on me...


~# /etc/init.d/mysql restart
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql restart

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the restart(8) utility, e.g. restart mysql

so in future, I'll be using

restart mysql

Anyway hope this is of help to someone

Marco Ovalle's picture

Access denied for user root


ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
This error appears when Inserting sentece is executed.... Why?... 
Guest's picture

How about a Range of IP Addresses

Using Google Apps Scripts JDBC with MySQL requires remote access from an extensive range of IP addresses like: - - - - - - - - - -

For 0 - 255 I could use %. But how about the address ranges like - Can CIDR be used in the INSERT INTO? 

Jeremy Davis's picture

TBH I'm not sure...

However the current TKL (v13.0) is based on Debian Wheezy (aka Debian 7) so anything that applies there should apply to TKL. Google probably has some ideas...

If you find a good solution be great if you could post back as it will help others in the future no doubt! :)