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

ssh [email protected]

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

How to create AWS rds PostgreSQL Database

In this article, we are going to talk about detailed steps to create an AWS Postgres database instance on AWS cloud. Amazon RDS has two options for PostgreSQL. The first one is the normal AWS Postgres RDS database and Another one is aurora PostgreSQL. AWS Postgres gives you a lot of performance gain and HA options comparing with the normal data center (DC) database. You can get AWS Postgres with few steps. I have mentioned all possible details in the post about AWS PostgreSQL. If you need any AWS PostgreSQL pricing details you can open the AWS calculator portal and can get details about estimated the cost.

In case if you have any question let me know my comments. Let’s start creating an AWS PostgreSQL RDS database instance.

1# Login to AWS console and Search for ‘RDS‘.

#2. Click on create on Database

#3. Select PostgreSQL .

#4. Select the database template. In my case, I have selected ‘Free tier’ you can select ‘Production’ or ‘Dev/Test‘ according to your requirement. Production and Dev/test will not be free from the day one it will be chargeable.

#5. Give some meaningful database name and Master database username with password.

#6. I have selected the free database classes which come with the free tier.

#7. Based on your database storage needs to select the ‘Allocated space’.

#8. If your database is business-critical you can choose a Multi-Availability Zone for database high availability.

#9. Chose the default VPC. If you are configuring it for Production or development chose the appropriate VPC using the dropdown button.

[su_box title=”IMP Note for Additional connectivity configuration” box_color=”#fe2227″ title_color=”#101112″]In case if you like to access the database from your laptop/Desktop. Select the option ‘Publicly Accessible‘ to yes.[/su_box]

#10. Chose the option according to your application and the AWS ecosystem setup.

#11. To enable Cloudwatch logs or backup you can configure ‘Additional configuration‘.

#12. Click on ‘Create Database‘

#13. It will take a few minutes to create database.

#14. You can view the database credentials after clicking on ‘View credentials details‘ .

#15. You will see this message once your database is ready for action.

Please comment if you have any doubt. Like our Facebook page

How to Become Oracle apps DBA?
What does DBA mean?
150 Oracle DBA Interview Questions
Top 5 Future Technologies for Database Administrators (DBA) to learn in 2020
Top 5 Software Technology Trends in 2020 List

MySQL query output to csv

How to create AWS RDS PostgreSQL Database

How to create AWS rds PostgreSQL Database