Category: Amazonrds

  • Khóa học miễn phí Amazon RDS – MySQL DBA Tasks nhận dự án làm có lương

    Amazon RDS – MySQL DBA Tasks



    As with every other database, Amazon RDS MYSQL also needs DBA tasks to fine tune the database and do periodic health checks etc. But as the AWS platform does not allow the shell access to the DB, there are a limited number of DBA tasks that can be performed as compared to the on-premise installation of MySQL. Below is a list of common DBA tasks that can be performed in AWS RDS MySQL database and their descriptions.

    Accessing Error Logs

    The MySQL error log ( mysql-error.log) file can be viewed by using the Amazon RDS console or by retrieving the log using the Amazon RDS CLI. mysql-error.log is flushed every 5 minutes, and its contents are appended to mysql-error-running.log. The mysql-error-running.log file is then rotated every hour and the hourly files generated during the last 24 hours are retained.

    Using RDS Console

    Below there are links to two log files described above.

     mysql_rds_log1.JPG

    Using CLI

    Using CLI the log files are published to CloudWatch Logs as a JSON Object.

    aws rds modify-db-instance
        --db-instance-identifier mydbinstance
        --cloudwatch-logs-export-configuration ''{"EnableLogTypes":["audit","error","general","slowquery"]}''
        --apply-immediately
    
    

    Killing a Long Running Session or Query

    Sometimes the DBA needs to kill a long running session or query which is not giving the result quick enough. This DBA task is done by first finding the process ID of the query and then using a RDS function to kill the query. The below commands are the examples.

    # get the ID
    Select * from INFORMATION_SCHEMA.PROCESSLIST
    #Apply the Kill Function
    CALL mysql.rds_kill(processID);
    

    Improve Crash recovery Time

    We can improve the recovery time from a crash by setting a DB parameter called innodb_file_per_table. We can find this parameter in the RDS console as shown below.

     mysql_DBA_parameters.JPG

    Next we can Search for the parameter name as shown below.

    mysql_innodb_file_param.JPG

    Amazon RDS sets the default value for innodb_file_per_table parameter to 1, which allows you to drop individual InnoDB tables and reclaim storage used by those tables for the DB instance. This speeds up the recovery time from the crash.

    Stop and Reboot DB

    Stopping a DB, Rebooting it or creating snapshots etc can be done easily through RDS console as shown in the below diagram.

    mysql_db_stop_reboot.JPG

    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

  • Khóa học miễn phí Amazon RDS – Oracle DBA Tasks nhận dự án làm có lương

    Amazon RDS – Oracle DBA Tasks



    As an industry leading database technology, oracle has many in-built features which makes it easy to manage the DBA activities, even in the cloud. The Amazon RDS oracle DB provides access to many stored procedures and functions which can be accessed using the SQL developer client tool. This procedure can be executed using the user ID and password created during the Amazon RDS instance creation. Below are the examples of some of the most frequently used DBA activities.

    Killing a Session

    Sometimes a long running query or any other DB activity needs to be killed by killing the session. We use the Amazon RDS procedure rdsadmin.rdsadmin_util.kill to kill a session. The following code does that.

    # First get the session identifier and the session serial number,
    select SID, SERIAL#, STATUS from V$SESSION where USERNAME = ''AWSUSER
    
    # Next use the procedure
    begin
        rdsadmin.rdsadmin_util.kill(
            sid    => sid,
            serial => serial_number);
    end;
    /
    
    

    Setting the Default Tablespace

    The Amazon RDS procedure rdsadmin.rdsadmin_util.alter_default_tablespace can be used to set to the default tablespace for a DB using the following command.

    exec rdsadmin.rdsadmin_util.alter_default_tablespace(tablespace_name => ''AWSuser'');
    
    

    Setting the Database Time Zone

    We can use the Amazon RDS procedure rdsadmin.rdsadmin_util.alter_db_time_zone to changes the time zone for the DB.

    # Change the time zone of the DB to UTC + 5.30
    exec rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => ''+5:30'');
    # Change the time zone to a specific region
    exec rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => ''Asia/Kolkata'');
    

    Adding Online Redo Logs

    We can use the Amazon RDS procedure rdsadmin.rdsadmin_util.add_logfile to add additional redo logs. The following command adds a log file of size 128MB.

    exec rdsadmin.rdsadmin_util.add_logfile(p_size => ''128M'');
    

    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

  • Khóa học miễn phí Amazon RDS – Oracle Creating DB nhận dự án làm có lương

    Amazon RDS – Oracle Creating DB



    As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a Oracle DB 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 Oracle SQL DB instance.

    Step-1

    Select the Oracle db Engine form the console.

     oracle_create_db_1.jpg

    Step-2

    Specify the required DB details.

    oracle_create_db_2.jpg

    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.

    oracle_create_db_3.jpg

    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 important options.

    oracle_create_db_4.jpg

    Using CLI

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

    aws rds create-db-instance
        --engine oracle-se1
        --db-instance-identifier mydbinstance
        --allocated-storage 20
        --db-instance-class db.m1.small
        --db-security-groups mydbsecuritygroup
        --db-subnet-group mydbsubnetgroup
        --master-username masterawsuser
        --master-user-password masteruserpassword
        --backup-retention-period 3
    

    Using API

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

    https://rds.amazonaws.com/
        ?Action=CreateDBInstance
        &AllocatedStorage=250
        &BackupRetentionPeriod=3
        &DBInstanceClass=db.m1.large
        &DBInstanceIdentifier=mydbinstance
        &DBSecurityGroups.member.1=mysecuritygroup
        &DBSubnetGroup=mydbsubnetgroup
        &Engine=oracle-se1
        &MasterUserPassword=masteruserpassword
        &MasterUsername=masterawsuser
        &SignatureMethod=HmacSHA256
        &SignatureVersion=4
        &Version=2014-10-31
        &X-Amz-Algorithm=AWS4-HMAC-SHA256
        &X-Amz-Credential=AKIADQKE4SARGYLE/20140305/us-west-1/rds/aws4_request
        &X-Amz-Date=20140305T185838Z
        &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
        &X-Amz-Signature=b441901545441d3c7a48f63b5b1522c5b2b37c137500c93c45e209d4b3a064a3
    

    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

  • Khóa học miễn phí Amazon RDS – Oracle Connecting to DB nhận dự án làm có lương

    Amazon RDS – Oracle Connecting to DB



    To connect to Amazon RDS Oracle DB we need a client software. In this case we use Sql Developer. 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.

     oracle_db_conn_1.jpg

    Step-2

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

    oracle_db_conn_2.jpg

    Step-3

    Once connected, we get the following window.

    oracle_db_conn_3.jpg

    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

  • Khóa học miễn phí Amazon RDS – Oracle DB Data Import nhận dự án làm có lương

    Amazon RDS – Oracle DB Data Import



    To import data into the RDS oracle database or export data from it, we need to consider the size of the data involved and use a appropriate technique. The Sql Developer tool which we use to connect to AWS RDS oracle instance, can be used for both export and import small volume of data like 20MB or so. But to import data of size in terabytes, we need to use oracle data pump.

    Exporting Data Using SQL Developer

    Step-1

    After connecting to the AWS RDS Oracle DB, choose tools and Database Export.

    oracle_export_1.jpg

    Step-2

    The next step asks for the type of export where we decide the export format.

    oracle_export_2.jpg

    Step-3

    Next we decide on the DB objects to be exported.

    oracle_export_3.jpg

    Step-4

    We can further decide on the name of the objects to be exported.

    oracle_export_4.jpg

    Step-5

    We can further decide on the objects attributes to be exported.

    oracle_export_5.jpg

    Step-6

    Finally we get the summary screen where we can revisit the objects we have choosen.

    oracle_export_6.jpg

    Clicking finish on the above step will export the DB into a file in choosen format.

    Importing Data Using SQL Developer

    Similar to the export steps above we can choose to import the db by using Database Copy command from the Tools menu option.


    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

  • Khóa học miễn phí Amazon RDS – Oracle Features nhận dự án làm có lương

    Amazon RDS – Oracle Features



    Oracle is very popular Relational DB which is available in the amazon RDS services with its enterprise edition features. Almost every feature of Oracle can be leveraged in the RDS platform. Below is a brief description on MYSQLs major features in RDS platform.

    Supported Versions

    The versions 11.2 and 12.1 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 the 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=''oracle-ee-12.1'',
        DefaultOnly=True,
        Engine='''',
        EngineVersion='''',
        ListSupportedCharacterSets=False, #True,
    )
    print(response)
    

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

    
    {
       "ResponseMetadata": {
          "RetryAttempts": 0,
          "HTTPStatusCode": 200,
          "RequestId": "f6805635-3e16-4014-83cd-dfdaf3f17950",
          "HTTPHeaders": {
             "x-amzn-requestid": "f6805635-3e16-4014-83cd-dfdaf3f17950",
             "date": "Fri, 14 Sep 2018 03:46:38 GMT",
             "content-length": "1455",
             "content-type": "text/xml"
          }
       },
       "u''DBEngineVersions''": [
          {
             "u''Engine''": "oracle-ee",
             "u''DBParameterGroupFamily''": "oracle-ee-12.1",
             "u''SupportsLogExportsToCloudwatchLogs''": true,
             "u''SupportsReadReplica''": false,
             "u''DefaultCharacterSet''": {
                "u''CharacterSetName''": "AL32UTF8",
                "u''CharacterSetDescription''": "Unicode 5.0 UTF-8 Universal character set"
             },
             "u''DBEngineDescription''": "Oracle Database Enterprise Edition",
             "u''EngineVersion''": "12.1.0.2.v12",
             "u''DBEngineVersionDescription''": "Oracle 12.1.0.2.v12",
             "u''ExportableLogTypes''": [
                "alert",
                "audit",
                "listener",
                "trace"
             ],
             "u''ValidUpgradeTarget''": []
          }
       ]
    }
    

    Oracle Licensing

    There are two options for using oracle licenses in RDS. They are License Included and Bring Your Own License.

    License Included Model

    In this model Amazon holds the license for the software you are going to use. Also AWS itself provides the support for both AWS and Oracle software thorugh its support program. So the user does not purchase any separate license. The platform pricing includes the charges for licensing cost the user pays. The two editions supported in this model are Standard Edition One and Standard Edition Two.

    Bring Your Own License (BYOL)

    In this model the user brings in the license she holds into RDS platform. It is the user’s responsibility to maintain the compatibility between the license, database instance class and database edition. The user directly contacts the Oracle support channel for any need. In this model the supported editions are Enterprise Edition (EE), Standard Edition (SE), Standard Edition One (SE1) and Standard Edition Two (SE2).

    For a multi A-Z deployment, the user should have license for both primary DB instance and the secondary DB instance.

    Oracle DB Parameter Group

    The oracle DB involves many DB parameters to be configured for various features and performance needs of the database. Aws makes these parameters visible through CLI commands, which the user can use to query for the parameter values. Below is the CLI command and the sample output.

    aws rds describe-engine-default-parameters --db-parameter-group-family oracle-ee-12.1
    
    Below are the some important parameters obtained as a result of above CLI command.
    {
        "EngineDefaults": {
            "Parameters": [
                {
                    "AllowedValues": "TRUE,FALSE",
                    "ParameterName": "_allow_level_without_connect_by",
                    "ApplyType": "dynamic",
                    "Description": "_allow_level_without_connect_by",
                    "IsModifiable": true,
                    "Source": "engine-default",
                    "DataType": "boolean"
                },
                {
                    "AllowedValues": "CHOOSE,OFF,CUBE,NESTED_LOOPS,MERGE,HASH",
                    "ParameterName": "_always_semi_join",
                    "ApplyType": "dynamic",
                    "Description": "_always_semi_join",
                    "IsModifiable": true,
                    "Source": "engine-default",
                    "DataType": "string"
                },
                {
                    "AllowedValues": "TRUE,FALSE",
                    "ParameterName": "_b_tree_bitmap_plans",
                    "ApplyType": "dynamic",
                    "Description": "_b_tree_bitmap_plans",
                    "IsModifiable": true,
                    "Source": "engine-default",
                    "DataType": "boolean"
                },
        {
                    "AllowedValues": "TRUE,FALSE",
                    "ParameterName": "parallel_automatic_tuning",
                    "ApplyType": "static",
                    "Description": "enable intelligent defaults for parallel execution parameters",
                    "IsModifiable": true,
                    "Source": "engine-default",
                    "DataType": "boolean"
                },
                {
                    "AllowedValues": "ENABLE,DISABLE",
                    "ParameterName": "xml_db_events",
                    "ApplyType": "dynamic",
                    "Description": "are XML DB events enabled",
                    "IsModifiable": false,
                    "Source": "engine-default",
                    "DataType": "string"
                }
            ]
        }
    }
    

    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

  • Khóa học miễn phí Amazon RDS – MariaDB Connecting to DB nhận dự án làm có lương

    Amazon RDS – MariaDB Connecting to DB



    To connect to Amazon RDS MariaDB we need a client software. In this case we use Navicat. 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.

    mariadb_db_details_1.JPG

    Step-2

    From connecitons choose Amazon AWS -> Amazon RDS for MariaDB.

    mariadb_start_conn_2.JPG

    Step-3

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

    mariadb_start_conn_3.JPG

    Step-4

    Once connected, we get the following window.

    mariadb_start_conn_4.JPG

    Step-5

    Next you can connect to specific db and view the details.

    mariadb_start_conn_5.JPG

    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

  • Khóa học miễn phí Amazon RDS – PostgreSQL creating DB nhận dự án làm có lương

    Amazon RDS – PostgresSQL creating DB



    As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a PostgreSQL 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 PostgreSQL instance.

    Step-1

    Select the PostgreSQL Engine form the console.

     create_postgresSQL_step_1.JPG

    Step-2

    Specify the required DB details.

    mariadb_step_2.JPG

    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.

    create_postgresSQL_step_3.JPG

    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.

    create_postgresSQL_step_4.JPG

    Stpe—5

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

    create_postgresSQL_step_5.JPG

    Using CLI

    To create a PostgreSQL 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 pgdbinstance
        --allocated-storage 20
        --db-instance-class db.t2.small
        --engine postgres
        --master-username masterawsuser
        --master-user-password masteruserpassword
    

    Using API

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

    https://rds.amazonaws.com/
        ?Action=CreateDBInstance
        &AllocatedStorage=20
        &BackupRetentionPeriod=3
        &DBInstanceClass=db.t2.small
        &DBInstanceIdentifier=pgdbinstance
        &DBName=mydatabase
        &DBSecurityGroups.member.1=mysecuritygroup
        &DBSubnetGroup=mydbsubnetgroup
        &Engine=postgres
        &MasterUserPassword=
        &MasterUsername=
        &SignatureMethod=HmacSHA256
        &SignatureVersion=4
        &Version=2013-09-09
        &X-Amz-Algorithm=AWS4-HMAC-SHA256
        &X-Amz-Credential=AKIADQKE4SARGYLE/20140212/us-west-2/rds/aws4_request
        &X-Amz-Date=20140212T190137Z
        &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
        &X-Amz-Signature=60d520ca0576c191b9eac8dbfe5617ebb6a6a9f3994d96437a102c0c2c80f88d
    

    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

  • Khóa học miễn phí Amazon RDS – PostgreSQL Features nhận dự án làm có lương

    Amazon RDS – PostgreSQL Features



    PostgreSQL is a powerful, open source object-relational database system which has earned a strong reputation for reliability, feature robustness, and performance. AWS RDS runs various versions of PostgreSQL. It supports point-in-time restore and backups, creation of DB snapshots and running it on a multi-AZ environment.

    Supported Versions

    The versions 9.3 through 10.4 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=''postgres'',
        EngineVersion='''',
        ListSupportedCharacterSets=False, #True,
    )
    
    print(response)
    

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

    {
       "ResponseMetadata": {
          "RetryAttempts": 0,
          "HTTPStatusCode": 200,
          "RequestId": "c85cd49f-2c16-44b4-9890-cb233651f962",
          "HTTPHeaders": {
             "x-amzn-requestid": "c85cd49f-2c16-44b4-9890-cb233651f962",
             "date": "Fri, 14 Sep 2018 07:31:34 GMT",
             "content-length": "995",
             "content-type": "text/xml"
          }
       },
       "u''DBEngineVersions''": [
          {
             "u''Engine''": "postgres",
             "u''DBParameterGroupFamily''": "postgres10",
             "u''SupportsLogExportsToCloudwatchLogs''": false,
             "u''SupportsReadReplica''": true,
             "u''DBEngineDescription''": "PostgreSQL",
             "u''EngineVersion''": "10.4",
             "u''DBEngineVersionDescription''": "PostgreSQL 10.4-R1",
             "u''ValidUpgradeTarget''": []
          }
       ]
    }
    
    

    Database Preview Environment

    The PostgreSQL community releases new versions and new extensions continuously. You can try out new PostgreSQL versions and extensions before they are fully supported by Aws RDS. To do that, you can create a new DB instance in the Database Preview Environment.

    DB instances in the Database Preview Environment are similar to DB instances in a production environment. However, keep in mind several important factors:

    • All DB instances are deleted 60 days after you create them, along with any backups and snapshots.

    • You can only create a DB instance in a virtual private cloud (VPC) based on the Amazon VPC service.

    • You can only create M4, T2, and R4 instance types. For more information about RDS instance classes,

    • You can”t get help from AWS Support with DB instances. You can post your questions in the RDS Database Preview Environment Forum.

    • You can only use General Purpose SSD and Provisioned IOPS SSD storage.

    • You can”t copy a snapshot of a DB instance to a production environment.

    • Some Amazon RDS features aren”t available in the preview environment, as described following.

    Logical Replication

    Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow cascading replication or more complex configurations. It is used for the below actions.

    • Sending incremental changes in a single database or a subset of a database to subscribers as they occur.

    • Consolidating multiple databases into a single one (for example for analytical purposes).

    • Replicating between different major versions of PostgreSQL.

    • Replicating between PostgreSQL instances on different platforms (for example Linux to Windows)

    • Giving access to replicated data to different groups of users.

    • Sharing a subset of the database between multiple databases.

    To enable logical replication for an Amazon RDS for PostgreSQL DB instance

    • The AWS user account requires the rds_superuser role to perform logical replication for the PostgreSQL database on Amazon RDS.

    • Set the rds.logical_replication parameter to 1.

    • Modify the inbound rules of the security group for the publisher instance (production) to allow the subscriber instance (replica) to connect. This is usually done by including the IP address of the subscriber in the security group.


    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

  • Khóa học miễn phí Amazon RDS – MariaDB Data Import nhận dự án làm có lương

    Amazon RDS – MariaDB Data Import



    Amazon RDS MariaDB provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the MariaDB 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- MariaDB database.

    From an Existing MariaDB database

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

     on_premise.jpg

    Creating a backup from On-Premise DB

    As a first step we create a backup of the on-premise database using the below command. MariaDB being a clone of MySQL, can use nearly all the same commands as MySQL.

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

    A file with name backupfile.sql is cerated 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 MariaDB database is present. You can follow to learn about how to upload.

    Import data from Amazon S3 to RDS- MariaDB database

    You can use the following Amazon CLI command to import the data from S3 to MariaDB 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- MariaDB Instance

    There may be scenarios when you want data from an existing RDS MariaDB DB to be taken into another RDS MariaDB. 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 – MariaDB 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 – MariaDB, 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.

     amazon_dms.jpg

    Exporting Data from MariaDB

    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 MariaDB running external to Amazon RDS.
    • Designate the MariaDB 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 mysqldump command to transfer the data

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

    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