2004.09_Mysql-Learn How to Administer Your Own Professional Database.pdf

KNOW HOW

MySQL Database Management

Nicely Set Up

Weblogs, wikis, image galleries –

most Web-based applications need

a database. A few basic tricks are all

you need to handle your own admin-

istration work. You do not need to be

a highly skilled administrator to

make the most of databases. With

MySQL , anyone can learn simple

tricks at home.

BY OLIVER FROMMEL

like professional IT, and the

MySQL [1] database system is

used by many a large company. Never

fear though, even though advanced

applications assume high skill levels,

MySQL administration can be quite a

simple task. This article looks at the

basic MySQL administration steps

required by many Web-based applica-

tions that use databases. We will be

taking just a brief look at the installation

procedure, as most distributions have

MySQL pre-installed (see the “Installa-

tion” box to the right).

There are a few GUI-based MySQL

administration tools, but of course the

command line programs included with

MySQL, the MySQL Monitor or mysql

client, which we will be using through-

out this article, are perfectly okay. You

need the same skills to use the client as

most GUI-based tools, but at least you do

not need to become accustomed to the

menu and window structure. Once you

have learnt how to handle the MySQL

monitor, you should have no trouble

whatsoever with the GUI-based pro-

grams. KMySQLAdmin [2], MySQL Navi-

gator, and mysqlcc by the database

manufacturer itself, which is useful for

the current version of the database, ver-

sion 4, are just some examples of

GUI-based management programs..

allows the user to specify a different

name; -p prompts for a password, and

can optionally be followed by the name

of the required database. To access the

mysql as the administrator, after supply-

ing a password, enter the following

command:

Passwords

When called without any parameters,

the mysql client uses the current Linux

username without a password to connect

to the MySQL server. The -u parameter

mysql -u root -p mysql

The Monitor is quite easy to use for a

command line program. It has a history

Box 1: Installation

MySQL is easy to install, as practically any

distro you can think of includes the data-

base. If your distro is one of the rare

exceptions, use your distribution’s package

manager to search for the MySQL packages.

It makes sense to install all the packages,

including the developer packages (which

typically have “devel” or “dev” in their

names), as many scripting languages and

programs need these packages if you com-

pile them from source code.

For example, Fedora 2 includes the following

packages: mysql-3.23.58-9.i386.rpm (MySQL

library, MySQL monitor and other tools),

mysql-bench-3.23.58-9.i386.rpm (scripts and

benchmarking data used for performance

tests), mysql-devel-3.23.58-9.i386.rpm

(developer packages), mysql-jdbc-3.0.8-

2.noarch.rpm (database interface for Java),

mysql-jdbc-tomcat-3.0.8-2.noarch.rpm

(adapter for the Tomcat Java server), mysql-

server-3.23.58-9.i386.rpm (the MySQL server

itself).

Suse has a similar collection, although the

monitor is in the mysql-client package and

the library in mysql-shared . The most impor-

tant file is /etc/my.cnf , which stores the

MySQL server configuration.

September 2004

www.linux-magazine.com

MySQL Database Management

D atabase administration sounds

MySQL Database Management

KNOW HOW

(users can press the up arrow to scroll

back). Entries at the mysql> prompt are

completed by typing a semicolon. If you

press the Enter key before completing

your entries, the program will output ->

on the console to show that it needs

more input. Typing \q will quit the Moni-

tor program.

To delete a data-

base, use drop

instead of create .

To use a specific

database, type use

Database in the

Monitor program.

MySQL will use

the selected data-

base for any tables

that you then cre-

ate or modify. If

you want to edit

the MySQL system

tables (for exam-

ple, to modify user

permissions as described later), first

select MySQL’s database: use mysql; .

Failing to do prompts MySQL to output

an error: ERROR 1046: No Database

Selected . You can then enter show tables;

to list the tables in the selected database

ready for you to change and manipulate.

Creating a Database

Strictly speaking, MySQL is not a “data-

base” but a database management

system (DBMS). In other words, the pro-

gram can manage multiple databases

(groups of tables) at the same time. For

example, users would typically create

two different databases to manage their

CD collection and their address lists, but

the same MySQL server would manage

them.

Typing show databases; tells the client

to display a list of existing databases.

To create a new database using

mysqladmin , the standard program

for MySQL administration, type the

following:

Figure 2: The new GUI-based management program, mysqlcc ,by the soft-

ware manufacturer.

commands, creating or deleting tables. If

the user does not exist when these per-

missions are assigned, MySQL will

simply create that user.

mysql> GRANT ALL ON pic_db.* TO U

pic_user@localhost IDENTIFIED U

BY 'secret';

mysql> flush privileges;

Managing Users

The database admin does not need to be

the Linux root user. This said, most dis-

tributions set the database user root as

the default administrator with top level

access. The mysql_install_db script fills

the system table fields if the database

happens to be empty when you launch

the program. It is a good idea for both

practice and security to set the admin

password straight away:

mysqladmin create pic_db

As already mentioned, the first com-

mand assigns all privileges for a

database called pic_db to the user

pic_user , who will need to enter the

password secret in future. The @local-

host section specifies that the user is

only allowed to talk to the server on the

local machine. The following flush privi-

leges makes the changes known to the

MySQL process.

To make MySQL prompt you for a pass-

word, use the same option as for the

client, that is -p , for example mysqlad-

min -p create pic_db . If you are already

working with the MySQL Monitor, you

do not need to quit the program to create

a database, as the process is just as easy

with the Monitor tool. Type the follow-

ing:

mysqladmin -u root U

password 'secret'

Remote Access

To permit remote access, you would

need to enter the hostname or IP address

at this point. Security is beyond the

scope of this article, but you should put

some thought into how you will be using

the database.

For example, if you want to allow other

machines to access your server, you will

want to hide it behind a firewall, or use

some other method of hardening the

server. The safest approach is to permit

local access only – this may be restric-

tive, but it is fine for most

Web-applications as they are typically

hosted on the same machine.

Tw o programs included in the MySQL

package can be a big help: mysql_setper-

mission for permissions management,

and mysql_access for a neat overview of

current permissions.

mysql> create database pic_db;

To separate databases, you might like to

create a user for each one, using the

same name for the

user and the data-

base. Many

Web-applications

need to know the

database and user

names, and the

password, before

they get to work.

It makes sense to

assign the data-

base user any

required permis-

sions. For

example, the user

should be capable

of running SQL

Figure 1: mysql a text-based command line program has a neat look and feel.

www.linux-magazine.com September 2004

KNOW HOW

MySQL Database Management

ple fields of infor-

mation

about

three characters. DATE is a date format

which MySQL outputs as ‘YYYY-MM-

DD’, that is 2004-08-31 for August 31st

2004. MySQL has a big selection of data

types, all of which are described in the

documentation at [3].

images.

We can create

this new database

(called pic_db)

and add a table

(pic_info) with

just a few easy

commands. The

first table to hold

data can be gener-

ated by typing the

following com-

mands:

Figure 3: The mysql client showing the table structure for pic_info in the

pic_db database.

GUI Tools

GUI-based tools definitely make table

management easier, as users do not need

to worry about commas and brackets.

MySQL Monitor has an alternative entry

mode; typing edit launches an editor so

that you can enter your data right up to

the final semicolon. Use the EDITOR envi-

ronment variable to specify the editor.

MySQL Monitor uses the describe com-

mand to output the table structure (see

Figure 3):

Creating Tables

Creating a table is very similar to creat-

ing databases or adding users, however,

in this case you need to first describe the

table structure. Each table comprises

several fields where the database data

will be stored at a later date. Each data

field is associated with a data type.

Data types describe and specify the

kind of data the fields can store: for

example, text, single characters, decimal

numbers, integers and so on.

Web-applications often create the

tables independently, so you usually

only need to create the database and

user, and specify a password for access.

Let’s first look at a simple example,

using a new database to hold some sim-

mysql> CREATE TABLE

-> pic_info (

-> file VARCHAR(20),

-> format CHAR(3),

-> date DATE);

mysql> describe pic_info;

VA RCHAR represents strings (letters,

numbers, ..) of variable length, although

this example restricts the length to 20

characters. CHAR(3) expects exactly

Te xt files facilitate inputting multiple line

table definitions. The MySQL client has

several approaches to handling these

files. If the client is running, you can use

Listing 1: Speaking SQL

01 mysql> INSERT INTO pic_info (file, format) VALUES ('heaven.jpg',

'JPG');

02 Query OK, 1 row affected (0.00 sec)

04 mysql> INSERT INTO pic_info (file, format) VALUES ('earth.gif',

'GIF');

05 Query OK, 1 row affected (0.00 sec)

07 mysql> select * from pic_info;

08 +----+------------+--------+-------+

10 +----+------------+--------+-------+

11 |1|heaven.jpg | JPG | NULL |

12 |2|earth.gif | GIF | NULL |

13 +----+------------+--------+-------+

14 2rows in set (0.00 sec)

16 mysql> SELECT * FROM pic_info WHERE format = 'JPG';

17 +----+------------+--------+-------+

19 +----+------------+--------+-------+

20 |1|heaven.jpg | JPG | NULL |

21 +----+------------+--------+-------+

22 1row in set (0.03 sec)

24 mysql> UPDATE pic_info SET date = '2004-07-22' WHERE file =

'heaven.jpg';

Table 1: Important tools

Tool Description

mysql Client program or MySQL

Monitor

mysqladmin Management tool, e.g. for

creating databases, changing

passwords etc.

mysql_setpermission Permission management

mysqlaccess

Displays permissions

mysqlcheck

Checks, optimizes, and repairs

tables

mysqldump

Outputs the structure and

content of a database as a text

file

mysqlhotcopy

This tool creates a copy of a

database while the MySQL

server is running,

mysqlimport

Imports structured files, e.g.

comma-separated values

myisamcheck

Checks and repairs tables

mysqlshow

Displays the structure of a

database

mysql_config

Displays the location of header

files, libraries , MySQL versions

and ports

26 mysql> DELETE FROM pic_info WHERE id = 1;

September 2004

www.linux-magazine.com

KNOW HOW

MySQL Database Management

the source filename command ( \. for

short) to tell it to parse a file. In the

Linux command line, you can redirect

standard input to tell the client to read

from a file. For example we could use the

following:

Box 3: License Trouble

familiar with the basics. The basic SQL

commands are SELECT, INSERT, UPDATE

and DELETE . Listing 1 shows how you

can apply them to the table discussed

earlier.

The INSERT command inserts a line

into the table. It is not normally neces-

sary to specify all the fields, but you will

need the primary key. In this case,

MySQL takes care of the primary key, as

it is an AUTO_INCREMENT field. The

SELECT command uses a wildcard (*) to

list all fields of a row. WHERE applies a

restriction. In this case, SELECT will list

only those lines that have JPG in the for-

mat field.

UPDATE changes individual fields in

any existing lines, and respects the

WHERE condition just like SELECT . In a

similar way, the DELETE command

deletes complete lines – in this case

the line with the ID number 1, which

was assigned by MySQL’s AUTO_

INCREMENT function.

The sky is the limit for tinkering with

databases, and the books on this subject

could easily, and frequently do, fill

whole libraries. Fortunately, getting

started is as easy as pie. You don’t need a

degree in computer science to manage a

database.

MySQL and the tools that accompany

the program are well-organized and easy

to use. Running pre-programmed Web-

applications should not pose too many

riddles. From there, it is just a small step

to setting up you own database applica-

tion. Check out the Linux Magazine

website at [6] for more details on making

your databases safe by backing up.

Fedora is one of only a few distributions

not to include the current MySQL Version 4.

The reason for this is that MySQL changed

the free license en route from version 3 to

version 4. Instead of using the more liberal

LGPL, the database is now under the GPL

(GNU Public License) [4]. The GPL is more

restrictive as it does not allow programs

with other license types to use parts of

MySQL, without becoming free software in

the GPL definition of that term. The LGPL

permitted this. This is an attempt by MySQL

to force commercial users to use the com-

mercial database license, rather than the

free license, and thus to generate funds.

For Fedora, the move to the GPL is reason

enough to stick to version 3 for the time

being, as the license would clash with the

PHP package license [5].

mysql < sqldata.txt

This approach allows you to pass files

written by the mysqldump program to

MySQL.

Normally, each table has a so-called

primary key, a field that cannot be

empty, as it provides access to each line

of the table. This field is typically a serial

number that cannot be manipulated, in

contrast to any other field in the table.

MySQL uses the AUTO_INCREMENT

attribute to automatically enter a num-

ber into each line, incrementing the

number each time it does so. If we add

these two elements, our example now

looks like this:

can simply include the ID numbers for a

photographer’s images. However,

designing a database model is a non-triv-

ial task, and beyond the scope of this

article.

mysql> CREATE TABLE

-> pic_info (

-> id INT AUTO_INCREMENT,

-> file VARCHAR(20),

-> format CHAR(3),

-> date DATE,

-> PRIMARY KEY (id));

Queries

Structured Query Language (SQL) is a

standardized language for writing data to

tables, manipulating and reading data,

and of course MySQL supports this lan-

guage. Although programming languages

or GUI-based programs often remove the

need to speak SQL, it makes sense to be

A database only really starts to make

sense when you link multiple tables to

provide efficient data storage. In the case

of our example, this would mean creat-

ing tables to store photographers’ details,

the image content, or even the images

themselves. Instead of storing the pho-

tographers’ details in the table we were

just looking at, the photographers table

Box 4: Security at Suse

Suse has been adding the so-called TCP

wrapper tool tcpd to MySQL for quite a

while now. This allows users to specify the IP

addresses or hostnames allowed to connect

to the database. Hosts with access permis-

sions need to be added to /etc/hosts.allow ,

whereas /etc/hosts.deny has a list of hosts

without permissions. To allow a machine

with an IP address of 192.168.1.1 to connect,

you would add the following line to

/etc/hosts.allow :

mysqld: 192.168.1.1

The next step would be to add an entry to

/etc/hosts.deny to deny access to any other

address:

mysqld: ALL

Typing man 5 hosts_access pops up the

documentation. Additionally, MySQL needs

to permit access from other hosts, using

the GRANT command, as described in the

“Managing Users” section.

■

INFO

Box 2: Forgotten Your

Password?

[1] MySQL homepage:

http://www.mysql.com/

[2] KMySQLAdmin: http://www.

alwins-world.de/programs/kmysqladmin/

[3] MySQL data types: http://dev.mysql.com/

doc/mysql/en/Column_types.html

[4] GNU Licenses: http://www.gnu.org/

copyleft/copyleft.html

[5] Fedora licensing issue:

http://www.internetnews.com/dev-news/

article.php/3358061

[6] Backing up MySQL databases,

Thomas Wolfer, Linux Magazine, Issue 44,

July 2004, p58

If you forget the database administrator’s

(typically root ) password, you will be unable

to use the admin account to connect to the

database. Fortunately, you can launch

MySQL in a mode where the program

ignores the the grant tables (which contain

the permissions). Thus, you can launch

MySQL in this mode, and change the data-

base password for root :

safe_mysqld --skip-grant-tables

mysqladmin -u root flush- U

privileges password " newpassword "

September 2004

www.linux-magazine.com

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: