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. We have followed created a new directory and change datadir values in mysqld.conf 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://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

  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,

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

Friday, March 24, 2017

SQL*Loader-704: Internal error: ulconnect:OCIServerAttache [0] ORA-12154: TNS:could not resolve the connect identifier specified

  When you see the above error , first thing come into mind is 

Is our tnsnames.ora file is exist , if its there used connection string is there

If you have it correctly and still issue is persist .. Then whats happens everything is good 

1.First thing you have to check if tnsnames is fine

sqlldr scott/HD@y45Hhds@orcl control=E:\test\table.ctl log=E:\test\table.log

Check if you have password having '@' symbol

2.Then change it as below , you will be able to load the millions of files into oracle table 

sqlldr scott/\"HD@y45Hhds\"@orcl control=E:\test\table.ctl log=E:\test\table.log

Make it useful ..

Tuesday, July 21, 2015

Set up 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.,

Friday, April 18, 2014

Experience with McAfee Mysql Audit Plugin?

This blog provides step by step tutorial for setting up McAfee MySQL Audit Plugin in MySQL-Server-5.5.37 and platform Linux 2.6.39-400.17.1.e16uek.x86_64 #1

It is difficult task comparing with others auditing tasks.Auditing much important for all DBMS.Comparing with other DBMS MySQL auditing need to be third party plugins otherwise use MySQL Enterprise Audit Log Plugin.Using general log its performance degradation in production environment.

Third party plugin as McAfee MySQL Audit Plugin.McAfee and this plugin provide audit capabilities for MySQL Database and MySQL started supporting for plugins API since MySQL 5.1.

Below processes successfully tested in my local environment.

  • Installing McAfee Audit Plugin needs MySQL 5.1 to latest version of MySQL Server.
  • Only Linux Environment is feasible for McAfee Audit Plugin.
Step 1 : Download Audit plugin for your version [root@selva]# wget https://github.com/downloads/McAfee/mysql-audit/audit-plugin-MySQL-5.5-1.0.5-473-Linux-x86_64

Step 2 : Extract the audit-plugin-mysql-5.5-1.0.5-473-linux-x86_64.zip [root@selva]# unzip audit-plugin-mysql-5.5-1.0.5-473-linux-x86_64.zip

Step 3 : Check the location of plugin directory in Linux using MySQL server 
[root@selva]# mysql -u root -p
mysql>show global variables like 'plugin_dir';

The plugin_dir values in my environment /usr/lib64/mysql/plugin/

Step 4 : Copy the unziped plugin into plugin directory [root@selva]# cp ./audit-plugin-mysql-5.5/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/

Step 5 : When we done copy the plugin into directory use install plugin following the command
mysql>INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

When you installing this command you lucky gey Query OK result.Otherwise you will get any of below different errors in installation,
  • SQL Error (1126):can't open shared library '/usr/lib64/mysql/plugin/libaudit_plugin.so'(errno:2 /usr/lib64/mysql/plugin/libaudit_plugin.so:undefined symbol:_cxa_pure_virtual)
  • SQL Error (1123):can't initialize function 'AUDIT';Plugin initialization function failed.
Using this command you will get error log path for mysql server 
mysql>show global variables like 'log_error';

You will get these error in log_error file , 
  • 140417 12:06:07 [Note] Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1
  • 140417 12:06:07 [Note] Audit Plugin: mysqld: /usr/sbin/mysqld (b77de3909af62707ca068be61a64f406)
  • 140417 12:06:07 [Note] Audit Plugin: Couldn't find proper THD offsets for: 5.5.37
  • 140417 12:06:07 [ERROR] Plugin 'AUDIT' init function returned error.
  • 140417 12:06:07 [Note] Audit Plugin: deinit
Never get worried failure leads to success , Use correct version of audit plugin you will get install soon.Once you struggling in this do one more things,try this all plugin for your required environment surely you will get result.Follow below link to get all versions audit plugins,

Step 6 : Once you successfully installed , check 

mysql>show plugins;

You will get list of plugins installed in the mysql server.You can find audit plugin version you have installed in the mysql server,


Step 7 : Once audit plugin installed and loaded into mysql server , we can set the audit plugin confguration system variables,check available audit plugin variables using below command,

mysql>show global variables like 'audit%';

Check available audit plugin configuration variables below link,

Step 8 : We must need to set only three or four configuration variables , changes made in my.cnf file or via command.we can set this using command,no need to add via my.cnf file.

All audit activities store in the JSON format.It supports directly auditing activities to a file or a Unix socket .

we need to set , audit_json_file disable/enable , audit_record_cmds like DDL or DCL and Connect,Failed Login,Quit , audit_record_objs like databases (mysql,*).

mysql>set global audit_json_file=1;

mysql>set global audit_record_cmds='Connect,Quit,Failed Login,insert,drop,create';

mysql>set global audit_record_objs='mysql,sakila';

After completing this check again , for all configuration variables is updated .

mysql>show global variables like 'audit%';

Step 9 : Now check the mysql-audit.json file in mysql datadir it will update the result.

Note:Will Truncate the mysql-audit.json file will be replace the data once you have did any changes in databases.If you deleted the mysql-audit.json need to install 'libaudit_plugin.so' again.

Step 10 : Steps will not complete mysql-audit.json file will growth like general log , so we need to backup or update into another file , keep log in separate json files . forthcoming posts will update export json file data into mysql table and the processes.

Thank You,

Be Better Than You Were Yesterday