Home / server / Questions / 444401
Accepted
drewrockshard
Asked: 2012-11-02 07:21:28 +0800 CST

MySQL Dump Viewer

  • 772

I'm a linux user, however, I'm provided with a Windows laptop in the office. I could fire up a small web server with PHPmyadmin or even upload to a remote system and import into a database, however, for what I'm doing, I'd like to know if there is anything easier?

What I have is a MySQL Dump file of a database. I need to know if there is anything out there that can read in a MySQL dump file and then allow me to perform selects (to lookup data), without my having to have an actual MySQL database. Almost like a virtual MySQL database as a file sort of thing. Basically, I'm trying to cross reference data, and I don't exactly want to create a new database, do the import, and then read the data like that. I will if I have to, but was just wondering if there was some sort of program that would read my dump file and let me just view it as if I was connected to the database.

mysql

3 Answers

  1. Best Answer

    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.

    • 3

  2. 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.

    • 3

  3. If you don't want a full fledged local mysql installation you could also import your mysql-dump into a sqlite database and (temporarily) work with this. For converting the mysql-dump you could use this ...

    • 1