Orchestrator mysql setup

 I installed Orchestrator on ec2 instances for monitoring the DB replication and found it a wonderful tool for managing replication/failover. In my test setup i have  1 master mysql server --> 2nd replica --> 3rd replica (chained replication). I installed Orchestrator on 3rd replica.

Below are the steps for installation:

jq is required for installation hence we need to install that as the first step:

1. yum install epel-release

2. yum install jq

Install orchestrator from github as per the OS platform

3. yum install https://github.com/github/orchestrator/releases/download/v3.0.11/orchestrator-3.0.11-1.x86_64.rpm

The installed files consist of an example file with basic configuration : orchestrator.conf.json.sample that can be coped to /etc/orchestrator.conf.json and then we edit this file.

As per the official documentation i made these changes..

"MySQLOrchestratorHost": "127.0.0.1",

"MySQLOrchestratorPort": 3306,

"MySQLOrchestratorDatabase": "orchestrator",

"MySQLOrchestratorUser": "orchestrator",

"MySQLOrchestratorPassword": "orch_backend_password",

...

Then we need to create new user on master server to detect replication topology:

GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'%' IDENTIFIED BY 'orch_topology_password';

then edit the file /etc/orchestrator.conf.json and add the below:

"MySQLTopologyUser": "orchestrator",

"MySQLTopologyPassword": "orch_topology_password",

Then start the service:

service orchestrator start

Once service is started we can access using ip : https://10.0.0.1:3000/

In My setup i initiated a failover using orchestrator from Master1 to Replica1 , hence the Master1 server is now an independent mysql server. Post failover Replica1 became master server and Replica 2 is the slave server. To initiate failover we need to make a few changes in the config file, that i will mention in another post. Am still exploring this tool and the capabilities

After sometime i created the original topology again , well i added the Master1 DB server back as the master and pointed replica2 to use master1 and orchestrator was quick to detect the change , below is the updated topology post the change.

Percona Mysql 5.6.* Minor version upgrade

 Today i upgraded percona mysql version 5.6.46-86.2 to 5.6.51-91.0.

This was a minor version upgrade for testing purposes and below are the steps i have used on Centos 7.7

Before starting ensure you have a backup of database and backup of my.cnf (db config file).

Install Versions 5.6.46-86.2 on Centos

Before starting install percona repo using yum:

$yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

To test the repo:

yum list | grep percona

Install Percona Mysql server based on your version requirement as below using yum:

1. yum -q install Percona-Server-server-56-5.6.46-rel86.2.1.el7 Percona-Server-client-56-5.6.46-rel86.2.1.el7 Percona-Server-shared-56-5.6.46-rel86.2.1.el7

the above should run nicely on the server and install the server as per the default setup on centos. 

Start the database using service mysqld start

2. Login to the server and create a few database 

mysql > create database test1;

mysql> create database test2;

mysql> create database test3;

3. Remove the above installed version using : 

  yum remove Percona-Server* (this should remove installed version and not the db related files)

4. Install the latest version or required version using below :

    yum install Percona-Server-server-56

 After successful completion start mysqld service : service mysqld start

This should bring up the service and you should be able to login

mysql > select @@version:

 5.6.51-91.0

mysql > show databases;

  information_schema

  mysql

  performance_schema

  test1

  test 2

  test3

We have successfully completed minor database version upgrade with this.

Creating Mysql RDS instance using Terraform

 I have been working on terraform for sometime now and find it very useful , We can create most of the cloud infrastructure using terraform including RDS-MYSQL instances. 

Today i will be posting a simple code to create a MYSQL RDS instance with basic configuration for a beginner.

Create a main.tf file and add the below values in your testing environment. Am assuming AWS access keys are already present in the laptop that is connecting to the AWS environment for access , otherwise terraform will be unable to communicate with AWS.

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

terraform {

 required_providers {

 aws = {

       source = "hashicorp/aws"

      version = "~> 3.27"

       }}}

 provider "aws" {

  profile = "default"

 region = "specify region"

}

resource "aws_db_parameter_group" "default" {

 name = " testnew"

family = "mysql5.7"

}

resource "aws_db_instance" "default" {

identifier =  "testdb"

allocated_storage = 10

engine = "mysql"

engine_version = "5.7"

instance_class = "db.t3.micro"

name = "mydb"

username = "testuser"

password = "Specify password"

parameter_group_name = "newpggroup"

}  

once the above code is complete, run a terraform init, terraform plan to validate the plan and finally terraform apply..

Post this you should see an RDS instance created based on the inputs provided above

This is a very basic example for someone to start with terraform and we can provide many other parameters in the above code. Do refer to terraform documentation for the same 

Log_slave_updates Mysql

  Today we will discuss about a parameter related to mysql Replication - Log_slave_updates.

This parameter is useful when configuring chain replication e.g. A-->B-->C . Here A is the master server and B is a replica of A and B further replicates to Mysql Replica C. If we don't enable this parameter on Replica B then updates coming to B will not be replicated to Replica C.

Normally, a replica does not log to its own binary log any updates that are received from a source server. Enabling this variable causes the replica to write the updates performed by its replication SQL thread to its own binary log. 

We will look at the process of updating this parameter on Replica server B:

mysql > show variables like "%log_slave%";

Variable_name             Value

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

log_slave_updates         OFF

Now we cannot change this parameter dynamically and changes need to made to my.cnf file followed by mysql restart. in my case the files is /etc/my.cnf.

I added the line as below and restarted mysql replica: 

log_slave_updates = ON;

Post that again checked the status of variable on the replica Server B

mysql > show variables like "%log_slave%";

Variable_name             Value

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

log_slave_updates         ON

Now any updates received by Replica B will be reflecting in Replica C as well.

Install AWS CLI on Centos

 Today we will look at installing AWS CLI on Centos, i installed on one of the EC2 instances recently and will share the steps as below:

1. Download AWS CLI:

url "https://s3.amazonaws.com/aws-cli/awscli-bundle.zip" -o "awscli-bundle.zip"

2. Once downloaded, we need to install the zip, but first need to unzip. IN my case unzip was not installed on the host , hence i have downloaded and installed as below:

yum makecache

yum install unzip

3. Once done use unzip to unpack the downloaded bundle:

unzip awscli-bundle.zip

4. Next step is to install :

./awscli-bundle/install -b ~/bin/aws

........

You can now run: /root/bin/aws --version

Refer AWS documentation as well to get further clarity on the steps/versions

Install mysql 8.0 on Centos 7

 Today we will look at installing mysql 8.0 on centos 7, i created a new ec2 instance having centos 7.0 and then installed mysql 8.0.

to install mysql 8.0 follow the below steps:

here i have used yum to install 8.0 edition:

1. yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

.............

...........

Running transaction

  Installing : mysql80-community-release-el7-1.noarch                                                                                                                                                   1/1 

  Verifying  : mysql80-community-release-el7-1.noarch                                                                                                                                                   1/1 

Installed:

  mysql80-community-release.noarch 0:el7-1                                                                                                                                                                  

Complete!

post this we need to install :

user@instance]# sudo yum install mysql-community-server

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

 * base: d36uatko69830t.cloudfront.net

 * extras: d36uatko69830t.cloudfront.net

 * updates: d36uatko69830t.cloudfront.net

mysql80-community                                                                                                                                                                    | 2.6 kB  00:00:00     

mysql80-community/x86_64/primary_db                                                                                                                                                  | 128 kB  00:00:00     

Resolving Dependencies

--> Running transaction check

....................

................

Installed:

  mysql-community-libs.x86_64 0:8.0.22-1.el7                      mysql-community-libs-compat.x86_64 0:8.0.22-1.el7                      mysql-community-server.x86_64 0:8.0.22-1.el7                     

Dependency Installed:

  mysql-community-client.x86_64 0:8.0.22-1.el7                     mysql-community-client-plugins.x86_64 0:8.0.22-1.el7                     mysql-community-common.x86_64 0:8.0.22-1.el7                    

Replaced:

  mariadb-libs.x86_64 1:5.5.64-1.el7                                                                                                                                                                        

Complete!

user@instance]# systemctl start mysqld

this should start mysql service

The temporary root password can be found in the mysql log file and that can be used to login to the server.

however in this version, when we login with the given root password it will ask to reset this using 'alter command'.