v1.0

Introduction

Greetings geeks and geekettes out there in cyberspace. It’s no surprise that the MySQL database has become so popular, what with the proliferation of blogs and websites powered by database back ends and such. Plus, with Sun Microsystems taking over MySQL and offering their world class support, MySQL’s future looks as bright as ever.

To that end, I have compiled some common things that I do when working with MySQL databases so I can share it with you. From the start where we are creating databases, to creating and altering tables and more. Best of all, I am approaching this from the command line. Everything I touch on here will be command line driven, either from a UNIX/Linux shell prompt, or the SQL prompt.

I have two reasons for doing this, one is that once you have a better understanding of the way all of this works from the command line, the GUI makes a lot more sense. Two, is because the command line is much faster and in my experience more reliable. It’s good to get comfortable with the command line, once you harness it’s power, you never want to go back to point and click.

Let us begin with some references, when I am listing specific examples of commands, you need to know where those commands are to be entered:

#] <- This means it is a command to be entered at the shell (bash, ksh, sh, etc).

SQL] <- This means it is a command to be entered at the MySQL SQL prompt.

Now, with that is out of the way, let’s start with some good stuff.

MySQL Basics

*Note: To run these commands, you have to have a MySQL account and proper permissions in order for it to work.

*Note: If your MySQL user account is the same as the account you are logged in with, you can skip the -u username. By specifying the -p, you will be prompted for your password. If your MySQL account has a blank password, you can skip the -p there also.

Getting to the SQL prompt

The first think you need to know is how to get to the SQL prompt in the first place. Simply type mysql and the database you want to work with (although that is optional) and give it the proper credentials. It should look like this:

#] mysql mydatabase -u username -p

When it prompts you for your password, put it in and go crazy.

Create a new database

It is really easy to create a new database from the command line by using the mysqladmin command. Check this out:

#] mysqladmin create -u username -p databasename

Here you see we invoke the mysqladmin command, with the operation create and the name of the database.

List existing databases

It’s mighty handy to be able to list the databases that have already been created, and it’s simple too. Try this:

#] mysqlshow -u username -p

There you go, you will get a list of all the databases on the server.

Delete an existing database

Have a database that you don’t need anymore? Or maybe you want to drop it start over? Either way, it is easy to do with our friend mysqladmin. Check it out:

#] mysqladmin drop -u username -p databasename

You will get prompted, asking if you are sure. If you say yes, that database is gone so be careful, because you can’t get it back once it is gone.

Backup or Dump the contents of a database

One way I like to backup my MySQL databases, is to use the mysqldump command to dump the structure and contents of the database to a flat text file in SQL format. This way, if I need to, I can recreate the database and import everything back in. The other good thing about doing it this way, is that your database backup is in plain text so it is easy to dig into your data and surgically extract one piece of it if you don’t need the whole thing.

– Dump a database

This is the easy one, just issue the mysqldump command followed by the database you want to dump, and it all comes out. Try this:

#] mysqldump -u username -p databasename

Now, what you will get with this is the contents and structure of your database flying past you on the screen. To get this to a text file, simply redirect the output to a specified filename:

#] mysqldump -u username -p databasename > /my/dir/mydatabase.sql

Whatever that path and filename is that you enter, whether relative or absolute, you will end up with a nice little SQL file and you can import back into your database. Guess what? It even works on Windows too! It also works for the rest of the mysqldump commands below, so keep that in mind even if I don’t mention it for every example. That might get old pretty quick.

– Dump just one table

This can be easily done with the mysqldump command by telling it not only the database, but also the table to dump. Then mysqldump will only dump that one table. The format looks like this:

#] mysqldump -u username -p databasename tablename

So, if you wanted to dump the table balls from the database sportsequip as root, it would look like this:

#] mysqldump -u root -p sportsequip balls

Naturally you can send it to a file like so:

#] mysqldump -u root -p sportsequip balls > balls.sql

– Dump using a query

Let’s say you have that list of all balls in the sports equipment database, but what you really want is all the information on soccer balls, not all balls in general. Yes, you can parse the text file with awk or sed or PERL or something similar, but that takes time to write and get right and all that. So, in that case the easiest thing would be to dump only the data you want by adding some SQL statements to the mysqldump command. The format looks like this:

#] mysqldump -u username -p databasename tablename --where "sql=whatever"

So, using the same example above, you could do something like this:

#] mysqldump -u root -p sportsequip balls --where "type=soccer"

That would only pull the rows from the balls table where the type descriptor equals soccer. In other words, it would only list the soccer balls.

Create a new table

OK, I normally don’t create tables at the SQL prompt directly. Not by typing the query in directly anyway. What I usually do is use a text editor and build the query that will be the structure of my database tables there. Once that is correct, I will import it into my database.

Let’s start at the beginning, with basic table structure:

CREATE TABLE mytable (
  `id` bigint(21) NOT NULL auto_increment,
  `firstname` varchar(36),
  `lastname` varchar(36),
  PRIMARY KEY (id)
);

What we have above is a small example of SQL code that creates a table. It starts off simple enough with CREATE TABLE and then the table name. Next we add rows that represent the columns in the table. Now, in this case, I start with an ID table to be an auto incremented unique id for each row of data. The format of these rows follows this: column name, data type(data size), any options (like NOT NULL and auto_increment), and a comma. The last line designates the primary key by the column name. As you can see I chose the field ‘id’ for my key which makes sense since it is setup to have all values unique. You can make any column your primary key, but the data in the rows must be unique.

If you are importing into a database and these tables already exist, you will get an error. So, it is convenient to add the following before the CREATE statement:

DROP TABLE IF EXISTS `mytable`;

This does just what it says, if that table already exists it will drop it and then re-create it. Make sure you understand though, this deletes the table and all of the data in it, so don’t use this unless you don’t need the data. I use this when I am in the early dev stages and am still working out the structure.

After the code for creating the table, you can also set other options if you want them to be different than whatever is the default. Here are some of the common ones:

• ENGINE=MyISAM
• DEFAULT CHARSET=utf8
• COLLATE=utf8_bin
• AUTO_INCREMENT=1

These should be pretty self explanatory, other than the ENGINE option. The engine option determines how the database stores data and whether or not it uses transaction logs (simply put that is). You can read some great info about ENGINE options here:

http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

All of these options will go in between the closing parenthesis “)” and the semicolon “;”. In the end, this same SQL code will look like this with all the goodies added:

DROP TABLE IF EXISTS `mytable`;
CREATE TABLE mytable (
  `id` bigint(21) NOT NULL auto_increment,
  `firstname` varchar(36),
  `lastname` varchar(36),
  PRIMARY KEY (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

There you go, you have a nice little table. Once you get used to the SQL code, you’ll see just how easy and fast it is to create and tweak your database tables versus the point and click, plus you have a built in backup this way if you need to re-create the database for some reason.

Importing a SQL file into your database

OK, you have your dandy little .sql file, what the heck do you do with it? Simple, you use the mysql command. You issue the command, name the database to import into, and name the .sql file like so:

#] mysql mydatabase -u username -p < mydb.sql

All the nifty goodness in your .sql file will get imported into your specified database, just as if you typed them all in by hand at the SQL prompt. If there are any errors, the import will abort and you will get some type of message.

Describe or view the structure of a table

Need to see what a table is made of? You can use the desc command, and it will show you the structure of the specified table.

SQL] desc mytable

It’s that easy.

Adding a column to an existing table

What if you have a nice neat little table created when all of a sudden you remember a column that you meant to have in there, but you forgot. What do you do now? Delete the table and re-create it? Nope, let’s alter that table and add your column! It will look something like this:

SQL] ALTER TABLE mytable ADD mynewcolumn VARCHAR(60);

There you go, we added the column mynewcolumn to our table called mytable and made the data type VARCHAR with a length of 60. Now one other neat thing is that by default like above, the new column will be added to the end of the other columns, it will now be the last one in line. Therefore you can add a couple options to place the new column where you want, like so:

SQL] ALTER TABLE mytable ADD mynewcolumn VARCHAR(60) AFTER anothercolumn;

Pretty self explanatory, this puts the new column after the one you just named.

SQL] ALTER TABLE mytable ADD mynewcolumn VARCHAR(60) FIRST;

Again, pretty obvious, this adds the new column first in line. Awesome!

Removing a column from an existing table

In much the same way we added a column up above, we can remove one just as easily. Take a look:

SQL] ALTER TABLE mytable DROP mynewcolumn;

There you have it.

Managing and Adding users

Now that you have your MySQL databases created, and have cool ways to back them up, it is inevitable that your users will want to actually connect to their database. Sarcasm aside, it’s pretty easy to add users via the SQL prompt. You have to understand one important thing though, user access controls go beyond just username and password. There is a third attribute, and that is the host that a user will be connecting from.

Let me break it down for you. You might have a user named john, and you set his password to corona. You also have to specify the address that he is allowed to connect from, you can almost think of this as part of the username. This is because john@localhost is different from john@myserver.com. So, if you create the account john with the password corona and the host localhost so hiw web application can talk to the database, he will not be able to connect from anything other than localhost. If he tries to log into the database from home for example, he will be denied. If you add the IP address or hostname of his computer at home, he would then be able to connect.

One other thing about the host part of creating users, you can use the percent sign ‘%’ as a wildcard. If you put that in for the host field instead of an IP address or hostname, that user would be able to connect from any host. I don’t recommend doing that, but there you have it if you need it.

Once you get past the authentication part, then you have to assign privileges, which means what commands that user can run against the database (SELECT, INSERT, UPDATE, etc).

Now, on to the command to create a user. I am going to show you a few things here, so hang on.

– Create a new user

This command will create a user named john with the password corona and allowed to connect from localhost. It will also grant all privileges to his database ‘johnsdb’, check it out:

SQL] GRANT ALL PRIVILEGES ON johnsdb.* TO john@localhost identified by 'corona';

Did that make sense? It works literally like it reads. Now, if you only want the user to be able to read data and nothing else, you can grant SELECT or maybe SELECT,INSERT instead of ALL PRIVILEGES so they could get data and add data but not change data.

But that was just a grant privileges statement you ask? You are right, it was. That will however, create the user you are granting to, or update an existing user. Nice, huh? You don’t have seperate steps to create, set a password, assign privies, etc.

Now, let me tip you to one more thing. As of version 5 (or thereabouts, I have read different), MySQL changed the algorithm that they use to has passwords for users. The result is that it is not compatible with anything that came before or uses resources prior to MySQL 4.1. If your clients connecting to the database are pre-4.1, they won’t be able to authenticate.

What do you do? Short of upgrading all of your clients, you can use a built in function to change the stored password back to a pre-4.1 format so your clients can connect. That function is called OLD_PASSWWORD() … real original, right?

Anyway, the error message that you will most likely see will look something like this:

“Client does not support authentication protocol requested by server; consider upgrading MySQL client”

You can fix it with this command, realizing that you have to put in the appropriate values. In this case, we will follow the example above with john’s database. Check it out:

SQL] SET PASSWORD FOR 'john'@'localhost' = OLD_PASSWORD('corona');

See? We gave it the username, the host and what we want the pre-4.1 password to be. It’s just that easy.

Listing users

Another handy thing is to list the users that have already been created. From the SQL prompt you would enter this command:

SQL] select user,host from mysql.user;

Now, some folk say to ‘select *’ when you do that, but I find you get some much stuff that you can’t make heads nor tails of all the output flying by, so I stick with what I want to know, which is the username and the host.

Summary

I hope you have gotten some good use out of this article, and that it gave you some helpful information. I plan on this being a living and growing document, so as I get more goodies to add to this I’ll come back and add to it. Thanks!