Performance Zone is brought to you in partnership with:

Andy is a freelance journalist based in Coventry, United Kingdom. He also codes web applications. Andy is a DZone MVB and is not an employee of DZone and has posted 26 posts at DZone. You can read more from them at their website. View Full User Profile

MySQL and Ubuntu 12.10

04.15.2013
| 2467 views |
  • submit to reddit

I recently had reason to be shunting large amounts of MySQL data around on my Ubuntu 12.10 powered laptop. The data was exported from MySQL in .sql files, and they had a file size in excess of 50Mb. That ruled out PHPMyAdmin for re-importing the data. Here is what I did to solve the problem.

As far as I am aware, there isn’t a software application available for Ubuntu that provides a GUI environment for this kind of task. I know of Navicat, but it is expensive ($129/£83 for the standard edition) for an app that still only runs in Wine. MySQL databases

It’s not MySQL and PHPMyAdmin, it’s me

I am NOT complaining about PHPMyAdmin. It’s a great piece of work, and I use it for most of my querying work on my Ubuntu machine. It just can’t handle huge data imports without a lot of configuration changes. And I really don’t like configuring things. Well, not if there is another (simpler) solution available.

So, the problem…

I had several large .sql files available on the local network that I wanted to import into my local copy of MySQL. The data was an export from a production database that was going to allow me to test some coding changes in as realistic a way as possible.

I just need to get that data in…

The solution – hello command line

If you read that heading and involuntarily convulsed, fear not. This is going to be (mostly) painless. I’m going to assume that you have MySQL installed via synaptic; a standard installation in other words.

Connecting

Open up the terminal window and do:

mysql -u username -p -h localhost

That is how you connect. Simple as that. It’s not too bad is it? Mind you, the cursor will be flashing at you, so you might be wondering what to do next.

While this isn’t meant to be a full tutorial on using MySQL from the command line, here are a few basic things.

Choosing a database to use

That is an easy one:

use testing

After you have connected, that will make sure you are now working with the database called testing. To see what databases you have do:

show databases
You can create a database and some tables too

This shouldn’t come as a surprise – of course you can. How though? Something like this:

CREATE DATABASE test_db;
use test_db;

Now we are using our new database. It doesn’t have any tables though, so let’s fix that:

CREATE TABLE `notes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `note` text,
  PRIMARY KEY (`id`)
)

This creates a simple table in the test_db database. We can now take a look at the table we just created:

mysql> describe notes;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(200)     | YES  |     | NULL    |                |
| note  | text             | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

mysql>

I think that is pretty painless so far. Next, we’ll deal with the process of importing some data.

Getting data in

Typing “quit” allows you to leave the MySQL monitor so do that now. I have found that the import works best by running a connection and import command together. It looks like this:

mysql -u root -p -h localhost test_db < /path/to/file/world.sql

Depending on the size of the file, nothing will happen for a while, but then the prompt will return. That’s it, job done.

Make sure you are connected and using the the test_db then do:

mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| City              |
| Country           |
| CountryLanguage   |
| notes             |
+-------------------+
4 rows in set (0.00 sec)

Then you will be able see the table(s) you just imported. By the way, I was using the world sample database for MySQL availble from here. It’s not a particularly large file, but it will give you the idea.

Finally…

So in the end the command line is the best way to work with large .sql files on Ubuntu. I suspect that might the case for Linux in general. The process is not too difficult though, and there is something quite refreshing about just banging in a few commands and getting things done.

Published at DZone with permission of Andy Hawthorne, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)