MySQL Tutorial (Part 1)
Table of Contents
Introduction
Welcome to the world of MySQL. To properly introduce this topic, let’s make sure its something you need and want.
First, do you need a database? That depends on a number of factors: how much data you have to store, what is the nature of the data you have and how do you want to use the data you have?
If you have a phone list of 20-50 people, that is purely text based, all the information about each entry is compact and fits with only that entry and using the data is merely pulling out a name and phone number or address, then you probably would be better served by using a flat-file scheme.
Now if you have a 200 CD collection with multiple CDs by the same artist, you have cover art to store and you want to make it all Web accessible then MySQL is the answer to your prayers. Author’s Note: a sickeningly large DVD collection can be tamed as well…
Note that these are only samples and you may have a mailing list of 500 people that is storing some changing data (like who has responded) and so many other things are what make databases so very useful.
Now that we’ve determined you need a database just to make life pleasant and to impress your friends and colleagues, you have some choices to make. Do you go with Micro$oft products like Access or the big powerhouse that is Oracle?
Well there’s a better choice and best all, the price is right: FREE.
Installation
We’ve chosen MySQL because it’s FREE, it’s powerful, supported on any platform worth thinking about (yes, even Intel platforms), and hey, did I mention it’s FREE?
Now all we have to do is decide whether we’re good with software or if we duck and cover when someone mentions compiling.
If you’re not too bad at compiling and installing software in a Unix-like environment, then you might want to go for the Source Code approach, configure it exactly to your needs and run with it. On the other hand, if you are learning MySQL and want to get proficient without losing your mind, you might want to install the Binary Distribution for your platform and use it for a while (forever?) until you find something more you need that only a compiled version you had a hand in configuring can do.
First, no matter which way you decide to go there are a few tasks you should complete first:
- In Server Admin, create a group for the database to operate under. Call it, oh I don’t know, mysql.
- In Server Admin, create a user for the database to operate as. Let’s call it, hmmm, mysql. Let’s also make sure that this user has as its primary (and only) group, mysql. Let’s also assure ourselves that the home directory for this user is the base directory of the install, which is usually /usr/local/mysql unless you have something else going on…in which case, adjust to your local standard.
-
NOTE: You will need to use the command line to set the mysql user’s home directory, since you don’t want /usr/local/ as a share point. To do so, launch Terminal and assume super-user rights. Type the following command:
-
niutil -createprop / /users/mysql home /usr/local/mysql
- You can also edit the setting using NetInfo Manager.
Binary Package
Ah, the easy way. Pride and joy of the “Lazy Man’s Guide To Computers Using A Unix-Like Operating System.”
Point your favorite web browser to http://www.mysql.com/ and click on the “Downloads” link on the left side of the screen. Might I suggest strongly that you choose the “Release” version of the software and not the “Alpha” or other links. Note that you also have the choice between MySQL and MySQL MAX. Max basically includes a lot of things you may not need, like support for InnoDB and BerkeleyDB. If you know what these are and you use them, go with MySQL MAX, otherwise, pick the standard MySQL package.
Now some of you may be saying bad things right now, foul un-repeatable words, about my heritage right after saying “but I’m not in a windowed environment to CLICK on ANYTHING!!” Have no fear! There are many mirror sites listed at http://www.mysql.com/downloads/mirrors.html and many of those are FTP or HTTP downloads so even the most faithful lynx or wget users may get this wonderful software for their own use.
Once you have the package, it’s in a .tar.gz state. Everyone has their own way of getting it out of this format, here’s mine:
- Un-gz it with gzip –d big_long_filename.tar.gz
- Move it to /usr/local if it’s not already there with mv big_long_filename.tar /usr/local/.
- Un-tar it with tar –xvf big_long_filename.tar
- Symlink the untarred package to mysql with ln –s big_long_filename mysql
Yes, you can be tricky and untar/ungzip all at once and ftp it to /usr/local/ in the first place, but you should only be root when you really need root’s privileges and not to do your normal daily things.
I highly suggest once you have it in place that you open up the README or the INSTALL_README file in the top directory of the distribution. It goes step-by-step through the install of the binary package and you can pick it right up from where we are.
Source Package
I’m going to be skimpy on the source installation instructions. They’re very clearly and concisely documented in the README and other files that comes with the source code. You can obtain the source package at the same place you obtained the binary package, http://www.mysql.com/.
One last thing. Before we bless this as ‘installed’, we should make sure it will start when the server boots. Nothing more annoying than forgetting to start a service and then go to use it and have it error out. In standard Unix, you would put it in the /etc/rc.d/rc#.d directory (where # is the runlevel(s) you want MySQL to be started within). You can copy the sample from mysql_home/support-files/mysql.server which is indeed the startup script which requires no changes at all if you put this in /usr/local/mysql to begin with. For Mac OS X Server, it’s slightly more complicated. You need to make a directory for starting the daemon in /Library/StartupItems called MySQL. Inside that directory, you need three files: MySQL which is the startup script (mysql_home/support-files/mysql.server) with some minor changes for printing to the screen, a Resources directory, which contains some localization directories that are blank (you can skip this one), and a file called StartupParameters.plist, which has the contents:
{
Description = “MySQL database server";
Provides = ("MySQL");
Requires = ("Resolver");
Preference = “Late";
Messages = {
start = “Starting MySQL";
stop = “Stopping MySQL";
};
}
So now it’s installed. You’ve started the database up with the command provided in the INSTALL_README (usually ./bin/safe_mysqld –user=mysql &).
The very first thing you must do, before anything else, is to change the root password for MySQL. It is blank by default. Can’t have that, it’s insecure and asking for trouble.
User Management and Security
Changing the root password, your first task, is rather easy. Here’s how:
mysqladmin –u root –p password ‘new_password’
Then hit Enter when it prompts you for the current password. Mysqladmin lives in mysql_home/bin/mysqladmin. You should add this to your path as root and as your normal user account.
(Author note: I like adding it to the system default path for users. Check the man page for your shell. That way, every user can have access if they know how and you’ve made them a MySQL login with access.)
Alright, so lets evaluate where we are and what’s left to do. We have gotten the package, configured the system, started the daemon and changed our password. Quite a lot in a relatively short period. Next we need to talk about adding and removing additional users, other than the root account which is provided, permissions for database control and use and then we can begin using this fantastic tool.
So much like any multi-user thing, you have to keep a list of your users and their passwords and permissions somewhere… MySQL stores it in, well, a database table.
Start the MySQL user interface command line as root (mysql –u root –p), type in the new password you selected when prompted and when you arrive at the mysql> prompt, enter this command exactly: use mysql;
What this does is selects the correct database to edit the system itself. Yes, a “database” is what we’re calling MySQL but really it’s a Database Manager as you can create any number of “databases” within MySQL, right up to the point where you run out of hard drive space.
Next we’re going to look at the user table and see how MySQL is storing information about its users. Enter this command exactly at the mysql> prompt: select * from user;
You should see four entries in this table. Here’s what I see:
mysql> select * from user; +-----------+---------+------------------+-------------+-------------+-------------+ -------------+-------------+-----------+-------------+---------------+------------- -+-----------+------------+-----------------+------------+------------+ | 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 | +-----------+---------+------------------+-------------+-------------+-------------+ -------------+-------------+-----------+-------------+---------------+------------- -+-----------+------------+-----------------+------------+------------+ | localhost | root | 5689074447b6c95d | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | darius | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | darius | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
You’ll have to excuse the ASCII graphics, but kudos for trying to make it look pretty. So how do you read this?
At the top, you’ll see a couple of lines that have +----+, just fluff there. Next comes the table header which contains the name of each column in this table and then a few more lines of fluff +----+. From the header we see the data stored is: 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 and Alter_priv. Ugh. Let’s start at the first and keep going. Host is pretty easy, just the host name from which the user can be coming. Localhost is ‘this machine’ and in this case, darius, is also named. Next comes User which is the user name. Then comes password which is a bunch of numbers and letters. Basically this is a hash of the password so just anyone can’t come along and read the password. You get passwords with the password function. You can imbed it right in an SQL query like: password(‘new_password_here’).
After the password comes the permission part and from here on it’s Y for yes or N for no if they have or don’t have the permission respectfully. We’ll cover them in detail later when we hit SQL but suffice it to say, root needs all, normal users need Select, Insert, Update and Delete and developers need those as well as Create and Drop. Grant means allowed to give permission to others to do up to the level that user can. Shutdown, Reload, Process, File and Index affect the running of MySQL itself and Alter allows changes to tables which might be something you want developers to have.
I know that was a lot to take at once. Take time to look over it.
Notice that there are two entries for root listed here and two for an “unnamed” user. That unnamed user is a test account and you can remove it if you like with delete from user where User=’’; at the mysql> prompt. You should get back: Query OK, 2 rows affected (0.00 sec) which tells you that your syntax was ok, that 2 rows were affected (deleted in this case) and that it took 0.00sec or close to that to execute.
A new select will show that now only two rows for root exist. You should set the password on the other root account to the same password you chose earlier. You can update that row with: update user set Password=password(‘new_password_here’) where User=’root’ and Host=’your_hostname_here’;
How do I make a new user? Well it’s not that bad so here’s the syntax then we’ll discuss it:
insert into User (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) values \ (‘localhost’, ‘New_User_Name’, password(‘new_password’), ‘Y’, ‘Y’, ‘Y’, ‘Y’, \ ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’);
where you substitute in the New_User_Name and new_password with your own values.
Note that I have given all permissions to this user which is not a good idea as we discussed earlier. Think about the role of this person and make the necessary Y to N changes as you see fit.
After you hit Enter and get back your 1 row affected message, press the up arrow key on your keyboard to bring back the last command, use the left arrow to move back to the localhost and then delete it and insert the hostname of your machine and hit enter again.
This will make the two entries you need and reduce the amount of typing. The history buffer is stored in a file in your home directory and is created automatically for you. It even remembers between sessions.
Don’t worry if this looks like Greek to you now. By the time we finish SQL, it will be second nature to you!
Did I mention how in-demand SQL knowledge is in the marketplace?
There is more granular security available and it involves using three tables in MySQL. I would suggest if you want more granular control of table or object access, that you consult a good book on MySQL and reference the security section. The O’Reilly Nutshell book on MySQL is an excellent reference and I will list it in the References section at the end of part two of this tutorial.
GUI and Other Tools
Wow! We have a database, it’s been tightened up a little security wise and we’ve found something out, if we hated the command line before, we hate it a lot more now with those extra-long SQL statements. Isn’t there anything graphical that can be done about that?
Well, yes. There are quite a few GUI tools for administration and SQL use available. You can find some of them on the http://www.mysql.com/ site. I’m not going to go into the use of graphical tools here because no two are alike and quite honestly, SQL is a standard language and the best way to learn it is to use it and not have a pretty front-end program write it for you.
This completes part I of the MySQL tutorial. Check back soon, part II is coming!