Category Archives: MySQL Operations

Posts about MySQL Operations

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 →

Advertisement

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 →

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 →