Using phpMyAdmin to Manage Local Databases

It's crazy to think about – phpMyAdmin has been around for almost 20 years now. For many, it is the tool for managing MySQL and MySQL-like databases (MariaDB, Percona, etc.). In this article, I'm going to walk through setting up a phpMyAdmin application on your local machine which you can then use to manage MySQL databases running with Nanobox on your local machine.

Nanobox

Nanobox is a "micro-platform" that builds and runs your application anywhere – your local machine, a CI server, or in production – using settings defined in your boxfile.yml.

Learn More About Nanobox

If you haven't already, go ahead and create a free account and download Nanobox.

Things to Note

When phpMyAdmin is installed using the method outlined in this article, it can only be used to manage MySQL databases running locally with Nanobox, not production databases (at least not easily). phpMyAdmin is a standalone, containerized application that is able to interact with databases running inside Nanobox. When running locally, every MySQL database container is given a virtual IP that can be used to access the database from your local machine and/or inside the local Nanobox VM/network.

You can manage multiple databases from multiple apps all running inside Nanobox. It would essentially look like this:

phpMyAdmin with Nanobox - Container Architecture

Now, on to the good stuff...

Create a New Project Directory

Create a new project directory for your phpMyAdmin install and cd into it. For this walkthrough, I'm going to use nanobox-phpmyadmin as my directory name, but you can use whatever you'd like.

mkdir nanobox-phpmyadmin && cd $_

Add a boxfile.yml

Nanobox uses the boxfile.yml to build and prepare your app's runtime. Create boxfile.yml in the root of your project with the following:

run.config:
  engine: php
  engine.config:
    runtime: php-7.1
    extensions:
      - mcrypt
      - mysqli
      - mbstring
      - gd
      - session
      - xml
      - tokenizer
      - dom
      - curl
      - simplexml
      - xmlwriter
      - zip

This includes everything phpMyAdmin needs to run.

Start the Dev Environment

With the boxfile.yml in place, run the following to setup and build a PHP development environment configured specifically for phpMyAdmin.

# add a convenient way to access phpmyadmin from the browser
nanobox dns add local phpmyadmin.local

# start the dev environment
nanobox run

Install phpMyAdmin

The nanobox run command will start your local development environment and drop you into a console inside. Once in, cd into the /tmp directory and use Composer to install phpMyAdmin. Then copy the generated files into your project root, /app. Your local working directory is mounted into the Nanobox container, so any changes made here will propagate down to your machine. This means all the necessary phpMyAdmin files will be available in your filesystem.

# cd into /tmp
cd /tmp

# use composer to install phpMyAdmin
composer create-project phpmyadmin/phpmyadmin

# copy the phpMyAdmin to your project root and cd back into /app
shopt -s dotglob
cp -a phpMyAdmin/* /app
cd /app

Configure phpMyAdmin

In the root of your project, there's a phpMyAdmin configuration template named config.sample.inc.php. Copy this file and name it config.inc.php.

cp config.sample.inc.php config.inc.php

Get Your Database Credentials

phpMyAdmin pulls database configuration from the config.inc.php. It doesn't allow you to specify a host in the UI, so you have to include your databases' hosts in that file.

This next steps assume you have a project with MySQL already setup and running with Nanobox. If not, the Nanobox Guides are the best places to get started.

To get the host of your database, open a new terminal session, cd into your project directory, and pull the information for the app.

# cd into your project with a db
cd project-dir

# view the app info
nanobox info local

In the output, you will see the connection credentials for your database.

data.db
  IP      : 172.21.0.3
  User(s) :
    root - XxxXXxXXxX
    nanobox - xXXXXXxXxx

Also note the username and password for the database. You'll use those to login to your database through the phpMyAdmin UI. You can also include these in your config.inc.php to keep from having to enter them in the UI.

Update the Hosts in Your config.inc.php

Replace the host for your database in your config.inc.php with the IP of your project's database.

/* Server parameters */
$cfg['Servers'][$i]['host'] = '172.21.0.3';

Managing Multiple Databases

You can use this phpMyAdmin app to manage multiple databases from multiple apps running with Nanobox. Get the host of each database by repeating the process above. You can then add each host to your config.inc.php.

Start the array with [1] and increment up from there.

/* Server parameters */
$cfg['Servers'][1]['host'] = '172.21.0.3';
$cfg['Servers'][2]['host'] = '172.21.0.4';
$cfg['Servers'][3]['host'] = '172.21.0.5';

Start phpMyAdmin

With your host(s) configured, you're ready to start phpMyAdmin. From inside the Nanobox console, run:

php-server

Start the App with Your Database

In another terminal, if you haven't already, start the app that includes the database you're going to manage. If the app isn't running, there won't be a running database to connect to.

cd project-dir
nanobox run

Manage Your Database(s)

Visit phpmyadmin.local in your browser.

phpMyAdmin UI

Enter your database's username and password. If you have multiple databases, select the host you're trying to connect to from the "Server Choice" dropdown. That's it!

Anytime you want to fire up your phpMyAdmin app again, just cd into the nanobox-phpmyadmin directory and run:

nanobox run php-server

Note: phpMyAdmin could be used to manage a live database by including it in your project's codebase and running it on live servers, but, for security reasons, I don't recommend going that route.

Posted in PHP, phpMyAdmin, MySQL