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
- Run the MySQL Client
- Log In
- MySQL Command Line
- MySQL from Test File
- Backup and Restore
- Start and Stop a Server
- Set the Root Password
- Add a User
- MySQL Queries
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);