MySQL Replication Setup

  • Replication used to replicate data from the Master node to a slave node[Replica].
  • By default Replication is asynchronous.
  • It uses binary logs for reading data from the Master node and relay log on slave[Replica].
  • Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Replication Methods:

  • File-based Replication:

The traditional method is based on replicating events from the master’s binary log, and requires the log files and positions in them to be synchronized between master and slave.

  • GTID Replication:

The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files. Replication using GTIDs guarantees consistency between master and slave as long as all transactions committed on the master have also been applied on the slave.

 

Configuration variables for replication:

MASTER Conf:

server_id=145

gtid-mode=on

enforce-gtid-consistency=1

log-slave-updates=1

log-bin=/mysql/log/binary/mysql-bin145

relay-log=/mysql/log/relay/relay-bin145

relay_log_recovery= on

master_info_repository = TABLE

relay_log_info_repository = TABLE

SLAVE conf:

server_id=146

gtid-mode=on

enforce-gtid-consistency=1

log-slave-updates=1

log-bin=/mysql/log/binary/mysql-bin146

relay-log=/mysql/log/relay/relay-bin146

relay_log_recovery= on

master_info_repository = TABLE

relay_log_info_repository = TABLE

# don’t replicate the mysql database

replicate_wild_ignore_table = mysql.%

read-only

skip-slave-start

 

Replication setup:

  1. Create replication user on MASTER with replication privileges.

CREATE USER IF NOT EXISTS ‘rpluser’@’%’ IDENTIFIED BY ‘rpluser@1234’;

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘rpluser’@’%’;

  1. On SLAVE: setup replication as follows :

File-based Replication:

CHANGE MASTER TO MASTER_HOST='<MASTER_IP’,MASTER_USER=’rpluser’,MASTER_PASSWORD=’rpluser1234′,MASTER_PORT=3306,MASTER_LOG_FILE=’mysql-bin.000002′,MASTER_LOG_POS=690;

GTID Replication:

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’

Ref: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-howto.html

 

Advertisements

MySQL 5.7 Binary install on Linux

In Generic binary Installation method, we are extracting compressed tar files of MySQL and later we can do customization for MySQL setup as per our requirement. Like having different data directory, log directory etc.

MySQL Generic Binaries can be used to install MySQL on Unix/Linux platforms.

MySQL Installation has a dependency on the libaio library. Make sure that is there on a server before mysql install.

  • Create a mysql user and group, which we will use as a service account for the mysql server.

shell> groupadd mysql

shell> useradd -r -g mysql -s /bin/false mysql

  • Extract MySQL binaries TAR to mysql base directory for example in /mysql dir

  • Create require directories and set appropriate permissions.

shell> cd mysql

shell> mkdir data mysql-files log tmp socket pid

shell> chown -R mysql:mysql .

shell> chmod 750 mysql-files

Sample my.cnf

  • Add/update the following variables to the appropriate configuration section of my.cnf.

[mysqld]

#GENERAL

user = mysql

port = 3306

server_id = 1

skip-name-resolve

bind-address= 0.0.0.0

default_storage_engine = InnoDB

character_set_server = utf8

socket = /mysql/socket/mysql.sock

pid-file = /mysql/pid/mysqld.pid

# DATA STORAGE #

basedir= /mysql

datadir = /mysql/data

#INNODB

innodb_file_per_table = 1

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend

innodb_log_buffer_size = 16M

innodb_log_file_size = 100M

innodb-log-files-in-group = 3

innodb_flush_method = O_DIRECT

innodb_file_format = Barracuda

innodb_tmpdir= /mysql/tmp

#MyISAM

key_buffer_size = 64M

#Logging

log_error = /mysql/log/mysqld.log

master_info_repository = TABLE

relay_log_info_repository = TABLE

log-bin = /mysql/log/binary/mysql-bin1

relay-log = /mysql/log/relay/relay-bin1

relay_log_recovery = on

log-slave-updates = 1

expire_logs_days = 15

gtid-mode = on

enforce-gtid-consistency = 1

binlog_format = row

binlog_row_image = minimal

# General logs (only enable for debugging – it use too much I/o)

#general-log = on

#general-log-file = /mysql/log/general-query.log

# Slow query logs (optional)

slow_query_log = on

long_query_time= 3

slow_query_log_file = /mysql/log/slow-query.log

max_allowed_packet=300M

[mysql]

socket = /mysql/socket/mysql.sock

[client]

socket = /mysql/pid/mysql.sock

port = 3306

 

  • Initialize mysql server with my.cnf file configuration.

shell> bin/mysqld –defaults-file=/etc/my.cnf –initialize –user=mysql —basedir=/mysql/ –datadir=/mysql/data

NOTE: –defaults-file should be the first option always while using a command-line option.

START MySQL

shell> bin/mysqld -–defaults-file=/etc/my.cnf –user=mysql

NOTE: If it is not working try to start mysql with mysqld_safe, this for Syetem V platforms which needs mysqld_safe to run mysqld. 

shell> bin/mysqld_safe -–defaults-file=/etc/my.cnf –user=mysql

MySQL SHUTDOWN

shell> mysqladmin –u -p shutdown

Run MySQL as a service

shell> cp support-files/mysql.server /etc/init.d/mysql

MySQL START/STOP/STATUS

shell> /etc/init.d/mysql start/stop/status

Ref: https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

 

Replication handler error HA_ERR_KEY_NOT_FOUND and Memory Tables In Replication.

 Last_SQL_Error: Could not execute <Update/insert/delete>_rows event on table <dbname>.<tablename>; Can’t find record in ‘<tablename>’, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event’s master log <master binary log file name>, end_log_pos <master log file position>

This is one of the error for MySQL Replication when MASTER and SLAVE replica not in sync. The Reason for replication getting out of sync due to various reasons. But the common cause is particular table row or data not matched/missing on the slave while applying transaction received from MASTER. This transaction can be insert/update/delete type.

In this blog post will discuss support for MEMORY storage engine table with MySQL Replication and few examples with a possible solution. As per official MySQL DOC,  support for replication with memory storage engine is “LIMITED”.

The reason is limited because of the MEMORY storage engine functionality, which stores all data in memory other than table metadata and structure. And this data will get flushed from the memory at the time of MySQL server restart.

mem_rpl

Above diagram illustrating stages before and after mysqld service restart on the slave.

Possible Solutions to bring backup replication in sync: [ Without downtime]

Option 1: Set slave_exec_mode to IDEMPOTENT It will suppress duplicate-key and no-key-found errors. Default slave_exec_mode is STRICT  means no such suppression takes place.

Option 2: Skip memory tables from replication using replication filter “replicate_ignore_table”. From 5.7.3 we can set replication filters dynamically without mysql service restart.

Option 3: If you want to keep data persistently then better to covert this table from MEMORY to InnoDB storage engine type using ALTER TABLE… ENGINE=Innodb command.

Selecting one of the options is depending on what we really need. For example, rather than skipping transaction one by one we can set Set slave_exec_mode to IDEMPOTENT  for the time being to get replication in sync and after that set it back to STRICT again.

All set !!

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

In my preview blog posts, I have written about this exam topics and this blog post will cover remaining topics.

Let start with it,

MySQL Security

This MySQL Security section covers general MySQL server inbuild option plus enterprise tools offered by MySQL enterprise edition.

  • Execute MySQL Authorization and Privilege Management

Reference doc: MySQL privilege and access control

  • Manage MySQL Password Policies

Explore for password_validation plugin and Password Expiration policies for MySQL USER.

Leverage MySQL Enterprise Monitor

  • Explain MySQL Enterprise Monitor benefits and features
  • Describe the topology of the MySQL Enterprise Monitor (Service Manager, Agents)
  • Manage MySQL Query Analyzer
  • Manage the customizability of MySQL Enterprise Advisors

Reference Link: MySQL Enterprise Monitor

Leverage MySQL Backup

Logical (mysqldump) Backup import and export from MySQL Workbench.

  • Describe the MySQL Enterprise Backup process
  • Install a MySQL Enterprise Backup
  • Configure MySQL backups
  • Configure MySQL Encryption and Compression

Reference doc: MySQL Enterprise Backup

MySQL Overview of High Availability and Replication

  • Describe MySQL High Availability solutions
  • Explain Replication
  • Set up MySQL Replication using global transaction identifiers (GTID’s) and binlog
  • Set up MySQL Cluster
  • Explain Replication utilities
  • Deploy High Availability and Scalability features

To prepare these topics, learn about MySQL replication in depth including GTID replication knowledge like how to enable GTID, setup replication, and troubleshooting.

GTID Replication BLOG POST

MySQL Cluster is another MySQL High Availability solutions.

Learn about Replication Utilities:

  1. mysqlrpladmin — Administration utility for MySQL replication
  2. mysqlfailover — Automatic replication health monitoring and failover
  3. mysqlreplicate — Set Up and Start Replication Between Two Servers
  4. mysqlrplms — Set Up and Start Replication from a Slave to Multiple Masters
  5. mysqlrplcheck — Check Replication Prerequisites
  6. mysqlrplshow — Show Slaves for Master Server

MySQL Cloud Service

  • Describe Cloud Services options
  • Establish Cloud Service connection via Secure Shell (SSH)
  • Describe security rules within Oracle Compute Cloud Service
  • Configure security rules within Oracle Compute Cloud Service
  • Troubleshoot Cloud Service connections issues
  • Describe alerts and notification options using Cloud Service
  • Configure MySQL Cloud Service Backup

For MySQL Cloud Service, I read cloud documents provided by Oracle and also did practice on Oracle MySQL Could server.

These include MySQL implementation options, security, access from SSH, Backup -Restore on cloud container / local disk. How to patch MySQL server, configuring notification for managing MySQL cloud service.

Reference document:  MySQL Cloud Service

And that’s all !!

Do study everything in detail and practice is must for these topics.

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…

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

Continuation of my previous 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 CREATE 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 with MySQL Cloud Service. Best study resource for this exam is “MySQL official documentation”. Along with MySQL Enterprise topics given in exam topics, 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/

 

Good luck with your studies 🙂