MIGRATION & INTEGRATION
Getting started with MySQL
Barry Railton
01.02.2007
Rating: -3.75- (out of 5)
Many companies have data scattered across several databases that are often maintained by proprietary software. For example, we might have a computer inventory in a specialized application that keeps track of assets,
an office phone list in a Word document, employee locations in an Excel spreadsheet, etc.
LAMP (Linux, Apache, MySQL, PHP) is a combination of free software that offers a convenient way to centralize and administer data. In this tip, we'll discuss the benefits and drawbacks of LAMP.
The key component of LAMP is the MySQL database. Everything else serves to interface to that database and either adds, deletes, modifies or queries that data.
A simple PHP form that administers the data might look something like the following:
[IMAGE]
One advantage to having such a system is that it's flexible, extensible
and we're not beholden to any one vendor should we wish to add a feature.
Suppose, for example, we want to email a certain group whenever a change
is made to the employee's office location field. This is relatively easy for a LAMP
programmer to implement. But if we use proprietary software, such a feature
may not exist or may need to be custom written at considerable cost.
Another advantage is that because MySQL is so ubiquitous,
it's much easier to pull information and use it in other applications. For example, an online phone list can be compiled directly into a new HTML page every time an employee's phone extension changes. We can also pull information into customized lists, such as all the employees who report to a particular supervisor or all the employees in a particular department. Or we could pull employee office location data into a Java application that generates floor plans. Our options here are essentially limited only by our needs and our imagination.
Further, we can control security by protecting certain information
(databases, or database tables, e
To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
tc) directly from PHP by simply requiring a password to access such information.
The disadvantage to such a system, given that we're not all LAMP programmers,
is that we must secure the services of a programmer capable of developing and maintaining such a system. Here we have to weigh the costs of deploying off-the-shelf propriety software with its limited capabilities against the costs of hiring a LAMP developer with unlimited flexibility.
We must also consider the longterm big picture. An off-the-shelf proprietary solution may adequately serve our immediate needs today, but if we expect to grow and expand, we must be wary of vendor lock-in and the costs of migrating from such a system to LAMP should we need to do so in the future.
MySQL tutorial for newbies
For those completely unfamiliar with MySQL, here's a brief tutorial on obtaining, installing and running MySQL on Linux:
The first thing to do is download a binary MySQL distribution for your platform from www.mysql.com
and save the tar file in /usr/local
For security, we'll want to run our MySQL server as a non root user. So
we'll need to add a mysql group and then add a new mysql user as part of that group. We can do this with the following commands:
[TABLE]
Now let's untar the binary. Replace X.X with the MySQL version you downloaded in the following:
[TABLE]
For convenience, let's make a soft link in /usr/local called mysql that points to the mysql directory we just untarred.
[TABLE]
Next we need to initialize the mysql database containing the grant tables that store the server access permissions. We do this with the mysql_install_db script.
[TABLE]
The --force option turns off DNS lookups. You don't need this option if the computer you're installing MySQL on has a DNS entry and can be resolved.
For security we assign ownership of everything except the data directory to the root user. The data directory should belong to the mysql user and group ownership of everything should be assigned to the mysql group. So in the /usr/local/mysql directory we do:
[TABLE]
Now we run the MySQL server with the following:
[TABLE]
Assign a password for the root user with the following:
[TABLE]
Replace your-password with a real password.
Now we connect to the server as user root using the password we assigned in the previous step.
[TABLE]
We should see the mysql> command prompt.
[TABLE]
Now we're ready to begin using mysql commands. To begin with, let's create a database.
[TABLE]
MySQL commands are generally not case sensitive. For clarity, we'll type the commands in uppercase and the variables in lowercase. We've just created a database called acme_tool_co. Let's make sure it's there with the following:
[TABLE]
You should see acme_tool_co listed in the output, like so:
[TABLE]
The other databases are the ones that were set up when we ran the mysql_install_db script above.
Now we need to create a table within the acmetool_co database. First we select the database for use with the use command like this:
[TABLE]
Then we create a table.
[TABLE]
Here we created a table called employee_info that contains the fields employee_name, phone_extension, and location. Note that varchar is a data type which is a variable length string, the number of characters assigned to that string is the number in parenthesis. There are many data types in MySQL including types for date and time, numeric operations and several for text. Here we'll just use varchar for simplicity.
So now we've created a database and a table containing some fields within that database. Now we're ready to add some data. Let's add two employees john and jane doe and give them phone extensions and office locations. We can do this using the insert into command like so:
[TABLE]
We can view the data we added by using the select and from commands like this:
[TABLE]
We should see the following:
[TABLE]
We can also use the select and from commands to refine the output. For example, suppose we only want employee names and extensions. In this case, we could use:
[TABLE]
Which will generate the following containing only the employee_name and phone_extension fields.
[TABLE]
Further we can use the order by command to sort the output. To sort alphabetically by employee name, we could use:
[TABLE]
Which will generate the following alpha sorted output.
[TABLE]
We can use the '=' comparison operator to generate output for a particular condition.
If we wanted to get information on the employee at location 223C, we could use:
[TABLE]
This will generate only the information we want.
[TABLE]
To delete an entry from the table we can use the delete command like this.
[TABLE]
Here we use from the employee_info table where our condition is that employee_name ='john doe'.
We can check the user 'john doe' has been deleted using an earlier command like this.
[TABLE]
To exit the MySQL command prompt, we use the QUIT command.
[TABLE]
This will take us back to a regular shell prompt.
Stopping the server:
Finally, we can stop the MySQL server by changing to the mysql directory and using the mysql.server command in the support-files directory by using the following commands:
[TABLE]Barry Railton has worked as a Linux and Unix systems
administrator since the early 1990s.
|
|
|
|
|
|
