Skip to content
This repository has been archived by the owner on Jan 31, 2019. It is now read-only.

Latest commit

 

History

History
234 lines (177 loc) · 9.93 KB

README.md

File metadata and controls

234 lines (177 loc) · 9.93 KB
Raw

MySQL

Status: Tested
Description: MySQL
Maintainer: Cloudify
Maintainer email: cloudifysource@gigaspaces.com
Contributors: tamirko
Homepage: http://www.cloudifysource.org
Demo: cloudifysourcetv
License: Apache 2.0
Build: Cloudify 2.1.1 GA , Cloudify 2.2.0 M2 and Cloudify 2.2.0 M4
Linux sudoer permissions*: Mandatory
Windows Admin permissions*: Not required
Release Date: September 24th 2012

Tested on:

  • EC2:
  • CentOs 5 imageId "us-east-1/ami-76f0061f", hardwareId "m1.small", locationId "us-east-1"
  • Ubuntu 11.10: "us-east-1/ami-e1aa7388", hardwareId "m1.small", locationId "us-east-1"
  • Ubuntu 12.04: imageId "us-east-1/ami-82fa58eb", hardwareId "m1.small", locationId "us-east-1"
    .
  • OpenStack:
  • CentOs 5: imageId "1234" CentOS 5.6 64-bit, hardwareId "103" standard.large - 4 vCPU / 8 GB RAM / 240 GB HD , az-1.region-a.geo-1 .
  • Rackspace:
  • CentOs 6: imageId "118", hardwareId "4" (2GB server = 2G RAM 80 GB HD).

We disable the requiretty flag in /etc/sudoers on the installed VMs, so that Cloudify will be able to invoke remote ssh commands as a sudoer. This feature will be a part of Cloudify in the near future. Until then, please use the Cloud Drivers Repository.

Synopsis

This folder contains a service recipe for MySQL.

This recipe enables users to install MySQL in two modes: standalone and master-slave.

In a master-slave mode, the first instance of the service is the master and all the other instances are slaves.

In order to work in a master-slave mode, you need to set the masterSlaveMode property to true in mysql-service.properties file.

The default port is 3306, but it can be modified in the mysql-service.properties file.

You can inherit and extend this recipe very easily, just by changing the mysql-service.properties file, without changing even one line of code in the recipe.

This is achieved thanks to the following :

1: A Start Detection Query

In most of our recipes, we use something like : ServiceUtils.isPortOccupied(port).
In this case, it is usually NOT enough, because the port just means that the DB is up, but we also need the schema to be ready.
So we added a startDetectionQuery property (to the properties file) in which you can insert an SQL query.
The service instance is alive only if the port is occupied AND the startDetectionQuery result is true.

2: Post Start Actions

In the properties file you can insert an array of postStart commands (as many commands as you want).
These post start commands will be invoked during the... postStart lifecycle event.
There are four types of postStart commands :
a) mysqladmin : for invoking any administrative command ( for example : creating a new DB )
b) mysql : for invoking any SQL statement: insert, update, grant permissions etc.
c) import : for importing a DB schema ( by providing a full URL of the zip file that contains the schema )
d) mysqldump : for creating a db dump (snapshot)

Examples :

   
   /* In this case, dbName is a property which is defined in mysql-service.properties    
      a) All the occurrences of MYSQLHOST in actionQuery, 
	  b) will be replaced with the private IP address on which this service instance resides.   
   */ 
   [    
		"actionType" : "mysqladmin",   
		"actionQuery" : "create",  
		"actionUser"  : "root",  
		"actionDbName" : "${dbName}",  
		"debugMsg" : "Creating db - Name  : ${dbName} ... "  
	]  

	
	/* In this case, dbUser and dbPassW are properties which are defined in mysql-service.properties   
       a) All the occurrences of MYSQLHOST in actionQuery, 
	   b) will be replaced with the private IP address on which this service instance resides.   
	*/  
	[   
		"actionType" : "mysql", 		  
		"actionQuery" : "\"CREATE USER '${dbUser}'@'localhost' IDENTIFIED BY '${dbPassW}';\"",  
		"actionUser"  : "root",  
		"actionDbName" : "${dbName}",  
		"debugMsg" : "Creating db user ${dbUser} at localhost, passw ${dbPassW} in ${dbName} db... "   
	]  

	  
   /* In this case:  
        a) dbName,currDBZip,currImportSql are properties which are defined in mysql-service.properties   
        b) currDBZip is the local name of the zip file ( after download )  
        c) currImportSql is the name of the sql file which is stored in currDBZip.   
        d) All the occurrences of REPLACE_WITH_DB_NAME in currImportSql, will be replaced with ${dbName}   
   */
   [   
		"actionType" : "import",   
		"importedZip" : "${currDBZip}",  
		"importedFile" : "${currImportSql}",  
		"importedFileUrl" : "http://dropbox/1/222/mysql.zip",  
		"actionUser"  : "root",  
		"actionDbName" : "${dbName}",  
		"debugMsg" : "Importing  to ${dbName} ..."  
	]	

	
   /* In this case:  
        a) dbName is a property which is defined in mysql-service.properties.  
        b) If actionDbName is an empty string,  then --all-databases will be used. 
        c) actionArgs contain the flags that you want to use with this mysqldump command  
        d) Do NOT database flags, because they will be set according to the actionDbName.  
           i.e: Do NOT use the following  : --all-databases,-A,--databases  
        e) Do NOT -u flag flags, because it will be set according to the actionUser  
   */
   [   
		"actionType" : "mysqldump",   
		"actionArgs" : "--add-drop-database -c --lock-all-tables -F",  
		"actionUser"  : "root",  
		"actionDbName" : "${dbName}",  
		"dumpPrefix" : "myDumpFile_",  
		"debugMsg" : "Invoking mysqldump ..."   
	]

3: my.cnf variables Replacement

In the properties file you can insert an array of my.cnf variables and their corresponding values (as many as you want).
Do NOT set the server-id variable, as it will be set by this recipe.

These variables will be replaced during the install lifecycle event.

Examples :

   
   /* In this case, the value of the log-bin, 
      will be set to mysql-bin in the mysqld section of my.cnf.
   */      
	[  
		"section" : "mysqld", 
		"variable" : "log-bin" ,
		"newValue"  : "mysql-bin"
	], 

   /* In this case, the value of the binlog_format   , 
      will be set to MIXED (ROW and STATEMENT) in the mysqld section of my.cnf.
   */ 	
	
	[  
		"section" : "mysqld", 
		"variable" : "binlog_format" ,
		"newValue"  : "MIXED"
	]

Custom Commands

A) mysqldump:

This custom command enables users to create a database snapshot (mysqldump).
Usage : invoke mysql mysqldump actionUser dumpPrefix [dbName]
Example: invoke mysql mysqldump root myPrefix_ myDbName

B) query:

This custom command enables users to invoke an SQL statement.
Usage : invoke mysql query actionUser [puserPassword] dbName query
Examples:

  1. If you want to update the users table in myDbName with the following statement :
    update users set name='James' where uid=1
    then you need to run the following custom command :
    invoke mysql query root myDbName \"update users set name=\'James\' where uid=1\"
    or invoke mysql query myUser pmyUserpassword myDbName \"update users set name=\'James\' where uid=1\"

  2. If you want to insert a new user named Dan, into the users table in myDbName, and you need the following SQL statement:
    INSERT INTO users VALUES (17,'Dan','hisPassword','hisemail@his.com',0)
    then you need to run the following custom command :
    invoke mysql query root tamirDB \"INSERT INTO users VALUES \\(17,\'Dan\',\'hisPassword\',\'hisemail@his.com\',0\\)\"

C) import:

This custom command enables users to import a zipped file to a database Usage : invoke mysql import actionUser dbName zipFileURL Example: invoke mysql import root myDbName http://www.mysite.com/myFile.zip

D) addSlave:

This custom command enables users to add a slave to the master. It should be invoked only on a master instance (by a remote slave) and only if masterSlaveMode is set to true on both the slave and master. As a result, the following will be invoked :
mysql -u root -D dbName -e "GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO slaveUser@'slaveHostIP' IDENTIFIED BY 'slavePassword';"

Usage : invoke mysqlmaster addSlave actionUser dbName slaveUser slavePassword slaveHostIP

E) showMasterStatus:

This custom command enables users to show the master's status. It should be invoked only on a master instance (either by the master or by a remote slave) and only if masterSlaveMode is set to true. As a result, the following will be invoked :
mysql -u root -D dbName -e "show master status;" and then the mysql-bin will be stored in context.attributes.thisApplication["masterBinLogFile"] and the master's log's position will be stored in context.attributes.thisApplication["masterBinLogPos"]

Usage : invoke mysqlmaster showMasterStatus actionUser dbName

Known Issues

  • Monitoring is not implemented yet. It will be available in the future.
  • Failover in the master-slave mode is NOT implemented yet. It will be available in the future.