MySQL
MySQL is a multithreaded, multi-user, SQL (Structured Query Language) Database Management System (DBMS).
Contents
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.
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.
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").
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
