MySQL fails to start and no errors in the log? Check out this possible reason!

02 Friday Aug 2019

Posted by in MySQL, MySQL Operations

≈ 2 Comments

Tags

, ,

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! Continue reading →

Backing up users and privileges in MySQL

14 Monday May 2018

Posted by in Backup and Recovery, MySQL, MySQL Operations

≈ 9 Comments

Tags

, , , ,

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
Continue reading →

Handy stored procedure for regular DBA tasks

24 Monday Apr 2017

Posted by in MySQL, MySQL Cluster, MySQL Operations

≈ 3 Comments

Tags

, , , , , , , , ,

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!

Continue reading →

Replication filter per channel is now available in MySQL!

12 Wednesday Apr 2017

Posted by in MySQL, MySQL Replication

≈ 2 Comments

Tags

, , , , ,

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.
Continue reading →

What is the default sharding key in MySQL Cluster?

09 Monday Jan 2017

Posted by in High Availability (HA), MySQL, MySQL Cluster, Sharding

≈ 3 Comments

Tags

, , , ,

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. Continue reading →

Partitions number in MySQL Cluster

16 Friday Dec 2016

Posted by in High Availability (HA), MySQL, MySQL Cluster, Sharding

≈ 6 Comments

Tags

, , , ,

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 test cases to show that the number of partitions in the cluster has a relation with the number of LDM threads as well as the number of data nodes. Continue reading →

Can we set Replication Filters per channel in Multi-Source Replication?

12 Tuesday Apr 2016

Posted by in High Availability (HA), MySQL, MySQL Replication

≈ 4 Comments

Tags

, , , ,

Multi-Source Replication is one of the awesome features in MySQL 5.7 which allows the slave to get the replication streams from multiple masters by having a replication channel for each master.
But what about the replication filters? Can we set replication filters per channel in the multi-source replication?

In brief, replication filtering is a way used to eliminate part of the master’s events (not) to be replicated to the slaves (Also known as Partial Replication). This can be done on either master (using --binlog-do-db and --binlog-ignore-db options) or slaves (using --replicate-* options) while it is not recommended on the master anyway at least to provide Point in Time Recovery (PiTR).

Worth mentioning here that changing the replication filters online is yet another very nice improvement in MySQL 5.7 as well in which we have to stop only the SLAVE SQL_THREAD – instead of restarting the whole MySQL service – to change the replication filters by using “CHANGE REPLICATION FILTER” statement. Continue reading →

Useful queries on MySQL information_schema

15 Wednesday Jul 2015

Posted by in MySQL, MySQL Operations

≈ 8 Comments

Tags

, , , , ,

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.

Continue reading →

MySQL High Available with MHA

01 Monday Jun 2015

Posted by in High Availability (HA), MySQL, MySQL Operations

≈ 18 Comments

Tags

, , ,

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 use it in your system and even no additional servers are required.
  2. No system changes or DB redesign are required to use MHA, keep your system as it is and just use the tool! (Assuming that MySQL replication is already being used).
  3. MHA is easy to implement and its usage is simple.

Continue reading →

Spider for MySQL – Implementation

04 Monday May 2015

Posted by in MySQL, Sharding

≈ 5 Comments

Tags

, , ,

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):

Continue reading →

Percona Live MySQL & Expo Conference: GTID Replication slides

22 Wednesday Apr 2015

Posted by in High Availability (HA), MySQL, MySQL Replication

≈ 2 Comments

Tags

, ,

If you couldn’t have the chance to attend my session “GTID Replication – Implementation and Troubleshooting” at Percona Live MySQL & Expo Conference in Santa Clara April 13-16, 2015, the slides of my presentation are now available.
The talk was mainly about the new feature in MySQL 5.6 “GTID”, what is the concept, benefits, GTID replication implementation and troubleshooting and how to perform the migration from classic replication to GTID replication in both MySQL 5.6 and 5.7.
If you have any question, feel free to contact me 🙂

Spider for MySQL – Overview

03 Friday Apr 2015

Posted by in MySQL, Sharding

≈ 3 Comments

Tags

, , ,

Having big tables is one of the expected database problems, especially, for the fast growing database systems. In fact, big tables itself is not a problem but with big tables, the following problems are strongly expected:

  1. Retrieving data from big tables is so slow.
  2. It is a very hard job to maintain those tables like adding/removing an index, adding/dropping/modifying a column, … etc.
  3. System resources, especially, the IO system might not be able to handle such huge traffic of writes and reads.
  4. When it comes to the reporting queries, it might be a horrible nightmare!
  5. Always cause disk space problem.

Continue reading →

Speaking at MySQL Conference & Expo April 13-16 2015

17 Tuesday Mar 2015

Posted by in MySQL

≈ 1 Comment

Tags

, ,

Percona Live MySQL Conference and Expo, April 13-16, 2015
In a few weeks, I’ll be speaking at MySQL Conference & Expo about the new major feature in MySQL 5.6, Global Transaction ID (GTID). I’ll explain what is GTID and how to implement a GTID Replication and troubleshoot most of the common issues that might be faced in GTID Replication.
Also, I’ll talk in brief about how to perform the migration from Classic to GTID replication in MySQL 5.6 and the online migration in MySQL 5.7 as well.
My talk is titled “GTID REPLICATION – IMPLEMENTATION AND TROUBLESHOOTING“, more information about my talk can be checked out here.
The conference will be held in April 13-16 2015 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.
There are a lot of cool talks in the conference, and you can get a 10% off by using the discount code “SeeMeSpeak“, so if you didn’t register yet, what are you waiting for!!

Looking forward to meeting you there 🙂

Starting to Blogging Again!!

13 Friday Mar 2015

Posted by in MySQL

≈ Leave a comment

Tags

, ,

I’ve been working on MySQL since 2008 but I didn’t write any technical blogs until I joined FromDual GmbH and my first blog was published in October 24th 2013! (You can find all my blogs at FromDual here).
I liked writing blogs for many reasons but mainly for the following two:

  1. Sharing knowledge and contributing with MySQL community to make the information easier and available for all.
  2. Very good reference for myself to do my daily work more efficiently.

Now, I’m eager to blog again so I decided to create my own blog, MySQL Step-by-Step Blog.
The idea of choosing the name “Step-by-Step” is I like to explain all steps needed for doing a task – you may have discovered that already in my blogs at FromDual – so, I’m hoping that my blogs will not only be useful for advanced MySQL users but also for beginners as well.

MySQL Step-By-Step Blog is still under constructions, I need to prepare it well but at least for now, I’ve a place where I can publish my blogs 🙂

Looking forward to contributing again to the MySQL community by writing useful blogs.

Abdel-Mawla Gharieb