Monday, December 16, 2019

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 :

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

Tuesday, November 26, 2019

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 !!!

Monday, November 25, 2019

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:// --recv E52529D4
2.sudo bash -c 'echo "deb 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

  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 -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 !!!

Monday, September 24, 2018

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,


Visual Studio -

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 ...

Tuesday, July 21, 2015

Deploy a MySQL Cluster step by step in Linux

Before proceeding installation things , go through cluster basics from the MySQL website and forums. It will be helpful to easily understand installation and configuration. First time is difficult to get things to done in one shot is not possible , no problem will learn it .

MySQL Cluster Installation will be mostly done by two ways.,
  1. Using binaries
  2. rpm(Rethat,CentOS..,)
 Different Linux flavors has different methods to install format , here I have explaining with CentOS7 .

1.Have prepared two different machines to install MySQL Cluster . First you have to login two different machines

Two VM Machines
2.Be ready with mysql rpm's

3.Now get ready to start installation of MySQL Cluster

Create mysql user ...

4.Install downloaded Server and Client rpm's in both VM

5.MySQL Server needs to be install fully

6.Please verify cluster/mysql folder,once installation is completed

All files needs to be there , for MySQL Cluster you will get new database called ndbinfo .

7.This is time to create configuration file for mysql cluster environment

Only two configuration files needs to create for mysql cluster
  • my.cnf for mysql (SQL node)
  • config.ini for cluster 
Config.ini file for my cluster environment below
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
#DataMemory=80M # How much memory to allocate for data storage
#IndexMemory=18M # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the "world" database takes up
# only about 500KB, this should be more than enough for
# this example Cluster setup.
#[tcp default]
# TCP/IP options:
# portnumber=2202 # This the default; however, you can use any
# port that is free for all the hosts in the cluster
# Note: It is recommended that you do not specify the port
# number at all and simply allow the default value to be used
# instead
# Management process options:
hostname= # Hostname or IP address of MGM node
# Management process options:
hostname= # Hostname or IP address of MGM node
# Options for data node "A":
# (one [ndbd] section per data node)
hostname= # Hostname or IP address
datadir=/var/lib/mysql-cluster/ # Directory for this data node's data files
# Options for data node "B":
hostname= # Hostname or IP address
datadir=/var/lib/mysql-cluster/ # Directory for this data node's data files
# SQL node options:
Same file you have to prepare and place in /var/lib/mysql-cluster directory . If any single word is missed cluster will not be start properly.

As per standalone mysql setup you have to prepare my.cnf file for SQL Node 

8.This is very important step you follow before you get into cluster environment

You have to start cluster as below.,
  1. Start management node in both mysqlone and mysqltwo 

     2.  Start data node in both mysqlone and mysqltwo

     3.  Start SQL node in both mysqlone and mysqltwo , after data node is up . If you start before data node might be crash.

 9.Now you have to check cluster status in any one of VM machine , using ndb_mgm utility

Once you start the data node , it will be in starting mode . Meanwhile you have to check howmuch data and index memory cluster is using as below.,

It will be start slowly never expect as much as you started , once data and management node start you will see like below.,

if data and index usage is above 90% you have to increase RAM memory and increase data memory , index memory parameter changes in config.ini file .

10.After you have to started Data node you will needs to start the SQL Node as said before

Once you started management,data and sql node in order wise . You ready with access high availability feature in MySQL .

Please contact me if you need further assistance on MySQL support , Thanks in advance .

Monday, April 21, 2014

MySQL Server Instance Configuration Wizard (Windows is Not Responding)

On windows me had this problem two times , first in my own laptop , second time in my friend laptop . I took long time to resolve this issue but before done so many methods to resolve it .

I will share the experience to resolve the instance configuration error step by step,

Error : MySQL Server Instance Configuration Wizard (Windows is Not Responding)

Steps to resolve it ,

First we clear when you get this error,in my experience first time had installed the MySQL server and deleted MySQL folder in my machine or you have tried to uninstall and install MySQL Server.

Step 1 : In your windows installation driver , you have Program files(x64),Program files and Program Data.You need to delete MySQL Folder in these three folders.But few of you installed InnoDB Table Settings in some other drive , so you need delete in that installed driver.I have installed into D drive.

Step 2 : Uninstall MySQL Server in Control Panel\All Control Panel Items\Programs and Features\MySQL

Step 3 :
In your keyboard press HOME+R in this cmd prompt type regedit you will get registry editor HKEY_LOCAL_MACHINE-->System-->CurrentControlSet-->services-->Delete MySQL folder with subkeys.

Step 4 : Now you have completely removed mysql server folder in your machine . Then why waiting for next step lets start MySQL Server installation.

Step 5 : Will we meet on next issues.,