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 user’s 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 World’s 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.