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

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

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

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

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