MySQL GTID Replication and lower_case_table_names

Error 'Table 'EMPLOYEES.POSITION' doesn't exist' on query. Default database: 'employees'. Query: 'ALTER TABLE EMPLOYEES.POSITION ADD COLUMN phone VARCHAR(15)'

Interesting , table exist on slave server . But we are getting above error frequently and unable to broken the replication because database size is too big

We are having environment as GTID replication setup with windows server (Master) to Ubuntu Linux machine (Slave) . When we dig into all the findings , concluded its may be with case sensitivity issue . Lower_case_table_names variable value is same on both servers . But as per MySQL documents 

Database and table names are not case-sensitive in Windows , but are case-sensitive in most varieties of Unix.Column,index,stored routine, and event names are not case-sensitive on any platform,nor are column aliases

So , decided to tackle the current situation without data loss in slave server . Changing the lower_case_table_names is not best practice and it will corrupt case_sensitive names data files . 

we have stopped the slave , executed all the DML and DDL statements in slave server to match the schema deployments with master server .

And followed GTID replication error skipping process to skip the transactions manually handled in slave servers 

Capture the error GTID with master binlog files

root@master:/var/log/mysql# mysqlbinlog --start-position=904 mysql-bin.000004

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#200102 11:35:24 server id 1 end_log_pos 123 CRC32 0x14c9c2f7 Start: binlog v 4, server v 5.7.28-0ubuntu0.16.04.2-log created 200102 11:35:24

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

fNUNXg8BAAAAdwAAAHsAAAABAAQANS43LjI4LTB1YnVudHUwLjE2LjA0LjItbG9nAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AffCyRQ=

'/*!*/;

# at 904

#200102 12:06:51 server id 1 end_log_pos 969 CRC32 0x3564eba7 GTID last_committed=4 sequence_number=5 rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= '69b98063-2d31-11ea-9586-12b321a8670d:78'/*!*/;

# at 969

#200102 12:06:51 server id 1 end_log_pos 1042 CRC32 0xb527b5d3 Query thread_id=328 exec_time=0 error_code=0

Skipped the binlog position 904 transactions in slave server

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SESSION.GTID_NEXT= '69b98063-2d31-11ea-9586-12b321a8670d:78';

Query OK, 0 rows affected (0.00 sec)

mysql> begin ; commit;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET gtid_next=automatic;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Finally , GTID Replication begins to sync pending SQL threads and upto date the data with Master server !!!

Docker with Percona Backup for MongoDB

Sounds interesting from Percona Backup tool for MongoDB !!! I just wants to try and explore the tool with docker on today !!! Docker is first time for me , but in few days docker become favorite one to use for all kind of HA scenario works

Lets move into today's practices and issues ,

Note : Percona Backup for MongoDB supports Percona Server for MongoDB or MongoDB Community Server version 3.6 or higher with MongoDB replication enabled

Step 1 : Launched the Ubuntu 16.04 machine from AWS , then updated the latest packages and installed the docker 

sudo apt-get update 

sudo apt install docker.io

sudo systemctl start docker

sudo systemctl enable docker  

Once docker installed with latest package verify the docker version using docker --version 

Step 2 : Installed two docker mongo containers with replica set enabled 

docker run --detach --name datablogs-mongo-primary --volume /var/lib/mongo:/data/db --volume /etc/mongodb.conf:/etc/mongo.conf --publish 44444:27017 mongo --replSet datablogs-repl-set

docker run --detach --name datablogs-mongo-secondary --volume /var/lib/mongo-slave:/data/db --volume /etc/mongodb-slave.conf:/etc/mongo.conf --publish 55555:27017 mongo --replSet datablogs-repl-set

We need to access the mongoDB instances outside the world , so I have publised the mongo db ports with different one 

                                                             --publish 44444:27017

                                                             --publish 55555:27017

To access the mongoDB , we need to check the IP Address of both containers using below command

docker inspect datablogs-mongo-primary | grep IPAddress

docker inspect datablogs-mongo-secondary | grep IPAddress

-- usage of extensions refer with docker help

Step 3 : Configure mongo replica 

We need to login with docker command line and configure and start the replication between mongo servers . login into primary mongo container and execute below commands in mongo shell ,

docker exec -it datablogs-mongo-primary /bin/bash

config = {"_id" : "datablogs-repl-set","members" : [{"_id" : 0,"host" : "172.17.0.2:27017"},{"_id" : 1,"host" : "172.17.0.3:27017"}]};

rs.initiate(config);

Once we initiated the replication primary mongo shell will be changed 

Step 4 : Install Percona Backup and Configure 

Before proceeding this activity , we need to update and upgrade the packages using apt-get on each mongo containers

Installed the percona backup for mongoDB with below reference URL , we need to follow percona site for proper installation 

https://www.percona.com/doc/percona-repo-config/percona-release.html

https://www.percona.com/blog/2019/12/17/percona-backup-for-mongodb-in-action/

Once installed the pbm tool , login each mongo containers  set storage path and start the pbm agent . I have used local storage path for mongo backup

storage.yaml : 

type: filesystem
filesystem:
  path: /tmp

pbm store set --config=storage.yaml --mongodb-uri="mongodb://127.0.0.1:55555/"

pbm-agent --mongodb-uri mongodb://172.17.0.2:27017 &

Step 5 : Backup and restore the collections using pbm 

Once completed the setup running the backup in secondary mongo server 

pbm backup --mongodb-uri mongodb://127.0.0.1:27017

Dropped the datablogs db and restored the backup using pbm

Finally verfied the db and collections in primary server 

Am Really happy tested percona backup for mongoDB with Docker today !!! Keep learning !!!

Sensitive Data Cleaning with MasKING

Wow !!! Its easy too restore sensitive data without any fear !!!

Its really tough sometimes , we restored the sensitive data without knowing and test mails triggered to customer as $100 deducted from your account for purchase . Its strange scenario when we missed to cleansing the customer data in DEV Sandbox !!!

Yes MasKING sensitive / Credential is easy now !!! Reference : https://github.com/kibitan/masking

Just tried simple practice for masking the paymentdb table data with masking , Its working as expected

Step 1 : Installed latest Ruby version and masking using below commands . Before doing install , update the server with latest packages

rvm install ruby-2.6.3

gem install masking

Step 2 : Create masking.yml file anywhere in your linux machine . I have created the file inside etc directory with below values

In this address is table in paymentdb and name , email are few columns in address table

Step 3 : Now ready to take backup with masking utility of paymentdb

mysqldump --complete-insert -u root -p paymentdb | masking -c /var/lib/mysql/masking.yml > /tmp/paymentdb.sql

                                

Step 4 : Restoring the paymentdb backup database into paymentdb_masked

mysql -u root -p paymentdb_masked < /tmp/paymentdb.sql

Step 5 : Yay !!! Data has been successfully masked with masking utility . Results are below

Yup !!! Saved one more escalation !!! Thanks to MasKING :)

Repair GTID Based Slave on Percona Cluster

Problem : 

We are running 5 node percona cluster on Ubuntu 16.04, and its configured with master-slave replication. Suddenly we got an alert for replica broken from slave server, which was earlier configured with normal replication 

We have tried to sync the data and configure the replication, unable to fix that immediately due to huge transactions and GTID enabled servers. So we have decided to follow with innobackupex tool, and problem fixed in 2 hours 

Followed all the steps from percona doc and shared the experience in my environment 

Steps involving to repair the broken Replication :

1.Backup master server 

2.Prepare the backup 

3.Restore and Configure the Replication

4Check Replication Status

1.Backup master server 

We need to configure the complete master server database into a slave. So we are taking a full backup from the master server, before proceeding the backup we should check disk space available for the backup because of its system-level backup 

We have created a specific user for taking a backup from master server, once the backup is completed will get OK like below,

2. Prepare the backup for Restore

We need to prepare the backups to apply the transaction logs into data files, once it is OK, data files are ready to restore 

Before moving the prepared files into slave server, verify the GTID information from xtrabackup_binlog_info 

3.Restore and Configure the Replication

We can restore the backup else to create a new data directory and move the files into the directory. Stop mysql service before start restoring

innobackupex --copy-dir /mnt/2019-11-26_09-35-31

Default it will restore the data related files in server datadir , using the server my.cnf configuration file .Once changed the data directory, we need to change the owner and permission of the MySQL data directory 

chown mysql:mysql /mnt/mysqldatanew

And restart the service with a new data directory, once its started login with master MySQL root user password. Because we have taken file backup from the master so metadata will be the same of master

Execute below commands to configure the replication 

4. Check Replication Status

Once slave is configured, verify the replication status as below 

Also, Slave has retrieved a new transaction 

Thanks for Reading !!!

Database Clusters with MongoDB Sharding

MongoDB supports horizontal scaling of the data with the help of the shared key. Shared key selection should be good and poor shared key split the data in only a single shared 

Today have tried a simple setup of MongoDB sharding with two shared nodes, sharing the simple steps to configure the same. Initially prepared with server lists and IP addresses of each server to avoid confusion by myself 

Launched 6 ubuntu servers and installed mongo in all the servers, set hostname accordingly. As above 2 mongo shared, 1 mongo router and 3 mongo config servers have been launched. Before installing mongo update the system with the latest packages 

sudo apt-get update && sudo apt-get upgrade

Then start installing the MongoDB in all the servers 

  1.sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E52529D4
2.sudo bash -c 'echo "deb http://repo.mongodb.org/apt/ubuntu xenial/mongodb-org/4.0 multiverse" > /etc/apt/sources.list.d/mongodb-org-4.0.list'

3.sudo apt update 

4.sudo apt install mongodb-org

5.systemctl enable mongod.service 

6.systemctl start mongod.service

For secure authentication, MongoDB recommends the X.509 certificate to secure connections between production systems. we need to create a key file for secure authentication between the members of your replica set.

Initially in primary config server create the key file with OpenSSL and copy the same SSL file to another server in the same location

1.openssl rand -base64 756 > mongo-keyfile

2.sudo mkdir /data/mongo

3.sudo mv ~/mongo-keyfile /data/mongo

4.sudo chmod 400 /data/mongo/mongo-keyfile

5.sudo chown mongodb:mongodb /data/mongo/mongo-keyfile

Once a key file is created, add value in all the /etc/mongod.conf. Its should be same as below because mongod.conf file is case sensitive

security:

  keyFile: /opt/mongo/mongodb-keyfile

sudo systemctl restart mongod

Main Components : 

Config Server: This stores metadata and configuration settings for the rest of the cluster

Query Router: The Mongols daemon acts as an interface between the client application and the cluster shards. It’s like a listener of mongo instances

Shard: A database server that holds a portion of your data. Items in the database are divided among shards either by range or hashing

Steps involving in the configuration : 

   1.Configure the config servers

   2.Configure the Query Router

   3.Configure the sharding 

1.Configure the config servers 

Using single config server is not enough to maintain the metadata at the time of the disaster, we are setting up one primary and two secondary replica set 

On each config server, edit below values in mongod.conf. bind IP values will be different for each server 

Then restart mongo service using below command on each config servers 

sudo systemctl restart mongod

Once restarted initiate the config server using below command, please replace the hostnames accordingly 

And do check the rs.status of config server replica sets 

Configuring the config server is completed, let's move on next steps 

2.Configure the Query Router

Using the config server metadata information, send read and write queries to the correct shards 

Create /etc/mongos.conf file and add the below lines 

Create a new systemd unit file for mongos called /lib/systemd/system/mongos.service

Once we created files, needs to enable systemctl for mongos.service using below commands

1.sudo systemctl stop mongod
2.sudo systemctl enable mongos.service
3.sudo systemctl start mongos
4.systemctl status mongos

3.Configure the sharding servers

On each shared server, edit below values in mongod.conf. Bind IP values will be different for each server and restart the mongod service 

Once everything is completed, using mongo query router address login into any one of shared servers, I have created a separate admin user for MongoDB. If required create it 

mongo 172.31.42.214:27017 -u adminuser -p --authenticationDatabase admin

Connect mongos interface and add the shared nodes, if you have replica set for shared nodes steps will be different to add shared 

It’s done, shared000 and shared001 are added. There are many links available for sharding the database and collections to mangos. Tried with below examples for my test and its working as expected 

Thanks for reading !!!

MySQL for Entity Framework

The tutorial guides you through MySQL Database connectivity with Entity Framework

We are facing entity framework database provider compatible with version could not be found for your data connection issue while connecting with MySQL 5.7 and 8.0. Problem with MySql.Data.Entity.EF6.dll missing and not able to connect with Entity framework

As per my requirements, the customer wants to migrate MSSQL to MySQL server. So I want to use any version of MySQL, installed with below configurations and achieve the connectivity 

1.MySQL Server : 5.6.41
2.MySQL Connector/Net : 6.9.12
3.Entity Framework : 6.2.0
4.Visual Studio : Professional 2017

Configuration steps are below,

1. Download required versions,

MySQL - https://dev.mysql.com/downloads/windows/installer/5.7.html

Visual Studio - https://visualstudio.microsoft.com/downloads/

2. Once downloaded, Launch web API project and Install entity framework 6.2.0 through NuGet Packages 

3.Install the MySQL 5.6 with Connector/Net 

Once you installed, you will see below dll files local server 

C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.12\Assemblies\v4.5

4. Add the dll files in Project reference and rebuild the project 

Once we rebuild, open the web.config file add the below lines if already provider tag part exist remove and add it 

5. Again rebuild the project and add the connection. You will successfully be connected

Thanks for reading ...