The Database Manager for MySQL 5.1/5.5 ServerTemplate installs and configures a MySQL database server. The MySQL version installed depends on the default package available for the OS. Currently, CentOS 6.5 installs MySQL 5.1 and Ubuntu 12.04 installs MySQL 5.5. This ServerTemplate provides a high-availability master/slave database configuration that you can use as the backbone for a variety of applications and workloads.
The Database Manager for MySQL 5.1/5.5 ServerTemplate creates and attaches cloud based volumes used to store database data. These volumes are periodically backed up based on the volume snapshot features provided by the cloud.
Note: For a complete list of the Database Manager for MySQL 5.1/5.5 ServerTemplate's features and support, view the detailed description under its Info tab.
Software Application Versions
The Database Manager for MySQL 5.1/5.5 ServerTemplate consists of scripts in all three sections - Boot Sequence, Operational Scripts, and Decommission Sequence.
ephemeral_lvm::default - This recipe sets up available ephemeral devices to be an LVM device, formats it, and mounts it.
rightscale_tag::default - Sets the standard machine tags for a RightScale server which are server:uuid, server:public_ip_X, server:private_ip_X (where X is 0, 1, etc.).
rs-base::default - All-in-one recipe to run recipes required for optimal server management within the RightScale management platform.
rs-mysql::default - Installs the MySQL server and tunes the attributes used in the my.cnf based on the available system memory and the server usage type. If the server usage type is 'dedicated', all resources in the system are dedicated to the MySQL server and if the usage type is 'shared', only half of the resources are used for the MySQL server. This 'shared' usage will be used in building a LAMP stack where the same system is used to run both the MySQL server and the PHP application server. This recipe also installs the collectd plugins for MySQL and tags the server as a standalone MySQL server.
rs-mysql::default - See description above in Boot Sequence.
rs-mysql::collectd - Installs the collectd plugins for MySQL.
rs-mysql::volume - Creates a new volume from scratch or from an existing backup based on the value provided in node['rs-mysql']['restore']['lineage'] attribute. If this attribute is set, the volume will be restored from a backup matching this lineage otherwise a new volume will be created from scratch. This recipe will also format the volume using the filesystem specified in node['rs-mysql']['device']['filesystem'], mount the volume on the location specified in node['rs-mysql']['device']['mount_point'], and move the MySQL database directory to the volume.
rs-mysql::stripe - Creates a logical volume composed of volumes that are created from scratch or from an existing backup based on the value provided in node['rs-mysql']['restore']['lineage'] attribute. If this attribute is set, the volumes will be restored from a backup matching this lineage otherwise volumes comprising the logical volume will be created from scratch. This recipe will create a striped logical volume using LVM on the volumes and format the logical volume using the filesystem specified in node['rs-mysql']['device']['filesystem']. This recipe also mounts the volume on the location specified in node['rs-mysql']['device']['mount_point'] and moves the MySQL database directory to the logical volume.
rs-mysql::master - This recipe sets up the database to act as the master. It makes sure the database is not read-only by overriding the mysql/tunable/read_only to false and includes the rs-mysql::default recipe which installs MySQL and performs the configuration. The master database specific tags are added to the server and the master is reset. The master database can be provided with a fully qualified domain name (FQDN) by setting the node['rs-mysql']['dns']['master_fqdn'] attribute. The DNS provider credentials (node['rs-mysql']['dns']['user_key'] and node['rs-mysql']['dns']['secret_key']) must also be set to create/update the DNS records in the DNS provider.
rs-mysql::slave - This recipe modifies the MySQL server to be read-only and includes the rs-mysql::default recipe which installs MySQL, performs configuration, and tags the server as a slave server. It obtains the information about the master database with the help of the find_database_servers helper method provided by the rightscale_tag cookbook and changes the master host of the slave to the latest master available in the deployment. If this recipe is run after rs-mysql::volume or rs-mysql::stripe and a backup was restored, this recipe will use information from the backup to assist with catching up with the master MySQL database.
rs-mysql::backup - Takes a backup of all volumes attached to the server (except boot disks if there were any) with the lineage specified in the node['rs-mysql']['backup']['lineage'] attribute. During the backup process, the MySQL database will be read-only and the filesystem will be frozen. With the help of a chef exception handler, the filesystem will be unfrozen and the MySQL database will no longer be read-only after the backup even if the backup process fails . This recipe also cleans up the volume snapshots based on the criteria specified in the rs-mysql/backup/keep/* attributes.
rs-mysql::schedule - Adds/removes the crontab entry for taking backups periodically at the minute and hour provided via the node['rs-mysql']['schedule']['minute'] and node['rs-mysql']['schedule']['hour'] attributes.
rs-mysql::dump_import - Retrieves a dump file from a Git repository and imports it into the database server. The Git repository is specified by node['rs-mysql']['import']['repository'] with revision/branch specified by node['rs-mysql']['import']['revision']. The dump file in the repository is specified by node['rs-mysql']['import']['dump_file']. The private key attribute, node['rs-mysql']['import']['private_key'], must be set if retrieving from a private repository.
rs-mysql::decommission - If the node['rs-mysql']['device']['destroy_on_decommission'] attribute is set to true, this recipe moves the MySQL database back to the root volume, drops the database specified by node['rs-mysql']['application_database_name'] if it is specified, and detaches and deletes the volumes attached to the server. This operation will be skipped if the server is entering the stop state or rebooting.
The Database Manager for MySQL 5.1/5.5 ServerTemplate contains recipes to manage cloud volumes. These volumes are used by the database for its data storage location. The cloud's snapshot feature is used to do backups and restores. However, if the cloud does not support volumes, backups will not be possible.
The rs-mysql::volume script creates a single volume of the size provided by the ‘Device Volume Size’ input, attaches, and formats the volume. The rs-mysql::stripe script creates multiple volumes, attaches them, uses them to create a logical volume, and formats.
The size of the volumes created and attached when using rs-mysql::stripe is determined by the ‘Device Volume Size’ and ‘Device Count’ inputs using this formula: (total_size.to_f / device_count.to_f).ceil
Backup and Restore
Backups are perfromed using the rs-mysql::backup script. It signals the cloud to create a snapshot of all volumes attached to the server (with the exception of root disks if applicable) with the lineage specified in the 'Backup Lineage' input. During the backup process, the MySQL database will be read-only and the filesystem will be frozen. Due to the freezing of the database and filesystem, it is recommended that backups are done on 'slave' databases. Once the backup process is complete, regardless of returning a success or failure, the filesystem and database will be unfrozen. The completion of the backup process is is determined by cloud.
Restoring a database, which means restoring volumes, is done using rs-mysql::stripe or rs-mysql::volume with the ‘Restore Lineage’ input set. The optional ‘Restore Timestamp’ input can also be set if there is a specific timestamp that needs to be restored.
The rs-mysql::schedule script is used to configure continuous backups at specified times. Please review the script details above.
Database Dump Retrieval
The rs-mysql::dump_import script can be used to retrieve a MySQL database dump file from a Git repository. Please review the script details above.
MySQL Tuning Parameters
The most common customization of a MySQL database server is tuning the MySQL parameters to be more optimized for your own database. The Database Manager for MySQL 5.1/5.5 ServerTemplate performs automatic tuning based on available system memory and the server usage type. As you become more familiar with the performance characteristics of your database, you may want to modify some of these parameters accordingly.
Default MySQL turning parameters:
To learn how to override the default settings see Customize a Chef-Based ServerTemplate.