mysql – KeenerTech

February 10, 2013May 5, 2026

MySQL: A Grab Bag of Useful Bits

Database management tools are nice to have, but they’re not essential (I’m talking about you, MySQL Workbench). When the tools aren’t working, for whatever reason, you can always go back to basics and create the objects you need from the simple, mysql command-line utility. Here’s a grab bag of things you can do…

What User Accounts Exist?

   mysql> use mysql

   Database changed

   mysql> desc user;

   +------------------------+---------------+------+-----+---------+
   | Field                  | Type          | Null | Key | Default |
   +------------------------+---------------+------+-----+---------+
   | Host                   | char(60)      | NO   | PRI |         |
   | User                   | char(16)      | NO   | PRI |         |
   | Password               | char(41)      | NO   |     |         |
   | Select_priv            | enum('N','Y') | NO   |     | N       |
   | Insert_priv            | enum('N','Y') | NO   |     | N       |
   | Update_priv            | enum('N','Y') | NO   |     | N       |
   | Delete_priv            | enum('N','Y') | NO   |     | N       |
   | Create_priv            | enum('N','Y') | NO   |     | N       |
   | Drop_priv              | enum('N','Y') | NO   |     | N       |
   | Reload_priv            | enum('N','Y') | NO   |     | N       |
   | Shutdown_priv          | enum('N','Y') | NO   |     | N       |
   | Process_priv           | enum('N','Y') | NO   |     | N       |
   | File_priv              | enum('N','Y') | NO   |     | N       |
   | Grant_priv             | enum('N','Y') | NO   |     | N       |
   | References_priv        | enum('N','Y') | NO   |     | N       |
   | Index_priv             | enum('N','Y') | NO   |     | N       |
   | Alter_priv             | enum('N','Y') | NO   |     | N       |
   | Show_db_priv           | enum('N','Y') | NO   |     | N       |
   | Super_priv             | enum('N','Y') | NO   |     | N       |
   | Create_tmp_table_priv  | enum('N','Y') | NO   |     | N       |
   | Lock_tables_priv       | enum('N','Y') | NO   |     | N       |
   | Execute_priv           | enum('N','Y') | NO   |     | N       |
   | Repl_slave_priv        | enum('N','Y') | NO   |     | N       |
   | Repl_client_priv       | enum('N','Y') | NO   |     | N       |
   | Create_view_priv       | enum('N','Y') | NO   |     | N       |
   | Show_view_priv         | enum('N','Y') | NO   |     | N       |
   | Create_routine_priv    | enum('N','Y') | NO   |     | N       |
   | Alter_routine_priv     | enum('N','Y') | NO   |     | N       |
   | Create_user_priv       | enum('N','Y') | NO   |     | N       |
   | Event_priv             | enum('N','Y') | NO   |     | N       |
   | Trigger_priv           | enum('N','Y') | NO   |     | N       |
   | Create_tablespace_priv | enum('N','Y') | NO   |     | N       |
   | ssl_type               | enum('',      | NO   |     | NULL    |
   |                        |  'ANY',       |      |     |         |
   |                        |  'X509',      |      |     |         |
   |                        |  'SPECIFIED') |      |     |         |
   | ssl_cipher             | blob          | NO   |     | NULL    |
   | x509_issuer            | blob          | NO   |     | NULL    |
   | x509_subject           | blob          | NO   |     | NULL    |
   | max_questions          | int(11)       | NO   |     | 0       |
   |                        |   unsigned    |      |     |         |
   | max_updates            | int(11)       | NO   |     | 0       |
   |                        |   unsigned    |      |     |         |
   | max_connections        | int(11)       | NO   |     | 0       |
   |                        |   unsigned    |      |     |         |
   | max_user_connections   | int(11)       | NO   |     | 0       |
   |                        |   unsigned    |      |     |         |
   | plugin                 | char(64)      | YES  |     |         |
   | authentication_string  | text          | YES  |     | NULL    |
   | password_expired       | enum('N','Y') | NO   |     | N       |
   +------------------------+---------------+------+-----+---------+
   43 rows in set (0.01 sec)

   mysql> select host, user from user;

   +--------------------------+------------+
   | host                     | user       |
   +--------------------------+------------+
   | %                        | davekeener |
   | 127.0.0.1                | root       |
   | ::1                      | root       |
   | Davids-MacBook-Pro.local |            |
   | Davids-MacBook-Pro.local | root       |
   | localhost                | root       |
   +--------------------------+------------+
   6 rows in set (0.00 sec)

The mysql schema contains system information, including user accounts, privileges, etc. Once you’ve switched so that it’s your active schema, you can do a “desc” on the USER table to see what columns are available.

The only columns of general interest for our purposes are HOST and USER. That’s what the final SQL statement shows for us, revealing that there are 6 user accounts defined.

What Schemas Exist?

In MySQL, a schema, or database, is a unit that can have its own privileges, and which can contain other database objects such as tables, views, etc. You typically can’t see schemas that you don’t have permissions on, for security reasons. In order to see all of the available schemas, you’ll have to login as root.

   mysql> show schemas;

   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | ktech_dev          |
   | mysql              |
   | performance_schema |
   | test               |
   +--------------------+
   5 rows in set (0.00 sec)

Changing a User’s Password

   mysql> SET PASSWORD FOR 'dave'@'localhost' = PASSWORD('mvemjsun');
   Query OK, 0 rows affected (0.28 sec)

The change can also be done directly by updating the USER table in the mysqlschema, but…really…I don’t advise manual updates of system tables.

June 5, 2011May 5, 2026

MySQL and Rails 3 on a PC

Rails 3.0 is the new big thing, and deservedly so. But getting MySQL working with Rails 3 on a PC can be problematic. Here’s some easy steps to make your life a whole lot easier if you’re doing Rails and MySQL development on a PC (although most of the instructions, except for the obvious PC-isms, can apply to Mac or Linux systems, too).

First, let’s go over some background technical issues.

Rails 3 works with both Ruby 1.8.7 and Ruby 1.9.2. When it comes to the PC environment, working with Ruby 1.8.7 is still a little more reliable than 1.9.2, so our installation procedure will focus on Ruby 1.8.7.

Traditionally, Rails applications have used the mysql gem to work with MySQL databases. However, this gem hasn’t been updated since 2009, and the ActiveRecord component of Rails, which is responsible for object-relational database mapping, has changed dramatically. Accordingly, the new mysql2 gem is recommended instead.

The mysql2 gem requires some native extensions, so you’ll need the DevKit or the gem can’t be built properly.

I’ve found the easiest way to get all of the components that I want on my PC, e.g. — Ruby, DevKit, etc., is to download RailsInstaller.

So, let’s get started:

Install RailsInstaller: The RailsInstaller package comes with Ruby 1.8.7, Rails 3.0.7, DevKit, Git, bash and a few other niceties.
Test Your PATH Environment Variable: RailsInstaller should set up your PATH properly. To test this, go to the Start menu, under Programs -> RailsInstaller, where you’ll see entries for “Command Prompt with Ruby and Rails” and “Git Bash”. The first gives you a standard PC command window (but with C:\RailsInstaller\Ruby1.8.7\setup_environment.bat automatically called to set up the environment) and the second gives you a UNIX-like window and a bunch of UNIX commands (compliments of DevKit).To view the PATH in the command window: > echo %PATH%To view the PATH using bash: $ echo $PATHCheck that both C:\RailsInstaller\DevKit\bin and C:\RailsInstaller\Ruby1.8.7\bin appear in the PATH. The PATH should be correct for the command window; if not, edit C:\RailsInstaller\Ruby1.8.7\setup_environment.bat as needed.If the PATH is not correct for the bash window, update the PATH environment using the Control Panel (preferred) or by creating a separate .bashrc file.To do this, bring up the Control Panel, click the System icon and then select the Advanced tab. Click the Environment Variables button, then edit the PATH in the “System variables” section to include the relevant directories.This requires the “Local Admin” privilege on your PC. If organization policy precludes this level of access, which is common with government agencies and government contractors, then set the path in C:\Documents and Settings\<username>\.bashrc. This file will conform to standard bash shell conventions.
Install MySQL: Install the Community Edition of MySQL (the free one), which is currently at version 5.5. Download and install it from the MySQL site. Accept the various defaults, and make sure you agree to set it up as a service (so you don’t have to worry about starting/stopping it manually). During the install, you’ll be asked to provide a password to go with the “root” account — remember your password, please.Upon completion of the installation, ensure that MySQL’s bin directory is added to the PATH (so you can easily use tools like the mysql client program).
MySQL Workbench: Strictly speaking, this isn’t required, but if you’re going to download MySQL you might as well also install the free tools that go with it. MySQL Workbench provides features that allow you to run queries against the database, design your database tables and manage your server instance (including user accounts).
Create Database: Create a schema for your Rails application, along with a user account. I typically create users with MySQL Workbench, but I create schemas using the mysql client (since MySQL Workbench is terrible in this area).
Install the mysql2 Gem: You’ll need to do the install manually, rather than via bundle, since there are some key parameters that need to be provided. Also, you’ll want to do the install from bash, so the various UINX-ish utilities are available to the build process.Also, a note on versions. If you’re using Ruby 1.8.7, you should be using version 0.2.7 of the gem. Those using Ruby 1.9.2 (at your own peril), should be using the latest version of the gem.To do the install:   $ gem install mysql2 -v=0.2.7 —
     ‘–with-mysql-lib=”c:\Program Files\MySQL\MySQL Server 5.5\lib”
     –with-mysql-include=”c:\Program Files\MySQL\MySQL Server 5.5\include”‘Note the careful use of quotes.
Setup Your Rails App: Make sure your Rails app is properly configured to use the mysql2 gem. List the mysql2 gem in the application’s Gemfile with version 0.2.7. Also, in the database.yml file, the adapter should be “mysql2” and not “mysql”.
Start the Rails Server: Start up your Rails application. Everything should be fine at this point. However, if the application is complaining about not finding “libmysql.dll” then go to the next step (otherwise skip it).
Workaround: If your application complained about not being able to find “libmysql.dll” then you’ll need to do this workaround. Copy “libmysql.dll” from the lib directory of your MySQL installation into C:\RailsInstaller\Ruby1.8.7\bin. Then start your Rails app again and everything should be fine.

Well, the steps aren’t as painless as I’d like, but they should get you up and running with Ruby 1.8.7, Rails 3 and MySQL. Good luck!

October 9, 2008May 5, 2026

Managing Database Dumps Using MySQL

When you’re developing applications that use a MySQL database, it’s often beneficial to work with real data. Since you never want to develop against your production database, it would be nice if you could take a snapshot of your production database, load it into a local database of your own and develop against the copy.

In fact, it’s not hard to do this at all.

Dumping a MySQL Database

Here’s the command to dump a MySQL database on a UNIX/Linux system:

mysqldump -h <host> -u <user> -p <database> | zip > staging.zip

When using the above command, it will prompt you for the password, which seems to work better than trying to pass the password as a parameter.

I typically also pipe the dump output to the zip command because the databases I usually deal with are generally large enough that it’s difficult to find enough room to store them on disk in an uncompressed form. Since a MySQL dump file is just text, it tends to compress by 75% or more.

Loading a Database Dump

Download the dump file from whatever machine you created it on. Unzip the file, which will create a local file called “-“. Now, this file name was created as an artifact of the original zip process, and it’s not a very friendly file name. So rename “-” to something more useful, like “dump.sql”.

In order to load the dump file, you’re going to have to take care of a few prerequisites, which should be fairly logical. You need to make sure that the database already exists, and is empty.

You also need to make sure that the user name you’re going to use when loading the dump file exists and has the privileges to create all of the necessary database objects. If necessary, you can use the following MySQL command to give a user all privileges:

GRANT ALL ON *.* TO someuser@somehost

To load the dump file, run the following command:

mysql -h <host> -u <user> –password=<pass> <database> < dump.sql

That’s all there is to it. You should now be able to easily dump and load a MySQL database in order to support your development efforts.