Support Search
Try RightScale for free
RightScale Cloud Management Support Portal > Clouds > IDC Frontier > Runbooks > Database Manager with MySQL 5.1 for IDC Frontier Runbook

Database Manager with MySQL 5.1 for IDC Frontier Runbook

    TOC
    1. 1. Overview
    2. 2. Description and Inputs
      1. 2.1. Boot Scripts
        1. 2.1.1. RightScript:  MISC API Access Tool install for IDC Frontier
        2. 2.1.2. RightScript:  MISC Set link /var/spool/ec2 -> /var/spool/cloud for IDC Frontier
        3. 2.1.3. RightScript:  MISC Create & Attach Volume for IDC Frontier
        4. 2.1.4. RightScript:  SYS Lvm install for IDC Frontier
        5. 2.1.5. RightScript:  DNS Set Resolv.conf for IDC Frontier
        6. 2.1.6. RightScript:  DNS Tools install for IDC Frontier
        7. 2.1.7. RightScript:  DB MySQL 5.1 server install for IDC Frontier
        8. 2.1.8. RightScript:  DB MySQL Import mysqldump from SSH Storage for IDC Frontier
        9. 2.1.9. RightScript:  DB MySQL continuous mysqldump backups to SSH Storage for IDC Frontier
        10. 2.1.10. RightScript:  DB Master or Slave init at boot for IDC Frontier
        11. 2.1.11. RightScript:  DB set Privileges for Application Server
      2. 2.2. Operational Scripts
        1. 2.2.1. RightScript:  DB MySQL backup to SSH Storage for IDC Frontier
        2. 2.2.2. RightScript:  DB Slave init for IDC Frontier
        3. 2.2.3. RightScript:  DB Freeze mysqldump backup cron for IDC Frontier
        4. 2.2.4. RightScript:  DB Unfreeze mysqldump backup cron for IDC Frontier
        5. 2.2.5. RightScript:  DB promote to master for IDC Frontier
        6. 2.2.6. RightScript:  DB restore and become master for IDC Frontier
        7. 2.2.7. RightScript:  DB mysqldump backup and stop MySQL for IDC Frontier
        8. 2.2.8. RightScript:  MISC Create Snapshot for IDC Frontier
      3. 2.3. Decommission Scripts
        1. 2.3.1. RightScript:  DB mysqldump backup and stop MySQL for IDC Frontier
    3. 3. Requirements and Restrictions
      1. 3.1. Requirements
      2. 3.2. Restrictions
    4. 4. Common Runbook Operations
      1. 4.1. Take a backup of the database
      2. 4.2. Freeze and unfreeze the scheduled backup process
      3. 4.3. Promote slave database to master when master is normal state
      4. 4.4. Promote slave database to master when master is failed state
      5. 4.5. Re-initialize an old master database to a new slave database
    5. 5. See also

    Table of Contents

    1. 1. Overview
    2. 2. Description and Inputs
      1. 2.1. Boot Scripts
        1. 2.1.1. RightScript:  MISC API Access Tool install for IDC Frontier
        2. 2.1.2. RightScript:  MISC Set link /var/spool/ec2 -> /var/spool/cloud for IDC Frontier
        3. 2.1.3. RightScript:  MISC Create & Attach Volume for IDC Frontier
        4. 2.1.4. RightScript:  SYS Lvm install for IDC Frontier
        5. 2.1.5. RightScript:  DNS Set Resolv.conf for IDC Frontier
        6. 2.1.6. RightScript:  DNS Tools install for IDC Frontier
        7. 2.1.7. RightScript:  DB MySQL 5.1 server install for IDC Frontier
        8. 2.1.8. RightScript:  DB MySQL Import mysqldump from SSH Storage for IDC Frontier
        9. 2.1.9. RightScript:  DB MySQL continuous mysqldump backups to SSH Storage for IDC Frontier
        10. 2.1.10. RightScript:  DB Master or Slave init at boot for IDC Frontier
        11. 2.1.11. RightScript:  DB set Privileges for Application Server
      2. 2.2. Operational Scripts
        1. 2.2.1. RightScript:  DB MySQL backup to SSH Storage for IDC Frontier
        2. 2.2.2. RightScript:  DB Slave init for IDC Frontier
        3. 2.2.3. RightScript:  DB Freeze mysqldump backup cron for IDC Frontier
        4. 2.2.4. RightScript:  DB Unfreeze mysqldump backup cron for IDC Frontier
        5. 2.2.5. RightScript:  DB promote to master for IDC Frontier
        6. 2.2.6. RightScript:  DB restore and become master for IDC Frontier
        7. 2.2.7. RightScript:  DB mysqldump backup and stop MySQL for IDC Frontier
        8. 2.2.8. RightScript:  MISC Create Snapshot for IDC Frontier
      3. 2.3. Decommission Scripts
        1. 2.3.1. RightScript:  DB mysqldump backup and stop MySQL for IDC Frontier
    3. 3. Requirements and Restrictions
      1. 3.1. Requirements
      2. 3.2. Restrictions
    4. 4. Common Runbook Operations
      1. 4.1. Take a backup of the database
      2. 4.2. Freeze and unfreeze the scheduled backup process
      3. 4.3. Promote slave database to master when master is normal state
      4. 4.4. Promote slave database to master when master is failed state
      5. 4.5. Re-initialize an old master database to a new slave database
    5. 5. See also

    Overview

    This ServerTemplate is based on Database Manager with MySQL 5.1 - 11H1 [rev 16], which is written in Bash-based RightScripts. And, this ServerTemplate contains the following functions to support the IDC Frontier cloud service.

    • Create and attach additional persistent disks (such as EBS volumes of AWS)
    • Supports SSH Storage Server to upload and backup mysqldump files.
    • Supports internal dynamic DNS server which is created from the ServerTemplates SSH Storage Server for IDC Frontier or Internal Dynamic DNS Server for IDC Frontier”.

    Description and Inputs

    Boot Scripts

    The following Boot scripts are listed in order.

    RightScript:  MISC API Access Tool install for IDC Frontier

    Description:  This script installs a command-line tool which operates CloudStack API of IDC Frontier cloud.
    Required Inputs and Default Settings: None

    RightScript:  MISC Set link /var/spool/ec2 -> /var/spool/cloud for IDC Frontier

    Description:  This script creates a symbolic link from /var/spool/ec2 to /var/spool/cloud.
    Required Inputs and Default Settings:None

    RightScript:  MISC Create & Attach Volume for IDC Frontier

    Description:  This script creates and attaches additional volumes (like EBS volume of AWS) to the instance.

    Required Inputs and Default Settings:

    CLOUD Category

    IDCF_COMPUTE_API_KEY The API key of your account on IDC Frontier cloud. 
    Default Value:  Cred:IDCF_COMPUTE_API_KEY.
    Required.
    IDCF_COMPUTE_HOST API endpoint URL of IDC Frontier cloud.
    Default Value: Cred:IDCF_COMPUTE_HOST. 
    You have to set the following URL to the Credential : https://api.noahcloud.jp/portal/client/api
    Required.
    IDCF_COMPUTE_SECRET_KEY The secret key of your account on IDC Frontier cloud. Default Value: Cred:IDCF_COMPUTE_SECRET_KEY.
    Required.

     

    UNCATEGORIZED Category

    CREATE_PART Flag whether to create partition.
    Default Value: True
    Required.
    EXISTING_VOL_MOUNT Flag whether to mount the volume which name is the same as defined in $VOL_NAME.
    Default Value: False
    Required.
    MOUNTPOINT Where the additional volume mounts. You should not change it because many RightScripts published from IDC Frontier depend on this.
    Default Value: /home/data
    Required.
    SUBDIR Sub directory will be created under the $MOUNTPOINT. You can add subdirectories separated by space.
    Default Value: webapps dump
    Required.
    VOL_COUNT The number of disks to construct a stripe. You can choose 1 – 3.
    Default Value: 1
    Required.
    VOL_NAME The name of the additional disks.
    Required.
    VOL_TOTAL_SIZE Total size in GB of additional disks. The size of each disk will become VOL_TOTAL_SIZE/VOL_COUNT.
    Default Value:50
    Required.
    RightScript:  SYS Lvm install for IDC Frontier

    Description:  This script creates an LVM volume from the additional disks which are created by the RightScript MISC Create & Attach Volume for IDC Frontier

    Required Inputs and Default Settings:None

    RightScript:  DNS Set Resolv.conf for IDC Frontier

    Description:  This script edits /etc/resolv.conf and /etc/sysconfig/network-script/ifcfg-eth0 to use an internal dynamic DNS server when you select DNS_PROVIDER as “NSUpdate”. If you choose DNS_PROVIDER other than NSUpdate, this script executes nothing.

    Required Inputs and Default Settings:

    DNS Category

    DNS_PROVIDER DNS provider you choose to use.
    Default Value: NSUpdate
    Required.
    DNS_SERVER_IP_ADDRESS IP address of the internal DNS server. You should change the value to the private IP address of internal dynamic DNS.
    Default Value: 10.1.xx.xx
    You can use “env:(DNS server):PRIVATE_IP” if DNS server belongs to the same Deployment.
    Required.
    RightScript:  DNS Tools install for IDC Frontier

    Description:  This script installs RightScale’s DNS tools. If you choose NSUpdate as the DNS_PROVIDER, the DNS tools created by IDC Frontier will be installed.

    Required Inputs and Default Settings: 

    DNS Category

    DNS_PROVIDER DNS provider you choose to use.
    Default Value: NSUpdate
    Required.
    RightScript:  DB MySQL 5.1 server install for IDC Frontier

    Description:  This script installs, configures, and starts MySQL 5.1 on a server. This RightScript is based on “DB MySQL 5.1 server install - 11H1 [rev 18]”

    Required Inputs and Default Settings: 

    DNS Category

    DB_BINLOG_PREFIX Prefix of MySQL binary log file
    Default Value: /mnt/mysql-binlogs/mysql-bin
    Required.
    DB_SERVER_USAGE Whether this server is used by the DB only or with the other functions.
    Default Value: dedicated
    Required.

     

    UNCATEGORIZED Category

    OPT_DB_MYSQL_TIMEOUT Period of time to wait for MySQL service to startup  
    Default Value: (None)
    Optional.
    OPT_MYSQL_MAX_CONNECTIONS The maximum number of MySQL connections that can be established across the entire deployment. (Default: 500)
    Default Value: (None)
    RightScript:  DB MySQL Import mysqldump from SSH Storage for IDC Frontier

    Description:  This script downloads the MySQL dump file from the following path of SSH Storage Server and restores the database.
    PATH: “/home/data/dump/”$DB_MYSQLDUMP_PREFIX”-YYYYMMDDhhmm-[master|slave].gz

    Required Inputs and Default Settings:

    DATABASE Category

    DB_MYSQLDUMP_PREFIX Prefix name of the mysqldump file name.
    Default Value: (None)
    Required.
    DB_SCHEMA_NAME Schema name of the MySQL database which will restored.
    Default Value: (None)
    Required.
    FORCE_IMPORT Whether to force import mysqldump if there exists a “DB_SCHEMA_NAME” database.
    Default Value: false
    Required.
    INIT_SLAVE_AT_BOOT Whether to create this server as a slave database.
    Default Value; Off
    Required.

     

    SSH Category

    SSH_STORAGE_SERVER FQDN or IP address of SSH Storage Server.
    Default Value: (None)
    Required.
    RightScript:  DB MySQL continuous mysqldump backups to SSH Storage for IDC Frontier

    Description:  This script creates a cron script which executes a mysqldump and backups to the SSH Storage Server daily.

    Required Inputs and Default Settings: 

    DATABASE Category

    DB_MYSQLDUMP_PREFIX Prefix name of mysqldump file name.
    Default Value: (None)
    Required.
    DB_SCHEMA_NAME Schema name of MySQL database which will restored.
    Default Value: (None)
    Required.
    INIT_SLAVE_AT_BOOT Whether to create this server as a slave database.
    Default Value; Off
    Required.

     

    SSH Category

    SSH_STORAGE_SERVER FQDN or IP address of SSH Storage Server.
    Default Value: (None)
    Required.
    RightScript:  DB Master or Slave init at boot for IDC Frontier

    Description:  This script initializes the database as a master DB or Slave DB.

    Required Inputs and Default Settings: 

    DATABASE Category

    DBREPLICATION_PASSWORD The password that is used for replication between the master and slave MySQL databases.
    Default Value: Cred:DBREPLICATION_PASSWORD
    Required.
    DBREPLICATION_USER The user name that is used for replication between the master and slave MySQL databases.
    Default Value: Cred:DBREPLICATION_USER
    Required.
    DB_BINLOG_PREFIX Prefix of MySQL binary log file
    Default Value: /mnt/mysql-binlogs/mysql-bin
    Required.
    DB_SCHEMA_NAME Schema name of the MySQL database which will restored.
    Default Value: (None)
    Required.
    INIT_SLAVE_AT_BOOT Whether to create this server as a slave database.
    Default Value: false
    Required.
    MASTER_DB_DNSID The unique identifier that is associated with the DNS A Record of the Master-DB.
    Default Value: (None)
    Required.
    MASTER_DB_DNSNAME The FQDN that is associated with the DNS A Record of the Master-DB.
    Default Value: (None)
    Required.
    SLAVE_DB_DNSID The unique identifier that is associated with the DNS A Record of the Slave-DB.
    Default Value: (None)
    Required.

     

    DNS Category

    DNS_PASSWORD The password that is used to access and modify your DNS A Records.
    Default Value: Cred:DNS_PASSWORD
    Required for DNSMadeEasy,DynDNS,Route53
    No need to set for NSUpdate
    DNS_USER The user name that is used to access and modify your DNS A Records.
    Default Value: Cred:DNS_PASSWORD
    Required for DNSMadeEasy,DynDNS,Route53
    No need to set for NSUpdate
    RightScript:  DB set Privileges for Application Server

    Description:  This script sets privileges for application server to access database.

    Required Inputs and Default Settings: 

    DATABASE Category

    DBAPPLICATION_PASSWORD The password of the database user that is used to access from the application server.
    Default Value: (None)
    Required.
    DBAPPLICATION_USER The username of the database user that is used to access from the application server.
    Default Value: (None)
    Required.

    Operational Scripts

    The following script is an optional script that is designed to be executed on-demand for the operations for the database such as backup, failover, stop or re-initialize database.

    RightScript:  DB MySQL backup to SSH Storage for IDC Frontier

    Description:  This script executes the backup script /etc/cron.daily/mysql-dump-backup which is created by Boot Script DB MySQL continuous mysqldump backups to SSH Storage for IDC Frontier. The backup file is stored at /home/data/dump of SSH Storage Server.

    Required Inputs and Default Settings: None

    RightScript:  DB Slave init for IDC Frontier

    Description:  This script initializes the database as a slave and starts replication with master database.

    Required Inputs and Default Settings:

    DATABASE Category

    DBREPLICATION_PASSWORD The password that is used for replication between master and slave MySQL databases.
    Default Value: Cred:DBREPLICATION_PASSWORD
    Required.
    DBREPLICATION_USER The user name that is used for replication between the master and slave MySQL databases.
    Default Value: Cred:DBREPLICATION_USER
    Required.
    DB_BINLOG_PREFIX Prefix of MySQL binary log file
    Default Value: /mnt/mysql-binlogs/mysql-bin
    Required.
    DB_SCHEMA_NAME The schema name of the MySQL database which will restored.
    Default Value: (None)
    Required.
    INIT_SLAVE_AT_BOOT Whether to create this server as a slave database. You MUST change the value to “true”.
    Default Value: false
    Required.
    MASTER_DB_DNSNAME The FQDN that is associated with the DNS A Record of the Master-DB.
    Default Value: (None)
    Required.
    SLAVE_DB_DNSID The unique identifier that is associated with the DNS A Record of the Slave-DB.
    Default Value: (None)
    Required.

     

    DNS Category

    DNS_PASSWORD The password that is used to access and modify your DNS A Records.
    Default Value: Cred:DNS_PASSWORD
    Required for DNSMadeEasy,DynDNS,Route53
    No need to set for NSUpdate
    DNS_USER The user name that is used to access and modify your DNS A Records.
    Default Value: Cred:DNS_PASSWORD
    Required for DNSMadeEasy,DynDNS,Route53
    No need to set for NSUpdate

     

    RightScript:  DB Freeze mysqldump backup cron for IDC Frontier

    Description:  This script disables the backup script by removing executable privilege.

    Required Inputs and Default Settings: None

    RightScript:  DB Unfreeze mysqldump backup cron for IDC Frontier

    Description:  This script enables the backup script by adding executable privilege.

    Required Inputs and Default Settings: None

    RightScript:  DB promote to master for IDC Frontier

    Description:  This script promotes a slave database to master by executing the following tasks.

    • If master database is normal state, sets “READ_ONLY” to master to stop updating.
    • Disables the replication, changes master/slave status, and removes “READ_ONLY” option.
    • Sets the privileges for replication

    • Changes the backup script and takes a backup for initial state.

    Required Inputs and Default Settings:

    DATABASE Category

    DBADMIN_PASSWORD The password of the database user (DBADMIN_USER) that has "admin" privileges.
    Default Value: Cred:DBADMIN_PASSWORD
    Required.
    DBADMIN_USER The username of the database user that has "admin" privileges.
    Default Value: Cred:DBADMIN_USER
    Required.
    DBREPLICATION_PASSWORD The password that is used for replication between the master and slave MySQL databases.
    Default Value: Cred:DBREPLICATION_PASSWORD
    Required.
    DBREPLICATION_USER The user name that is used for replication between the master and slave MySQL databases.
    Default Value: Cred:DBREPLICATION_USER
    Required.
    DB_BINLOG_PREFIX Prefix of the MySQL binary log file
    Default Value: /mnt/mysql-binlogs/mysql-bin
    Required.
    MASTER_DB_DNSID The unique identifier that is associated with the DNS A Record of the Master-DB.
    Default Value: (None)
    Required.
    MASTER_DB_DNSNAME The FQDN that is associated with the DNS A Record of the Master-DB.
    Default Value: (None)
    Required.
    SLAVE_DB_DNSID The unique identifier that is associated with the DNS A Record of the Slave-DB.
    Default Value: (None)
    Required.

     

    DNS Category

    DNS_PASSWORD The password that is used to access and modify your DNS A Records.
    Default Value: Cred:DNS_PASSWORD
    Required for DNSMadeEasy,DynDNS,Route53
    No need to set for NSUpdate
    DNS_USER The user name that is used to access and modify your DNS A Records.
    Default Value: Cred:DNS_PASSWORD
    Required for DNSMadeEasy,DynDNS,Route53
    No need to set for NSUpdate
    RightScript:  DB restore and become master for IDC Frontier

    Description:  This script restores the database from the newest backup and configures as a master.

    Required Inputs and Default Settings:

    DATABASE Category

    DBADMIN_PASSWORD The password of the database user (DBADMIN_USER) that has "admin" privileges.
    Default Value: Cred:DBADMIN_PASSWORD
    Required.
    DBADMIN_USER The username of the database user that has "admin" privileges.
    Default Value: Cred:DBADMIN_USER
    Required.
    Required.DBREPLICATION_PASSWORD The password that is used for replication between the master and slave MySQL databases.
    Default Value: Cred:DBREPLICATION_PASSWORD
    Required.
    DBREPLICATION_USER The user name that is used for replication between the master and slave MySQL databases.
    Default Value: Cred:DBREPLICATION_USER
    Required.
    DB_MYSQLDUMP_PREFIX Prefix name of the mysqldump file name.
    Default Value: (None)
    Required.
    DB_SCHEMA_NAME Schema name of the MySQL database which will restored.
    Default Value: (None)
    Required.
    MASTER_DB_DNSID The unique identifier that is associated with the DNS A Record of the Master-DB.
    Default Value: (None)
    Required.
    OPT_DB_RESTORE_TIMESTAMP Timestamp of the backup file by 12-digits.
    Default Value: (None)
    Optional.

     

    DNS Category

    DNS_PASSWORD The password that is used to access and modify your DNS A Records.
    Default Value: Cred:DNS_PASSWORD
    Required for DNSMadeEasy,DynDNS,Route53
    No need to set for NSUpdate
    DNS_USER The user name that is used to access and modify your DNS A Records.
    Default Value: Cred:DNS_PASSWORD
    Required for DNSMadeEasy,DynDNS,Route53
    No need to set for NSUpdate

     

    SSH Category

    SSH_STORAGE_SERVER FQDN or IP address of SSH Storage Server.
    Default Value: (None)
    Required.
    RightScript:  DB mysqldump backup and stop MySQL for IDC Frontier

    Description:  This script takes a backup of database and stop MySQL service.

    Required Inputs and Default Settings: None

    RightScript:  MISC Create Snapshot for IDC Frontier

    Description:  This script takes a snapshot of volume.

    Required Inputs and Default Settings:

    CLOUD Category

    IDCF_COMPUTE_API_KEY The API key of your account on IDC Frontier cloud. 
    Default Value:  Cred:IDCF_COMPUTE_API_KEY.
    Required.
    IDCF_COMPUTE_HOST API endpoint URL of IDC Frontier cloud.
    Default Value: Cred:IDCF_COMPUTE_HOST. 
    You have to set the following URL to the Credential : https://api.noahcloud.jp/portal/client/api
    Required.
    IDCF_COMPUTE_SECRET_KEY The secret key of your account on IDC Frontier cloud.
    Default Value: Cred:IDCF_COMPUTE_SECRET_KEY.
    Required.

     

    UNCATEGORIZED Category

    VOL_NAME The name of the additional disk of which you want to take a snapshot.
    Default Value: (None)
    Required.

    Decommission Scripts

    This ServerTemplate contains a Decommission Script for backup and stops MySQL service before terminating the instance.

    RightScript:  DB mysqldump backup and stop MySQL for IDC Frontier

    Description:  This script takes a backup of the database and stops MySQL service.

    Required Inputs and Default Settings: None

    Requirements and Restrictions

    Requirements

    None.

    Restrictions

    1. Internal dynamic DNS service included in this ServerTemplate is only able to resolve “cloud.local” domain.
    2. You have to change /etc/resolv.conf of all instances which use internal dynamic DNS by executing the RightScript DNS Set Resolv.conf for IDC Frontier.
    3. DNS records are erased when you relaunch the server. So you have to re-register DNS records of all instances by executing the RightScripts DNS External id register - 11H1 or DNS ID register Public/Private IP.

    Common Runbook Operations

     This runbook is not meant to provide detailed information about the usage of this ServerTemplate. Further information can be found in the 3 Tier Setup with IDC Frontier

    Take a backup of the database

    Execute the Operational Script, DB MySQL backup to SSH Storage for IDC Frontier.

    Freeze and unfreeze the scheduled backup process

    To freeze the scheduled backup when you maintain the database, execute the Operational Script DB Freeze mysqldump backup cron for IDC Frontier. To unfreeze the scheduled backup to maintain the database, execute the Operational Script DB Unfreeze mysqldump backup cron for IDC Frontier.

    Promote slave database to master when master is normal state

    To promote the slave server to become the master database, execute the Operational Script DB promote to master for IDC Frontier at the slave database instance. This script checks if the replication status is normal or not, and stops updating the old-master database if possible. After configuring the database as a master, this script updates the DNS setting and takes a first backup so that a slave server can retrieve the replication information (bin-log file name and log position.) After you have completed promoting to master, you should re-initialize an old-master database. 

    Promote slave database to master when master is failed state

    If master and slave databases have collapsed, you can recover the database from the backup file. To do so, execute the Operational Script DB restore and become master for IDC Frontier at the master database instance.

    Re-initialize an old master database to a new slave database

    To re-initialize an old master database server to a new slave database, you should do the following procedures:

    1. Configure the Input “$INIT_SLAVE_AT_BOOT” to “true”
    2. Execute the Operational Script DB MySQL Import mysqldump from SSH Storage for IDC Frontier at the new slave instance. This script restores from the first backup of new-master database and retrieves the replication information.
    3. Execute the Operational Script DB Slave init for IDC Frontier which configures the database as a slave and updates the DNS setting.
    Powered by MindTouch