Getting Started with MySQL

This page provides a terse guide to basic MySQL functions an features. The need for the guide arose because of some database driven projects. Although the MySQL documentation is very good and throrough, my needs require something simpler and shorter. Just the most common commands are required along with an example or two. As Joe Friday used to say, "just the facts ma'am". So hopefully this guide will be useful not just to myself but other developers getting started with MySQL.

Table of Contents

MySQL Administration

Running the MySQL Client

There are several differnt ways to run the MySQL client. Each method has its advantages.

MySQL Command Line Client

The default method is to simply type:

mysql

Note: This runs the mysql command line client. This of course assumes the mysql server is running an you can connect to the server.

The mysql command line client starts and looks something like this:

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2554
Server version: 5.1.37 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
	

To exit out of the shell type:

quit

Log In

Most of the time you want to log into the MySQL server you are working with. To do this, you generally need to include a few more options on the command line.

User name and password prompt

To just log into to the local MySQL server, specify a user name and the -p option. This prompts you for the password to log in.

mysql -udbuser -p

Specify Everything on Command Line

To log in a host by name and database to use, creat a comman line like this. Notice that instead of prompting for a password this time, it is included on the command line.

mysql -udbuser -pdbpass -h127.0.0.1 dbname

The 127.0.0.1 specifies the localhost. It should be the same as "localhost" but my Mac makes me use the number.

Running from the Command Prompt or Shell

You can also run SQL commands from the command line for shell prompt. For example:

ysql -u blueskydbuser -pdbpass -e "use login; select * from users"

Issuing SQL Commands with Text Files

Often, you will want to issue a number of queries or or a more complext SQL statement. In situations like this, storing the SQL commands in a text file can be helpful. To run the commands, simple pipe them into the MySQL client.

mysql -uUserName -pPassWord dbname < file.sql

If you include use dbname; command as the first line, you can shorten the command to this:

mysql -uUserName -pPassWord < file.sql

Back Up and Restore

Backing up and Restoring a DB is pretty straight forward.

Backing Up a DB

To backup a database, use the following command:

mysqldump -uUser -pPassword dbname > dbname.sql

This creates a dumpfile that looks something like this:

   1:DROP TABLE IF EXISTS `users`;
   2:
   3:CREATE TABLE `users` (
   4:  `id` int(11) NOT NULL AUTO_INCREMENT,
   5:  `email` varchar(40) NOT NULL,
   6:  `user_password` varchar(30) NOT NULL,
   7:  `first_name` varchar(20) NOT NULL,
   8:  `last_name` varchar(20) NOT NULL,
   9:  `nick_name` varchar(20) DEFAULT NULL,
  10:  `registration_date` datetime NOT NULL,
  11:  `last_online` datetime DEFAULT NULL,
  12:  `jdata` text,
  13:  PRIMARY KEY (`id`)
  14:) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
  15:
  16:LOCK TABLES `users` WRITE;
  17:
  18:INSERT INTO `users` VALUES
  19:(1,'gwashington@example.com','test','George','Washington','georgew','2009-09-12
  20:03:54:00',NULL,NULL),(2,'jadams@example.com','test','John','Adams','johna','2009-09-12
  21:03:56:30',NULL,NULL),(3,'tjefferson@example.com','test','Thomas','Jefferson','tommyj','2009-09-12
  22:03:56:58',NULL,NULL),(5,'mwashinton@example.com','test','Martha','Washington','horsegirl','2009-09-12
  23:04:01:08',NULL,NULL),(6,'aadams@example.com','test','Abagail','Adams','aaa','2009-09-12
  24:04:03:12',NULL,NULL),(7,'jodoe@example.com','test','John','Doe','doedude','2009-09-12
  25:04:06:24',NULL,NULL),(8,'jadoe@example.com','test','Jane','Doe','janed','2009-09-12
  26:04:06:42',NULL,NULL);
  27:
  28:UNLOCK TABLES;

This creates a text file full of the SQL commands you need to restore the database. Note that mysqldump adds a number of comments to this output file. Some of the comments are not compatible with the MySQL client, so in this example I removed them.

Restoring the DB

To restore the database, first start the MySQL client and create the database. For example:

create database dbname;

To restore the tables and data, pipe the SQL commands from the dumpfile back into the MySQL client:

mysql -uUsername -pPassword dbname < dbname.sql

 

Starting and Stopping the Server

These examples are for Mac OS X. They also assume that the server commands are in the path.

Starting the Server on OS X

To start the MySQL Server on OS X, sudo to a bash shell. Type:

mysqld_safe --user=mysql &

Shutting Down the Server

To shutdown the server, type:

mysqladmin shutdown

Set the root password

To set the MySQL Root password, use the mysqladmin client, not the mysql client. Type the following:

mysqladmin -u root password secret

where secret is the password for root you want to use. Since you will be typing this password into a configuration file in plain text in the next step, don't use one of your real password. Use something specific for this task.

Reset the Root Password

Steps described here:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Add a User

To add a user with all privileges, do the following:

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'YourPasswordHere'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' with grant option';

In more normal circumstances, you would want to limit the user to a single database and limited set of privileges. Reference: http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

Checking the results

Assuming you have root access to the MySQL server, you can check if the user has been added.

use mysql; select * from user;

Troubleshooting

ERROR 1396 (HY000): Operation CREATE USER failed for 'user'@'localhost'

This error is caused by deleting a user from the user table, then trying to add the user back. The deleted row may still be cached. Restart the mysql server or flush the table caches using: flush tables;

 

MySQL Queries

This section provides an overview of commonly used queries.

Create a Table

A table is created with the create table command. This requires some knowledge of the MySQL data types and a little SQL.

For example to create a table to manage users on a web site, my create table statement would look something like this.

   1:CREATE TABLE users (
   2:id int not null auto_increment primary key,
   3:email varchar(40) not null,
   4:first_name varchar(20) not null,
   5:last_name varchar(40) not null,
   6:user_password char(30) not null,
   7:registration_date datetime not null,
   8:last_online datetime,
   9:jdata text
  10:);

Insert a Row

To insert a row into a table, the syntax would look like this:

   1:insert into users values(null, "gwashington@example.com", "test", "George",
   2:"Washington", "georgew", utc_timestamp(), null, null);
   3:
   4:insert into users values(null, "jadams@example.com", "test", "John", "Adams",
   5:"johna", utc_timestamp(), null, null);

Select Data

Selecting data for MySQL is the same as for any other SQL database. For example, to retrieve all thr rows in a table, the command would be:

select * from dbname;

To return only specific columns from the rows specify them instead of "*".

select first_name, last_name from dbname;

To specific specific rows, use a where clause.

select first_name, last_name where last_name="Doe";

Update a Row

You update fields in a row using the update command.

For example, if I wanted to udpate the access_level field for a one user I could write something like this:

update users set access_level=99 where nick_name="johna";

Say for example I added the access_level column and needed to load it with default data. That can be done with a query like this:

update users set access_level=1;

Without the where clause, all the rows in the table will be affected.

Add a Column to a Table

Opps you just forgot to add a column to you table. How do you fix it. With the alter table command.

alter table TableName add new_column type;

For example:

alter table TestTable add middle_name varchar(40);