To connect to Amazon RDS PostgreSQL DB we need a client software. In this case we use pgAdmin. Install it using the link .

After it is successfully installed we follow the steps below to connect it to the Amazon RDS.

Step-1

From the DB instance details get the end point.

Step-2

As it is a browser based interface, choose the add server option as shown in the below diagram.

Step-3

Use the end point and the master user credentials as the connection details.

Step-4

Once connected, we get the following window.

Amazon RDS PostgreSQL provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the PostgreSQL database we can use CLI tools to run the import and export commands to get the data from other sources in and out of the RDS database.

Below are the steps through which the PostgreSQL data migration happens using the export and import mechanisms.

Importing from an Amazon EC2 Instance

When there is a PostgreSQL server on an Amazon EC2 instance and it needs to be moved to a RDS – PostgreSQL DB instance, we use the below steps to do that.

Export The Data

Create a file using pg_dump that contains the data to be loaded. A dump file containing data and all the meta data of the database is created using the pg_dump utility. The following command in the psql utility cerates the dump file from the database named mydbname.

pg_dump dbname=mydbname -f mydbnamedump.sql

Create Target DB Instance

Next, we create the target DB instance and restore the data into it using the pg_restore command.

createdb [new database name]
pg_restore -v -h [endpoint of instance] -U [master username] -d [new database name] [database].dump

Create Target Database

Use psql to create the database on the DB instance and load the data.

psql
   -f mydbnamedump.sql
   --host awsdbpginstance.d34f4mnfggv0.us-west-2.rds.amazonaws.com
   --port 8199
   --username awsdbuser
   --password awsdbpassword
   --dbname mynewdb

Amazon RDS MySQL provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the MySQL database we can use CLI tools to run the import and export commands to get the data from other sources in and out of the RDS database. Below are the scenarios to consider when deciding on the approach to the import the data into the Amazon RDS – MySQL database.

From an Existing MySQL database

An existing MySQL DB can be present on premise or in another EC2 instance. Diagrammatically what we do is shown below.

Creating a backup from On-Premise DB

As a first step we create a backup of the on-premise database using the below command.

mysqldump -u user -p[user_password] [database_name] > backupfile.sql

A file with name backupfile.sql is created which contains the table structure along with the data to be used.

Storing the backup file in S3.

Upload the backup file created above to a pre-decided Amazon S3 bucket in the same region where the target RDS MySQL DB database is present. You can follow to learn about how to upload.

Import data from Amazon S3 to RDS- MySQL database

You can use the following Amazon CLI command to import the data from S3 to MySQL DB.

aws rds restore-db-instance-from-s3
--allocated-storage 125
--db-instance-identifier tddbidentifier
--db-instance-class db.m4.small
--engine mysql
--master-user-name masterawsuser
--master-user-password masteruserpassword
--s3-bucket-name tpbucket
--s3-ingestion-role-arn arn:aws:iam::account-number:role/rolename
--s3-prefix bucketprefix
--source-engine mysql
--source-engine-version 5.6.27

From Another RDS-MySQL Instance

There may be scenarios when you want data from an existing RDS MYSQL DB to be taken into another RDS MYSQL DB. For example, to cerate a Disaster recovery DB or create a DB only for business reporting etc. In such scenario, we create read replicas which are a copy of their source DB and then promote that read replica to a new DB instance. They are used to prevent direct heavy read from the original source DB when we want to copy the data.

create a read-replica

aws rds create-db-instance-read-replica
    --db-instance-identifier myreadreplica
    --source-db-instance-identifier mydbinstance

Promote a Read replica to DB Instance

Now as we have the replica, we can promote it to a standalone DB instance. This will serve our end need of importing data from o RDS – Mysql DB to a new one. The following command is used to complete the promotion of a read replica to a db instance.

aws rds create-db-instance-read-replica
    --db-instance-identifier readreplica_name
    --region target_region_name
    --db-subnet-group-name subnet_name
    --source-db-instance-identifier arn:aws:rds:region_name:11323467889012:db:mysql_instance1

From Any Database

In order to import data from any other database to Amazon RDS – MySQL, we have to use the amazon Data Migration Service also called Amazon DMS. It uses Schema conversion tool to translate the existing data base to a the MYSQL platform. The below diagram explains the overall process. Also it works on the similar principle of replication as described in the previous section.

Exporting Data from MySQL

Exporting of data from Amazon RDS Mysql DB is a straight forwards process where it works on the same replication principle we have seen above. Below are the steps to carry out the export process.

• Start the instance of MySQL running external to Amazon RDS.
• Designate the MySQL DB instance to be the replication source.
• Use mysqldump to transfer the database from the Amazon RDS instance to the instance external to Amazon RDS.

Below is the code for the mysqldum command

mysqldump -h RDS instance endpoint
    -u user
    -p password
    --port=3306
    --single-transaction
    --routines
    --triggers
    --databases  database database2
    --compress
    --compact | mysql
        -h MySQL host
        -u master user
        -p password
        --port 3306

As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a MariaDB can be done in three ways. Using AWS management console, AWS CLI or AWS API. We will look at each of these approaches one by one.

Using AWS management Console

AWS management console is the most convenient way to get started with RDS. You login to the AWS console using your AWS account details, locate the RDS service and then follow the steps shown below to create a MariaDB instance.

Step-1

Select the MariaDB Engine form the console.

Step-2

Specify the required DB details.

Step-3

In this step you decide on the db instance class, amount of storage allocated also set the master password along with few other details.

Stpe—4

This is the final step when you mention the vpc and security settings, encryption, backup options and log export etc. For brevity the screen shot has been shortened showing only the final options.

Stpe—5

In the final step we choose the create Data base option.

Using CLI

To create a MariaDB instance by using the AWS CLI, call the create-db-instance command with the parameters below.

aws rds create-db-instance
    --db-instance-identifier mydbinstance
    --db-instance-class db.m4.xlarge
    --engine mariadb
    --allocated-storage 20
    --master-username masteruser
    --master-user-password masteruserpassword
    --backup-retention-period 3

Using API

To create a MariaDB instance by using the Amazon RDS API, we call the CreateDBInstance action with the parameters as shown below.

https://rds.us-west-2.amazonaws.com/
    ?Action=CreateDBInstance
    &AllocatedStorage=20
    &BackupRetentionPeriod=3
    &DBInstanceClass=db.m4.xlarge
    &DBInstanceIdentifier=mydbinstance
    &DBName=mydatabase
    &DBSecurityGroups.member.1=mysecuritygroup
    &DBSubnetGroup=mydbsubnetgroup
    &Engine=mariadb
    &MasterUserPassword=masteruserpassword
    &MasterUsername=masterawsuser
    &Version=2014-10-31
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140213/us-west-2/rds/aws4_request
    &X-Amz-Date=20140213T162136Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=8052a76dfb18469393c5f0182cdab0ebc224a9c7c5c949155376c1c250fc7ec3

As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a MYSqlDB can be done in three ways. Using AWS management console, AWS CLI or AWS API. We will look at each of these approaches one by one.

Using AWS management Console

AWS management console is the most convenient way to get started with RDS. You login to the AWS console using your AWS account details, locate the RDS service and then follow the steps shown below to create a MariaDB instance.

Step-1

Select the MSSql Engine form the console.

Step-2

Specify the required DB details.

Step-3

In this step you decide on the db instance class, amount of storage allocated also set the master password along with few other details.

Stpe—4

The final step is to click create database, after which the MySql DB is created with a available end point as shown below.

Using CLI

To create a MySql DB instance by using the AWS CLI, call the create-db-instance command with the parameters below.

aws rds create-db-instance
    --db-instance-identifier mydbinstance
    --db-instance-class db.m1.small
    --engine MySQL
    --allocated-storage 20
    --master-username masterawsuser
    --master-user-password masteruserpassword
    --backup-retention-period 3

Using API

To create a MariaDB instance by using the Amazon RDS API, we call the CreateDBInstance action with the parameters as shown below.

https://rds.us-west-2.amazonaws.com/
    ?Action=CreateDBInstance
    &AllocatedStorage=20
    &BackupRetentionPeriod=3
    &DBInstanceClass=db.m3.medium
    &DBInstanceIdentifier=mydbinstance
    &DBName=mydatabase
    &DBSecurityGroups.member.1=mysecuritygroup
    &DBSubnetGroup=mydbsubnetgroup
    &Engine=mysql
    &MasterUserPassword=masteruserpassword
    &MasterUsername=masterawsuser
    &Version=2014-10-31
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140213/us-west-2/rds/aws4_request
    &X-Amz-Date=20140213T162136Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=8052a76dfb18469393c5f0182cdab0ebc224a9c7c5c949155376c1c250fc7ec3

To protect data from being viewed by unintended parties, we can use connection encryption between the client application and the RDS DB instance. Encryption is available in all AWS regions and for all the DB types supported by AWS RDS. In this chapter we will see how encryption is enabled for MSSQL Server.

There are two ways to enable encryption.

• Force SSL for all connections — this happens transparently to the client, and the client doesn”t have to do any work to use SSL.

• Encrypt specific connections — this sets up an SSL connection from a specific client computer, and you must do work on the client to encrypt connections.

Force SSL

In this approach we force all the connections form the DB client to use SSL. This is done by using the rds.force_ssl parameter. Set the rds.force_ssl parameter to true to force connections to use SSL. As it is a static parameter, we must reboot your DB instance for the change to take effect. The below diagram shows how to reset the value by visiting the DB parameters settings page to set the value for rds.force_ssl parameter.

Encrypting Specific Connections

We can encrypt connections from specific client computers only to the RDS DB Instance. In order to do this, we need to install certificate on the client computer. Below are the steps to install the certificate.

Step-1

Download the certificate to the client computer from .

Step-2

Follow the path Windows -> Run -> type MMC and enter. It opens the following window.

Step-3

In the Add or Remove Snap-ins dialog box, for Available snap-ins, select Certificates, and then choose Add.

Step-4

Follow the Path Computer Account -> Local Computer -> Finish.

Step-5

In the MMC console, expand Certificates, open the context (right-click) menu for Trusted Root Certification Authorities, choose All Tasks, and then choose Import.

Step-6

Select the .pem file downloaded in the previous step and finish the import wizard by choosing the default values and clicking next.

Step-7

We can see the certificate installed as below.

Step-8

When connecting to AWS RDS MSSQL Db instance using SSMS, expand the options tab and choose Encrypt connection.

Now the client connection to RDS from this computer will be encrypted.

To connect to Amazon RDS MySQL DB we need a client software. In this case we use MySQL Workbench. Install it using the link .

After it is successfully installed we follow the steps below to connect it to the Amazon RDS.

Step-1

From the DB instance details get the end point.

Step-2

Use the end point and the master user credentials as the connection details.

Step-3

Once connected, we get the following window.

Step-4

We can browse the DB and query the DB now.

MariaDB is a popular open Source Relational DB which is available in the amazon RDS services with its community edition features. Almost every feature of MariaDB can be leveraged in the RDS platform. Below is a brief description on MariaDB”s major features in the RDS platform.

Supported Versions

The versions 10.0, 10.1,10.2 are the major versions supported in the RDS platform. If no version is mentioned during the DB creation, it defaults to the most recent version at that point in time. Below is an example of how to get all supported DB Engine versions using AWS API in a python SDK program.

import boto3

client = boto3.client(''rds'')

response = client.describe_db_engine_versions(
    DBParameterGroupFamily='''',
    DefaultOnly=True,
    Engine=''mariadb'',
    EngineVersion='''',
    ListSupportedCharacterSets=False, #True,
)

print(response)

When we run the above program, we get the following output −

{
   "ResponseMetadata": {
      "RetryAttempts": 0,
      "HTTPStatusCode": 200,
      "RequestId": "16179fbd-9d07-425b-9b86-cc61359ce7b4",
      "HTTPHeaders": {
         "x-amzn-requestid": "16179fbd-9d07-425b-9b86-cc61359ce7b4",
         "date": "Fri, 14 Sep 2018 06:45:52 GMT",
         "content-length": "1658",
         "content-type": "text/xml"
      }
   },
   "u''DBEngineVersions''": [
      {
         "u''Engine''": "mariadb",
         "u''DBParameterGroupFamily''": "mariadb10.2",
         "u''SupportsLogExportsToCloudwatchLogs''": true,
         "u''SupportsReadReplica''": true,
         "u''DBEngineDescription''": "MariaDb Community Edition",
         "u''EngineVersion''": "10.2.12",
         "u''DBEngineVersionDescription''": "mariadb 10.2.12",
         "u''ExportableLogTypes''": [
            "audit",
            "error",
            "general",
            "slowquery"
         ],
         "u''ValidUpgradeTarget''": [
            {
               "u''Engine''": "mariadb",
               "u''IsMajorVersionUpgrade''": false,
               "u''AutoUpgrade''": false,
               "u''Description''": "MariaDB 10.2.15",
               "u''EngineVersion''": "10.2.15"
            }
         ]
      }
   ]
}

Database Security

The security for RDS MariaDB is managed at three layers.

Using IAM

In this approach the IAM user should have appropriate policies and permissions. Granting of such permissions is decided by the account holder or the super user who grants these permissions.

Using VPC

You either use a VPC security group or DB security group to decide which EC2 instances can open connections to the endpoint and port of a DB instance. These connections can also be made using SSL.

Using IAM Database Authentication

In this approach you use a IAM role and an authentication token. The authentication token generates a unique value which is relevant to the IAM role that is used in the access process. Here the same set of credentials are used for database as well as other aws resources, like EC2 and S3 etc.

Cache Warming

Cache warming can provide performance gains for your MariaDB DB instance by saving the current state of the buffer pool when the DB instance is shut down, and then reloading the buffer pool from the saved information when the DB instance starts up. This approach bypasses the need for the buffer pool to “warm up” from normal database use and instead preloads the buffer pool with the pages for known common queries.

Cache warming primarily provides a performance benefit for DB instances that use standard storage.

You can create an event to dump the buffer pool automatically and at a regular interval. For example, the following statement creates an event named periodic_buffer_pool_dump that dumps the buffer pool every hour.

CREATE EVENT periodic_buffer_pool_dump
   ON SCHEDULE EVERY 1 HOUR
   DO CALL mysql.rds_innodb_buffer_pool_dump_now();

MySQL is a popular Relational DB which is available in the amazon RDS services with its community edition features. Almost every feature of MYSQL can be leveraged in the RDS platform with only some restrictions based on regions and availability zones. Below is a brief description on MYSQLs major features in the RDS platform.Supported Versions

The versions 5.5, 5.6 and 5.7 are the major versions supported in the RDS platform. Except for 5.6.27 all versions are supported in all AWS regions. If no version is mentioned during the DB creation, it defaults to the most recent version at that point in time. Below is an example of how to get all supported DB Engine versions using AWS API in a python SDK program.

import boto3

client = boto3.client(''rds'')

response = client.describe_db_engine_versions(
    DBParameterGroupFamily=''mysql5.6'',
    DefaultOnly=True,
    Engine=''mysql'',
    EngineVersion=''5.6'',
    ListSupportedCharacterSets=True,
)

print(response)

When the above code is run we get an output as below −

{
   "ResponseMetadata": {},
   "DBEngineVersions''": [
      {
         "Engine''": "mysql",
         "DBParameterGroupFamily''": "mysql5.6",
         "SupportsLogExportsToCloudwatchLogs''": true,
         "SupportedCharacterSets''": [],
         "SupportsReadReplica''": true,
         "DBEngineDescription''": "MySQL Community Edition",
         "EngineVersion''": "5.6.40",
         "DBEngineVersionDescription''": "MySQL 5.6.40",
         "ExportableLogTypes''": [
            "audit",
            "error",
            "general",
            "slowquery"
         ],
         "ValidUpgradeTarget''": [
            {
               "Engine''": "mysql",
               "IsMajorVersionUpgrade''": true,
               "AutoUpgrade''": false,
               "Description''": "MySQL 5.7.22",
               "EngineVersion''": "5.7.22"
            }
         ]
      }
   ]
}

Version Upgrade

There MySQL version number is maintained as MYSQL A.B.C. In this notation, A.B indicates the major version and C indicates the minor version. The upgrade approach is different between minor and major version upgrades.

Minor Version Upgrade

The DB instances are automatically upgraded to new minor versions when ever they are supported by Amazon RDS. This patching occurs during a schedules maintenance window which you can control. You can also manually upgrade to new versions if you prefer to turn off the automatic update.

Major Version upgrade

Major version upgrades are not available as automatic upgrade. It must be done by the account user manually by modifying the DB instance. Below flowchart indicated the steps in achieving the major version upgrade. This approach ensures that the upgrade process is thoroughly tested before it is applied on the live production database.

Database Security

The security for RDS MYSQL DB is managed at three layers.

Using IAM

In this approach the IAM user should have appropriate policies and permissions. Granting of such permissions is decided by the account holder or the super user who grants these permissions.

Using VPC

You either use a VPC security group or DB security group to decide which EC2 instances can open connections to the endpoint and port of a DB instance. These connections can also be made using SSL.

Using IAM Database Authentication

In this approach you use a IAM role and an authentication token. The authentication token generates a unique value which is relevant to the IAM role that is used in the access process. Here the same set of credentials are used for database as well as other aws resources, like EC2 and S3 etc.

As a RDS service the MSSQL DB has many DBA tasks available as managed service. You do not have the shell access to the DB, but through the console or through the commands in the client software you can execute various DBA activities. Below are the most common and frequently used DBA tasks performed in Amazon RDS Ms SQL server.

Change Data Capture

CDC captures changes that are made to the data in the tables. The changes that were made to user tables are captured in corresponding change tables. These change tables provide an historical view of the changes over time. The change data capture functions that SQL Server provides enable the change data to be consumed easily and systematically.

Use the below commands in SSMS connected to RDS MSSQL server to enable and disable CDC.

#Enable CDC for RDS DB Instance
exec msdb.dbo.rds_cdc_enable_db ''''

#Disable CDC for RDS DB Instance
exec msdb.dbo.rds_cdc_disable_db ''''

Next to track the changes in a specific table we use the stored procedure sp_cdc_enable_table with the below command.

#Begin tracking a table
exec sys.sp_cdc_enable_table
   @source_schema           = N''''
,  @source_name             = N''
''
,  @role_name               = N''''
, @captured_column_list   = ''''
;



Modifying tempdb Database Options
The tempdb system database is a global resource which is available to all users connected to the instance of SQL Server and is used to hold the following

Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting. 
Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions. 

Following are examples of How you modify the RDS MSSQL tempdb for various DBA activities.
# setting the size to 100 GB and file growth to 10 percent.
alter database[tempdb] modify file (NAME = N''templog'', SIZE=100GB, FILEGROWTH = 10%)

# set the MAXSIZE property to prevent tempdb database from using all available disk space.
alter database [tempdb] modify file (NAME = N''templog'', MAXSIZE = 2048MB)

# Shrinking the tempdb Database file size and requests a new size
exec msdb.dbo.rds_shrink_tempdbfile @temp_filename = N''test_file'', @target_size = 10;







OFFLINE to ONLINE  Transition




You can transition your Microsoft SQL Server database on an Amazon RDS DB instance from OFFLINE to ONLINE using the following command.




EXEC rdsadmin.dbo.rds_set_database_online name






Non-English Character Set




During the creation of RDS MSSQL instance, the default collation marked for the DB is English. But it can be changed to another non-English language by applying the COLLATE clause along with the name of the collation. The below example illustrates that.




CREATE TABLE [dbo].[Account]
(
    [AccountID] [nvarchar](10) NOT NULL,
    [AccountName] [nvarchar](100) COLLATE Japanese_CI_AS NOT NULL
) ON [PRIMARY];










		


	





 

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương:   Khóa học lập trình nhận lương tại trung tâm Toidayhoc




			
August 17, 2024