Some time ago, I was building a new MySQL DB server (5.7.25) and like all DBAs, I have a template of my.cnf that I use for the new instances after changing a few variables based on the instance resources, replication … etc. I had MySQL installed but I struggled on having the service started!

MySQL failed to start, no errors were printed at all in the MySQL error log – or the log was not created from the first place – even no errors in the system log and I had no clue what was going on!

After some digging in, I found the bad guy! The variable secure_file_priv referred to a directory that didn’t exist. When I had the directory created, everything was fine and the service started.

I tried to repeat the same scenario in MySQL 8 and it was much better. The error log indicated the root cause of the issue as below:
2019-03-25T23:39:59.810992Z 0 [ERROR] [MY-010095] [Server] Failed …

There are two simple ways to backup only the users and privileges in MySQL:

1- Using mysqlpump utility (as create user and grant statements):

[shell ~]$ mysqlpump -uUSER -p --exclude-databases=% --add-drop-user --users > /tmp/pump-all-users_privileges-timestamp.sql
Dump completed in 1364 milliseconds

Sample output:

[shell ~]$ head /tmp/pump-all-users_privileges-timestamp.sql

-- Dump created by MySQL pump utility, version: 5.7.21-20, Linux (x86_64)
-- Dump start time: Sun May 13 23:30:49 2018
-- Server version: 5.7.21
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET …

As a stored procedures fan, I use MySQL stored procedures to get some of my DBA tasks accomplished. To make it simple, I have a template stored procedure that can be customized for several purposes.
The template syntax contains cursor, continue handler, loop, if condition and prepared statement. Thought it may be useful for others – at least, who are searching for the MySQL Stored Procedure syntax – so I’m publishing this post!

Use case: Reorganize tables partition:

In MySQL Cluster, it is required to reorganize all NDB tables’ partitions after adding new data nodes to rebalance the data across all nodes. Also the tables need to be optimized afterwards to reclaim the memory space. For this task I use the following procedure:

DROP PROCEDURE IF EXISTS reorganize_tables;
DELIMITER //
CREATE PROCEDURE reorganize_tables (IN db_name VARCHAR(50))
BEGIN


…

On the same day last year, I wrote a post about replication filters in MySQL Multi Source Replication and whether we can set the filters per replication channel or not (Bug #80843). My feature request – as well as some others – has been implemented in MySQL 8.0.1. Thanks Oracle for the implementation. (Full list of changes can be checked out here)

In this post, I’ll demonstrate how to set replication filters per channel in MySQL Multi Source Replication.

Setting up and configuring Multi Source …

MySQL Cluster does an automatic sharding/partitioning to the tables across data nodes, enabling databases to scale horizontally to serve read and write-intensive workloads, but what is the default sharding key used in partitioning the data?
According to the recent update (Oct, 2016) of the MySQL Cluster white paper, primary key is the default sharding key:

By default, sharding is based on hashing of the primary key, which generally leads to a more even distribution of data and queries across the cluster than alternative approaches such as range partitioning.

However, that is not the case in all MySQL Cluster versions so far!
In this post, I’ll do some test cases on MySQL Cluster (of 4 datanodes) to confirm the default sharding key.

Testing on MySQL Cluster 7.2.26 …

As stated in the MySQL Cluster documentation:

Partition.  This is a portion of the data stored by the cluster. There are as many cluster partitions as nodes participating in the cluster. Each node is responsible for keeping at least one copy of any partitions assigned to it (that is, at least one replica) available to the cluster.

According to my understanding for the previous paragraph, if we have a cluster of 6 datanodes we should have 6 partitions for each NDB table. I claim that this is not true for all cases – at least, after the introduction of ndbmtd (Multi-Threaded Daemon) in MySQL Cluster 7.2 .
In this post, I’ll do some …

Whether for a maintenance, applying non dynamic config changes, MySQL upgrade or other many reasons, a MySQL shutdown/restart is required.
In this post I’ll list some of the best practices before shutting MySQL down to make it clean and fast which in turn, will lead to fast and safe start!

1. Double check the instance you are going to shutdown!!
   First of all, and before doing anything confirm first the instance you are going to shutdown. You definitely, don’t want to shutdown a wrong MySQL instance by mistake, especially, when you’re working on production environments.
2. Stop Replication:
   Although MySQL stops the replication automatically in the shutting down process but if it didn’t stop for any reason before the timeout is reached, it will be killed. So, if that server is a slave, it’s better to stop the replication threads first …

MySQL information_schema comes with useful information about the database instance, status, … etc. which is needed for daily DBA work.
There are some simple queries on the information_schema that I use on my daily basis in which I’m writing this post for my reference and maybe a good reference for someone else too …

Finding tables without Primary or Unique Keys:

PKs are so important, especially, for InnoDB tables as MySQL uses PKs as a clustered index and having no PKs might lead to severe performance problems.

Also having no PKs is one of the main causes of slave lagging problems mainly when using RBR (Row-Based Replication), e.g. if a delete statement on the master will delete 1 million rows on a table without PK, a full table scan will take place. This “might” not be a problem on the master but on the slave 1 million full table scan will take place – because changes to the individual rows are being …

Providing a suitable High Availability (HA) solution for each database system is one of the challenging tasks for a DBA and here we have to answer some questions like the following ones:

1. What is the cost for that HA solution?
2. Is it required to change the system structure or the DB design to use that HA solution?
3. Is it complicate to understand, use or maintain ?

Choosing the suitable HA solution for each system will depend on the answers of such questions …
In this post, I’m going to write about MySQL Master High Availability MHA as a nice tool which provides HA for MySQL by performing automatic fail-over or fast online master switching with almost no downtime!

Before going through more details about MHA, let’s first answer the previous questions:

1. MHA is a free opensource tool, no cost to …

In a previous post, I wrote an overview about Spider for MySQL with its advantages and disadvantages. Now I’ll go through a simple example demonstrating how to implement Spider for MySQL.

System information: MySQL instances information (shards):

• Server: 3 VMs (here in after will be mentioned as mysqla “192.168.56.51”, mysqlb “192.168.56.52” and mysqlc “192.168.56.53”).
• OS: CentOS 6.4
• RAM: 512MB
• MySQL version: 5.5.43 (tar-balls)
• Download link: http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.43-linux2.6-x86_64.tar.gz

…