fbpx

How to Build Application and MySQL RDS using Terraform

Here is a basic example of how you can use Terraform to create an EC2 instance with Apache and an RDS MySQL database. Keep in mind that this is just a starting point and you will likely need to customize it to fit your specific needs.

First, you will need to define the provider for AWS:

				
					provider "aws" {
  access_key = "ACCESS_KEY"
  secret_key = "SECRET_KEY"
  region     = "REGION"
}

				
			

Next, you can define the EC2 instance resource:

				
					resource "aws_instance" "example" {
  ami           = "AMI_ID"
  instance_type = "t2.micro"
  key_name      = "KEY_NAME"

  security_groups = [
    "SECURITY_GROUP_NAME"
  ]

  user_data = <<-EOF
              #!/bin/bash
              yum update -y
              yum install -y httpd
              service httpd start
              chkconfig httpd on
              echo "Hello, World!" > /var/www/html/index.html
              EOF
}

				
			

Next, you can define the EC2 instance resource:a

This will create an EC2 instance using the specified AMI, with Apache installed and running. The user_data script will run when the instance is launched, installing and starting Apache, and creating a simple “Hello, World!” index page.

To create an RDS MySQL database, you can use the following resource definition:

				
					resource "aws_db_instance" "example" {
  allocated_storage    = 10
  storage_type         = "gp2"
  engine               = "mysql"
  engine_version       = "5.7"
  instance_class       = "db.t2.micro"
  name                 = "DB_NAME"
  username             = "DB_USERNAME"
  password             = "DB_PASSWORD"
  parameter_group_name = "default.mysql5.7"
  skip_final_snapshot  = true
  vpc_security_group_ids = [
    "SECURITY_GROUP_ID"
  ]
}

				
			

This will create an RDS MySQL database with the specified parameters.

You will also need to create an output to display the public IP address of the EC2 instance:

				
					output "instance_ip" {
  value = "${aws_instance.example.public_ip}"
}

				
			

With these resources defined, you can use Terraform to create the EC2 instance and RDS MySQL database by running the following commands:

				
					terraform init
terraform apply

				
			

This will create the EC2 instance and RDS MySQL database, and display the public IP address of the EC2 instance. You can then visit this IP address in a web browser to see the “Hello, World!” page.

I hope this helps! Let me know if you have any questions or need further assistance.

See the Full Code:

				
					provider "aws" {
  access_key = "ACCESS_KEY"
  secret_key = "SECRET_KEY"
  region     = "REGION"
}

resource "aws_instance" "example" {
  ami           = "AMI_ID"
  instance_type = "t2.micro"
  key_name      = "KEY_NAME"

  security_groups = [
    "SECURITY_GROUP_NAME"
  ]

  user_data = <<-EOF
              #!/bin/bash
              yum update -y
              yum install -y httpd
              service httpd start
              chkconfig httpd on
              echo "Hello, World!" > /var/www/html/index.html
              EOF
}

resource "aws_db_instance" "example" {
  allocated_storage    = 10
  storage_type         = "gp2"
  engine               = "mysql"
  engine_version       = "5.7"
  instance_class       = "db.t2.micro"
  name                 = "DB_NAME"
  username             = "DB_USERNAME"
  password             = "DB_PASSWORD"
  parameter_group_name = "default.mysql5.7"
  skip_final_snapshot  = true
  vpc_security_group_ids = [
    "SECURITY_GROUP_ID"
  ]
}

output "instance_ip" {
  value = "${aws_instance.example.public_ip}"
}

				
			

MySQL query output to csv

There are situations when we have to run the select queries on MySQL databases and redirect the output in .cav format. This is required when we have to share the data with the developer or send it to some ETL job for external tables. In addition, if you are running MySQL on AWS RDS you get limited options to get the MySQL query output to CSV. As an alternative, you can use the AWS S3 bucket but again it’s a complex and cost-involved way of getting MySQL query output to CSV. I am going to show you an easy alternative that will work on a normal Linux command prompt. Using this you can fetch a long SQL output in CSV from MySQL RDS or from on promises MySQL Database instance.

 

Step 1: log in to any jump host or bastion host

Step 2: if your SQL output is going to take a long time use screen

screen -S mysql2csv_date

Step 3 Run MySQL export batch command and sed Linux command

mysql -A -h testdb01.us-east-1.rds.amazonaws.com -u root -p --batch -e "select * from testdb01.table_x where date_created >='2021-07-01 00:00:00'  AND LENGTH(emp_id) < 30 order by date_created;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysq2csv_output.csv

Step 4: once the command is completed you will see the output in .csv under the file mysq2csv_output.csv

Hope you like the article

References: https://dev.mysql.com/doc/refman/8.0/en/select-into.html

 

Read More

Take MySQL backup From Jenkins Job

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

 

Take MySQL backup From Jenkins Job

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