fbpx

Automation

Essential AWS Services for Database Administrators to Learn

by

Why AWS?

Cloud is becoming a vital part of Database Administration because it provides various database services & Infrastructure to run Database Ecosystems instantly. AWS (Amazon Web Services) is one of the pioneers in the Cloud according to the Gartner magic quadrant. Knowing more cloud infrastructure technologies is going to give more mileage to your Administrator career. In this article, you will find some of the AWS services which Database Administrators should know as they are basic to run Database opration.

Essential AWS Services List For Database Administrator (DBA)

Network
VPCs
Subnets
Elastic IPs
Internet Gateways
Network ACLs
Route Tables
Security Groups
Private Subnets
Public Subnets
AWS Direct Connect

Virtual Machine 
EC2
AWS Work Space

Storage
EBS
EFS
S3

Database as Services (RDS)
MySQL / MariaDB
PostgreSQL
Oracle
Micrsoft SQL Server
AWS Aurora PostgreSQL/MySQL

Database Managed Services
AWS Dynamo DB
AWS Elasticsearch 
Amazon DocumentDB

Messaging & Event Base Processing 
Apache Kafka (Amazon MSK)

 

Warehousing/ OLAP /Analytics Stagging DB
AWS Redshift

 

Monitoring 
Cloud watch
Amazon Grafana
Amazon Prometheus

 

Email Service
Amazon Simple Notification Service

Security 
IAM
Secrets Manager

Database Task Automation
AWS Batch
AWS Lambda
Cloud Formation

Command-line interface (CLI) to Manage AWS Services
AWSCLI

Migration 
Database Migration Service

Budget 
AWS Cost Explorer
AWS Budgets

Some other Services & Combination worth of Exploring

Bastion Host For DBA
MongoDB running on EC2
ELK (Elastic Search , LogStach, Kibana) running on EC2
Tunnels for Non stranded ports for Database Connections for more security
pg_pool or pg_Bouncer for PostgreSQL Databases

Stay Tuned For Latest Database, Cloud & Technology Trends

Read More >>

Take MySQL backup From Jenkins Job

by

Take MySQL Database Backup From Jenkins

In this post, we will explain and practically show how you can configure MySQL database backup using Jenkins jobs. in easy words, you will automate the MySQL database backup process from Jenkins GUI and MySQL dump command. Normally we use MySQL dump from CLI or using the windows option. In this case, just automate the MySQL dump triggering from a shell script which will be called by Jenkins job with parameter. MySQL dump needs few parameters to run just pass them by build with parameter option in jenkins.

 

1. Log to Jenkins default URL and port: http://192.168.56.21:8080/

If you want to install and setup Jenkins see this article

2. Go to New items

3.  Give some Name and select FreeStyle project

 

4.  In the build section click on Add build step radio button and select Execute shell.

5. Select boolean parameter for MySQL database host and database superuser password which is root password in MySQL database case.

 

6.  In this build part give the shell script name with full path and parameters like $HOST_IP & $Password

 

Shell Script

[root@master01 ~]# cat /opt/jenkins_scripts/mysql_full_db_backup.sh
#!/bin/bash
HOST_IP=$1
PASSWORD=$2
echo "starting mysql database full backup"
mysqldump -h $HOST_IP --all-databases --single-transaction --quick --lock-tables=false > /opt/jenkins_scripts/mysql_backup/full_backup_$(date +%F_%N).sql -u root -p$PASSWORD
echo "Backup has been done"

 

[su_box title=”IMP Note” box_color=”#fe2227″ title_color=”#101112″]Note: Make sure Jenkins have execute (chmod +x /opt/jenkins_scripts/mysql_full_db_backup.sh)  privilege on script[/su_box]

 

7. Let’s execute the Jenkins job

7.1  click on the the MySQL database backup job

 

7.2  Click on build with Parameter

 

7.3  Give your database server name or IP. In my case Mysql database running on the same server that’s thy, I will give localhost and database root password

 

7.4 Once your job is executed go to that job open it in console output mode and check the status of the job. And you are DONE!!!

Read More

How to fix ORA-28368: cannot auto-create wallet
AWS Services and their Azure alternatives 
How to Manage AWS S3 Bucket with AWS CLI

How to connect PostgreSQL Database from PgAdmin
How to create AWS rds PostgreSQL Database
Convert pem to ppk
How to Install PuTTy on Window
How to Install and create AWS EC2 Instance using Terraform
AWS MySQL RDS Database Creation using AWSCLI
How to Create MySQL Database with AWS RDS
How to connect to AWS MySQL / MariaDB RDS or EC2 database from MySQL WorkBench

How to Manage AWS S3 Bucket with AWS CLI

by

How to Manage AWS S3 Bucket with AWS CLI (Command Line)

 

In this article, we are going to see how we can manage the s3 bucket with AWS s3 CLI commands. AWS s3 CLI command is easy really useful in the case of automation. Using AWS s3 cli you can mange S3 bucket effectively without login to AWS console.

Prerequisites

  • AWS Access Key ID
  • AWS Secret Access Key
  • AWS CLI should be configured on your laptop or desktop.   You can see my article for the AWS CLI configuration.

 

List of Commands to manage AWS s3

 

#1. Create AWS s3 bucket

aws s3api create-bucket --bucket thedbadminbuk --region us-east-1
{
    "Location": "/thedbadminbuk"
}

 

#2. List all the S3 bucket which are already created

aws s3 ls
2020-04-13 15:22:48 abhitest1410
2020-04-13 18:00:54 thedbadminbuk

 

#3.  List all the bucket data

aws s3 ls s3://thedbadminbuk

 

#4. List all the data inside the directory inside the bucket

aws s3 ls s3://thedbadminbuk --recursive

 

#5. Copy file to s3 bucket

aws s3 cp server.txt s3://thedbadminbuk
upload: ./server.txt to s3://thedbadminbuk/server.txt

 

#6. Copy file to s3 bucket to another s3 bucket

aws s3 cp s3://thedbadminbuk123/server.txt s3://thedbadminbuk
upload: s3://thedbadminbuk123/server.txt to s3://thedbadminbuk/server.txt

 

#7. Copy Directory to S3 bucket

aws s3 cp Desktop s3://thedbadminbuk --recursive

 

#8. Copy Directory from one S3 bucket to another s3 bucket

aws s3 cp s3://thedbadminbuk123/Desktop s3://thedbadminbuk --recursive

 

#9.  Move file from local file system to s3 bucket

aws s3 mv server.txt s3://thedbadminbuk/client.txt

 

#10. Move file from one s3 bucket to another s3 bucket

aws s3 mv s3://thedbadminbuk/server.txt s3://thedbadminbuk/client.txt
aws s3 mv s3://thedbadminbuk/client.txt s3://thedbadminbuk/client.txt

 

#11. Move file from s3 bucket to local file system

aws s3 mv s3://thedbadminbuk/client.txt /tmp
move: s3://thedbadminbuk/client.txt to ../../tmp/client.txt

 

#12. Delete file from s3 bucket

aws s3 rm s3://thedbadminbuk/client.txt
delete: s3://thedbadminbuk/client.txt

 

#13. Delete Directory  from s3 bucket

aws s3 rm s3://thedbadminbuk/Desktop
delete: s3://thedbadminbuk/Desktop

 

#14. Sync file form local file system to s3 bucket

aws s3 sync Desktop s3://thedbadminbuk/

 

#15. Sync from s3 bucket to the local folder

aws s3 sync s3://thedbadminbuk/Desktop Desktop

 

#16. Presign URL for any S3 bucket objects. It will give you an https link to access the object from the browser or can embed it into the code. Like jpeg file etc.

aws s3 presign s3://thedbadminbuk/server.txt
https://thedbadminbuk.s3.amazonaws.com/server.txt?AWSAccessKeyId=AKIA5Q6SWIBFKZURW2DX&Expires=1586786199&Signature=qK5wPSzykmQB1tJjQLMKrf8vnys%3D

 

#17. Presign URL with expires value. It means presign URL will expire in 600 seconds.

aws s3 presign s3://thedbadminbuk/server.txt --expires-in 600
https://thedbadminbuk.s3.amazonaws.com/server.txt?AWSAccessKeyId=AKIA5Q6SWIBFKZURW2DX&Expires=1586783700&Signature=nMqqEHfcxu1HUbg0%2BWnp7sdSwp0%3D

 

#18. Delete s3 bucket

➜  Desktop aws s3 rb s3://thedbadminbuk
remove_bucket failed: s3://thedbadminbuk An error occurred (BucketNotEmpty) when calling the DeleteBucket operation: The bucket you tried to delete is not empty


➜  Desktop aws s3 rb s3://thedbadminbuk --force
delete: s3://thedbadminbuk/551222XXXXXXXXXX.PDF
delete: s3://thedbadminbuk/Old Desktop/Screenshot.png

 

#19. Check bucket file size

➜  ~ aws s3 ls s3://thedbadminbuk --recursive  --human-readable
2020-04-13 18:40:12   10.0 KiB .DS_Store
2020-04-13 18:40:12    0 Bytes .localized
2020-04-13 18:40:12  193.2 KiB 5523XXXXXXXXXX68_16-03-2020.PDF
2020-04-13 18:40:28  162.7 KiB Old Desktop/Screenshot 2020-03-29 at 5.56.10 PM.png
2020-04-13 18:40:28  160.8 KiB Old Desktop/Screenshot 2020-03-29 at 5.56.57 PM.png
2020-04-13 18:40:12  164.9 KiB Old Desktop/Screenshot 2020-03-29 at 6.49.44 PM.png
2020-04-13 18:40:12  174.9 KiB Old Desktop/Screenshot 2020-03-29 at 6.49.57 PM.png
2020-04-13 18:40:14   66.4 KiB Old Desktop/Screenshot 2020-04-03 at 3.21.08 PM.png
2020-04-13 18:40:12  112.3 KiB Old Desktop/Screenshot 2020-04-04 at 11.19.30 PM.png
2020-04-13 18:40:12   98.7 KiB Old Desktop/Screenshot 2020-04-06 at 1.31.14 AM.png
2020-04-13 18:40:12  102.6 KiB Old Desktop/Screenshot 2020-04-06 at 1.44.48 AM.png
2020-04-13 18:40:12  104.2 KiB Old Desktop/Screenshot 2020-04-06 at 1.46.23 AM.png
2020-04-13 18:40:12  115.4 KiB Old Desktop/Screenshot 2020-04-06 at 4.38.17 PM.png
2020-04-13 18:40:13    3.7 KiB Old Desktop/Screenshot 2020-04-10 at 10.49.35 AM.png

 

 

#20.  Total s3 Bucket size

➜  ~ aws s3 ls s3://thedbadminbuk --recursive  --human-readable --summarize
2020-04-13 18:40:12  193.2 KiB 5523XXXXX.PDF
2020-04-13 18:40:28  162.7 KiB Old Desktop/Screenshot10 PM.png
2020-04-13 18:40:28  160.8 KiB Old Desktop/Screenshot57 PM.png
2020-04-13 18:40:12  164.9 KiB Old Desktop/Screensho44 PM.png
2020-04-13 18:40:12  174.9 KiB Old Desktop/ScreenPM.png
2020-04-13 18:40:14   66.4 KiB Old Desktop/Scrpng
2020-04-13 18:40:27   85.1 KiB psql/4.png
2020-04-13 18:40:27  192.9 KiB psql/5.png
2020-04-13 18:40:27  107.9 KiB psql/6.png
2020-04-13 18:40:27  152.3 KiB psql/7.png
2020-04-13 18:40:27   72.0 KiB psql/8.png
2020-04-13 18:40:28   94.0 KiB psql/9.png
2020-04-13 18:40:28    0 Bytes test123
2020-04-13 18:40:28   59 Bytes todo.txt

Total Objects: 117
   Total Size: 17.8 MiB

 

#21. Exclude file while copy

aws s3  cp  ~/thedbadmin s3://thedbadminbuk/  --recursive --exclude ".ssh/*"
aws s3  cp ~/thedbadmin  s3://thedbadminbuk/  --recursive  --exclude   "thedb*.txt"

 

#22.  Include and exclude file while file copy.

aws s3 cp ~/thedbadmin s3://thedbadminbuk/ --recursive --exclude "*" --include "*.bash"
aws s3 cp ~/thedbadmin s3://thedbadminbuk/ --recursive --exclude "*" --include "*.sh" --include "*.bash"

 

Like our Facebook Page

 

Read More

How to connect PostgreSQL Database from PgAdmin
How to create AWS rds PostgreSQL Database
Convert pem to ppk
How to Install PuTTy on Window
How to Install and create AWS EC2 Instance using Terraform
AWS MySQL RDS Database Creation using AWSCLI
How to Create MySQL Database with AWS RDS
How to connect to AWS MySQL / MariaDB RDS or EC2 database from MySQL WorkBench