Sudeep's BLOG

Tech Notes:- How to verify if your connection to MySQL is using encryption or not

2020-10-02T13:52:00.005+10:00

It is often a confusion whether the client connection or the replication slave is using encryption while connecting to the MySQL server. The below query can tell you whether your connection is using encryption or not

SELECT sbt.variable_value AS tls_version, t2.variable_value AS cipher, processlist_user AS user, processlist_host AS host FROM performance_schema.status_by_thread AS sbt JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id WHERE sbt.variable_name = 'Ssl_version' and t2.variable_name = 'Ssl_cipher' ORDER BY tls_version;

Of course, there are other options to find out. But this one will list all the connections which use or do not use encryption. This worked for me on MySQL 5.7. I have not tried this on other versions and for sure this does not work on some of the older versions of MySQL where we do not have performance_schema.

Tech Notes :- ssh failure Offending RSA key in /var/lib/sss/pubconf/known_hosts

2020-08-24T08:03:00.005+10:00

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the ECDSA key sent by the remote host is
SHA256:gEFs4gmKdgDsV2n3UhB/lNhZ/sLSM8q+5VIbl+D1S3c.
Please contact your system administrator.
Add correct host key in /dev/null to get rid of this message.
Offending RSA key in /var/lib/sss/pubconf/known_hosts:8
Password authentication is disabled to avoid man-in-the-middle attacks.
Keyboard-interactive authentication is disabled to avoid man-in-the-middle attacks.

This is not a normal ssh error that can be fixed by editing ~/.ssh/known_hosts and removing the relevant key entries or by using the command ssh-keygen -f ~/.ssh/known_hosts -R <host>. If you try to do it, the key will come back and ends up with the same error.

Another option that we are tempted to try is to remove the offending key from /var/lib/sss/pubconf/known_hosts. This also will end in failure

Most probably this error occurs when the server was part of IPA setup and you just re-installed the server without removing the relevant configuration from the IPA server.In this case IPA server will still have the keys associated with the server before it got re-installed and we have to first remove the keys from the IPA server.

You can do that from the IPA Identity Management Web UI by browsing to Hosts--> <yourhostname> -->Settings. Now scroll down to the SSH Public keys section and then remove the keys and save it.

Now you should be able to login to your server using ssh as usual.

Tech Notes:- Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1

2020-07-06T18:15:00.000+10:00

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file ../binlogs/mysql-bin.000006, position 194.; the first event '' at 4, the last event read from '../binlogs/mysql-bin.000006' at 259, the last byte read from '../binlogs/mysql-bin.000006' at 259.'
Last_SQL_Errno: 1396
Master_UUID: ab7c6739-be64-11ea-b478-080027fd2c56

Retrieved_Gtid_Set: ab7c6739-be64-11ea-b478-080027fd2c56:1-29

Executed_Gtid_Set: 846dc749-bf34-11ea-852d-08002749f9b4:1-1032,

ab7c6739-be64-11ea-b478-080027fd2c56:1-4:30-114202

On master check the binary log 'mysql-bin.000006' at position 194

mysql> show binlog events in 'mysql-bin.000006' from 4 limit 5;
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+
| mysql-bin.000006 | 4 | Format_desc | 55 | 123 | Server ver: 5.7.30-33-log, Binlog ver: 4 |
| mysql-bin.000006 | 123 | Previous_gtids | 55 | 194 | ab7c6739-be64-11ea-b478-080027fd2c56:1-29 |

+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+

The solution is to skip the transactions associated with GTID ab7c6739-be64-11ea-b478-080027fd2c56:1-29
after making sure that this has been replicated already if not, we need to find and apply the transactions manually.

SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:1';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:2';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:3';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:4';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:5';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:6';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:7';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:8';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:9';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:10';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:11';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:12';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:13';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:14';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:15';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:16';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:17';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:18';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:19';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:20';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:21';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:22';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:23';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:24';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:25';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:26';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:27';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:28';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:29';
BEGIN;COMMIT;
SET GTID_NEXT='AUTOMATIC';

A script may be programmed to generate the above statements
Now we should be able to start the slave
start slave;

Tech Notes:- Enabling back GTID based replication on-line on a server which was started with GTID mode on , did some transactions and then switched it to GTID mode off

2020-07-06T14:33:00.000+10:00

This is a special scenario were two MySQL 5.7 servers were started with GTID_MODE=ON, executed some transactions like creating users and granting privileges, and then the GTID_MODE was switched off on both.

Now we want to configure these servers as master-slave, enable the traditional method of replication and then later switch to GTID based replication.

Here are the challenges.

1. We already created some transactions(creating users) on the master which means we have some GTID based transactions already logged in the binary logs as you can see below from the master binary logs
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 1037 |
| mysql-bin.000003 | 6798 |
| mysql-bin.000004 | 217 |
| mysql-bin.000005 | 217 |
| mysql-bin.000006 | 825 |
| mysql-bin.000007 | 217 |
| mysql-bin.000008 | 378 |
| mysql-bin.000009 | 11592653 |
| mysql-bin.000010 | 36338956 |
| mysql-bin.000011 | 68473681 |
| mysql-bin.000012 | 198652424 |
+------------------+-----------+
12 rows in set (0.04 sec)

mysql> show binlog events in 'mysql-bin.000001';

+------------------+-----+----------------+-----------+-------------+------------------------------------------+

+------------------+-----+----------------+-----------+-------------+------------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 55 | 123 | Server ver: 5.7.30-33-log, Binlog ver: 4 |

| mysql-bin.000001 | 123 | Previous_gtids | 55 | 154 | |

| mysql-bin.000001 | 154 | Stop | 55 | 177 | |

+------------------+-----+----------------+-----------+-------------+------------------------------------------+

3 rows in set (0.02 sec)

mysql> show binlog events in 'mysql-bin.000002';

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

| mysql-bin.000002 | 4 | Format_desc | 55 | 123 | Server ver: 5.7.30-33-log, Binlog ver: 4 |

| mysql-bin.000002 | 123 | Previous_gtids | 55 | 154 | |

| mysql-bin.000002 | 154 | Gtid | 55 | 219 | SET @@SESSION.GTID_NEXT= 'ab7c6739-be64-11ea-b478-080027fd2c56:1' |

| mysql-bin.000002 | 219 | Query | 55 | 287 | BEGIN |

| mysql-bin.000002 | 287 | Table_map | 55 | 459 | table_id: 99 (mysql.user) |

| mysql-bin.000002 | 459 | Update_rows | 55 | 793 | table_id: 99 flags: STMT_END_F |

| mysql-bin.000002 | 793 | Query | 55 | 862 | COMMIT |

| mysql-bin.000002 | 862 | Gtid | 55 | 927 | SET @@SESSION.GTID_NEXT= 'ab7c6739-be64-11ea-b478-080027fd2c56:2' |

| mysql-bin.000002 | 927 | Query | 55 | 1014 | FLUSH PRIVILEGES |

| mysql-bin.000002 | 1014 | Stop | 55 | 1037 | |

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

10 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000003';

+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+

| mysql-bin.000003 | 4 | Format_desc | 55 | 123 | Server ver: 5.7.30-33-log, Binlog ver: 4 |

| mysql-bin.000003 | 123 | Previous_gtids | 55 | 194 | ab7c6739-be64-11ea-b478-080027fd2c56:1-2 |

| mysql-bin.000003 | 194 | Gtid | 55 | 259 | SET @@SESSION.GTID_NEXT= 'ab7c6739-be64-11ea-b478-080027fd2c56:3' |

| mysql-bin.000003 | 259 | Query | 55 | 464 | CREATE USER 'slave'@'db-%.example.com' IDENTIFIED WITH 'mysql_native_password' AS '*B60C3C82CCEBA73F2BDC69601B65DFBAB435A2DA' |

| mysql-bin.000003 | 464 | Gtid | 55 | 529 | SET @@SESSION.GTID_NEXT= 'ab7c6739-be64-11ea-b478-080027fd2c56:4' |

| mysql-bin.000003 | 529 | Query | 55 | 685 | GRANT REPLICATION SLAVE ON *.* TO 'slave'@'db-%.example.com' |

2. After we switched the GTID mode to off, we have anonymous transactions in the binlogs on master

mysql> show binlog events in 'mysql-bin.000006';

+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+

+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+

| mysql-bin.000006 | 4 | Format_desc | 55 | 123 | Server ver: 5.7.30-33-log, Binlog ver: 4 |

| mysql-bin.000006 | 123 | Previous_gtids | 55 | 194 | ab7c6739-be64-11ea-b478-080027fd2c56:1-29 |

| mysql-bin.000006 | 194 | Anonymous_Gtid | 55 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000006 | 259 | Query | 55 | 444 | CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*0E840B1E4D3B2DDE222EA13524CA92099785A3E4' |

| mysql-bin.000006 | 444 | Anonymous_Gtid | 55 | 509 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000006 | 509 | Query | 55 | 645 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' |

| mysql-bin.000006 | 645 | Anonymous_Gtid | 55 | 710 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000006 | 710 | Query | 55 | 802 | flush privileges |

| mysql-bin.000006 | 802 | Stop | 55 | 825 |

+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+

9 rows in set (0.00 sec)

3. At this point, we are adding a slave which will connect and replicate using the traditional approach (MASTER_AUTO_POSITION = 0)

4. Now on both master and slave, we enable GTID mode

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: db-55.example.com

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 16689209

Relay_Log_File: mysqld-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Please note that the slave is now at mysql-bin.000011

5.Switch the method of replication to use GTID(MASTER_AUTO_POSITION = 1)

At this stage, we get an error in the slave

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file ../binlogs/mysql-bin.000006, position 194.; the first event '' at 4, the last event read from '../binlogs/mysql-bin.000006' at 259, the last byte read from '../binlogs/mysql-bin.000006' at 259.'

Last_SQL_Errno: 1396

Last_SQL_Error: Error 'Operation CREATE USER failed for 'test@'127.0.0.1'' on query. Default database: ''. Query: 'CREATE USER 'test'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*47FFA1D679CD8C02395ED76C3640A48AA3C3411C''

The reason for this error is that when we tried to switch to GTID based replication, the slave started reading all the binlogs and started with the transactions from binlogs which were already replicated using the traditional method(note that the slave was at mysql-bin.000011 before switching.

Now, how do we fix this?

First find out the UUID of the master by issuing the below command on the master which should match the master UUID in show slave status

mysql> show variables like '%uuid%';

+---------------+--------------------------------------+

| Variable_name | Value |

+---------------+--------------------------------------+

| server_uuid | ab7c6739-be64-11ea-b478-080027fd2c56 |

+---------------+--------------------------------------+

1 row in set (0.01 sec)

Now, look at the show slave status

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: db-55.example.com

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 194

Relay_Log_File: mysqld-relay-bin.000003

Relay_Log_Pos: 898

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1396

Last_Error: Error 'Operation CREATE USER failed for 'test'@'127.0.0.1'' on query. Default database: ''. Query: 'CREATE USER 'test'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*47FFA1D679CD8C02395ED76C3640A48AA3C3411C''

Skip_Counter: 0

Exec_Master_Log_Pos: 685

Relay_Log_Space: 9652

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1236

Last_SQL_Errno: 1396

Replicate_Ignore_Server_Ids:

Master_Server_Id: 55

Master_UUID: ab7c6739-be64-11ea-b478-080027fd2c56

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 200706 13:19:15

Last_SQL_Error_Timestamp: 200706 13:19:15

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: ab7c6739-be64-11ea-b478-080027fd2c56:1-29

Executed_Gtid_Set: 846dc749-bf34-11ea-852d-08002749f9b4:1-29,

ab7c6739-be64-11ea-b478-080027fd2c56:1-4:30-53010

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+--------------------------+----------------------------------------------------------------------------------------------+

+------------------+----------+--------------+--------------------------+----------------------------------------------------------------------------------------------+

ab7c6739-be64-11ea-b478-080027fd2c56:1-4:30-53010 |

+------------------+----------+--------------+--------------------------+----------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

Here 846dc749-bf34-11ea-852d-08002749f9b4:1-29 --> Last slave GTID executed

ab7c6739-be64-11ea-b478-080027fd2c56:1-4:30-53010 --> Last GTID executed on master

In order to fix the problem, we need to mark these as purged in slave.so that the slave continues with the next set of GTIDs. On slave run the below commands.

Please note that running reset master on slave would also delete all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file.

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql> set global GTID_PURGED='846dc749-bf34-11ea-852d-08002749f9b4:1-29,ab7c6739-be64-11ea-b478-080027fd2c56:1-53010';

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Queueing master event to the relay log

Master_Host: db-55.example.com

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 17944864

Relay_Log_File: mysqld-relay-bin.000007

Relay_Log_Pos: 781024

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema

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

Relay_Log_Space: 1256570

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

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

Master_UUID: ab7c6739-be64-11ea-b478-080027fd2c56

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: System lock

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: ab7c6739-be64-11ea-b478-080027fd2c56:1-29:53011-56993

Executed_Gtid_Set: 846dc749-bf34-11ea-852d-08002749f9b4:1-29,

ab7c6739-be64-11ea-b478-080027fd2c56:1-55487

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Tech Notes:- MySQL replication error 2005

2020-06-21T10:42:00.000+10:00

The below error mostly indicates that you have a name resolution issue on your slave side. For me, it turned out that I configured the wrong IP for the DNS server. I just corrected it, stopped and started the slave and everything was ok.

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: master.example.com
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 23649264
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
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: 23649264
Relay_Log_Space: 106
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2005
Last_IO_Error: error connecting to master 'repl@master.example.com:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Tech Notes:- How to increase the expiry of certificates issued by FreeIPA from the default 2 years to say 5 years

2020-05-29T22:00:00.001+10:00

By default, the certificates issued by Freeipa has an expiry of 2 years. Here is how we can extend the expiry to 5 years by default

On my host(ipa-client) I already have a certificate with validity till 2022-05-30 (2 years)
sudo ipa-getcert list -i 20200529110800 | grep expires
expires: 2022-05-30 21:08:01 AEST

I want to have a certificate with 5-year validity.

On the Freeipa server
sudo ipa certprofile-show caIPAserviceCert --out=caIPAserviceCert.profile

Open the file caIPAserviceCert.profile and then update the below values
Update the value of below from 731(2*365+1) to 1826(5*365+1)
policyset.serverCertSet.2.default.params.range=1826

Update the value of below from 740(2*365+10) to 1835(5*365+10)
policyset.serverCertSet.2.constraint.params.range=1835

Run the below command to modify the default expiry date
sudo ipa certprofile-mod caIPAserviceCert --file=caIPAserviceCert.profile

Now, if you want to update your already installed certificates to have the new 5-year validity, run the below command on the host(ipa-client)

sudo ipa-getcert rekey -i 20200529110800

where 20200529110800 is the tracking request identifier

Now if we check the validity it should have 5 years
sudo ipa-getcert list -i 20200529110800 | grep expires
expires: 2025-05-29 21:46:31 AEST

Tech Notes:- Increase the size of tmpfs files system in CentOS 6

2020-05-22T10:48:00.003+10:00

Here are the steps to increase the size of the tmpfs file system in CentOS 6. Suppose you want to increase the size to say 1GB

Update the below entries in /etc/fstab

tmpfs /dev/shm tmpfs size=1g 0 0

Run the below command

sudo mount -o remount tmpfs

Now check the tmpfs size using df -lh.It should reflect 1GB

Tech Notes:- Adding additional source to Mysql 5.7 multi-source replication with GTID

2020-05-14T22:53:00.001+10:00

There are not many details on how to add an additional source to a multi-source Mysql 5.7 replication based on GTID. This note helps you to understand the simple steps to add additional sources.

Here we already have a channel running in a multi-source replication configuration.

As you can see below, we have one channel already configured and working

[sudeep@db-slave ~]$ sudo mysql -e "SHOW SLAVE STATUS\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.27
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 459719526
Relay_Log_File: mysqld-relay-bin-db@002d01.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,information_schema,sys
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.user
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 459719526
Relay_Log_Space: 632
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: 15
Master_UUID: a21bbd99-935e-11ea-a7c0-080027677b11
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: a21bbd99-935e-11ea-a7c0-080027677b11:1-3369484
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: db-master-1
Master_TLS_Version:
One important thing that we need to make a note in the case of multi-source replication is that the gtid_purged is global to the slave. So if we try to setup gtid_purged value based on the news source, the replication for the existing channel will fail.

The solution is to make sure that we take the value of gtid_executed from the existing source say db-master-1 and make a note of it first
On Master1
----------------
[sudeep@db-master-1 ~]$ sudo mysql -e "show global variables like 'gtid_executed'"
+---------------+------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------+
| gtid_executed | a21bbd99-935e-11ea-a7c0-080027677b11:1-3369484 |
+---------------+------------------------------------------------+

On master2
---------------
Take a mysqldump from db-master-2 , say mysqldump_db-master-2_2020-05-14.sql.gz and then copy it to slave

On slave
------------
[sudeep@db-slave ~]$ unzip mysqldump_master-2_2020-05-14.sql.gz

[sudeep@db-slave ~]$ cat mysqldump_db-master-2_$(date +%F).sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''
475d926a-9309-11ea-9dee-080027cfdd46:1-2493391

[sudeep@db-slave ~]$ sed '/GTID_PURGED/d' mysqldump_db-master-2_$(date +%F).sql > mysqldump_db-master-2_gtid_nopurge_$(date +%F).sql

[sudeep@db-slave ~]$ sudo mysql -e "source mysqldump_db-master-2_gtid_nopurge_$(date +%F).sql"
[sudeep@db-slave ~]$ sudo mysql -e "RESET MASTER"

[sudeep@db-slave ~]$ sudo mysql -e 'SET @@GLOBAL.gtid_purged = "a21bbd99-935e-11ea-a7c0-080027677b11:1-3369484,475d926a-9309-11ea-9dee-080027cfdd46:1-2493391"'

[sudeep@db-slave ~]$ sudo mysql -e "CHANGE MASTER TO MASTER_HOST='172.16.1.28',MASTER_USER='repl',MASTER_PASSWORD='Password',MASTER_AUTO_POSITION = 1 FOR CHANNEL 'db-master-2'"

[sudeep@db-slave ~]$ sudo mysql -e "START SLAVE FOR CHANNEL 'db-master-2'"

[sudeep@db-slave ~]$ sudo mysql -e "SHOW SLAVE STATUS\G"

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.27
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 459719703
Relay_Log_File: mysqld-relay-bin-db@002d01.000002
Relay_Log_Pos: 591
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,information_schema,sys
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.user
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 459719703
Relay_Log_Space: 809
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: 15
Master_UUID: a21bbd99-935e-11ea-a7c0-080027677b11
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a21bbd99-935e-11ea-a7c0-080027677b11:3369485
Executed_Gtid_Set: 475d926a-9309-11ea-9dee-080027cfdd46:1-2493391,
a21bbd99-935e-11ea-a7c0-080027677b11:1-3369485
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: db-master-1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.28
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 365
Relay_Log_File: mysqld-relay-bin-db@002d02.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,information_schema,sys
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.user
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 365
Relay_Log_Space: 632
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: 28
Master_UUID: 475d926a-9309-11ea-9dee-080027cfdd46
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 475d926a-9309-11ea-9dee-080027cfdd46:1-2493391,
a21bbd99-935e-11ea-a7c0-080027677b11:1-3369485
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: db-master-2
Master_TLS_Version:

We have added a second source channel db-master-2.

Tech Notes:- Mysql replication error 1236 for GTID based replication

2020-05-14T17:21:00.000+10:00

For GTID based MySQL replication we sometimes end up with below error

Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary lo
gs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period.

The way to solve this is outlines below

On Master

--------------
Take a mysqldump from the master and then copy it to the slave. Say your dump file is mysqldump_db-01_2020-05-13.sql.gz

On Slave

-------------

[sudeep@db-slave ~]$ gunzip mysqldump_db-01_2020-05-13.sql.gz

Record the gtid_purged value that mysqldump added to the dump file.
[sudeep@db-slave~]$ cat mysqldump_db-01_$(date +%F).sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''
a21bbd99-935e-11ea-a7c0-080027677b11:1-100

Remove the line from the dump file that contains the SET @@GLOBAL.gtid_purged statement

[sudeep@db-slave~]$ sed '/GTID_PURGED/d' mysqldump_db-01_$(date +%F).sql > mysqldump_db-01_gtid_nopurge_$(date +%F).sql

[sudeep@db-slave~]$sudo mysql -e "source mysqldump_db-01_gtid_nopurge_$(date +%F).sql"

[sudeep@db-slave ~]$ sudo mysql -e "RESET MASTER"

The reset master command should clear the GTID_EXECUTED on slave

[sudeep@db-slave ~]$ sudo mysql -e "show global variables like '%GTID_EXECUTED%'"

+---------------+----------+

| Variable_name | Value |

+---------------+----------+

| gtid_executed | |

+---------------+----------+

[sudeep@db-slave ~]$ sudo mysql -e "set global GTID_PURGED='a21bbd99-935e-11ea-a7c0-080027677b11:1-100'"
[sudeep@db-slave ~]$ sudo mysql -e "START SLAVE io_thread"
[sudeep@db-slave ~]$ sudo mysql -e "SHOW SLAVE STATUS\G" | grep -i yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Tech Notes:- Filebeat fails with the message Exiting: Registry file path must be a file. /var/lib/filebeat/registry is a directory

2020-04-26T20:53:00.000+10:00

Filebeat fails with the message

"Exiting: Registry file path must be a file. /var/lib/filebeat/registry is a directory.

This usually happens when you have installed a higher version of filebeat, removed it, and then installed a lower version. In my case, I installed version 7.x and then I found that it is not compatible with Elasticsearch 6.x.So I removed it and then installed filebeat 6.8.x.It worked initially and then after a restart, this error started appearing. The solution is simple. Just remove the directory /var/lib/filebeat/registry and then start filebeat

Tech Notes:-How to copy files from Windows Subsystem For Linux to Windows

2019-04-12T11:35:00.004+10:00

If you work on Windows Subsystem for Linux either Ubuntu or OpenSuse, I am sure you have faced with situations where you had to copy files from Ubuntu or OpenSuse to Windows.

If you are not aware how to do it. Here are the easy steps

Right-click the start and then run , paste the below path and press enter

%userprofile%\AppData\Local\Packages

Now in the windows explorere search packages option type ubuntu. This should show the Ubuntu folder with the name something like

CanonicalGroupLimited.Ubuntu18.04onWindows_79rhkp1fndgsc

Open this folder and browse to LocalState --> rootfs
Now this is the root file system of your Ubuntu.Mostly your files will be saved under /home/UserName/
Now you should be able to copy the files using windows explorer itself

Tech Notes:- How To Bootsrap a Percona Xtradb Cluster After All The Nodes Are Down

2019-04-07T15:17:00.000+10:00

Often there is confusion regarding which node to start first after we shut down all the nodes or a Percona XtraCB Cluster. So here is how we can decide that

Safe_to_bootstrap Flag

Our decision should be based on the value of this flag, if we are sure that the cluster was shut down properly. If we are talking about a 3 nodes cluster, you have to check the value of this flag in the file /var/lib/mysql/grastate.dat in each of the nodes.

The node with the value 1 should be the one we use for bootstrap. Others will have a value of 0. So here in my case, Node1 with the value of 1 for safe_to_bootstrap should be the one which we bootstrap.

But what happens if you try to bootstrap from the nodes 2 and 3 which had a value of 0 for safe_to_bootstrap. The cluster will refuse to start those nodes. You can see the below message in the logs /var/log/mysqld.log

It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates.

What if the nodes were not shut down properly.?

In case of a hard crash or an abrupt shutdown, all nodes will have a value of 0 for the safe_to_bootstrap. In this case, we have to check the DB engine to determine which node has committed the last transaction. For this, we have to start mysqld in each node with the wsrep-recover variable

sudo mysqld_safe --wsrep-recover

The above command will have something like below in the output

mysqld_safe WSREP: Recovered position f23bb233-5840-11e9-919a-9eac3900b870:14

The number after the UUID string is the one to look for. Select the node that has the highest number and update its /var/lib/mysql/grastate.dat to set safe_to_bootstrap: 1

sudo less /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid: f23bb233-5840-11e9-919a-9eac3900b870

seqno: -1

safe_to_bootstrap: 1

Now it is safe to bootstrap this node.

Note:-The above procedure is not needed for most recent versions of Percona XtraDB cluster as there is a new option pc.recovery which is enabled by default and will recover the cluster promoting the node with the last commit as the primary node after all the members again start to see each other.

Tech Notes:-Using Cryptomator to encrypt you personal data in cloud

2019-03-24T21:42:00.001+11:00

One confusion most of us always had from the early years of cloud adoption is whether to upload confidential data to the cloud or not and how safe is the data.

Now, we have a free solution which we can use to encrypt the data uploaded to the cloud. This is using a tool called Cryptomator.

https://cryptomator.org/

Cryptomator is a free, opensource client-side tool for encrypting your cloud files.T he tool is available for Linux, Windows, Mac, Android and Apple.

Cryptomator encrypts the data with either a key or a password as per your choice before its uploaded from your device.This means that the data stored in the cloud will be in encrypted form. All that someone who gets access to your cloud account sees is like below which doesn't make any sense

It will make sense only once we decrypt it with the same password or key after we download it back to our device.

Now we will see how we can setup cryptomator to encrypt files and then sync it to google drive.

Setting up Google drive

First, we have to download and install Google Backup and Sync from below URL

https://www.google.com/drive/download/backup-and-sync/

On launching Google Backup and Sync, you will be asked to sign in with your Google credentials.
If you have enabled 2-step verification, you will be asked to complete the formalities for 2-step verification.
ClickAllow for trust verification message

Once you sign-in, follow below youtube video to add the folder that you want to sync. I have chosen the folder E:\Google Drive to sync. We will be creating a folder named MyDocs under the Google Drive folder and this folder will contain our data which will be encrypted using cryptomator.

https://www.youtube.com/watch?v=Y4kJ9-j_FUY

Setting up Cryptomator

Download and install cryptomator form below link

https://cryptomator.org/downloads/#winDownload

Launch cryptomator and click on the + and then select create a new vault
Browse to your Google drive path. In my case, it is E:\Google Drive
Name the vault and supply a password and then click on Create Vault
That's it, you have created the vault
Now click on the drop-down near Lock Vault and then select Reveal Drive
This will open the drive in Windows Explorer.
By default, the drive will be mounted in Windows Explorer as Z drive
Now whatever files you copy to this drive will be encrypted and since this folder is already part of Google Drive folder, this will be synced to Google drive.
But to access the contents, you need to have theCryptomator software as well as the password that you set for the vault.
Once your work is done, you can close this drive by selecting the drop-down near to the Lock Vault and then selecting eject drive

You can access your encrypted files from even your Android phone or iPhone As of this writing, the Cryptomator client for Android is not free.

Tech Notes:-Extracting specific tables from MySQL dump

2019-03-24T11:43:00.002+11:00

I encountered a situation where I had to extract only a specific table from a huge MySQL dump. Here is how you could accomplish this.

Lets say the name of the table is mytable and the file containing the huge mysql dump is mysql.dump. Here is the command to extract mytable

This will copy into the file mytable.dump what is located between CREATE TABLE mytable and the next CREATE TABLE corresponding to the next table.

If required you can then adjust the file mytable.dump which contains the structure of the table mytable, and the data.

Tech Notes:- Yum reinstall command

2019-03-24T10:51:00.000+11:00

The Problem

Often we encounter scenarios where we have to re-install packages in yum. Here I am explaining one such scenario.

Last week I installed an application using yum and then removed the rpm packages one by one. And then deleted the application folder. Later I decided to install it back and I used yum to install it. But after I installed the application I noticed that the application was not launching. On investigation, I could find that the yum install did not install certain dependancy packages which had some libraries required for the application to start.

Analysis

So what happened here?. Although I removed most of the packages using rpm, I missed to remove certain dependency packages and then later when the folder was deleted, the files associated with the dependency packages got deleted. But as per yum database, the dependency package is still installed and when we installed it using yum the second time, these packages were skipped.

Solution

How to deal with this?. First of all we have to find out which package provides the missing files.

rpm -qf filename

Now we can ascertain that the file is part of the dependency package for the packages we installed using yum.

Fortunately, yum has an option called reinstall which will completely re-install the packages including the dependancy packages. The syntax is simple

yum reinstall PACKAGE_NAME

Paella from Preston market Melbourne

2019-03-23T19:22:00.001+11:00

I was on a normal routine visit to Preston market in Melbourne and I noticed something special and that was seafood Paella. The deliciousness spreading through the air was so tempting that I decided to join the huge queue for a taste.

Now, like me, for those who don't know what is Paella. Paella is known to have originated in the Spanish city of Valencia and then spread across Spain and now spreading all over the world including Melbourne :).

We have different types of Paella now, veg, seafood and mixed. The one I had from Preston market is the seafood Paella and these are some of the ingredients I could figure out, a special kind of rice called Calasparra or Bomba rice, medium grain rice grown in Spain, crushed saffron, shrimp, calamari, mussels, chicken pieces etc.

Tech Notes:- Nginx reverse proxy issues in AWS due to name resolution failure

2019-03-23T19:02:00.001+11:00

The Problem

There are certain failure scenarios in AWS which is hard to detect. One of them is Nginx reverse proxy failure due to name resolution issues.

AWS has certain services like ELB, RDS with dynamic changes in underlying host IP. The problem here is that some applications resolve the IP only once mostly during the startup, restart or reload of that service. One such application is the Nginx web server.

Nginx resolves all the names to their IP and caches it during the start, restart or reload. If the DNS resource record changes in between, Nginx fails. This typically happens when Amazon ELB IP address changes. Amazon updates the DNS record, but Nginx never re-resolves the DNS record and stays pointing at the old IP address. Subsequently requests to the backend start failing once Amazon drops services from the old IP address.

Analysis

But how do you find out if the Nginx issues are caused due to name resolution? One of the best options in front of us is Amazon CloudTrail. Normally ELB IP changes when the ELB scales or there is a scale up or down of hosts behind the ELB.

Refer the below Amazon link to read about the ELB behavior

https://aws.amazon.com/articles/best-practices-in-evaluating-elastic-load-balancing/

AWS CloudTrail logs all the API activities associated with your AWS account and this includes the changes related to ELB as well.

Solution

Now how do we resolve this problem? If we are using the community edition of Nginx, the best option is to configure a resolver entry.

But how do you know the IP address of the DNS that you should use in resolver?AWS documentation comes handy here

https://docs.aws.amazon.com/vpc/latest/userguide/VPC_Subnets.html

The first four IP addresses and the last IP address in each subnet CIDR block are not available for you to use, and cannot be assigned to an instance. For example, in a subnet with CIDR block 10.0.0.0/24, the following five IP addresses are reserved:

10.0.0.0: Network address.
10.0.0.1: Reserved by AWS for the VPC router.
10.0.0.2: Reserved by AWS. The IP address of the DNS server is always the base of the VPC network range plus two; however, we also reserve the base of each subnet range plus two. For VPCs with multiple CIDR blocks, the IP address of the DNS server is located in the primary CIDR. For more information, see Amazon DNS Server.
10.0.0.3: Reserved by AWS for future use.
10.0.0.255: Network broadcast address. We do not support broadcast in a VPC, therefore we reserve this address.

Apart from that there is VPC independent up address which can be used as resolver and that is 169.254.169.253. Here the catch is you lose the ability to resolve up outside AWS from nginx point of view.

Tech Notes:- How to selectively merge files from a git branch to master

2019-03-09T19:44:00.001+11:00

People who work with git regularly especially with branches might encounter a situation where they want to merge specific files from one branch to another. Here I am explaining how I accomplished this

My Scenario

I have a master branch and a new branch called "observium". I wanted to merge a few files to the master branch. To demonstrate it I did a checkout of the master branch, created a file called sreedev.txt and then committed it.

[sudeep@dev ansible]$ git checkout master
Switched to branch 'master'

[sudeep@dev ansible]$ git status
# On branch master

[sudeep@dev ansible]$ vim sreedev.txt

I entered the text "This is a file from the main branch" and saved it

[sudeep@dev ansible]$ git commit -m "Added a new file for demonstrating selective file merge"
[master e3a1a33] Added a new file for demonstrating selective file merge
1 file changed, 1 insertion(+), 2 deletions(-)

[sudeep@dev ansible]$ git push origin master
Counting objects: 5, done.
Compressing objects: 100% (2/2), done.
Writing objects: 100% (3/3), 324 bytes | 0 bytes/s, done.
Total 3 (delta 1), reused 0 (delta 0)
To git@bitbucket.org:sudeepkumarks/ansible.git
6ffda3a..e3a1a33 master -> master

Now I did a git checkout of my branch observium

[sudeep@dev ansible]$ git checkout observium

Switched to branch 'observium'

Created a file with the same name sreedev and updated a similar content which will conflict with content in the main branch. The intention here is to show how we handle the conflict during the selective merge

[sudeep@dev ansible]$ git add sreedev.txt
[sudeep@dev ansible]$ git commit -m "Added a new file for demonstrating selective file merge"
[observium 04b5fa6] Added a new file for demonstrating selective file merge
1 file changed, 1 insertion(+), 3 deletions(-)

How to merge the files

Now we will try to merge the files sreedev in observium branch to the main branch

[sudeep@dev ansible]$ git checkout master

Switched to branch 'master'

[sudeep@dev ansible]$ git checkout --patch observium sreedev.txt

diff --git b/sreedev.txt a/sreedev.txt

index e7d96cb..fe3f28c 100644

--- b/sreedev.txt

+++ a/sreedev.txt

@@ -1,2 +1 @@

-This is a file from the main branch

+This is a file from the observium branch

Apply this hunk to index and worktree [y,n,q,a,d,/,e,?]?

Since there is a conflict its asking questions on resolving the conflicts. At this stage, you have the below options

y - stage this hunk

n - do not stage this hunk

q - quit; do not stage this hunk nor any of the remaining ones

a - stage this hunk and all later hunks in the file

d - do not stage this hunk nor any of the later hunks in the file

g - select a hunk to go to

/ - search for a hunk matching the given regex

j - leave this hunk undecided, see next undecided hunk

J - leave this hunk undecided, see next hunk

k - leave this hunk undecided, see previous undecided hunk

K - leave this hunk undecided, see previous hunk

s - split the current hunk into smaller hunks

e - manually edit the current hunk

? - print help

If you press y, the line "This is a file from the main branch" will be removed and the line "This is a file from the observium branch" will be added. If you want to keep the first line and remove the second one which is from the observium branch, you can press e and make manual changes and save it like how you save files in vim editor.

Once you complete the changes by saving, it will go to the next hunk if there is any conflict. This will repeat until all the conflict in the file is resolved. Now the git status should show the file as modified and added for commit.

[sudeep@dev ansible]$ git checkout --patch observium sreedev.txt

diff --git b/sreedev.txt a/sreedev.txt

index e7d96cb..fe3f28c 100644

--- b/sreedev.txt

+++ a/sreedev.txt

@@ -1,2 +1 @@

-This is a file from the main branch

+This is a file from the observium branch

Apply this hunk to index and worktree [y,n,q,a,d,/,e,?]? y

[sudeep@dev ansible]$ git status

# On branch master

# Changes to be committed:

# (use "git reset HEAD ..." to unstage)

# modified: sreedev.txt

# Untracked files:

# (use "git add ..." to include in what will be committed)

# master

Now you can commit the changes like normal to have the changes from Observium branch in your master branch. This can be followed for merging specific files from any branch.

Behavioral interview questions related to managing work.How do you handle situations where you did not have enough work to do

2019-03-09T18:41:00.000+11:00

Although most of the time we will have more than enough work to do, there are situations in an organization where there is not much work to do. This can be due to a transition phase of the organization or the organization might be in a restriction period for change implementation or for new projects because of the annual high transaction period or budget constraints. In an interview, there can be questions related to this and the intention of asking this question is to understand the attitude of the candidate.

Here we review some sample questions related to this.

Sample questions related to managing your work

Have you been in a situation where you did not have enough work to do

How do you keep you busy if you don't have much to do at work

How do you kill time when there is not much work to do

Situation

I was working in a financial services organization which enforces restriction period wherein no project specific changes are deployed during the busy transaction period from November to January

Restriction period basically translates to less busy time with respect to work. And there are days where we cannot do anything with the systems which we work on.

Approach

Although I didn't have enough work during the restriction period, there were opportunities to improve my knowledge as well as to share it. I was utilizing this opportunity to learn new technology relevant to work as well as sharing my knowledge with others in my team.

I also took this as an opportunity to research on improving the existing systems, optimizing the process/systems to get some re-engineering saves.

I also took initiatives to do the proof of concept study for some of the new improvements that I found.

Results

Although there was not much work, I was able to utilize the work hours effectively to the advantage of the organization in all possible manner.

How effective is the above answer

The candidate was able to demonstrate that he will be committed to the betterment of the organization and he thinks innovatively and comes up with better ideas instead of sitting idle or spending work hours in a non-fruitful manner.

Summary

Spending your work time wisely and effectively is something that every organization expects and questions related to this is common in an interview. It's also good to have a story handy to address these questions.

Behavioral interview questions related to managing conflicts

2019-03-09T15:16:00.001+11:00

It is very important for an organization to assess candidates ability to manage conflicts as a part of assessing interpersonal skills during an interview. The expectation here is that the candidate should showcase his competency to get along with different types of people as we cannot expect everybody in the organization to behave nicely to you :). He should also show how he manages those disagreements gracefully.

Here we will see some sample questions related to conflict management and how to answer them effectively.

Conflict management

What do you do when a team member refuses to complete his or her quota of the work?
Tell me about a time when you had to disagree with a specific approach or process
Tell me about a time you had a conflict at work.
Tell me about an incident where you had to handle an unhappy customer/employee/supervisor/colleague
Tell me about a situation where you had to work with a colleague who is difficult to deal with.

Situation

I was the project manager for a high visibility project with a tight deadline As. the project was a very important one from an organizational perspective, senior leadership including directors were closely monitoring the progress.
I was managing a bridge call to complete the User Acceptance Testing(UAT). Multiple teams including leadership and all other stakeholders joined the call except the systems assurance analyst who was supposed to do the UAT.
When I approached the systems assurance analyst who was supposed to join the call and complete the UAT,, he blew up at me.

Approach

Although I was not getting an expected response from him. I maintained my composure and explained to him once again about the importance/visibility of the project as well as the importance of completing the UAT on time.
He apologized for not making it to the call and explained about the competing priorities he has with other projects.
We discussed further to find a solution and finally decided to approach his Director to appraise her of the importance of this project over others
She decided to assign another Systems Assurance Analyst who is more experienced to handle my project's UAT testing

Results

Although we lost almost half an hour of our UAT time, we could complete the UAT well before the deadline because we had a well experienced Systems Assurance Analyst doing our UAT.
We were able to complete the project on schedule and the handling of conflicting situations was well appreciated by the leadership in the final project completion notification.

How effective is the above answer

Here the candidate was able to handle the situation without impacting the project schedule with a final positive outcome. He was able to handle the conflict very well without impacting the relationship with Systems Assurance Analyst.

Summary

Conflict resolution is not only relevant to the interview but its also important in our day to day life. It's important to learn the art of conflict management and to resolve conflicts with a positive outcome for all the parties involved.

This is a sure shot question in an interview and preparing the story well in advance and practicing to answer will definitely impact the interview outcome.

All the best with the interview preparation!

Behavioral interview questions related to culture

2019-03-09T13:34:00.000+11:00

Although there are a lot of questions and sample answers available for common questions on the internet, I found that there are some specific questions for which answers are not available. Here I will be covering questions related to culture.

Cultural awareness

Tell me about a time when you had to take someone's cultural perspective into account when dealing with them.

Give me an example of a situation where you had to take into account the sensitivities of different parties.

Tell me about a time when you have worked in a team with individuals from different cultural backgrounds.

Here is the example of an answer which follows the STAR approach.

Situation

Here we need to provide a context for the story and this section should be concise and to the point.

Recently I had a chance to manage a project which involved the release of specific software to multiple regions across the globe over Citrix infrastructure.
One of the tasks involved was setting up and managing weekly bridge calls involving different teams including business, technology and leadership teams from different countries across the globe.
During these calls, I noticed that we were not getting much input from some regions and the leadership was getting the impression that everything was going smoothly.

Approach

In the previous section, you described the situation. Now the important part comes which is how you demonstrated the specific competency you have which you want to highlight through the steps that you have taken to resolve it

The project was on a tight deadline so everybody was keen on moving things as quickly as possible.
However, I was very concerned about the lack of inputs from specific regions where the software will be rolled out.
As a project manager, I decided to set up calls with each regional team and get their inputs, just to make sure that they don't have any inputs/concerns.
It turned out that several of them had concerns regarding the response time and some specific features related to the customization as per the language.
They were hesitant to express their concerns because they did not want to contradict opinions from the leadership team.
From there on I started setting up regular bridge calls with specific regional teams, note their concerns/opinions and then summarise and present it in the larger team calls.

Results

I was happy that I was able to capture concerns/opinion of regional teams well before the rollout and that saved us from trouble which could have happened after the roll-out.
I found that people from regional teams are more confident, comfortable and gets more time to explain their concerns/opinions in detail when we setup calls with individual teams.
I got appreciations from my leadership team for this approach as we were successful in addressing the concerns from regional teams well before the rollout which otherwise could have remained unknown.
This experience helped me understand how important is to have a platform where everyone is comfortable to communicate effectively.

How effective is the above answer

Here we are highlighting

the project manager's project management as well as general people management skills.
he is meticulous, a good learner and willing to go the extra mile to achieve results
above all, he shows that he is very good at dealing with people from different cultural or regional backgrounds.

Summary

Nowadays with more and more organizations going global, you can expect questions related to cultural perspective in most of the interviews. Therefore it's very important that you prepare a few stories in advance to avoid surprises.

All the best with the interview preparation!

Behavioral interview questions

2019-03-03T12:40:00.001+11:00

What are behavioral interview questions?

Most often we come across these categories of questions in a job interview. Here I am providing a summary of what these questions are all about, how to answer them and some sample questions along with sample answers.

How to identify if the question being asked is a behavioral interview question

If the question starts with something like below, it's highly likely to be a behavioral question

"Give me an example of ....."
"Have you ever..."
"Describe a time when..."
"Have you had to.."
"What do you do if you.."

Methods for answering behavioral interview questions

The STAR technique

STAR stands for SITUATION TASK ACTION and RESULT

Situation/Task - Explain the event or task that you encountered.

Action - Explain what steps you undertook

Result - Describe the outcome that you delivered

The CAR technique

This method is similar to the STAR method except that here you provide a CONTEXT, ACTION, and RESULT

Context - Explain the situation and the background

Action - Describe what measure of steps you took

Result- Describe the outcome you achieved

Pointers to answering behavioral interview questions effectively

Due diligence - Find out what skills and behaviors the employer is looking for, and find answers for possible questions including well-known questions that you will definitely get in an interview. If possible, prepare answers from your own previous experience. If there are questions beyond your experience, make sure you have a story ready. Remember, the expectation here is to demonstrate how you handled those situations.

Practice - answering the questions using STAR or CAR method out loud in advance for those questions you identified. Make sure that while telling your story, you stay on point. Better if you have someone to listen to your answers and provide corrections.

Tech Notes:- log file at /var/log/ansible.log is not writeable and we cannot create it, aborting

2018-12-26T14:40:00.000+11:00

This is one of the errors that we encounter after installing ansible and trying to run our first playbook as a non-root user. To fix this all that we need to do is to create a group called ansible , add the user who is executing the playbook to the ansible group, then touch the file /var/log/asnible.log with root as the owner and ansible as the group.Permission should be 775 which is read write and execute for owner and group and read and execute for others.

# sudo touch /var/log/ansible.log

#sudo groupadd ansible

# sudo chown root:ansible /var/log/ansible.log

# sudo chmod 775 /var/log/ansible.log

# sudo usermod sudeep -aG ansible

You need to signout and sign in before executing the playbook.

Tech Notes:- Mysql Replication Error 'Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file''

2018-12-16T20:30:00.001+11:00

I kept my slave mysql db down for a week and then when I switched on the slave db server and checked the replication status, I started seeing this error.

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

The most probable reason for this error is that the binary log required for the replication by slave servers no longer exists on the master database server. This could have happened because someone might have manually deleted the binary files on the MySQL master either by removing the files from the file system or using the purge binary logs command.

Another reason could be that a variable has been set in the conf file to expire the logs. In my case, it was this one and the variable was set to 2 days

expire_logs_days=2

Let's have a detailed look at SHOW SLAVE STATUS and SHOW BINARY LOGS on master

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.25.1.20
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000023
Read_Master_Log_Pos: 94955731
Relay_Log_File: mysqld-relay-bin.000007
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000023
Slave_IO_Running: No
Slave_SQL_Running: Yes

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000029 | 1315 |

+------------------+-----------+

1 row in set (0.21 sec)

So reading it all to gether, slave is looking for the master log file mysql-bin.000023 at position 94955731. But the master is at mysql-bin.000029 and I dont have the file mysql-bin.000023. So this basically means that the mysql slave stopped replcation while at mysql-bin.000023.

How do we solve this?

The simplest way as well as the advisable approach is to dump the mysql from the master, restore it to slave and then restart the replication. But that may take time if the database is huge and there is no way to take the dump without impacting production performance.

If its a test system and you are ok with losing some data in replication, the best bet would be to set the position of the slave to the start of the new available master file.

STOP SLAVE;

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=4;

START SLAVE;

You may need to skip some positions sometimes. If you have patience and time, the best option is to examine the events in the log and point to the position accordingly. You can do it using the command below

sudo mysqlbinlog /var/lib/mysql/mysql-bin.000029 | more

Tech Notes :- Ansible Ad hoc Command Examples

2018-11-25T22:56:00.003+11:00

Ansible ad-hoc commands are useful when you want to run something quickly on multiple machines. Here I am providing some examples of different situations that might help. Let's assume that our inventory name is 'servers' and we the names of all servers in this file

Checking the status of all servers in our inventory file 'servers'
# ansible 'servers' -m ping

For checking the uptime of all servers
# ansible 'servers' -a 'uptime'

Create a user sudeep and set the password on all servers in the inventory file servers
# ansible 'servers' -m user -a "name=sudeep password=redhat"

Updating Linux server with latest updates
# ansible 'servers' -m package -a 'name=* state=latest'

Installing a specific package in all servers in the inventory
# ansible 'servers' -m package -a "name=httpd state=present"

Reboot the servers and wait for it to come back
# ansible 'servers' -b -B 1 -P 0 -m shell -a "sleep 5 && reboot"

Remove a text from a file
# ansible 'servers' -m lineinfile -a "dest=/etc/somefile.txt regexp='texttoberemoved' state=absent"

Checking entries in a file
# ansible 'servers' -m command -a 'grep listen /etc/nginx/conf.d/nginx.conf'

Replacing strings in a file on multiple servers
# ansible 'servers' -m replace -a 'path=/etc/nginx.conf.d/nginx.conf regexp="192.168.1.20" replace=192.168.1.100'

Commenting a line in a file based on finding a pattern in that line.For example comment the line containing the word backup in a cron job

ansible 'servers' -m replace -a 'path=/etc/cron.d/backup_schedule regexp="(.*backup.*)" replace="#\1"'

Check the hypervisor on remote machines
# ansible 'servers' -m setup -a "filter=ansible_virtualization_*"

List remote files
# ansible 'servers' -m command -a 'ls -l /var/www/html'

Copy files
# ansible 'servers' -m copy -a 'src=/home/sudeep/test.php dest=/var/www/html/test.php owner=sudeep group=sudeep'

List all variables a partiucular host can see
# ansible -m debug -a 'var=hostvars[inventory_hostname]' foo.com

Schedule a reboot
# ansible 'foo.com -a '/sbin/shutdown -r +5'

Checking the remote git version on multiple servers
# ansible 'servers' -m command -a "git status args: chdir=/var/www/html/sudeep"

List all tasks in a playbook
# ansible-playbook --list-tasks playbooks/app-server.yml

Get an excerpt from a file
# ansible 'servers' -a 'grep -A 2 port /etc/supervisord.conf'

Remove a port from firewall where the firewall is configured using firewalld
# ansible 'server' -m command -a "firewall-cmd --remove-port='8080/tcp' --zone=internal --permanent "

Reload firewalld
#ansible 'server' -m command -a 'systemctl reload firewalld'

List all opened ports in public zone
#ansible 'server' -m command -a 'firewall-cmd --list-ports --zone=public'

List all services in public zone
#ansible 'server' -m command -a 'firewall-cmd --list-services --zone=public'

List all sources in public zone
#ansible 'server' -m command -a 'firewall-cmd --list-sources --zone=public'

Adding a server to proxysql with a default_hostgroup of 80, writing the changes to disk and loading it to runtime.
ansible 'myserver.example.com' -m proxysql_backend_servers -a 'hostgroup_id=80 hostname=172.16.1.55 load_to_runtime=True login_host=proxysql.example.com login_password=Password123 login_port=6032 login_user=proxysql-admin save_to_disk=True state=present status=ONLINE comment="My new mysql database server"'

We can send the output of the ansible command(or any command) both to a file as well as stdout.See the example below
ansible 'servers' -m command -a "git status args: chdir=/var/www/html/sudeep" 2>&1 | tee ~/outfile