Setting up MySQL access on the iPad

Kamal Kamalaldin
Jul 30, 2017 · 6 min read

There are multiple reasons why you might want to manage your MySql database from your iPad:

  • You use your iPad as the main device, and the command line is not always the most efficient way of viewing a table. Apps on the iPad make it much easier to view tables than on the command line.
  • You are a sysadmin with an iPad and you would like the convenience of having that instant power of editing tables and checking things without pulling out your laptop.
  • You are a one-man team and you always need access to the SQL database from every possible device, including your iPad.
  • You like to explore new frontiers in technology, and lets face it, MySql on an iPad is quite a frontier that most people would laugh at.

This guide/personals notebook assumes that you are connecting to a server with the following properties:

  • Running on a Digital Ocean droplet
  • You have root access to the server.
  • Running Ubuntu 16.04

The guide also assumes that you have terminal access from some sort of a remote device. By this, I mean that you should already know how to SSH into the server by using a simple terminal. If you don’t, please look this up and only continue after you are comfortable with using the ssh command.


Installing MySql on your server

This should be a rather straightforward step. I will not discuss it here because the installation process is well documented over O’ so many Stack Overflow pages. Google ‘How to Install MySql on Ubuntu 16.04 Digital Ocean’ and you should find plenty of results.


Creating a Remote User for MySql

While it would certainly be convenient to login as root to your MySql server, is it not particularly secure. It would be more adequate to create a user that can access the server remotely, and preserve the root access to only localhost. So we want the following users and their permissions:

  • ROOT: can only access from localhost (when logged in to the server via ssh prompt)
  • REMOTE_UESR: can access from a remote IP (or any remote IP), and localhost.

Here, we use ROOT and REMOTE_USER as placeholder, here are a few other placeholder will user throughout this step:

  • ROOT: stands for the root access name, which is ‘root’ in unix
  • REMOTE_USER: whatever you want to call your user in MySql.
  • PASSWORD: the password you want for REMOTE_USER.
  • REMOTE_IP: the IP that you want to access MySql on your server from. If you want to access the server from any IP (e.g. if you want to work from a coffee shop), then the IP should be ‘%’

To do this, we need to access the MySql prompt, so

  1. SSH into your server
  2. Login as ROOT by typing the prompt below. It will ask for a password. This should be the same password you set up during installation.

Mysql -u root -p

3. Creat the REMOTE_USER (remember, this is just a placeholder. User whatever username you want in places of this). Here, PASSWORD is a placeholder as pointed out earlier. Below is a link for other options available when creating the remote user.

CREATE USER ‘REMOTE_USER’@’%’ IDENTIFIED BY ‘PASSWORD’;

4. Give the user permissions to view and alter databases. If you want REMOTE_USER to have access to all the databases, then this should be as easy as:

GRANT ALL PRIVILEGES ON *.* TO ‘REMOTE_USER’@’REMOTE_IP’;

FLUSH PRIVILEGES;

This was copied mainly from the link below, but other sources were used to verify (meticulously, via error exhaustion — meaning I was exhausted from errors) that this is how to grant permissions.

To verify that REMOTE_USER has the privileges, you can run

SHOW GRANTS FOR ‘REMOTE_USER’@’REMOTE_PASSWORD’;

It should look something like this

You should see something like


Allowing MySql Through the Network

MySql by default restricts access to only the localhost (meaning you can only access it if you are already on the server’s terminal/computer). To use MySql connections over the internet, we need to do two things:

  1. Tell MySql to allow incoming connections from the internet.
  2. Tell the Operating System firewall to allow MySql to listen to connections from the internet

Setting up MySql for Network Connections

The steps here require that we modify some MySql configurations. It might seem daunting, but if you have already logged in to a server and used MySql on the command line, you are prepared for this.

This is summarized in steps #2–4 of this link

  1. Edit the MySql configuration file found in /etc/mysql/my.cnf. This file was somewhat deserted in my case (like in https://askubuntu.com/questions/699903/why-is-etc-mysql-my-cnf-empty) so the file I actually edited is /etc/mysql/mysql.conf.d/mysqld.cnf. Here, all you need to do is simply put REMOTE_IP where it says bind-address (like the image below).

2. To allow MySql through the network, we can use a tool like ‘ufw’ (uncomplicated fire wall) which comes with ubuntu by default.

sudo ufw allow 3306/tcp

The port 3306 is the default port for mysql, so unless you are using a specific port, 3306 should be what you use here.

We need to restart ufw for the changes to take effect, so run

Sudo service ufw restart

To check that this worked, run

sudo ufw status

and you should see something like

Now we are all set to go on the Ubuntu machine. All we need is a client on the iPad to access the database remotely.


Using an iPad MySql

There are many option on the App Store for a MySql client, some free and others paid. At the moment, I am using ‘Query — A MySql client’, a free app, since my needs are not that demanding. I have never had problems with it, and I can recommend for the following set of features it provides considering it’s a free app:

  • Saving remote database credentials so you don’t have to remember the ports and passwords if you’re managing more than one database
  • Connecting via SSH or SSL, with the option to set up an SSH key, which can be more secure than using a password.
  • TouchID support, so you can ensure that your DB access is protected by a pin or your fingerprint (though for you this could be less safe than a strong password).

Here is a screenshot that should explain the settings you can use for the app.

All that’s left now is hitting the big beautiful “CONNECT” button and you’re all set to use your iPad to manage your MySql database remotely!

Kamal Kamalaldin

Written by

A Machine Learning and A.I. Software Engineer. I have a passion for building things that haven't been built before.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade