Troubleshooters.

Com, Code Corner and MySQL Starter Present

MySQL Basic Tutorial

Copyright (C) 2006 by Steve Litt
Note: All materials in MySQL Starter are provided AS IS. By reading the materials in MySQL Starter you are agreeing to assume all risks involved in the use of the materials, and you are agreeing to absolve the authors, owners, and anyone else involved with MySQL Starter of any responsibility for the outcome of any use of these materials, even in the case of errors and/or omissions in the materials. If you do not agree to this, you must not read these materials.

To the 99.9% of you honest readers who take responsibility for your own actions, I'm truly sorry it is necessary to subject all readers to the above disclaimer.

CONTENTS

About this Tutorial MySQL Administrivia Working with Tables MySQL Commands vs. Postgres Commands

About this Tutorial

I'll be adding to this tutorial in the coming weeks. If you have ideas of things you'd really like to see here, email me.

MySQL Administrivia
This section goes over the basics of maintaining a database rather than actual data. It includes creation of databases, creation of users, granting of priveleges to users, changing passwords, and other activities from within themysql CLI front end to MySQL.

Entering and Exiting the mysql Program

Your starting point for MySQL maintenance is the mysql program:

[slitt@mydesk slitt]$ mysql ERROR 1045: Access denied for user: 'slitt@localhost' (Using password: NO) [slitt@mydesk slitt]$

OOPs! User slitt is apparently password protected, so we must run mysql to query for a password, and then type in the password (which of course types invisibly)...
[slitt@mydesk slitt]$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 41 to server version: 4.0.18 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> quit Bye [slitt@mydesk slitt]$

In the preceding, you ran mysql with the -p option so that it would query for the password, you typed in the password, and then you exited the mysql program by typing quit. By the way, if the account has no password (this is sometimes an installation default), you would just press Enter when prompted for the password. Perhaps you want to log in as root instead of slitt. This would probably be the case if you wanted to add a database or any other high responsibility action. Here's how you log in as root:
[slitt@mydesk slitt]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42 to server version: 4.0.18 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> quit Bye [slitt@mydesk slitt]$

The -u root tells mysql to log you in as root, and as previously discussed, the p tells mysql to query for a password.

In mysql, when you see a prompt like this:

mysql>

you are in the mysql program. For the rest of this section, most examples will begin and end in mysql.

DANGER WILL ROBINSON

There are more than one root account, and you must password protect all of them. Read on...

mysql> use mysql Database changed mysql> select host, user, password from user; +-----------------------+-------+------------------+ | host | user | password | +-----------------------+-------+------------------+ | localhost | root | 2aa0e9e91dbd7cf2 | | localhost.localdomain | root | 2aa0e9e91dbd7cf2 | | localhost | | | | localhost.localdomain | | | | localhost | slitt | 2aa0e9e91dbd7cf2 | +-----------------------+-------+------------------+ 5 rows in set (0.00 sec) mysql>

As you can see, there's a root account at localhost and another to localhost.localdomain. Both must be password protected. In reality, all accounts should be password protected.

Exploring Your MySQL Installation

From within mysql you can find quite a bit of information concerning your installation. As what user are you logged into mysql? What databases exist? What tables exist in a database? What is the structure of a table? What users exist? Because different operations require different priveleges, to save time we'll perform all these actions logged into mysql as root. Let's start with finding out your username within mysql:
mysql> select user(); +----------------+ | user() |

+----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql>

Now let's list all the users authorized to log into mysql:
mysql> use mysql Database changed mysql> select host, user, password from user; +-----------------------+-------+------------------+ | host | user | password | +-----------------------+-------+------------------+ | localhost | root | 2aa0e9e91dbd7cf2 | | localhost.localdomain | root | 2aa0e9e91dbd7cf2 | | localhost | | | | localhost.localdomain | | | | localhost | slitt | 2aa0e9e91dbd7cf2 | +-----------------------+-------+------------------+ 5 rows in set (0.00 sec) mysql>

Notice there are two root logons -- one at localhost and one at localhost.localdomain. They can have different passwords, or one of them can even have no password. Therefore, YOU'D BETTER check to make sure they're both password protected. DO NOT forget the password(s). If you lose all administrative passwords, you lose control of your mysql installation. It's not like Linux where you can just stick in a Knoppix CD, mount the root directory, and erase the root password in /etc/passwd.

User Maintenance
Before discussing user maintenance, it's necessary to understand what properties exist for each user. To do that, we'll use mysql -e option to execute SQL commands and push the output to stdout. Watch this:
[slitt@mydesk slitt]$ mysql -u root -p -e "use mysql;describe user;" | cut -f 1 Enter password: Field Host User password Select_priv Insert_priv Update_priv Delete_priv Create_priv

Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections [slitt@mydesk slitt]$

The word "Field" is a heading, not a piece of information about a user. After that, the first three fields are the user's host, username and password. The password is encoded. If you hadn't included the cut command, you'd have seen that the same username can exist in multiple hosts, even if both hosts refer to the same physical machine. That's why it's vital to MAKE SURE TO password protect ALL users. The next several fields after password are priveleges that can be granted to the user, or not. As will be discussed later, there's a way to grant ALL privileges to a user. From a security point of view this is very dangerous, as ordinary looking users can be turned into backdoors. I'd suggest always granting and revoking specific privileges. Here is a list of the privileges that MySQL users can have, and what those privileges allow them to do: USER FIELD
Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv

PRIVILEGE FUNCTION
Select Insert Update Delete Create Drop Ability to use the select command. In other words, ability to read. Ability to insert new data -- insert a new row. Ability to change existing data -- change contents of a row. Ability to delete rows of existing data. Ability to create a new table. Ability to drop a table.

Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv

Reload Shutdown Process File Grant References Index Alter Ability to create new indexes or drop indexes. Ability to change the structure of a table. Ability to grant and revoke privileges to others.

Ability to create temporary tables. Ability to lock tables.

The root user, or any user given sufficient privileges, can create new users with the grant command:
mysql> grant select on test2.* to myuid@localhost identified by 'mypassword'; Query OK, 0 rows affected (0.00 sec) mysql> select host, user, password from mysql.user; +-----------------------+-------+------------------+ | host | user | password | +-----------------------+-------+------------------+ | localhost | root | 7939f45f28d0aa41 | | localhost.localdomain | root | 2aa0e9e91dbd7cf2 | | localhost | | | | localhost.localdomain | | | | localhost | slitt | 2aa0e9e91dbd7cf2 | | localhost | myuid | 162eebfb6477e5d3 | +-----------------------+-------+------------------+ 6 rows in set (0.00 sec) mysql>

In the preceding, we grant one privilege, select, on every table in the test2 database (test2.*), to user myuid at host localhost (myuid@localhost), giving that user the password "mypassword". We then query tableuser in the mysql database (mysql.user) in order to see whether user myuid has been created. Indeed he has.

Granting select privilege is insufficient for any user using any app that modies data. Let's give myuid more privileges in the test2 database:
mysql> grant Insert, Update, Delete, Create on test2.* to myuid@localhost; Query OK, 0 rows affected (0.00 sec) mysql>

Now user myuid can not only select, but can insert rows, update rows, delete rows, and create tables and databases. Conspicuously missing is the ability to drop tables and databases -- that can be more dangerous, in the hands of a careless but not malicious user, than some of the other abilities. Privileges granted by the grant command are not kept in the mysql.user table, but instead in the mysql.db table. You can see results by issuing this command from the operating system:
[slitt@mydesk slitt] mysql -u root -p -e 'select * from mysql.db where host="localhost" and user="myuid";' > temp.txt Enter password: [slitt@mydesk slitt]

The results look like this:

Host Db User Select_priv Delete_priv Create_priv References_priv Index_priv localhost test2 myuid Y Insert_priv Drop_priv Alter_priv Y Y Update_priv Grant_priv Create_tmp_table_priv Y Y N

Lock_tables N N

You can revoke privileges like this:

mysql> revoke Delete, Create on test2.* from myuid@localhost; Query OK, 0 rows affected (0.00 sec) mysql>

If you redo the select on mysql.db, you'll see that those two privileges are now marked N. To actually delete a user, you use a SQL statement to delete him from the mysql.users table after revoking all his privileges. DANGER WILL ROBINSON

When deleting the user from mysql.user, if you forget the where clause, or any of its tests, especially the

column user, you will delete too many users -- possibly all users, in which case you'll have no way to opera database. BE VERY CAREFUL!

Now that you understand the potential landmines, here's how you delete a user:
mysql> revoke all on test2.* from myuid@localhost; Query OK, 0 rows affected (0.00 sec) mysql> delete from mysql.user where user='myuid' and host='localhost'; Query OK, 1 row affected (0.00 sec) mysql>

Exploring Your Databases

You can refer to a table in two ways: By name after connecting to its database with a use statement, and with the databasename.tablename syntax. The former is much more common in applications, but the latter is often used in database administration, especially when you must access the system database (mysql) in order to perform work on a different database. The first item for exploration is to find all databases:

mysql> show databases; +-------------------+ | Database | +-------------------+ | depot_development | | depot_production | | depot_test | | mysql | | test | | test2 | +-------------------+ 6 rows in set (0.00 sec) mysql>

In the preceding, you went into the mysql program, determined what databases existed. Now let's explore the test database:
mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | dogs | | people |

+----------------+ 2 rows in set (0.00 sec) mysql>

So the database test has two tables, dogs and people. Let's examine the columns in each of those tables:
mysql> show columns from dogs; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | owner | varchar(20) | | PRI | | | | name | varchar(20) | | PRI | | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> show columns from people; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | lname | varchar(20) | | PRI | | | | fname | varchar(20) | | | | | | mname | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>

Another way to get the same information is with the describe command:
mysql> describe dogs; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | owner | varchar(20) | | PRI | | | | name | varchar(20) | | PRI | | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> describe people; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | lname | varchar(20) | | PRI | | | | fname | varchar(20) | | | | | | mname | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>

Working with Tables

The following script creates and loads the table. Explanations appear to the right of the code:
#################################################### # CREATE THE DATABASE #################################################### #drop database if exists test2; #create database test2; #use test2; #################################################### # REINITIALIZE THE TABLES. ASSUME USER IS ALREADY # CONNECTED TO THE PROPER DATABASE FROM WITHIN # mysql OR psql. #################################################### drop table if exists members; drop table if exists families; #################################################### # CREATE THE TWO TABLES FORMING A 1 TO MANY RELATIONSHIP. # FAMILIES IS THE ONE, AND MEMBERS IS THE MANY. # CREATE UNIQUE INDEX SUCH THAT FAMILY_ID PLUS NAME IN # MEMBERS IS FORCED TO BE UNIQUE. #################################################### create table families ( id int not null auto_increment, name varchar(20) not null, primary key (id) ); show tables; create table members ( id int not auto_increment, family_id int not name varchar(16) not primary key (id), foreign key (family_id) references on delete restrict on update cascade ); null null, null, families Use this only if creating a new database, otherwise leave commented out

Drop the tables if they exist to make room for new tables of the same name

The families table is the 1 of 1 to many.

The members table is the many of the 1 to many. members.family_id matches families.id. Foreign key means you can't delete a family that still has members This index prevent two family members from having the same first name

create unique index familymember on members (family_id, name);

describe families; describe members; #################################################### # LOAD families WITH THREE ROWS

#################################################### insert into families (name) values ('Albertson'); insert into families (name) values ('Becker'); insert into families (name) values ('Cintez'); #################################################### # LOAD members WITH THREE ROWS FOR THE 'Albertson' # FAMILY. USE MONOLITHIC SQL STATEMENTS TO ACCOMPLISH # THIS. #################################################### insert into members (family_id, name) select families.id, 'Alvin' from families where families.name = 'Albertson'; insert into members (family_id, name) select families.id, 'Andrea' from families where families.name = 'Albertson'; insert into members (family_id, name) select families.id, 'Arthur' from families where families.name = 'Albertson'; #################################################### # LOAD members WITH THREE ROWS FOR Becker and Cintez # FAMILY. INSTEAD OF MONOLITHIC SQL STATEMENTS, # LOOK UP families.id FROM families.name, AND THEN # USE THAT id TO INSERT THE MEMBERS. # SETTING @id TO NULL PREVENTS USAGE OF PREVIOUS VALUES WHEN # THE SELECT''S WHERE CLAUSE FAILS #################################################### select @id:=null; select @id:=id from insert into members 'Betty'); insert into members 'Ben'); insert into members 'Bob'); select @id:=null; select @id:=id from insert into members 'Charles'); insert into members 'Christina'); insert into members 'Cindy'); families where name='Becker'; (family_id, name) values(@id, (family_id, name) values(@id, (family_id, name) values(@id,

Show the structures of the two tables just created Load the families table.

Monolithic insert from select Monolithic insert from select Monolithic insert from select

families where name='Cintez'; (family_id, name) values(@id, (family_id, name) values(@id, (family_id, name) values(@id,

The following inserts are performed more procedurally, by first finding families.id based on families.name, and then using that id as members.family_id. Prevent ghosts of selects past Find id from families.name Do the insert

#################################################### # SHOW EACH FAMILY AND ITS MEMBERS #################################################### select families.id, families.name, members.name from families, members where (members.family_id = families.id);

Prevent ghosts of selects past Find id from families.name Do the insert

Join the tables in the where clause, and find all family members

The preceding code exercised the creation of databases and tables, insertion of rows, and viewing a one to many relationship with a join created in a where clause. The preceding is pretty much DBMS independent, and runs on both MySQL and Postgres. However, certain legitimate SQL queries don't work on some versions of MySQL:

This doesn't work in MySQL 4.0.18

update members set name='Bobby' where family_id = (select distinct id from families where name='Becker') and members.name='Bob';

The preceding code works perfectly in Postgres, but fails with an error in MySQL 4.0.18. In MySQL this query must be done procedurally. Read on...

In earlier MySQL versions where subqueries don't always work as expected, you can accomplish the same thing more procedurally. Try the following script:
select families.id, families.name, members.name from families, members where families.id = members.family_id order by families.name; select @id := null; select @id := id from families where name = 'Becker'; update members set name = 'Bobby' where family_id = @id and members.name = 'Bob'; select families.id, families.name, members.name from families, members where families.id = members.family_id order by families.name;

The top and bottom queries show first and last names of everyone. The middle bunch of queries is somewhat procedural. First @id is set to null so that if we fail to get it from querying the families table, we don't accidentally get a record from the last successful match.

Then we query families for the id column of the row whose last name is 'Becker'. That is the ID we put in members.family_id.

MySQL Commands vs. Postgres Commands

TASK
Comments in SQL code Connect to database Find all databases Find all tables in current database Show structure of one table Change database Add user Grant privileges Change user password

MySQL
# as first printable on line use dbname; show databases; show tables; describe tblname; use dbname; grant select on dbname.* to username@userhost identified by 'userpassword'; grant privlist on dbname.* to username@userhost identified by 'userpassword'; grant select on dbname.* to username@userhost identified by "userpassword";

Postgres
Anything between /* and */ \c dbname \l \dt \d tblname \c dbname

create user username with en password='userpassword';

grant privlist on tbllist to slit

alter user username encrypte 'userpassword';