Skip to main content

Use Emacs sql-mode to connect to database on a Docker image

  |   Petru Rebeja

While working on a project I had to load and process some resources from a MySQL database. I had a database dump and all I needed was to sudo apt-get install mysql but I decided against it because that would just bloat my OS with software used only once and drain my laptop battery with the service running in the background.

Instead, I decided to restore the database on a Docker image for MySQL and query the data using mysql-client.

Install mysql-client locally

This one is simple; just run: 

  sudo apt-get install mysql-client

Install Docker on Ubuntu

The first thing to do is to head to Docker documentation for instalation instructions which I've copied here:

  • Install packages to allow apt to use a repository over HTTPS: 

       sudo apt-get install \
       apt-transport-https \
       ca-certificates \
       curl \
       software-properties-common

  • Add the official GPG key for Docker 

       curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

  • Setup the stable repository. Make sure to select the proper architecture; in my case it's amd64 

       sudo add-apt-repository \
       "deb [arch=amd64] https://download.docker.com/linux/ubuntu \
       $(lsb_release -cs) \
       stable"

  • Update the package index 

       sudo apt-get update

  • Install Docker 

       sudo apt-get install docker-ce

Restore MySQL database to a Docker container

  • Download the Docker image for MySQL 

       sudo docker pull mysql

  • Create an empty database 

       sudo docker run --name <container-name> -e MYSQL_ROOT_PASSWORD=<password> -e MYSQL_DATABASE=<db-name> -d mysql

    This will create a new container with an empty database and login root.

  • Restore database from dump 

       sudo docker exec -i <container-name> mysql -u<user-name> -p<password> --database=<db-name> < <path-to-sql-file>

    Notes:

    1. Make sure that there is no space between -u and <user-name>, e.g. for user root the option should be -uroot
    2. The same goes for password - e.g. if password is my-secret then the option should be -pmy-secret
    3. path-to-sql-file should point to a file on host OS

Connect to MySQL database running on Docker container from Emacs

  • First, start the container sudo docker start <container-name>
  • Get the IP Address of the container
  • Get the container configuration using sudo docker inspect <container-name>
  • Copy the IP Address from the output under NetworkSettings/IPAddress
  • In Emacs execute M-x sql-mysql
    • For User: enter the value for <user-name> (root is the default)
    • For Password: enter the value for <password>
    • For Database: enter the value for <db-name>
    • For Server: enter the IP Address from 2.

That's it. Happy querying!

Comments

Comments powered by Disqus