Sunday Quicky #1 – Ingest an SQL file into MySQL

Introduction

Edit: I ran into an issue when setting up phpMyAdmin subsequent to this post. Issue and solution explained at the end.

I was asked earlier today how to view a .sql file in a friendly manner. I didn’t quite get to the answer to that question, I’ve added it to my list. However, while attempting to arrive at a solution I thought that ingesting the .sql file into a database system, MySQL for example, might be a step in the right direction. In my efforts to make it somewhat friendly I didn’t want to get down to the command line level. In order to successfully avoid that I thought that installation of phpmyadmin would help, and it did. I won’t bore you with the steps of how to set that up, in fact I found an excellent resource for setting it up on my system (Manjaro by the way), and I bet there’s a handy resource available for whatever system you happen to be running. A word of warning, unless you want the servers running constantly in the background, skip the steps which say enable, just start the services when you need them.

What I will briefly discuss is how to ingest an sql file using phpmyadmin, once there’s a system all set up to do so.

It’s pretty simple actually…

Warning: You’re php.ini file may need to be edited to increase file upload limits if you run into difficulty see this Frequently Asked Question for guidance on which settings to amend.

So, you’re all set up, it’s straight forward from here.

Log into your phpMyAdmin web administration panel. You will see something similar to the below Figure 1.

Figure 1: Databases view of phpMyAdmin

From here, in the dialog just above your database list, simply enter a name for the database you want to ingest from the sql file, the names don’t have to match, then click create. You will be brought the the interface in Figure 2 below, don’t enter anything, just click on the Import button at the top.

Figure 2: Interface following Create Table

In the import interface (Figure 3) click the “Choose File” button, a file chooser dialog should open, navigate to the location of your .sql file and choose it. Then simply click “Go” at bottom right. This will work away by itself for a minute or two, depending on the size of the .sql file.

Figure 3

You should see something similar to the message in Figure 4.

Figure 4: Successful message

Back to your list of Databases and you’ll see in Figure 5 that a new database is there, in my case “chats”. You can now click into the database and browse the tables and rows. It’s not overly friendly, as I said above, but it’s a step in the right direction (hopefully).

Figure 5: Database list with new database

That’s all, just a quicky. Let me know if you encounter difficulties.

My Difficulty

When re-setting this all up after a fresh install I installed phpMyAdmin and at the bottom of the page when logged in I saw this ugly warning:

Figure 6: tmp directory warning

It was a simple fix:

sudo mkdir /usr/share/webapps/phpMyAdmin/tmp
sudo chown http:http /usr/share/webapps/phpMyAdmin/tmp/
sudo systemctl restart httpd

And voila! Warning gone.

Leave a Reply

Your email address will not be published.