Difference between revisions of "MySQL"

Revision as of 21:58, 23 September 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

The MySQL prompt

It is possible to fully configure the MySQL prompt. These settings can be automatically loaded each time you start MySQL by placing them in the MySQL config file (ie, my.cnf, usually located in the /etc directory). You can also have your own personal config file by creating a ~/.my.cnf in your home directory.

Below is an example of what you can place in your .my.cnf file:

[mysql]
prompt=(\u@\h) [\d]>

which will display the current user ("\u"), current host ("\h"), and current database ("\d") at the MySQL prompt.

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.

SuSE Linux default configuration

Note: SuSE's default install is a major pain!

• Binaries: /usr/bin
• Datadir: /var/lib/mysql/
• Config file: /etc/my.cnf (note: user config file is ~/.my.cnf)
• Start/Stop MySQL: /etc/init.d/mysql {start|stop|status|reload|restart|try-restart|force-reload}
• Check if MySQL is running: netstat -ln | grep mysql

External links

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