Difference between revisions of "MySQL"

Revision as of 16:30, 1 May 2006

MySQL is a multithreaded, multi-user, SQL (Structured Query Language) Database Management System (DBMS).

Installing and Configuring MySQL

In this section, I will show how to install and configure MySQL for a LAMP-based system. This is not only the system I recommend (it is OSS) and I am highly biased towards, it is the only system I will discuss. If you use any other system, consulte the Web or the many books available for purchase.

Installing

The official MySQL website is http://www.mysql.com. They have many excellent resources to offer and this article is not intended to replace any of these. The goal of this article is to offer a simple step-by-step process of getting MySQL up-and-running on a LAMP-based system.

An RPM installation is the simplest and officially recommended way of installing (and upgrading) MySQL. I like to use the binary installation process because it offers a bit more control on a platform-specific optimized distribution. This article will only discuss the binary installation process.

STEP#1: Choose the closest (geographical) mirror to you from the following URL:

• http://www.mysql.com/downloads/mirrors.html

I use Linux (x86, glibc-2.2, "standard" is static, gcc) - Standard and, as of writing, it is around 30 MB. The "Standard" version should suit the needs of a standard user.

STEP#2: Terminal commands

Execute each of the following commands as the superuser ('root') of your system:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf -
shell> ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql &

Installation is complete.

Configuring

Set the MySQL Administrator Password

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('secret');
mysql> FLUSH PRIVILEGES;

Adding New User Accounts to MySQL

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sandusr'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

For a more specific creation and privilege control, use any of the following:

shell> mysql -u root -p mysql
mysql> INSERT INTO user
    ->     VALUES('localhost','monty',PASSWORD('some_pass'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
    ->     VALUES('%','monty',PASSWORD('some_pass'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
    ->     Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
    ->     VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

Datadir

I like to keep all of my user-generated data on a separate disk partition (usually /home). (This assumption will be made for the remainder of this article.) I like to treat mysql as any other user and give it a space under /home/mysql. This will be true for my database files.

shell> mkdir /home/mysql/data
shell> mv /usr/local/mysql/data/* /home/mysql/data

Then MySQL will have to be passed the location of the database files every time you start MySQL:

shell> ./bin/mysqld_safe --datadir=/home/mysql/data --user=mysql &

Starting and Stopping MySQL

• Starting

shell> cd INSTALL-DIR
shell> ./bin/mysqld_safe --datadir=/home/mysql/data --user=mysql &

• Stopping

shell> cd INSTALL-DIR
shell> ./bin/mysqladmin -u root -p shutdown

External data input

Example:

LOAD [LOCAL] DATA INFILE 'foo.txt'
INTO TABLE company
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

where [LOCAL] is optional and the following must be met:

• If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full pathname to specify its exact location. If given as a relative pathname, the name is interpreted relative to the directory in which the client program was started.
• If LOCAL is not specified, the file must be located on the server host and is read directly by the server.

External links

• MySQL Reference Manual - "LOAD DATA INFILE" entry
• MySQL — Offical website
• Wikipedia article on MySQL