MySQL Dump Viewer

There are a few tools out there for viewing the dump files out there. Keep in mind that it's just a huge text-file with sql commands. Any SQL Query Analizer tool out there can typically open and view the files. There is no tool (that I've seen) that will let you treat the txt file as an editable database. Even the MYSQL Workbench GUI has one built-in.

As a slightly-easier method of importing/modifying/etc the dump files, just use the mysql workbench to import the dump file into a locally running DB server... and export it back out when done. No need for the entire php framework with myPHPAdmin... and apache... and everything else.

I keep coming back to this question of how to query a DB snapshot and ended up throwing together a collection of Docker calls that pull up phpmyadmin for a .sql dump file. This isn't exactly a lightweight solution, but it's quick (after bringing in the Docker images the first time, anyway) and the created databases and servers are disposable.

With the (imperfect and OSX-specific) script below, it's a one-liner to pull in or start up the relevant Docker machines, load in the dump file to the mysql server, and pop open a browser with phpmyadmin pointed to the relevant database:

#!/bin/bash
ROOT_PWD=root

if [ $# -lt 1 ]; then
  echo "Need a dump file"
  exit 1
fi

dump="$1"
IP=$( docker-machine ip default )

if ! $( docker ps | grep -q "dump_db$" ); then
  # create the data container
  docker create --name dump_data -v /var/lib/mysql mysql

  # run the sql server
  docker run --name dump_db \
    --volumes-from dump_data -v /var/lib/mysql:/var/lib/mysql \
    -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql \
    -e MYSQL_DATABASE=dump -e MYSQL_ROOT_PASSWORD=$ROOT_PWD \
    -it -p 3306:3306 -d mysql

  # wait for it to be up and running...
  mysql_port=$( docker port dump_db | grep "3306/tcp" | awk -F: '{print $2;}' )
  while ! curl http://$IP:$mysql_port/
  do
    echo "waiting for mysql server..."
    sleep 1
  done
fi

# upload the dump
docker exec -i dump_db mysql -uroot -p$ROOT_PWD dump < $dump

if ! $( docker ps | grep -q "dump_phpmyadmin$" ); then
  # run phpmyadmin
  docker run -d --link dump_db:mysql -e MYSQL_USERNAME=root --name dump_phpmyadmin -p 80 ahebrank/phpmyadmin
fi

web_port=$( docker port dump_phpmyadmin | grep "80/tcp" | awk -F: '{print $2;}' )
open "http://$IP:$web_port/db_structure.php?server=1&db=dump"

(https://github.com/ahebrank/dumpviewer/blob/master/view)

This isn't really different from the original answer to load the file into a local mysql server. But it's three years later and containers make the provisioning and... well, containerization a little easier.

MySQL Dump Viewer

Related

Recent Posts