MySQL

From Christoph's Personal Wiki
Revision as of 19:55, 9 March 2009 by Christoph (Talk | contribs)

Jump to: navigation, search

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

see: MySQL/scripts for examples.

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:

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;

#~OR~
mysql> GRANT ALL PRIVILEGES ON sandbox.* TO 'sandusr'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; 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   # Make sure to shutdown mysql first!

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 &

Or, 

/usr/local/mysql/bin/mysqld_safe \
     --user=mysql \
     --datadir=/home/mysql/data \
     --socket=/tmp/mysql.sock \
     --tmpdir=/tmp &
  • 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]>

[client]
user=foo
host=127.0.0.1 # or localhost
password=yourpassword

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

User control

Change user password

  • Login to MySQL server:
% mysql -u root -p
mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='stine';
mysql> flush privileges;
mysql> quit;

Regex and string functions

Database Administration Statements

Statements

An incomplete list of useful admin statments: 

SHOW STATUS;
SHOW FULL PROCESSLIST;
SHOW VARIABLES;
SHOW VARIABLES LIKE '%version%';
SHOW INNODB STATUS; 
SHOW OPEN TABLES;

Tools

  • Checking status, etc.
shell> mysqlshow
shell> mysqlshow -u root -p mysql
shell> mysqladmin version status proc
  • to watch incremental changes to the query cache:
mysqladmin extended -r -i 10 | grep Qcache

mysql_explain_log [options] < log_file 
mysqlshow [options] [db_name [tbl_name [col_name]]]
...

etc. (see: for more)

Third-party

  • mtop — monitors a MySQL server showing the queries which are taking the most amount of time to complete.
  • innotop — MySQL and InnoDB monitor.
  • mytop — a top clone for MySQL
  • mysqlreport — Perl script for making friendly reports of important MySQL status values.
  • mysqlsla — Perl script for analysing general, slow, and raw MySQL statement logs.

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.

How to identify slow MySQL queries

  • Edit your /etc/my.cnf file and add the following:
[mysqld]
log-slow-queries = /var/log/mysql_slow.log # filename can be anything you like
long_query_time = 5 # log any queries that take longer than 5 seconds
  • Create log file and set appropriate permissions:
touch /var/log/mysql_slow.log
chown mysql.root /var/log/mysql_slow.log
  • Restart MySQL:
service mysql restart

Now wait a few minutes and review the log file for long queries. Either optimise or remove these queries. Make sure to either remove or comment out the two lines from the /etc/my.cnf after optimising, as they will slow down your server.

Automatic MySQL backup script

The guys over at Dagon Design have written a nice little PHP script to automatically backup your MySQL databases.

You will need the following files:

You can then place this script in a crontab: 

crontab -e
30 2 * * * php /root/scripts/backup_dbs.php > /dev/null 2>&1

which will perform the backup every day at 02h30 ("2:30 am").

Note: The command mysqldump -A is useful to dumping _all_ of your tables at once. See mysqldump — A Database Backup Program for extensive documentation.

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

See also

External links

Retrieved from "http://wiki.christophchamp.com/index.php?title=MySQL&oldid=5288"