Archive for March, 2012

Giving a Root User Remote Access to a mySQL database

Thursday, March 29th, 2012

I happen to be within a firewalled environment and I wanted to simply login as the root user for a mysql server from a development machine. Well, security purists would scoff at the idea, but I needed it (running some scripts as root from the dev machine).

The issue is mysql out-of-the-box is configured to explicitly dis-allow root from log in from anything other than the local machine.

Enabling remote access for any user is easy. Most of us have gone through this as part of mysql setup :

Step 1: On the mySQL server:

sudo vi /etc/my.cnf

Look for the line that says

bind-address=YOUR-SERVER-IP

Change that to:

bind-address=YOUR-SERVER-IP

Step 2: Restart mysql service

sudo /etc/init.d/mysql restart

All’s well. As long as individual users have been given permissions to a database through something like:

GRANT ALL ON *.* TO foo@bar IDENTIFIED BY 'PASSWORD';

Now what if I want the same for root? We need to update the allowed permissions for root on the server side with the following simple steps.

mysql -uroot -p

To Check what’s the current state of user,host login permissions we’ll lookup the default mysql table elegantly named: mysql.


mysql> use mysql;
mysql> select host, user from user;
+—————+——————+
| host | user |
+—————+——————+
| myhostname | root |
| localhost | root |
| % | myusername |
| myhostname | myusername |
+—————+——————+
3 rows in set (0.00 sec)

It seems that a non-root user ‘myusername’ was indeed allowed to login from anywhere. This is not the case for root. Now I’ll update the ‘myhostname’ entry to use the wildcard ‘%’, and then issue the command to reload the privilege tables.

mysql>update user set host=’%’ where user=’root’ and host=’myhostname’;
mysql>flush privileges;

Now, here’s the catch: It’s important to update the entry where host=’myhostname’. Inserting a fresh row will not work. Now you should be able to login from a remote machine (say a development box) to the mysql server as root. Any perl scripts will work fine. If this is not enabled on the remote machine, mysql command-line or any scripts for instance will look for ‘root’@'the-development-machine’ instead of ‘root’@'myhostname’. Thereby confusing the heck out of you.

$mysql -u root -h myhostname -p

This simple fix gave me a headache for sometime. So, this is a note to self.

Installing DBD::mysql on Mac OS X Lion

Wednesday, March 21st, 2012

I was trying to install the perl module DBD::mysql on Mac OS X Lion. I faced some hassles. Here are a couple of useful links (Thanks!). I had some peculiar problems of my own, so this post is a “note-to-self” for the next time :

http://blogs.perl.org/users/phillip_smith/2012/03/installing-dbdmysql-on-mac-os-x-107-lion.html

http://probably.co.uk/problems-installing-dbdmysql-on-os-x-snow-leopard.html

According to Joe Di Pol’s blog, OS X works slightly backwards to what most of us from Linux and Solaris backgrounds understand – compiled binaries look at a dynamic library, which in turns says where it is, rather than the traditional way of thinking which is to include a library search path in the compiled binary.


cpan[1]> install DBD::mysql

So, when it comes down to testing (make test), it fails.

# Failed test 'use DBD::mysql;'
# at t/00base.t line 21.
# Tried to use 'DBD::mysql'.
# Error: Can't load '/Users/aalap/.cpan/build/DBD-mysql-4.020-9nL8LG/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/aalap/.cpan/build/DBD-mysql-4.020-9nL8LG/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Library not loaded: libmysqlclient.18.dylib

There are two solutions:

  1. Symlinking ‘libmysqlclient.XX.dylib’ from ‘/usr/local/mysql/lib’ to ‘/usr/lib/’ (where XX is the version of the library that is available to link to).
  2. Run install_name_tool command, install DBD::mysql buillds

  3. sudo install_name_tool -id /usr/local/mysql-5.5.21-osx10.6-x86_64/lib


    otool -D `mdfind libmysqlclient.18.dylib`

Alas, it still failed for me. Building successfully doesn’t mean it is installed. CPAN performs make install only after make test. I had changed my root password. The Makefile expects “” (NULL) password. I had to pass a valid user and a valid password. Passing arguments to the Makefile to ensure that the tests run using a proper MySQL user (the Makefile defaults to the system user running the tests, if no other user is provided, which fails for me as that user doesn’t exist). After a few more turns I realized, it lacks permissions to place some files in /Library/Perl/5.12/darwin-thread-multi-2level/Bundle/DBD. I decided to use root and run sudo CPAN. The way to do it is:


cpan[2] o conf makepl_arg "--testuser=root --testpassword=Whatever_my_password_is"

Now run install DBD::mysql again

cpan[3] install DBD::mysql

Yoohoo..


Running make install
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bs
Installing /Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle
Installing /Library/Perl/5.12/darwin-thread-multi-2level/Bundle/DBD/mysql.pm
Installing /Library/Perl/5.12/darwin-thread-multi-2level/DBD/mysql.pm
Installing /Library/Perl/5.12/darwin-thread-multi-2level/DBD/mysql/GetInfo.pm
Installing /Library/Perl/5.12/darwin-thread-multi-2level/DBD/mysql/INSTALL.pod
Installing /usr/local/share/man/man3/Bundle::DBD::mysql.3pm
Installing /usr/local/share/man/man3/DBD::mysql.3pm
Installing /usr/local/share/man/man3/DBD::mysql::INSTALL.3pm
Appending installation info to /Library/Perl/Updates/5.12.3/darwin-thread-multi-2level/perllocal.pod
CAPTTOFU/DBD-mysql-4.020.tar.gz
/usr/bin/make install -- OK

All’s well that end’s well. Annoying errors had me stuck me for a while.