3K

What Is MySQL?

MySQL (pronounced “My Ess Cue El”) is one of the world’s most popular open-source relational database management systems (RDBMS).

It uses Structured Query Language (SQL) to:

• Create databases
• Store and retrieve data
• Modify records
• Manage users and permissions

MySQL is widely known for:

• Speed
• Reliability
• Scalability
• Flexibility

It powers millions of websites and applications worldwide.

Advantages of MySQL

Here are some key benefits:

1. Multithreaded Architecture

MySQL supports multiple concurrent connections without significantly impacting performance.

2. Broad Language Support

It integrates with many programming languages including:

• PHP
• Java
• C/C++
• Python
• Perl

3. Open Source & Cost Effective

MySQL Community Edition is free to download and use.

4. Cross-Platform Compatibility

Works on Linux, Windows, macOS, and Unix systems.

Installing MySQL on Linux

Using RPM (RedHat / CentOS)

Search for available packages:

yum search mysql-server

Install the package:

yum install mysql-server

Using DEB (Debian / Ubuntu)

Search:

apt-cache search mysql-server

Install:

apt-get install mysql-server

Installing from Source

Download the source file:

wget mysql.tar.gz

Extract:

tar zxvf mysql.tar.gz

Configure:

./configure

---

Starting and Stopping MySQL

The MySQL service can be controlled in multiple ways.

Using service command

service mysqld start
service mysqld stop

Using init.d

/etc/init.d/mysqld start

Using systemctl (modern systems)

systemctl start mysqld
systemctl stop mysqld

The MySQL server is started by the mysqld daemon, often through mysqld_safe.

MySQL Storage Engines

Storage engines define how data is stored and managed.

ISAM

• Non-transactional
• Stored in .frm, .ISD, .ISM files

MyISAM

• Improved version of ISAM
• Faster for read-heavy workloads
• Stored in .frm, .MYD, .MYI files

InnoDB

• Transaction-safe
• Supports commit & rollback
• Crash recovery capable
• Default engine in modern MySQL versions

For production systems, InnoDB is recommended.

Basic MySQL Commands

Login as root:

mysql -u root -p

Create Database

CREATE DATABASE sampledb;

Drop Database

DROP DATABASE sampledb;

Use Database

USE sampledb;

Create Table

CREATE TABLE president (
  last_name VARCHAR(15) NOT NULL,
  first_name VARCHAR(15) NOT NULL
);

Show Tables

SHOW TABLES;

Describe Table

DESCRIBE president;

---

MySQL User Permissions

Grant privileges:

GRANT ALL ON *.* TO 'db_user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

This assigns full privileges to a user.

Backing Up Databases Using mysqldump

Backup Database

mysqldump -u root -p mydatabase > backup.sql

Restore Backup

mysql mydatabase < backup.sql

Transfer Database to Remote Server

mysqldump mydatabase | mysql -h remote_ip newdatabase

---

MySQL Administration with mysqladmin

mysqladmin allows administrative operations.

Examples:

Create database:

mysqladmin create data

Show process list:

mysqladmin processlist

Check server status:

mysqladmin status

---

MySQL & PHP Integration

To enable MySQL support in PHP:

yum search php-mysql
yum install php-mysql

On WHM servers:

WHM → Apache Update → PHP Modules → Enable MySQL Module

Simple PHP connection example:

<?php
mysql_connect("localhost","admin","passwd") or die(mysql_error());
echo "Connected to MySQL";
?>

---

MySQL in cPanel & WHM

Every cPanel / WHM server includes MySQL by default.

Restart MySQL via WHM

WHM → Restart Services → MySQL

Or via shell:

/scripts/restartsrv_mysql

---

Optimizing MySQL (my.cnf)

Configuration file location:

/etc/my.cnf

Sample structure:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Key tuning parameters:

• innodb_buffer_pool_size
• max_connections
• query_cache_size
• table_open_cache

Proper tuning improves performance significantly.

---

MySQL Monitoring Tools

MTOP

Monitors queries and performance statistics.

MKILL

Kills long-running MySQL queries automatically.

FAQ

Is MySQL free?

Yes, MySQL Community Edition is open-source and free.

Which storage engine should I use?

In most cases, InnoDB is recommended for reliability and transaction support.

How do I restart MySQL safely?

Use systemctl restart mysqld or restart via WHM.

If you require help, contact SupportPRO Server Admin