Monitor MySQL command line imports and exports on Ubuntu using Pipe Viewer

If you’ve been working with PHP/MySQL based websites for any amount of time, you’ve probably heard about the MySQL command line tools for importing and exporting your database.

The syntax is quite simple, for exports you run

mysqldump -udbuser -p dbname > dbfile.sql

which exports the database to a file on the local machine. For imports you run

mysql -udbuser -p dbname < dbfile.sql

which imports the contents of the local file (dbfile.sql) into the empty database.

This is usually the quickest way to export a database from one location to import it into another.

One problem I’ve experienced from time to time is exporting and importing large databases. By default the tools don’t output any progress indicator (or anything at all really), and using the -v (verbose) switch outputs every single MySQL command being run from the import file, which is like watching the code in the Matrix.

via GIPHY

Fortunately, there is a way you can run imports and exports and show a progress bar, with Pipe Viewer.

Pipe Viewer is a terminal-based tool for monitoring the progress of data through a pipeline. The Pipe Viewer homepage has all the install commands for the various operating systems, and Ubuntu has an official package for it, so you can install it through apt.

sudo apt-get install pv

Once pipe viewer is installed, you merely need to make two small changes to you import and export commands.

pv dbfile.sql | mysql -udbuser -p dbname
mysqldump -udbuser -p dbname | pv -W > dbfile.sql

And viola! You will be presented with a progress monitor that shows file size imported, time taken, percentage progress. Much better than staring at a blank screen.

On Ubuntu 18.04, Installing MySQL Does Not Set the Root Password.

MySQL Root Password

One of the things I love about using Ubuntu as my primary operating system is that I can have quickly set up a ‘bare metal’ LAMP development environment. While I unusually run my client websites inside on of my custom Vagrant boxes, for working on personal projects or plugin/theme customisation everything’s much faster when it’s able to use the full power of the machine I’m working on.

After I switched back to a computer as my every day workstation, I came across a weird little issue with installing MySQL. Usually during the install process it asks me to set a root password, but this time it did not. This meant that I wouldn’t be able to access the database using the root user. Not a huge issue, as I could create another user to access any database, but as it’s a local install, connecting as the root MySQL user is just much easier.

As it turns out, on Ubuntu installs running MySQL 5.7 or later, the root  user is set to authenticate using the auth_socket plugin rather than using a  password. Thankfully the folks over at Digital Ocean have released an article which explains this information and provides the steps to switch the root user authentication from auth_socket to using a password.

UPDATE: As it turns out, this was not the only issue I was having, turns out the MySQL install was also corrupt somehow. Fortunately the internet is a wonderful place, and I found some instructions on how to completely remove all traces of MySQL server, and start again.

sudo dpkg -P mysql-server mysql-server-5.7
sudo apt-get autoremove
sudo apt-get clean
dpkg -l | grep -i mysql
sudo rm -rvf /var/lib/mysql
sudo apt-get install mysql-server

	

MySQL: filtering results from a list of id's

A short while ago I posted about testing if a variable exists in a list and I spoke about the MySQL “IN” keyword.

I realised this week that not everyone knows about the MySQL “IN” keyword, so I’ve decided to explain this very handy SQL keyword.

Lets say you have a user table with the following fields; user_id, username, password. You have a list of user ids (eg 1, 3, 5) and you want to draw the usernames and passwords for these users.

You could use the following SQL code:

SELECT `username`, `password` FROM `user`
WHERE `user_id` = 1 AND WHERE `user_id` = 3 AND WHERE `user_id` = 5;

Now while this is correct and will return the required results, it is quite a bit to code (especially if you have a larger list of user ids). Here comes the “IN” keyword to save us.

SELECT `username`, `password` FROM `user` WHERE `user_id` IN (1, 3, 5);

See how easy that is. Now if you have a much larger list (or the list grows) you simply add the user ids to the comma delimited list. Less time spent, and the same result. I’m all about the shortcuts….