How to identify MySQL replication slave lag

Let's first understand how the standard asynchronous
MySQL replication works at a high level.

For simplicity, let's assume that there is a Master and one Slave node.
And MySQL version 5.6

Thus, the master maintains a special log called binary log which stores replication events.
This binary log file stores data that the replication slave will be reading later.

MySQL can use 3 formats for writing data to the binary log:

Statement-based - the SQL query itself is written to the binary log.
Row-based - the row changes are logged in the binary log.

Mixed - statement-based logging is used by default, but the logging mode
             switches automatically to row-based in certain cases.

The slave node has 2 processes/threads that are responsible for the replication:

IO Thread  - a process that pulls data from Master's binary log over the network
                    and writes the data/changes to a local file called a relay log

SQL Thread - a process that continuously reads the relay log
                     and applies the changes to the slave database.

So, in asynchronous replication, the slave node requests a packet from the master,
the master sends the packet and doesn't check if the slave received the data packet or not.

Also as you can see there is only one thread that reads the binary log from the master.

The slave node keeps track of the position in the master's binlog of the last event applied on the slave. This allows the slave server to re-connect and resume from where it left off after replication has been temporarily stopped.

When you see a replication lag, the first thing you need to understand is which of the two
replication threads(IO Thread or SQL Thread) is behind

For this, execute the commands below :

On the master: show master status;

On the slave:   show slave status;

To check where IO Thread currently is, compare the values from the following fields:

From the output of the show master status;

      File -  for example: mysql-bin-changelog.549445

From the output of the show slave status;

      Master_Log_File – last file copied from the master
                                  for example: mysql-bin-changelog.549440

Compare the numeric value from the "File" field from master status and
the value of "master_log_file" from slave status.
The difference will be how many binlogs the salve is behind.(IO Thread)

To get the current status of the SQL Thread check the following values:

Read_Master_Log_Pos – holds until what position the binary log from master

                                      is copied over to the relay log on the slave.

Exec_Master_Log_Pos – holds position from master binary log is being
                                     executed by SQL thread.

You can calculate the lag of SQL_Thread like this:
Read_Master_Log_Pos - Exec_Master_Log_Pos

Also, the "Seconds_Behind_Master" field should give you some sort of indication.

Possible reasons for IO_Thread lag:

- Slow network between master/slave  - See if enabling 'slave_compressed_protocol' helps

- Binary logging on the slave - It is IO intensive, you can disable it unless you need
                                               it for a point in time recovery.

Possible reasons for SQL Thread lag:

- Tables without PK/Unique key when you use row based binlog format

- Long-running queries on the primary DB instance

Reference:
https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-high-replica-lag/

Image credit:
https://medium.com/swlh/zero-downtime-master-slave-replication-4f2814138edf

MySQL guide for SQL Server developers/DBAs - Part 1

Do you know what "My" means in MySQL?
"My" is the name of one of the co-founders Michael Widenius’s daughter.

If you are a SQL Server developer/DBA and need to switch to MySQL,
here are some useful gotchas:

	SQL Server	MySQL
Last inserted identity value	@@IDENTITY SCOPE_IDENTITY() Returns the last automatically generated value	LAST_INSERT_ID() Returns the first  (NOT LAST) automatically generated value. so, be careful when you insert multiple rows.
Nested transactions	Supported	Not supported. START TRANSACTION statement will implicitly commit any current transaction
Local variables	Supported everywhere: in SPs and in batches/scripts	There are local variables and system variables. Local variables can be used only inside SPs, you cannot use them in script/batch.
if / while statements	Supported everywhere	IF / WHILE statements are not supported in scripts/batches. They can be used only in SPs/Functions/Triggers
Alter stored procedure	alter stored procedure statement is supported	not supported, if you need to alter SP, you need to drop it and recreate it
Stored procedures parameters	Supports default values and can be named referenced	no default values and cannot be named referenced
PRINT statement	Supported	Not supported
Division by zero	select 1/0 will return an error	select 1/0 will return NULL
Temp tables	a temp table can be referenced as much as you want in a query	You cannot refer to a TEMPORARY table more than once in the same query
Row_Number()	Windows functions supported	not supported until version 8, can be emulated as SET @row_number = 0; SELECT (@row_number:=@row_number + 1) RowId
Activity monitoring	sp_who2, sp_whoisactive, sys.dm_exec_sessions, sys.dm_exec_requests	show processlist, show full processlist; SELECT * FROM information_schema.processlist; Tables under performance_schema: -events_statements_history_long - events_statements_history - events_statements_current, mysql.slow_log
Open Transactions	dbcc opentran or SELECT * FROM sys.sysprocesses WHERE open_tran = 1	select * from INFORMATION_SCHEMA.INNODB_TRX; Use this query to see which transactions are waiting and which transactions are blocking them: SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

 Enjoy :)

MySQL - ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

Let`s assume have a MySQL 5.6 Server installed on a Windows machine.

and you get the error below when trying to set time_zone to 'UTC' 

or a different time zone, follow the steps below to resolve this issue.

SET time_zone = 'UTC';

ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

The issue is you do not have the time zones data installed,

to install the time zones on a Windows machine, you will need:

1) Download "timezone_2020a_posix.zip - POSIX standard"

     from https://dev.mysql.com/downloads/timezones.html

2) Stop the MySQL service

3) Extract the files from the downloaded file 

    and copy them with overwrite

    to C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql

    

    Yes, to C:\ProgramData\.. and not to C:\Program Files (x86)\MySQL\...

4) Start the MySQL service

Enjoy:)