MySQL Partitioning [Quick Overview]

This Blog Post is an overview of MySQL Partitioning and its operations, Will explore following points for MySQL Partitioning,

About Partitioning:

  • It’s a plugin named as “Partition”
  • MySQL Partitioning has inbuilt support for binary install setup. If you are using MySQL source then build must be configured with the -DWITH_PARTITION_STORAGE_ENGINE
  • To disable partitioning start server with –skip-partition [ This will not allow any access/manipulation to partition table data.]
  • For MySQL commercial edition Partitioning supported by MySQL Enterprise and MySQL Cluster edition, Partitioning not supported by MySQL Standard edition.
  • MySQL Community binaries provided by Oracle include partitioning support.
  • No Special configuration needed in my.cnf file.
  • MySQL partitioning not supported for MERGE, CSV, or FEDERATEDstorage engines.
  • Metadata information about partition tables in INFORMATION_SCHEMA.PARTITIONS
  • Data and Index together will be part of Partitioning for InnoDB Storage engine.
  • For MyISAM tables, data and indexes can be stored across different disk/location.
SHOW PLUGINS;

SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME='partition';

Partition Types:

RANGE Partitioning:  Partitioning based on Range values in column . For example, partitioning data for each year.

Example:

CREATE TABLE pt_range (
inc_id BIGINT,
inc_tital VARCHAR(250) NOT NULL,
inc_desc TEXT,
owner VARCHAR(250),
created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE=INNODB
PARTITION BY RANGE( YEAR(created_date) ) (
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p1 VALUES LESS THAN (2003),
PARTITION p2 VALUES LESS THAN (2006),
PARTITION p3 VALUES LESS THAN (2009),
PARTITION p4 VALUES LESS THAN (2012),
PARTITION p5 VALUES LESS THAN (2015),
PARTITION p6 VALUES LESS THAN (2018),
PARTITION p7 VALUES LESS THAN MAXVALUE
);

NOTE:
If you are using primary key in partition table, then partitioning column must be a part of primary key otherwise table creation will fail with the following error,
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function

For Date and Time type columns only DATE and DATETIME datatypes are supported for RANGE Partitioning. For example use of TIMESTAMP data type in partitioning column will throw the following error,
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed.

With Primary key:

CREATE TABLE pt_range (
inc_id bigint NOT NULL AUTO_INCREMENT,
inc_tital VARCHAR(250) NOT NULL,
inc_desc TEXT,
owner VARCHAR(250),
created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (inc_id,created_date)
)ENGINE=INNODB
PARTITION BY RANGE( YEAR(created_date) ) (
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p1 VALUES LESS THAN (2003),
PARTITION p2 VALUES LESS THAN (2006),
PARTITION p3 VALUES LESS THAN (2009),
PARTITION p4 VALUES LESS THAN (2012),
PARTITION p5 VALUES LESS THAN (2015),
PARTITION p6 VALUES LESS THAN (2018),
PARTITION p7 VALUES LESS THAN MAXVALUE
);

 

LIST Partitioning:  Partitioning of data the basis of list of values in partition column. For example column with status_id

Example:

CREATE TABLE pt_list (
inc_id bigint NOT NULL AUTO_INCREMENT,
Inc_tital VARCHAR(250) NOT NULL,
inc_desc TEXT,
owner VARCHAR(250),
created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
status_id tinyint NOT NULL,
PRIMARY KEY (inc_id,created_date,status_id)
)
PARTITION BY LIST (status_id) (
PARTITION p1 VALUES IN (1,2),
PARTITION p2 VALUES IN (3),
PARTITION p3 VALUES IN (4,5)
);

Key point:  

  • Partitioning column must be an integer type.

 

List Column Portioning:  This is a variant of LIST partitioning allows the use columns of types other than integer types for partitioning columns, as well as to use multiple columns as partitioning keys.

Example:

CREATE TABLE pt_list_column (
inc_id bigint NOT NULL,
inc_tital VARCHAR(250) NOT NULL,
inc_desc TEXT,
owner VARCHAR(250),
created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
dept_name varchar(100) NOT NULL DEFAULT 'other',
KEY (dept_name)
)
PARTITION BY LIST COLUMNS (dept_name) (
PARTITION p1 VALUES IN ('support','development'),
PARTITION p2 VALUES IN ('sales'),
PARTITION p3 VALUES IN ('other','admin')
);

On Disk:

pt_key.frm
pt_key#P#p0.ibd
pt_key#P#p1.ibd
pt_key#P#p2.ibd
pt_key#P#p3.ibd
pt_key#P#p4.ibd

Key point:  

  • Supported in MySQL 5.6 version.
  • Enum datatype for partition column not supported.

 

HASH Partitioning: MySQL Hashing will do an even distribution of data for specified partitioning column with given number of partitions.

Example:

CREATE TABLE pt_hash (
inc_id bigint NOT NULL AUTO_INCREMENT,
inc_tital VARCHAR(250) NOT NULL,
inc_desc TEXT,
owner VARCHAR(250),
created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
status_id tinyint NOT NULL,
PRIMARY KEY (inc_id,created_date,status_id)
)
PARTITION BY HASH (status_id)
PARTITIONS 5;

On Disk:

pt_hash#P#p0.ibd
pt_hash#P#p1.ibd
pt_hash#P#p2.ibd
pt_hash#P#p3.ibd
pt_hash#P#p4.ibd

Key Points:

  • If you do not include a partition clause, the number of partitions defaults to 1.

 

LINEAR HASH Partitioning: Linear hashing utilizes a linear powers-of-two algorithm whereas regular hashing employs the modulus of the hashing function’s value.

Example:

CREATE TABLE pt_linear_hash (
inc_id bigint NOT NULL AUTO_INCREMENT,
inc_tital VARCHAR(250) NOT NULL,
inc_desc TEXT,
owner VARCHAR(250),
created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
status_id tinyint NOT NULL,
PRIMARY KEY (inc_id,created_date,status_id)
)
PARTITION BY LINEAR HASH (YEAR(created_date))
PARTITIONS 12;

 

KEY Partitioning: Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server.

Example:

CREATE TABLE pt_key (
inc_id bigint NOT NULL AUTO_INCREMENT,
inc_tital VARCHAR(250) NOT NULL,
inc_desc TEXT,
owner VARCHAR(250),
created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
status_id tinyint(5) NOT NULL,
PRIMARY KEY (inc_id,created_date,status_id)
)
PARTITION BY KEY (status_id)
PARTITIONS 5;

Key point:

  • Default partitioning on the basis of a primary key. If there no primary key then it will use available unique key.

 

Subpartitioning: [Composite Partitioning ]

Example:

CREATE TABLE pt_sub (
inc_id bigint NOT NULL AUTO_INCREMENT,
inc_tital VARCHAR(250) NOT NULL,
inc_desc TEXT,
owner VARCHAR(250),
created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
status_id tinyint NOT NULL,
PRIMARY KEY (inc_id,created_date,status_id)
)
PARTITION BY LIST (status_id)
SUBPARTITION BY HASH ( YEAR(created_date))
SUBPARTITIONS 12(
PARTITION p1 VALUES IN (1,2) ENGINE = InnoDB,
PARTITION p2 VALUES IN (3) ENGINE = InnoDB,
PARTITION p3 VALUES IN (4,5) ENGINE = InnoDB
);

On Disk:

pt_sub.frm                 pt_sub#P#p1#SP#p1sp4.ibd  pt_sub#P#p2#SP#p2sp10.ibd  pt_sub#P#p2#SP#p2sp6.ibd   pt_sub#P#p3#SP#p3sp1.ibd  pt_sub#P#p3#SP#p3sp8.ibd
pt_sub#P#p1#SP#p1sp0.ibd   pt_sub#P#p1#SP#p1sp5.ibd  pt_sub#P#p2#SP#p2sp11.ibd  pt_sub#P#p2#SP#p2sp7.ibd   pt_sub#P#p3#SP#p3sp2.ibd  pt_sub#P#p3#SP#p3sp9.ibd
pt_sub#P#p1#SP#p1sp10.ibd  pt_sub#P#p1#SP#p1sp6.ibd  pt_sub#P#p2#SP#p2sp1.ibd   pt_sub#P#p2#SP#p2sp8.ibd   pt_sub#P#p3#SP#p3sp3.ibd
pt_sub#P#p1#SP#p1sp11.ibd  pt_sub#P#p1#SP#p1sp7.ibd  pt_sub#P#p2#SP#p2sp2.ibd   pt_sub#P#p2#SP#p2sp9.ibd   pt_sub#P#p3#SP#p3sp4.ibd
pt_sub#P#p1#SP#p1sp1.ibd   pt_sub#P#p1#SP#p1sp8.ibd  pt_sub#P#p2#SP#p2sp3.ibd   pt_sub#P#p3#SP#p3sp0.ibd   pt_sub#P#p3#SP#p3sp5.ibd
pt_sub#P#p1#SP#p1sp2.ibd   pt_sub#P#p1#SP#p1sp9.ibd  pt_sub#P#p2#SP#p2sp4.ibd   pt_sub#P#p3#SP#p3sp10.ibd  pt_sub#P#p3#SP#p3sp6.ibd
pt_sub#P#p1#SP#p1sp3.ibd   pt_sub#P#p2#SP#p2sp0.ibd  pt_sub#P#p2#SP#p2sp5.ibd   pt_sub#P#p3#SP#p3sp11.ibd  pt_sub#P#p3#SP#p3sp7.ibd

Partition table info and SELECT Operations:

SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,PARTITION_METHOD,SUBPARTITION_METHOD
FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='db_name';

Sample data:

Partition details:
PARTITION BY RANGE( YEAR(created_date) ) (
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p1 VALUES LESS THAN (2003),
PARTITION p2 VALUES LESS THAN (2006),
PARTITION p3 VALUES LESS THAN (2009),
PARTITION p4 VALUES LESS THAN (2012),
PARTITION p5 VALUES LESS THAN (2015),
PARTITION p6 VALUES LESS THAN (2018),
PARTITION p7 VALUES LESS THAN MAXVALUE
);

mysql> select * from pt_range;
+--------+-------------------+-----------------+-------+---------------------+
| inc_id | Inc_tital         | inc_desc        | owner | created_date        |
+--------+-------------------+-----------------+-------+---------------------+
|      1 | login issue       | unable to login | lalit | 1999-06-26 12:33:21 |
|      2 | print issue       | unable to print | XYZ   | 2000-09-28 12:33:21 |
|      3 | print issue again |                 | maren | 2001-10-26 02:23:21 |
|      4 | scan issue        | NULL            | maren | 2005-06-26 02:23:21 |
|      5 | scan issue        | NULL            | abc   | 2010-07-26 02:23:21 |
|      6 | scan issue        | NULL            | abc   | 2013-07-26 02:23:21 |
|      7 | scan issue        | NULL            | abc   | 2015-07-26 02:23:21 |
|      8 | urgent            | NULL            | NULL  | 2016-07-26 02:23:21 |
|      9 | test              | NULL            | NULL  | 2017-09-28 12:38:35 |
+--------+-------------------+-----------------+-------+---------------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM pt_range PARTITION (p5);
+--------+------------+----------+-------+---------------------+
| inc_id | Inc_tital  | inc_desc | owner | created_date        |
+--------+------------+----------+-------+---------------------+
|      6 | scan issue | NULL     | abc   | 2013-07-26 02:23:21 |
+--------+------------+----------+-------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM pt_range  WHERE YEAR(created_date)='2000';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM pt_range PARTITION (p1);
+--------+-------------------+-----------------+-------+---------------------+
| inc_id | Inc_tital         | inc_desc        | owner | created_date        |
+--------+-------------------+-----------------+-------+---------------------+
|      2 | print issue       | unable to print | XYZ   | 2000-09-28 12:33:21 |
|      3 | print issue again |                 | maren | 2001-10-26 02:23:21 |
+--------+-------------------+-----------------+-------+---------------------+
2 rows in set (0.00 sec)

SELECT Analysis using EXPLAIN:

mysql> explain SELECT * FROM pt_range WHERE YEAR(created_date)='2000';
+----+-------------+----------+----------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+----------------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | pt_range | p0,p1,p3,p4,p5,p6,p7 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
+----+-------------+----------+----------------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM pt_range PARTITION (p1) WHERE YEAR(created_date)='2000';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | pt_range | p1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

NULL Column value and MySQL Partition:

Ideally, it’s better to avoid  NULL value in partition columns by enforcing the NOT NULL constraint, where ever it possible.

If NULL is allowed for partition column then, partition will handle null values as follows,

Type of Partition Handling NULL Example
RANGE Partitioning For partition column NULL value will be inserted into lower value partition. PARTITION p0 VALUES LESS THAN (2000),

PARTITION p1 VALUES LESS THAN (2003),

PARTITION p2 VALUES LESS THAN (2006)

NULL Value will go into partition p0

LIST Partitioning NULL should be part of one of the partition list value.

If NULL is not included in any partition list, Insert operation will fail with the error.

PARTITION BY LIST (status_id) (

PARTITION p1 VALUES IN (1,2)

INSERT INTO pt_list(status_id) values(NULL);

ERROR 1504 (HY000): Table has no partition for value NULL

ALTER TABLE pt_list

REORGANIZE PARTITION p1 INTO (PARTITION p1 VALUES IN (1,2,NULL));

Now NULL values will go into p1 partition.

HASH  and KEY Partitioning For both types of portioning NULL treated as 0 (zero) Hence NULL values will go to very first partition (eg . p0)

Partition table management:

Adding new partition:

CREATE TABLE `pt_list` (
`inc_id` bigint(20) NOT NULL,
`Inc_tital` varchar(250) NOT NULL,
`inc_desc` text,
`owner` varchar(250) DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status_id` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST (status_id)
(PARTITION p1 VALUES IN (1,2) ENGINE = InnoDB,
PARTITION p2 VALUES IN (3) ENGINE = InnoDB,
PARTITION p3 VALUES IN (4,5) ENGINE = InnoDB);

mysql> ALTER TABLE pt_list ADD PARTITION (PARTITION p4 VALUES IN (6,7,8));
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings:

mysql> show create table pt_list\G
*************************** 1. row ***************************
Table: pt_list
Create Table: CREATE TABLE `pt_list` (
`inc_id` bigint(20) NOT NULL,
`Inc_tital` varchar(250) NOT NULL,
`inc_desc` text,
`owner` varchar(250) DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status_id` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (status_id)
(PARTITION p1 VALUES IN (1,2) ENGINE = InnoDB,
PARTITION p2 VALUES IN (3) ENGINE = InnoDB,
PARTITION p3 VALUES IN (4,5) ENGINE = InnoDB,
PARTITION p4 VALUES IN (6,7,8) ENGINE = InnoDB) */
1 row in set (0.00 sec)

Alter/Change Partition:

mysql> ALTER TABLE pt_list REORGANIZE PARTITION p1 INTO (PARTITION p1 VALUES IN (1,2,NULL));
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table pt_list\G
*************************** 1. row ***************************
Table: pt_list
Create Table: CREATE TABLE `pt_list` (
`inc_id` bigint(20) NOT NULL,
`Inc_tital` varchar(250) NOT NULL,
`inc_desc` text,
`owner` varchar(250) DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status_id` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (status_id)
(PARTITION p1 VALUES IN (NULL,1,2) ENGINE = InnoDB,
PARTITION p2 VALUES IN (3) ENGINE = InnoDB,
PARTITION p3 VALUES IN (4,5) ENGINE = InnoDB,
PARTITION p4 VALUES IN (6,7,8) ENGINE = InnoDB) */
1 row in set (0.00 sec)

 

DROP Partition:

mysql> show create table pt_range\G
*************************** 1. row ***************************
Table: pt_range
Create Table: CREATE TABLE `pt_range` (
`inc_id` bigint(20) NOT NULL AUTO_INCREMENT,
`Inc_tital` varchar(250) NOT NULL,
`inc_desc` text,
`owner` varchar(250) DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`inc_id`,`created_date`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(created_date))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2003) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> select * from pt_range;
+--------+-------------------+-----------------+-------+---------------------+
| inc_id | Inc_tital | inc_desc | owner | created_date |
+--------+-------------------+-----------------+-------+---------------------+
| 1 | login issue | unable to login | lalit | 1999-06-26 12:33:21 |
| 2 | print issue | unable to print | XYZ | 2000-09-28 12:33:21 |
| 3 | print issue again | | maren | 2001-10-26 02:23:21 |
| 4 | scan issue | NULL | maren | 2005-06-26 02:23:21 |
| 5 | scan issue | NULL | abc | 2010-07-26 02:23:21 |
| 6 | scan issue | NULL | abc | 2013-07-26 02:23:21 |
| 7 | scan issue | NULL | abc | 2015-07-26 02:23:21 |
| 8 | urgent | NULL | NULL | 2016-07-26 02:23:21 |
| 9 | test | NULL | NULL | 2017-09-28 12:38:35 |
+--------+-------------------+-----------------+-------+---------------------+
9 rows in set (0.00 sec)

mysql> ALTER TABLE pt_range DROP PARTITION p2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from pt_range;
+--------+-------------------+-----------------+-------+---------------------+
| inc_id | Inc_tital | inc_desc | owner | created_date |
+--------+-------------------+-----------------+-------+---------------------+
| 1 | login issue | unable to login | lalit | 1999-06-26 12:33:21 |
| 2 | print issue | unable to print | XYZ | 2000-09-28 12:33:21 |
| 3 | print issue again | | maren | 2001-10-26 02:23:21 |
| 5 | scan issue | NULL | abc | 2010-07-26 02:23:21 |
| 6 | scan issue | NULL | abc | 2013-07-26 02:23:21 |
| 7 | scan issue | NULL | abc | 2015-07-26 02:23:21 |
| 8 | urgent | NULL | NULL | 2016-07-26 02:23:21 |
| 9 | test | NULL | NULL | 2017-09-28 12:38:35 |
+--------+-------------------+-----------------+-------+---------------------+
8 rows in set (0.00 sec)

mysql> show create table pt_range\G
*************************** 1. row ***************************
Table: pt_range
Create Table: CREATE TABLE `pt_range` (
`inc_id` bigint(20) NOT NULL AUTO_INCREMENT,
`Inc_tital` varchar(250) NOT NULL,
`inc_desc` text,
`owner` varchar(250) DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`inc_id`,`created_date`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(created_date))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2003) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

Advantages of partitioning:

  • Allow to store more data efficiently.
  • An unused portion of data can be easily removed from a table.
  • Due to the partitioning of data on physical level Read/Write operations are faster as compare non-partition table.

 

Refer MySQL official PARTITIONING_DOC for more…

Advertisements

Database Design: MYSQL CLOUD SERVICE 2018 [1Z0-320]

Continuation to my last Blog Post for MYSQL CLOUD SERVICE 2018 [1Z0-320] exam preparation, In this blog will discuss Database Design part for MySQL. Rather than rewriting given topics I have provided already exists good reference links for few topics.

Database Design

  • Describe Datatypes in a MySQL database
  • Create databases and tables
  • Create basic SQL queries
  • Maintain Databases, Tables, and Columns
  • Configure Indexes and Constraints
  • Join Tables
  • Partition MySQL Tables

Describe Datatypes in a MySQL database

Reference Link:

Link1: MySQL Data Types

Link2: MySQL Data Types

Create databases and tables

Create database:

CREATE DATABASE IF NOT EXISTS project1  CHARACTER SET utf8 COLLATE utf8_general_ci;

[On Filesystem level, this will create project1 directory inside mysql datadir with db.opt file in it.]

USE project1;

SHOW DATABASES 

CREATE TABLE IF NOT EXISTS dept ( deptid INT PRIMARY KEY, dname CHAR(50), location text ) ;

IF NOT EXISTS (optional): Prevents an error from occurring if the table exists with the same name. It will not compare table structure.

Create a table with INDEX and Foreign key:

CREATE TABLE emp (empid INT AUTO_INCREMENT PRIMARY KEY, fname CHAR(50), deptid INT , doj DATETIME , FOREIGN KEY fk_cat(deptid) REFERENCES dept(deptid)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB;

Create TEMPORARY TABLE: 

Available only within the current session and removed automatically after a session end.

CREATE TEMPORARY TABLE tmptbl (id int, c2 varchar(40));

Cloning or Copying a Table:

CREATE TABLE emp1 LIKE emp;

CREATE TABLE dept1 AS select * from dept;

Reference Links:

CREATE DATABASE

CREATE TABLE

MySQL Basic Operations:

  • Create basic SQL queries
  • Configure Indexes and Constraints
  • Join Tables
  • Subqueries
Types Query Commands / Details
READ DATA SELECT [ DISTINCT ,COUNT, SUM ,MAX, MIN, AVG, AS]
DATA FILTERS WHERE [ AND,OR, IN,BETWEEN,LIKE,LIMIT,IS NULL
SORT DATA ORDER BY
GROUP DATA GROUP BY, HAVING
SUBQUERY [Inner query, outer query]
MODIFY DATA INSERT [IGNORE]

UPDATE

DELETE [ ON DELETE CASCADE]

REPLACE

PREPARE Statements

JOINS CROSS, INNER, LEFT, RIGHT, self-join
INDEX PRIMARY KEY

UNIQUE, INDEX

FULLTEXT

All above are stored in B-trees.

Indexes on spatial data types use R-trees.

MEMORY tables also support hash indexes.

InnoDB uses inverted lists for FULLTEXT indexes.

CONSTRAINTS NOT NULL

PRIMARY KEY

FOREIGN KEY

UNIQUE

CHECK

Reference Links :

MySQL Basic Operations [ SQL Queries, Indexes and Constraints, Join, Subquery ]

MySQL INDEX

MySQL JOINS

MySQL Subquery 

Maintain Databases, Tables, and Columns

General Options for DB Objects Management:

Operation Commands and details
Database CREATE DATABASE

SHOW DATABASES

USE  [db_name]

DROP DATABASE

Table CRAETE TABLE

CREATE TEMPORARY TABLE

ALTER TABLE

RENAME TABLE

DROP TABLE

TRUNCATE TABLE

Column ALTER TABLE ADD COLUMN

ALTER TABLE MODIFY COLUMN

ALTER TABLE DROP COLUMN

Metadata Information: [Information_schema]
Information_schema database will keep metadata information about all DB objects.

Databases:

SHOW DATABASES;

Select Database:

USE [db_name];

Tables in Database:

SHOW TABLES;

SELECT TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'db_name';

ROUTINES:

select * from ROUTINES where ROUTINE_SCHEMA='db_name';

INDEX:

SELECT TABLE_NAME,INDEX_NAME,COLUMN_NAME,INDEX_TYPE  FROM INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = '<database name>';

View:

select * from information_schema.VIEWS where TABLE_SCHEMA = 'db_name';

TABLE Maintenance:

Analyze table: Update key-index stats for a table. 

Optimize table: Avoid defragmenting problem.

Check table: Check the integrity of database tables.

Repair table: Repair table structure. 

Reference links:

Maintaining MySQL Database Tables

Table Maintenance Statements

 

Will write about MySQL Partitioning  in next blog post.

Exam Preparation part 1: MySQL Cloud Service 2018 [1Z0-320]

Recently, Oracle added new certification for MySQL,

MySQL Cloud Service 2018 Implementation Essentials

This exam is all about MySQL Enterprise Edition 5.6. with MySQL Cloud Service. Best study resource for this exam is “MySQL official documentation”. Along with MySQL Enterprise topic, this exam also includes one major part of MySQL Cloud Service.

Exam Topics: 

Will write more about these exam topics in our next blog post.

Feel free to share your ideas and preparation tips for this exam.


I started reading these topics and following are my notes for few of them,

Oracle MySQL Enterprise Product Suite

  • Describe the difference between MySQL Enterprise Edition and the Community/Standard Edition

https://www.mysql.com/products/

  • Explain the products and features of MySQL Enterprise Products

https://www.mysql.com/products/enterprise/

  • Identity the Oracle products that are certified to work with MySQL Enterprise Edition

MySQL Enterprise Edition makes managing MySQL easier in these environments by certifying and supporting the use of the MySQL Database in conjunction with many Oracle products. These include:

  • Oracle Linux
  • Oracle VM
  • Oracle Fusion Middleware
  • Oracle Secure Backup
  • Oracle Golden Gate
  • Oracle Audit Vault and Database Firewall
  • Oracle Enterprise Manager
  • Oracle OpenStack for Oracle Linux
  • Oracle Clusterware

Installation and Architecture

  • Explain the MySQL infrastructure architecture

https://lalitvc.wordpress.com/2016/11/03/mysql-architecture-and-components/

  • Install a MySQL Enterprise Server

https://dev.mysql.com/doc/refman/5.6/en/installing.html

  • Configure settings for a MySQL Enterprise Server

https://dev.mysql.com/doc/refman/5.6/en/server-configuration.html

  • Access MySQL databases with MySQL Workbench
  • Manage MySQL Database users with MySQL Workbench

https://dev.mysql.com/doc/workbench/en/

 

MySQL 5.7 InnoDB Tablespace

By default for MySQL server, InnoDB Engine is getting used widely due it’s ACID support, optimized read-write performance and for many other reasons which are great significance for the database server.

In this blog post, we are going to cover the InnoDB tablespace and its features like,

  • InnoDB engine tablespaces
  • Tablespace Data Encryption
  • Tablespace related Configuration

InnoDB engine tablespaces

System tablespace:  

Common tablespace for MySQL server operations. Apart from the table data storage, InnoDB’s functionality requires looking for table metadata, storing and retrieving MVCC info to support ACID compliance and Transaction Isolation. It contains several types of information for InnoDB objects.

  • Contains:
    Table Data Pages
    Table Index Pages
    Data Dictionary
    MVCC Control Data
    Undo Space
    Rollback Segments
    Double Write Buffer (Pages Written in the Background to avoid OS caching)
    Insert Buffer (Changes to Secondary Indexes)
  • Variables:
    innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

    By enabling innodb_file_per_table (the default) option, we can store each newly created table (data and index) in a separate tablespace. Advantage for this storage method is less fragmentation within disk data file.

InnoDB data dictionary:
Storage area in system tablespace made up of internal system tables with metadata information for objets[tables, index, columns etc.]

Double write buffer:
Storage area in system tablespace where innodb writes pages from innodb buffer pool, before writing to their proper location in the data files.
In case mysqld process crash in the middle of a page writes, at the time of crash recovery InnoDB can find a good copy of the page from doublewrite buffer.

Variable: inndb_doublewrite (default enable)

REDO logs:
Use for crash recovery. At the time of mysqld startup, InnoDB performs auto recovery to correct data written by incomplete transactions. Transactions that not finish updating data files before an unexpected mysqld shutdown are replayed automatically at the time of mysqld startup even before taking any connection. It uses LSN(Log Sequence Number) value.
Plenty of data changes can not get written to disk quickly, so it will go under redo and then to the disk.

Why we need a redo for recovery?
Let’s take an example, User changing data in innodb buffer and commit, somewhere it needs to go before writing into a disk. Because in the case of crash buffer data will lost, that’s why we need redo logs.

– In redo, all changes will go with info like row_id, old column value, new column value, session_id and time.
– One commit complete data will under disk in a data file.
– Variables:
Innodb_log_file_in_group= [# of redo file groups]

innodb_log_buffer_size= [ Buffer size ] (Set greater value to hold large transactions in memory. Start from 10-20% of total log files size)

Innodb_log_file_size= [Size for each redo file]  (Should be set to a greater value greater for BLOB data types in database )

UNDO tablespace and logs:

UNDO tablespace contains one or more undo logs files.
UNDO manages consistent reads by keeping modified uncommitted data for active transaction [MVCC]. Unmodified data is retrieved from this storage area.Undo logs also called as rollback segments
By default, UNDO logs are part of system tablespace, MySQL allows to store undo logs in separate UNDO tablespace/s [Introduce in MySQL 5.6]. Need to configure before initializing mysqld server.

– When we configure separate undo tablespace, the undo logs in the system tablespace become inactive.
– Need to configure before initializing mysqld server and can not change after that.
– We truncate undo logs, but can not drop.
– The default initial size of an undo tablespace file is 10MB.
– Variables :
innodb_undo_tablespace : Number of undo tablespaces, default 0 , max 95
innodb_undo_directory : Location for undo tablespace,default is data_dir with 10MB initial size.
innodb_undo_logs : Number of undo logs in a single undo tablespace, default and max value is ‘128’ [ Deprecated in 5.7.19 , innodb_rollback_segments variable will control this]
innodb_undo_log_truncate: truncate undo tablespace, Default OFF  [When enabled, undo tablespaces that exceed the threshold value defined by innodb_max_undo_log_size are marked for truncation. ]

Key Points:

  • Truncating undo logs need separate undo logs. This means undo in system tablespace can not be truncated.
  • innodb_undo_tablespaces must be set to a value equal to or greater than 2.
  • innodb_rollback_segments must set to a value equal to or greater than 35.

Benefits: Reduce the size of the single tablespace (system tablespace), since we are storing long-running transactions into a sperate single/multiple UNDO tablespaces.

Temporary tablespace:

Storage to keep and retrieve modified uncommitted data for temporary tables and related objects.Introduce in MySQL 5.7.2 and use for rollback temp table changes while a server is running.

– Undo logs for temporary tables reside in the temp tablespace.
– Default tablespace file ibtmp1 getting recreated on server startup.
–  Not getting used for crash recovery.
– Advantage: Performance gain by avoiding redo logging IO for temp tables and related objects.
– Variable:
innodb_temp_data_file_path = ibtmp1:12M:autoextend (default)

General tablespace:

Shared tablespace to store multiple table data. Introduce in MySQL 5.7.6. A user has to create this using CREATE TABLESPACE syntax. TABLESPACE option can be used with CREATE TABLE to create a table and ALTER TABLE to move a table in general table.

– Memory advantage over innodb_file_per_table storage method.
– Support both Antelope and Barracuda file formats.
–  Supports all row formats and associated features.
–  Possible to create outside data directory.

Tablespace Data Encryption:

InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces using mysql keyring. MySQL 5.7.11 and higher includes a keyring plugin.

keyring_file: Stores keyring data in a file local to the server host. keyring_file must be loaded at each server startup using the –early-plugin-load option

keyring_okv: Back end keyring storage products such as Oracle Key Vault, This plugin is available in MySQL Enterprise Edition distributions.

Variables:
early-plugin-load : Settings ensure that plugin is available prior to initialization of the InnoDB storage engine.
keyring_file_data : keyring file path.

Tablespace related Configuration and operations:

MySQL InnoDB Configuration:

## DATA STORAGE ##
datadir=/var/lib/mysql

## InnoDB Configuration ##
innodb_file_per_table=1

# InnoDB Memory
innodb_buffer_pool_size = 2000M

# System Tablespace configuration
innodb_data_file_path= ibdata1:512M;ibdata2:512M:autoextend

# Redo log and buffer configuration
innodb-log-files-in-group=3
innodb_log_file_size=100M
innodb_log_buffer_size=30M

#InnoDB file formate
innodb_file_format = Barracuda

# UNDO Tablespace Configuration
innodb_undo_directory =  /var/lib/mysql/
innodb_undo_tablespaces = 3
innodb_undo_logs = 128
innodb_undo_log_truncate = ON
innodb_rollback_segments = 128

# Temp Tablespace Configuration
tmpdir = /var/lib/mysql/
innodb_temp_data_file_path = ibtmp1:20M:autoextend

# Keyring configuration
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

 

MySQL Server Initialization Logs:


[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Number of pools: 1
[Note] InnoDB: Using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
[Note] InnoDB: Opened 4 undo tablespaces
[Note] InnoDB: 4 undo tablespaces made active
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: Creating shared tablespace for temporary tables
[Note] InnoDB: Setting file './ibtmp1' size to 20 MB. Physically writing the file full; Please wait ...
[Note] InnoDB: File './ibtmp1' size is now 20 MB.
[Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
[Note] InnoDB: 32 non-redo rollback segment(s) are active.
[Note] InnoDB: Waiting for purge to start
[Note] InnoDB: 5.7.19 started; log sequence number 2454162
[Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
[Note] Plugin 'FEDERATED' is disabled.
[Note] InnoDB: Buffer pool(s) load completed at 170828 12:03:52

UNDO and Temporary tablespaces:

mysql_datadir

General tablespace Example:

General Tablespace can be created inside mysql datadir [ Default ] or outside of the MySQL data directory.

Example:


# Create General tablespace
mysql> CREATE TABLESPACE gen_tblsp ADD DATAFILE 'gen_tlbsp.ibd' ENGINE = INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from INFORMATION_SCHEMA.FILES  where TABLESPACE_NAME ='gen_tblsp'\G
*************************** 1. row ***************************
             FILE_ID: 27
           FILE_NAME: ./gen_tlbsp.ibd
           FILE_TYPE: TABLESPACE
     TABLESPACE_NAME: gen_tblsp
     ....
     ....

# Create table inside general tablespace.
mysql> CREATE TABLE gen_ts_tbl (id int(11), c_desc varchar(100), c_comments text ) TABLESPACE gen_tblsp;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO gen_ts_tbl values (1, 'test' , 'General tablespace testing');
Query OK, 1 row affected (0.01 sec)

mysql> select * from gen_ts_tbl;
+------+--------+----------------------------+
| id   | c_desc | c_comments                 |
+------+--------+----------------------------+
|    1 | test   | General tablespace testing |
+------+--------+----------------------------+
1 row in set (0.00 sec)

# Move Existing table into general tablespace.

mysql> create table innodb_table (id int (11), uname varchar(78));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into innodb_table values(1,'moving to gen_tblsp');
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE innodb_table TABLESPACE gen_tblsp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from innodb_table;
+------+---------------------+
| id   | uname               |
+------+---------------------+
|    1 | moving to gen_tblsp |
+------+---------------------+
1 row in set (0.00 sec)

# DROP General Tablespace [ We need to drop all table in general tablespace before dropping it]

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| gen_ts_tbl     |
| innodb_table   |
+----------------+
2 rows in set (0.00 sec)

mysql> drop table gen_ts_tbl;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table innodb_table;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.01 sec)

mysql> drop tablespace gen_tblsp;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.FILES  where TABLESPACE_NAME ='gen_tblsp'\G
Empty set (0.00 sec)

mysql_general_tablespace

InnoDB TDE using a keyring_file plugin:


mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

mysql> show variables like '%keyring%';
+-------------------+--------------------------------+
| Variable_name     | Value                          |
+-------------------+--------------------------------+
| keyring_file_data | /var/lib/mysql-keyring/keyring |
+-------------------+--------------------------------+
1 row in set (0.00 sec)

 mysql> CREATE TABLE innodb_tde (id int(11), c_desc varchar(100), c_comments text ) ENCRYPTION='Y';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM
       INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test         | innodb_tde | ENCRYPTION="Y" |
+--------------+------------+----------------+
1 row in set (0.01 sec)

mysql> INSERT INTO innodb_tde values (1, 'test tde' , 'innodb tde testing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from innodb_tde;
+------+----------+--------------------+
| id   | c_desc   | c_comments         |
+------+----------+--------------------+
|    1 | test tde | innodb tde testing |
+------+----------+--------------------+
1 row in set (0.01 sec)</pre>
# Disable - Enable ENCRYPTION from table

mysql> ALTER TABLE innodb_tde ENCRYPTION='N';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE innodb_tde ENCRYPTION='Y';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from innodb_tde;
+------+----------+--------------------+
| id | c_desc | c_comments |
+------+----------+--------------------+
| 1 | test tde | innodb tde testing |
+------+----------+--------------------+
1 row in set (0.00 sec)

#ENCRYPTION MASTER KEY Rotation

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from innodb_tde;
+------+----------+--------------------+
| id | c_desc | c_comments |
+------+----------+--------------------+
| 1 | test tde | innodb tde testing |
+------+----------+--------------------+
1 row in set (0.00 sec)

Refer MySQL doc for more:
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace.html

MySQL High Availability with Keepalived and HAProxy

In this blog post, we are going to test load balancer solution for MySQL high availability by integrating it with Keepalived, HAProxy, xinetd software components.

LB_diag

High availability databases use an architecture that is designed to continue to function normally even when there are hardware or network failures within the system.

Why we need this?

Let’s take a scenario where we have MySQL Multi-Master / MASTER-SLAVE replication setup for high availability. In the case of Hardware/Network failure on MASTER, In order to failover to a next available server, we need to manually do the configuration changes for client connections.In this case, downtime is expected since manual failover will take some times. To solve this we can integrate load balancer with MySQL to take care of this manual work and do automatic failover connections.

To avoid such downtimes and for the maximum high availability of the database, we can integrate load balancer with MySQL to take care of this manual work and do automatic failover connections.

Advantages:

  • Almost ZERO DOWNTIME for DB maintenance activities like database patching/upgrades, configuration changes that need DB restart etc.
  • Easy Read-Write load distribution.
  • Automatic Failover.
  • Easy to setup and manage.

Load Balancer is a set of integrated software components that provide for balancing IP traffic across a set of real servers. It consists of two main technologies to monitor cluster members and cluster services: Keepalived and HAProxy.

Keepalived: It uses Linux virtual server (LVS) to perform load balancing and failover tasks on.

HAProxy: It performs load balancing and high-availability services to TCP and HTTP applications.

xinetd: “Extended Internet daemon” is an open-source super-server daemon which runs on many Unix-like systems and manages Internet-based connectivity.xinetd runs constantly and listens on all ports for the services it manages. When a connection request arrives for one of its managed services, xinetd starts up the appropriate server for that service.

VIP:  Virtual IP addresses (or VIPs) allow you to use multiple IPs on a single physical network interface.

Keepalive Configuration:

Load Balancer #1 Configuration

global_defs {
   notification_email {
     mysql-user@mydomain.com
   }
   notification_email_from svr1@mydomain.com
   smtp_server localhost
   smtp_connect_timeout 30
}
vrrp_instance VRRP1 {
    state MASTER
#   Specify the network interface to which the virtual address is assigned
    interface eth0
#   The virtual router ID must be unique to each VRRP instance that you define
    virtual_router_id 71
#   Set the value of priority higher on the master server than on a backup server
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1066
    }
virtual_ipaddress {
192.168.2.111
192.168.2.112
}
}

Load Balancer #2 Configuration


global_defs {
   notification_email {
     mysql-user@mydomain.com
   }
   notification_email_from svr2@mydomain.com
   smtp_server localhost
   smtp_connect_timeout 30
}

vrrp_instance VRRP1 {
    state BACKUP
#   Specify the network interface to which the virtual address is assigned
    interface eth0
#   The virtual router ID must be unique to each VRRP instance that you define
    virtual_router_id 71
#   Set the value of priority higher on the master server than on a backup server
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1066
    }
virtual_ipaddress {
192.168.2.111
192.168.2.112
}
}

HAProxy Configuration on Load balancer #1 and #2 :


  global
        daemon
        maxconn 256

    defaults
        mode http
        timeout connect 5000ms
        timeout client 50000ms
        timeout server 50000ms

# MYSQL Configuration 

listen write_connections 192.168.2.111:3306
mode tcp
balance roundrobin
option httpchk
server mysql_db1 192.168.2.105:3306 check port 9200
server mysql_db2 192.168.2.106:3306 check port 9200 backup 

listen read_connections 192.168.2.112:3306
mode tcp
balance leastconn
option httpchk
server mysql_db3 192.168.2.107:3306 check port 9200 

Note: Add all IP and hostname entries in /etc/hosts file.

xinetd configuration on all mysql server:

/etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /opt/mysqlchk
log_on_failure += USERID
disable = no
only_from = 0.0.0.0/0
per_source = UNLIMITED

NOTE: Create following script on all mysql server to monitor mysql status.
mysqlchk.stauts script [/opt/mysqlchk.stauts] : The purpose of this script is make haproxy capable of monitoring mysql properly


# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#mysql> CREATE USER IF NOT EXISTS 'mysqlchkuser'@'localhost' IDENTIFIED BY 'P@ssword#567';
# mysql> GRANT show databases on *.* TO 'mysqlchkuser'@'localhost';
# mysql> flush privileges;

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="P@ssword#567"

TMP_FILE="/opt/tmp/mysqlchk.out"
ERR_FILE="/opt/tmp/mysqlchk.err"

#
# We perform a simple query that should return a few results.
#
mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE

#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
    # mysql is fine, return http 200
    /bin/echo -e "HTTP/1.1 200 OKrn"
    /bin/echo -e "Content-Type: Content-Type: text/plainrn"
    /bin/echo -e "rn"
    /bin/echo -e "MySQL is running.rn"
    /bin/echo -e "rn"
else
    # mysql is fine, return http 503
    /bin/echo -e "HTTP/1.1 503 Service Unavailablern"
    /bin/echo -e "Content-Type: Content-Type: text/plainrn"
    /bin/echo -e "rn"
    /bin/echo -e "MySQL is *down*.rn"
    /bin/echo -e "rn"
fi

Testing:

  • Test MySQL connection with VIP address.
  • Stop MySQL Active MASTER, and connect to MySQL.It should connect to backup MySQL server specified in HAProxy configuration.
  • Test Keepalived and HAProxy failover.

All Set!!

Basics of MySQL Administration and best practices

Following are the few best practices and basic commands for MySQL Administration.

MySQL Access and credential security

shell> mysql -u testuser -pMyP@ss0rd
mysql: [Warning] Using a password on the command line interface can be insecure.

By looking at OS cmd’s history using history cmd other os users can see/get MySQL user password easily. It always good to not use a password on the command line interface. Another option for securing password while automating MySQL scripts is a use of mysql_config_editor. For more info on this check out my blog post about credential security.

Consider of having following implementation for Strong access policy.

  • use of  validate_password plugin for a strong password policy.
  • Limit the user access by specifying IP or IP range in a hostname.
  • Do not grant accessive privileges to user/s.
  • Have separate users for different operations like backup user with required backup privileges only.
  • Avoid giving FILE and super privileges to remote users.
  • For public network communication between client and server use SSL connection method.

Replication

  •  IF EXISTS and IF NOT EXISTS use while creating DB objects.

Most common problem for replication break or errors is that OBJECT already exists on SLAVE. By using IF EXISTS and IF NOT EXISTS while creating database objects we can avoid.

  • Use of GTID and crash-safe replication.
  • Keep your slave in read-only mode.
  • Run your backups and query optimization on SLAVE. This will avoid unnecessary load on MASTER.

Logging

Logs are great significance for admin. Following types of logs, you can enable for MySQL servers.

  • Binary log: Extra copy of your database transactions.
  • Relay log:  By default enable and get created when you setup replication.
  • General log: To log MySQL client tool commands.
  • Slow query log: Log slow queries taking more time for execution.
  • Error / MySQL server log: Record NOTES, WARNINGS and ERROR for MySQL server.
  • Audit Log:  Log user info and activities like executes queries by a user/s along with source IP, timestamp, target database etc.
  • To maintain these logs like purging OLD logs using logrotate. Check MYSQL SERVER LOG MAINTENANCE for more info.

 MySQL STARTUP- SHUTDOWN

Always check MySQL error log for STARTUP- SHUTDOWN and make sure for clean STARTUP/ SHUTDOWN.

Basic commands for MySQL Administration

MySQL database and table creation

CREATE IF NOT EXISTS DATABASE:

CREATE IF NOT EXISTS  DATABASE test_db ;
Use test_db ;

CREATE TABLE:

CREATE IF NOT EXISTS TABLE t1 (id int(11) primary key auto_incremnet ,uname varchar(50), comments text);

INSERT INTO TABLE:

INSERT INTO t1 (id, uname, comments) VALUES(101,’lalit’,’mysql DBA’);

MySQL Database Users

CREATE USER:

  1. The CREATE USER statement creates new MySQL accounts.
CREATE USER IF NOT EXISTS 'local_user1'@'localhost' IDENTIFIED BY 'mypass'; (Remote connection restricted for this user)

If you specify only a username part of the account name, a host name part of ‘%’ is used.

CREATE USER IF NOT EXISTS 'remote_user1'@'%' IDENTIFIED BY 'mypass';

(Remote connection enabled for this user)

  1. User details are getting stored under user table.
SELECT user,host FROM mysql.user;

 

RENAME USER:

RENAME USER 'abc'@'localhost' TO 'xyz'@'%';

DROP USER:

 DROP IF EXISTS USER 'remote_user1'@'%’;

User password management:

  1. Change/Update user password.
ALETR USER IF EXISTS 'remote_user1'@'%' IDENTIFIED BY 'mypass';
  1. Password expire user account
ALTER USER IF EXISTS 'remote_user1'@'%' PASSWORD EXPIRE;
  1. Locked User account
ALTER USER IF EXISTS 'remote_user1'@'%' ACCOUNT LOCK;

 

MySQL Database Users Access Restrictions using privileges.

Grant privileges to a user:

Privileges can be granted on database/s, table/s and related objects to it.

Example.

Case1:  Grant all privileges on ‘db1’ database to user ‘remote_user1’@’%’

GRANT ALL PRIVILEGES ON db1.* TO 'remote_user1'@'%';

Case2: Grant selected privileges on ‘db1’ database to user ‘remote_user1’@’%’

GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'remote_user1'@'%';

Case3. Grant SELECT privilege single table access to user ‘remote_user1’@’%’

GRANT SELECT ON db1.table1 TO 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/grant.html

Revoking privileges from user:

Example:

REVOKE SELECT, INSERT, UPDATE, DELETE ON db1.* FROM 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/revoke.html

Check User Privileges using SHOW GRANTS command:

Example:

SHOW GRANTS FOR 'mysqldba'@'localhost';

SHOW GRANTS; (It will display the privileges granted to the current account)

SHOW GRANTS FOR 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/show-grants.html

MySQL monitoring

Check database size:

Information_schema (Metadata)

SELECT table_schema "Data Base Name",    sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",    sum( data_free )/ 1024 / 1024 "Free Space in MB"FROM information_schema.TABLESGROUP BY table_schema ;

Check Active users:

show processlist ;

InnoDB Engine Status:

SHOW STATUS;

SHOW ENGINE INNODB STATUS;
  1. Performance schema: Live statistics

Example:

– Enable Locking related instruments (if it’s not enabled):

UPDATE performance_schema.setup_instruments SET ENABLED=’YES’, TIMED=’YES’ WHERE NAME=’wait/lock/metadata/sql/mdl’;

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=’test’ AND OBJECT_NAME LIKE ‘t_’;

  1. MySQL Enterprise monitor
  2. Customized scripts

Check Database objects info:

Databases:

SHOW DATABASES;

Select Database:

Use db_name;

Tables in Database:

SHOW TABLES;

SELECT TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'test_db';

ROUTINES:

select * from ROUTINES where ROUTINE_SCHEMA='db_name’;

INDEX:

select TABLE_NAME,INDEX_NAME,COLUMN_NAME,INDEX_TYPE  from information_schema.STATISTICS where TABLE_SCHEMA = 'db_name';

View:

select * from information_schema.VIEWS where TABLE_SCHEMA = 'db_name';

Mysqldump  Backup-Restore:

Require privileges: mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

Backup:

Full Database backup:

mysqldump -u root  -p --single-transaction --databases db1  --routines > db1_fullbkp.sql

 OR

mysqldump -u root  -p --single-transaction  --databases db1 --routines | gzip >  db1_fullbkp.sql.gz

 

Single table backup:

mysqldump -u  -h  -p --single-transaction db_name table_name --routines > db1_full.sql

Ref : http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Restore:

To reload a dump file, you must have the privileges required to execute the statements that it contains, such as the appropriate CREATE privileges for objects created by those statements.

mysql -u username -p db_name < db1_fullbkp.sql

OR

gunzip < db1_fullbkp.sql.gz | mysql -u username -p db_name

MySQL Replication:

  1. Create replication user on MASTER with replication privileges.
CREATE USER [IF NOT EXISTS] 'rpluser'@'%' IDENTIFIED BY 'rpluser1234';GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rpluser'@'%';
  1. On SLAVE: setup replication as follows:
CHANGE MASTER TO MASTER_HOST='<MASTER_IP',MASTER_USER='rpluser',MASTER_PASSWORD='rpluser1234',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
  1. Start slave
START SLAVE;
  1. Check slave status
SHOW SLAVE STATUS;

Slave_IO_Running and Slave_SQL_Running column value should be ‘YES’

MYSQL service [Linux]

MySQL SHUTDOWN steps:

shell> sudo service mysqld stop

MySQL STARTUP steps:

shell> sudo service mysqld start

All Set !!

MySQL Server log Maintenance

As a part database administration, DBA has to take care of sub-components of database like server logs and has to plan for maintenance activity for these components regularly.

MySQL has various types of log i.e binary log, error log, slow query log, general log for different purposes. And after certain time these logs will grow and you will start seeing issues like low disk space, a large number of logs etc.

MySQL allows you to flush logs using flush logs command, next “How to rotate and clean up old MySQL logs? ”

Linux has a utility called “logrotate” , using logrotate we can implement log rotation for MySQL server logs.

Binary logs: This one is critical if you have replication setup, By enabling  expire_logs_days mysql variable you can manage cleanup and flush logs cmd will rotate binary log.

For General and Slow query that’s not the case, “flush log” cmd will flush the content from memory to respective log files, but it will not rotate these logs. logrotate by default configured and managed with OS root user.On a Linux (Red Hat) installation, you can use the mysql-log-rotate script for this. If you installed MySQL from an RPM distribution, this script should have been installed automatically. It kind of sample script for full implementation, let’s create a separate mysql-log-rotate script.

Prerequisites:

USER and Privileges:

CREATE USER  'logadmin'@'localhost' IDENTIFIED BY 'xyzpwd';
GRANT RELOAD ON *.* TO 'logadmin'@'localhost';

Secure user credentials using mysql_config_editor:

shell> mysql_config_editor set --login-path=logadmin_client --host=localhost --user=monitor --password                                                                                     

Enter password:<enter_mysql_logadmin_user_password>

NOTE: It will store user credentials info into .mylogin.cnf (This conf file will be get created under current OS user home directory)

mysql-log-rotate script

/PATH/log/mysqld.log /PATH/log/slow-query.log /PATH/log/general-query.log {
create 640 mysql mysql
rotate 5
daily
minsize 1M
notifempty
missingok
compress
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin
/usr/bin/mysqladmin --login-path=logadmin_client ping >/dev/null
then
/usr/bin/mysqladmin --login-path=logadmin_client flush-logs
fi
endscript
}

NOTE: Above script will flush logs 3 times since we have 3 logs in one code block.To flush log only at once you can create separate rotate code block for each log and add postrotate script only in the last rotation code block.

Automation:

00 03 * * * /usr/sbin/logrotate -s /PATH/log/logrotate.status /PATH/monitor/mysql-log-rotate.sh > /PATH/log/logrotate_cron.log 2>&1

Key points:

  • You can set rotation on the basis of SIZE, TIME or both. Explore logrotate option for more options.
  • -s /PATH/log/logrotate.status file will get create/update with log name and timestamp, Which will get use for next rotation on the basis of filename and timestamp it has.
  • -f, --force
    Tells logrotate to force the rotation, even if it doesn’t think
    this is necessary. Sometimes this is useful after adding new
    entries to logrotate, or if old log files have been removed by
    hand, as the new files will be created, and logging will continue correctly.

All Set !!