Macos – Command-line access to remote MySQL server

macosMySQLssh

I administer a website on a remote, shared host. My web host offers MySQL, and I am able to access this from my Mac OS X computer using a GUI program, Sequel Pro. That works great.

But I want to script some queries, and Sequel Pro is not scriptable. What should I do?

I've read about tunneling to mysql via SSH. I have shell access to the server, with an SSH key on my Mac, so

ssh jerrykri@123.456.789.123 -p 7978

gets me in. Should tunneling the MySQL port 3306 work? Like this?

ssh jerrykri@123.456.789.123 -L 3306:127.0.0.1:3306

(It "times out" after a minute.)

Do I need to install mysql on my Mac?

Best Answer

On the MySQL server, you need to modify the file my.cnf, whose location depends on OS: in Debian, it is in /etc/mysql, for instance. Edit it, find the line 

 [mysqld]

and add to it the following text:

 [mysqld]
 user            = mysql
 pid-file        = /var/run/mysqld/mysqld.pid
 socket          = /var/run/mysqld/mysqld.sock
 port            = 3306
 basedir         = /usr
 datadir         = /var/lib/mysql
 tmpdir          = /tmp
 language        = /usr/share/mysql/English
 bind-address    = The_IP_of_YOUR_MySQL_Server
 # skip-networking

Here, what is important is that you comment out the line skip-networking, and that you insert, in the line bind-address, the IP of the server. Save, restart mysql (again, this depends on OS), access MySQL with the usual 

  mysql -u root -p mysql

and allow access from your own remote IP to the existing database:

  mysql> update db set Host='Your_own_remote_IP' where Db='webdb';
  mysql> update user set Host='Your_own_remote_IP' where user='webadmin';

Now go back to your remote system, and test the new functionality:

 mysql -u webadmin –h The_IP_of_theMySQL_Server –p

For this to work, of course, you need a MySQL client on your Mac. You can now script your own MySQL queries, for instance, in a bash script as follows:

  #!/bin/bash

  result=`mysql -h The_IP_of_theMySQL_Server --user=webadmin --password=Your_Password --skip-column-names -e "select id from mydb.mytable where myattribute = 3"`

EDIT:

This is required if you want to connect remotely directly to the MySQL server. If you just want to script your queries locally (i.e., on the server), then MySQL client will do. You can find an introduction here.

Related Question