MySQL

* MySQL (pronounced my ess cue el) is an open source database management system.

* It uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database.

* MySQL is noted mainly for its speed, reliability,and flexibility.

Advantages of MySQL

* It is multithreaded allowing multiple connections at the same time without slowing down the system.
* You can use MySQL with many different application-programming interfaces. These range from Perl to C/C++ to Java.
* The feature that makes MySQL so very popular is,it costs you nothing to download.

Installing MySQL

* Using .rpm – RPM Package Manager

yum search mysql-server lists the compatible packages.
yum install pkg-name installs the package.

* Using .deb – a medium-level package manager for Debian

apt-cache search mysql-server
apt-get install pkg-name

* Using Source

Download the required source (.tar.gz) file using wget
Extract the tar file using tar zxvf mysql.tar.gz
Run ./configure

Start/Stop MySQL Server

mysql.server script is used to start/stop the service

After Installation the mysql.server script will be installed in the /etc/init.d directory with the name mysql or mysqld depending on the downloaded package.

The mysqld daemon starts the server by invoking mysqld_safe binary (/usr/bin/mysqld_safe)

Finally a chkconfig command helps to start the service at system startup :
chkconfig level 35 mysql/mysqld on : starts mysql service in runlevels 3 & 5

service mysqld start/stop , /etc/init.d/mysqld start/stop , /scripts/restartsrv_mysql are various other means by which we can start/stop the service manually.

Storage Engines

* ISAM – managed non-transactional tables. Each ISAM table is stored on disk in three files. A .frm table format, .ISD data file & .ISM extension index files

* MyISAM same as ISAM, but is more flexible in its properties when compared to the other. Each table is stored on disk in three files. A .frm table format, .MYD data file & .MYI extension index files

* InnoDB – provides MySQL with a transaction-safe storage engine that has commit, rollback, and crash recovery capabilities. InnoDB has been designed for maximum performance when processing large data volumes

Basic MySQL commands

shell> mysql u root p

Creating a database
mysql> CREATE database 134a;

Deleting a database
mysql> DROP database 134a;

Change the Current Database
mysql> USE 134a;

Creating a table
mysql> CREATE TABLE president ( last_name varchar(15) not null, first_name varchar(15) not null );

To see what tables are present
mysql> SHOW tables;

To view the structure of a table
mysql> DESCRIBE president;

Refer : http://www.pantz.org/database/mysql/mysqlcommands.shtml for more commands

MySQL Permissions & Grant Tables

In order to add a new user or update users privileges in mysql grant tables login to mysql as a root user

GRANT priv_type ON {db_name.tb_name} TO user [IDENTIFIED BY [PASSWORD] ] ;

Ex: Grant ALL on *.* to db_user identified by db_passwd
Grants all privileges for the user db_user to all databases

Execute Flush privileges to reset all the privileges

Backing Up Databases With MySQLDump

The mysqldump utility allows us to backup a database to an external resource such as a file, or even a completely different MySQL server running on the other side of the world!
Syntax :
shell>mysqldump user [user name] password=[password] [database name] > [dump file]

Backing up of database from one MySQL server to another with
just one command :
shell>mysqldump opt mydatabase | mysql host=remote_ip newdatabase
You must have already created the database on the remote server ( create database newdatabase; )

Restoring a dump file :
shell>mysql mydatabase < [dump file]

MySQLAdmin

mysqladmin is a client for performing administrative operations

shell> mysqladmin [options] command [command-arg]

Examples :
create db_name (ex : mysqladmin create data;)
password new-password
processlist : Show a list of active server threads
Status : Display a short server status message

Refer : http://dev.mysql.com/doc/refman/5.0/en/mysqladmin.html for more commands

MySQL & PHP

Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database 11 MySQL & PHP

To allow php to use MySQL this has to be configured first :
shell> yum search php-mysql

yum install pkg-name
Whm servers : whm -> Apache Update -> Php Module -> Mysql Module (check & Start Build)

A simple PHP & MySQL Code :
<?php mysql_connect(localhost, admin, passwd) or die (mysql_error()); echo Connected to MySQL<br />; ?>
Gives : Connected to MySQL result if everything is working fine

MySQL & WHM

Every cpanel/whm server by default comes with MySQL (a database server) set up.

[Up/Down]Grading MySQL
Whm -> Tweak Settings -> MySQL -> Check the version and Save the settings
To downgrade, the required version has to be specified in / var/cpanel/cpanel.config file
Now run /scripts/upcp force to fix up errors (if any)

Restart Service :
Whm : whm -> Restart Services -> MySQL
shell> /scripts/restartsrv_mysql

Databases through CPanel

Login to your cpanel at http://domain/cpanel

From the main page, click MySQL databases

Enter a name for the database and click Add Db

Enter a username and password for the database and click Add User

Select the appropriate user and their privileges for the database

Click Add User to DB

Database is now created with a user assigned to it

Tuning/Optimizing my.cnf file for MySQL

Mysql provides a configuration file located in /etc/my.cnf. From here you can set all of the memory, table, and connection limits as well as a host of other options

Below is a sample my.cnf file :
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

MySQL Monitoring Tools

MTOP (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. In addition, server performance statistics, configuration information, and tuning tips are provided
Download it from : http://mtop.sourceforge.net/

MKILL (MySQL kill) monitors a MySQL server for long running queries and kills them after a specified time interval.

Refer : http://www.linuxweblog.com/node/231/ for more info

Article Authored by Jeeva Abraham

Author, Jeeva, is a Systems Engineer with SupportPRO. Jeeva specializes in Cpanel and Linux servers. SupportPRO offers 24X7 technical support services to Web hosting companies and service providers.

If you require help, contact SupportPRO Server Admin