RDS Mysql start and stop replica

 Mysql commands to stop and start the replica are different from the normal mysql replica command, 

To stop RDS Mysql replica use the below command:

CALL mysql.rds_stop_replication;

Start RDS Replication :

CALL mysql.rds_start_replication;

Kill Running Processes:

CALL mysql.rds_kill(processID);

Skip Replication Error :

CALL mysql.rds_skip_repl_error;

Check number of hours binary logs are retained

  CALL mysql.rds_show_configuration;

Set retention for binary logs:

CALL mysql.rds_set_configuration(name,value);

Enadble Read_only RDS Mysql

In a mysql replica , when there is a need to enable slave as Read only so that no user can modify data, this is done by updating parameter read_only to On.

However when we try to enables the same in RDS from the command line this is the error:

To enable Read_only mode in an RDS replica, we need to change the db parameter group as shown below. Change this parameter in your PG to enable read_only mode (the below one is default PG and hence won't change).

Post making the changes the Replica should be in Read only mode    

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

Upgrading Mariadb from 10.1 to 10.3 on Centos

To upgrade Maraidb from 10.1 to 10.3 use the steps mentioned on Mariadb Website, i followed the same and was able to successfully upgrade the database without facing any issues.

I will share below, it is always recommended to go through the official documentation is detail to understand what new features are available post upgrade and what will become obsolete.

As a first step ensure to have a full DB backup before starting any activity.

1. Modify the yum repository configuration to point at install 10.3 Version:

# MariaDB 10.3 CentOS repository list

# http://downloads.mariadb.org/mariadb/repositories/

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.3/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

2. Next step is to stop running mariadb Database:

 Systemctl stop Mariadb

3. Uninstall the old/existing version from the server:

yum remove mariadb mariadb-server

This should remove existing installed software, it will not remove any of the existing Data related files

4. Once above is done install the new Version of Mariadb 10.3 using yum:

yum install mariadb mariadb-server

5. After successful installation edit my.cnf/server.cnf to make any changes as per the setup

6. Start Mariadb, it should start and monitor logs for any errors

 systemctl start mariadb

7. The last step is to run mysql_upgrade command from unix prompt, this will ask for root password or a user having equivalent privileges can also be used

mysql_upgrade does two things:

1. Ensures that the system tables in the mysql database are fully compatible with the new version.
2. Does a very quick check of all tables and marks them as compatible with the new version of MariaDB .

At the end the output will be OK, and we are all done

Mariabackup for taking backup of mariadb databases

Today i have taken backup using mariabackup, with this utility you can take online full and incremental backups.

Below are the steps to be followed:

1. Create user as for backup:

MariaDB [(none)] > Create user 'mariabackup'@'localhost' identified by 'password';

MariaDB [(none)] > Grant reload,process,lock tables, replication client on *.* to 'mariabackup'@'localhost';

2. Install Maria backup:

#yum install MariaDB-backup

you will observe packages being installed and at the end will get message Complete !

3. Create a directory for backups, in my case i created /backup

4. take backups :

   #mariabackup --backup --target-dir=/backup --user=mariabackup --password=password

Backup will start and at the end you will get a message completed OK !

now we can go to the directory and check files created, you will see multiple files along with DB files that have backup related info -

backup-my.cnf,  xtrabackup_info, xtrabackup_checkpoints 

Mariadb Installation on Centos7

I recently installed Mariadb10.0 on Centos , this was on Azure. To start with i have created a VM on AZURE with centos and post Completion installed Mariadb.

Installing mariadb is fairly simple and below are the steps to be followed..:

1. Enable Mariadb Repository , create a file /etc/yum.repos.d/MariaDB.repo and add:

      name = MariaDB

    baseurl = http://yum.mariadb.org/10.4/centos7-amd64

    gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

    gpgcheck=1

2. Next step is to install MariaDB server and Client packages , use yum:

     # yum install Mariadb-Server MariaDB-Client 

    this will download the required packages and complete the installation. Galera packages are also installed as it comes bundled in this version.

3. Start Mariadb :

    # Systemctl start Mariadb

    # Systemctl status Mariadb

At this point Mariadb should be up and running. Configuration file can be found in /etc/mariadb.d/server.cnf and default directory 

is in /var/lib/mysql.

With Mariadb 10.4 you can login to database without root password:

  #mysql -uroot

  Mariadb> 

Shell script to execute mysql query

We can run mysql queries via shell scripts, since this is required at times .

Say  a query is to be scheduled via crontab and shell script.

Here is a simple shell script example, as an example:

vi testscript.sh

-----------------------------------------------------------------------
#!/usr/bin/bash

DBUSER='username'
DBPASSWD='Password'

Query='show databases'   -- example query

mysql -u $DBUSER -p $DBPASSWD  <


$Query

EOF

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

The above is a simple script to show databases and is executed with shell script..

# sh testscript.sh

We can schedule the above in crontab to run at a designated time.

Many such scripts can be written to automate daily tasks - backups etc..

Executing mysql commands from unix command prompt

We can also run mysql queries from unix command prompt. This is useful in many cases, say i want to check status of db cluster or check running processes etc.

I am sharing a basic example here:

mysql -u username -p -e "show databases;"

The above command will ask for password and share output of current databases.

This is a quick way to get required information.

Maridb Galera Cluster

So after working on Oracle RAC, SQL Server Always-On and DB2 HADR now finally i have got a chance to get hands-on Mariadb Galera Cluster and i am amazed. The Galera cluster is an Active-Active cluster , what that means you can write on any node and data is available on all nodes, changes are getting replicated quickly across all nodes and it's just so simple to install while Oracle and other clusters take too much time..

Here i will list done a few commands to monitor Galera cluster status :

MariaDB > Show status like 'wsrep%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| wsrep_protocol_version |       |
| wsrep_last_committed   |       |
| ...                    | ...   |
| wsrep_thread_count   

You will see a list of variables related to galera in the output displaying about Number of Nodes in the cluster, their ip addresses, Current status..etc..

Will write more about these in my next post related to Galera.

Scheduling Events in Mariadb/MYSQL , event scheduler/Job scheduler

Event schedulers are a way of scheduling Database jobs in Mariadb/MYSQL databases, it is similar to job schedulers used in Oracle and other databases.
There are times where we need to run certain reports or run queries at specified times, these can be accomplished using event schedulers in MariaDB database.
Before scheduling events we need to turn ON event_scheduler variable, we can check the current status using below command :

show Variables like "event_scheduler";

output :
---------------------------------
Variable_Name     |    Value
---------------------------------
event_scheduler    |      ON
---------------------------------

It is visible above that scheduler is turned ON, and hence we can proceed with scheduling jobs.

If the event scheduler is not running and event_scheduler has been set to OFF, use:

SET GLOBAL event_scheduler = ON;

Create event syntax can be studied in detail on Mariadb/MYSQL website, i will create a simple event to demonstrate here.

Create event 'test' On schedule every 24 hours starts 'Date' ENDS 'Date' On Completion Preserve Enable DO
Begin
 select * from test.test1;

We create an event named Test scheduled to run every 24 hours at a specified time with start and END date. If we don't specify On completion Preserve the event will be dropped from the database and need to recreate, hence it is important ot mention On completion Preserve.
Event is also enabled using the Enable clause.

To check event scheduled we can query information_schema.events table, that will provide information about currently scheduled events and their execution statistics.

More details regarding the syntax and scheduling can be found in mariadb website.

mysqldump backup in MariaDB/mysql

As a DBA taking backup is a common task, and mysqldump utility works with MariadB , i find it a simplest way of taking backups.
Below are few examples of taking backups using mysqldump -

1. Taking full database backup :

mysqldump --all-databases -u root -p > /backup/full-backup.sql

The above will take full backup , backup of all databases

2. Taking backup with triggers,routines,events :

mysqldump --triggers --routines --events --all-databases -u root -p > /backup/full-backup.sql

The above command is useful in taking backups of triggers, routines,events for all the databases.

3. Backup individual databases with triggers,routines,events :

mysqldump --triggers --routines --events --all-databases -u root -p dbname > /backup/full-backup.sql

Replace dbname with your database name in above

4. Export database structure without any data :

mysqldump -u root -p --no-data dbname >  /backup/db-backup.sql

5. Backup a specific table in a database :

mysqldump -u username -p db1 table1 > /backup/db1-table.sql

6. Backup a specific database :

mysqldump - u username -p db1 > /backup/db1-backup.sql

MariaDB

I have been working on a mariadb from sometime now, earlier i have worked on Mysql but lately got an opportunity to work on Mariadb. I have found things similar to MYSQL (i use all the same commands as i have used in MYSQL). Will share new posts related to the same in upcoming days