24 Sep

Providing Friday Proofs to MySQL Consultants

In the MySQL world, I am surrounded by some of the best database consultant, it brings them immense joy about having a “Friday”, so much so that they cannot believe that it actually is a Friday. Regardless of what they see on their watches and calendars, it becomes a crucial task of the day to prove it. Prove it technically that it actually is a Friday.

This blog post is my attempt to share the proofs of existence of a “Friday”.

Using MySQL datetime function: dayname

MySQL [(Kedar)]> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Friday         |
+----------------+
1 row in set (0.000 sec)

Running system command from MySQL prompt

13 Jul

The Low Hanging Fruits of MySQL – Percona Live Community 2022

MySQL is one of the top used database system and with highly active community. Though without proper configuration you may not squeeze the most out of it.
This will be a talk about the low hanging fruits to get the best outcome preferably in a short time or with small efforts. This will include configuration, performance and the most common problem scenarios that you can relate, examine and implement.

I got opportunity to talk at Percona Live Community 2022. Please find the recording and slides below.

The-Low-Hanging-Fruits-Of-MySQL-KedarVaijanapurkar-v1 Download

19 Aug

2 many dots can break your replication only once

Two or more dots in your relay log or binary log names can break replication; but worry not, it will only do it once. If you request to start again, it will work. That’s it, that’s the blog. Should you choose to spend some more time, go ahead.

A new MySQL 5.7 GTID replication chain was being constructed and fail-overs were being tested while an oddity of MySQL replication revealed itself. The Orchestrator graceful fail-over was not able to attach the replica and reporting an error.

Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Every possibility was investigated:

10 Apr

Still have MySQL 5.6 variable binlog_error_action as IGNORE_ERROR?

Recently, we were affected by an ignored configuration option introduced in MySQL 5.6. This incident caused us to perform extended planning of downtime and the rebuilding of the slave. In this post, we’ll discuss our encounter with binlog_error_action and likely bad default.

The incident started with an alert from our monitoring platform that a check failed to verify a MySQL master’s binary log coordinates. My colleague working from the other side of the globe observed the following:

mysql> show master status;
    Empty set (0.00 sec)
    mysql> show binary logs;
    ERROR 1381 (HY000): You are not using binary logging

Interestingly, the MySQL slave was still reporting that everything was okay!

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000186
          Read_Master_Log_Pos: 1069066810
               Relay_Log_File: mysqld-relay-bin.000245
                Relay_Log_Pos: 1069067004
        Relay_Master_Log_File: mysql-bin.000186
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1069066810
              Relay_Log_Space: 1069067296
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 23294781
                  Master_UUID: 7e2e60eb-39fb-11ea-a248-005056b589fd
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 7e2e60eb-39fb-11ea-a248-005056b589fd:50961813-81101493
            Executed_Gtid_Set: 2366bbb5-39fb-11ea-a246-005056b5f82f:1-818,
7e2e60eb-39fb-11ea-a248-005056b589fd:1-81101493
                Auto_Position: 1
1 row in set (0.00 sec)

Guess what my colleague saw in the error log?

08 Oct

ProxySQL Configuration File & Startup Process Explained

When learning ProxySQL, we have seen some confusion around the configuration and especially around the usage of the configuration file. In this post, we will explain how ProxySQL treats its internal configuration and its configuration file.

The Proxysql.Cnf

For the initial startup, the configuration is loaded from the /etc/proxysql.cnf file. At this time, the configuration file will be parsed and the settings will be stored in the embedded SQLite database. From this point onwards, the settings will be accessed from the SQLite database.

However, there are four configuration settings which are always parsed from the config file, even when the SQLite database is present. This places a requirement for the configuration file to always be in the place, if missing, ProxySQL terminates.

datadir: The path of ProxySQL datadir which stores database file, logs.
restart_on_missing_heartbeats (new in 1.4.4): If MySQL threads miss restart_on_missing_heartbeats (number of) heartbeats, proxysql will raise a SIGABRT signal and restart. Default is 10.
execute_on_exit_failure (new in 1.4.4): If set, ProxySQL’s parent process will execute the defined script every time ProxySQL crashes. It is recommended to use this setting to generate an alert or log the event.
errorlog (new in 2.0.0): The file to be used as error-log. Defaults to ${datadir}/proxysql.log