The collection of Bash scripts that help to manage multiple MySQL instances inside cluster managed by Oracle Clusterware 11gR2 in order to achieve high availability. Each MySQL instance running inside this cluster is configured with a dedicated IP address (Virtual IP resource in clusterware).<\/p>\n
The scripts manage:<\/p>\n
Initializing MySQL data directory and configuring resources in Oracle Clusterware.
\nClusterware actionscript to manage instance startup, shutdown, cleanup and check.
\nAll scripts are built with support of running multiple MySQL instances in the same cluster.
\nEach MySQL instance can run with different MySQL software version.
\nAutomatic MySQL error\/slow\/general log rotation and archival.
\nAlthough these scripts are written with clusterware in mind, they can also be used without it – to help managing multiple MySQL instances (with different RDBMS versions) running in the same host.<\/p>\n
Download the scripts from github<\/a> These scripts are written to help running multiple MySQL instances (with multiple MySQL software versions) in a clustered environment managed by Oracle Clusterware 11gR2.<\/p>\n Oracle Clusterware is included in the Oracle Unbreakable Linux Basic Support package, with no additional cost.<\/p>\n The scripts have been tested with:<\/p>\n This document describes how to use Oracle Clusterware 11gR2 for making MySQL instances highly-available. Oracle Clusterware is mainly used for clustering Oracle Databases (Real Application Clusters – RAC), but in can also be used for making other applications run in a clustered environments using custom scripts.<\/p>\n All the scripts are prepared keeping in mind, that all cluster nodes use shared storage and the same filesystem structure. Filesystem on shared storage should be mounted read-write on every node.<\/p>\n I have used ACFS clustered filesystem for all shared filesystems, but if you have a reliable NAS device, then using NFS is also an option.<\/p>\n The following directory structure is just a recommendation.<\/p>\n Basically there are two different types of filesystem mountpoints: Creating ACFS filesystem for software:<\/p>\n Creating ACFS filesystem for MySQL instance data-directory:<\/p>\n Resource dependencies to ACFS clusterware resource (ma ke sure mysql instance is stared after filesystems are mounted):<\/p>\n Execute everything as user root.<\/p>\n Create user for running mysql<\/p>\n Copy all scripts to \/u02\/app\/mysql Edit instances.sh, this is a configuration file. Look over all global parameters. Download MySQL RDBMS software tar-release and unpack it to (assuming its 5.5.17 advanced edition) \/u02\/app\/mysql\/product\/5.5.17<\/p>\n Create mysql client defaults file with MySQL root password. This is needed to execute MySQL console in local server without password.<\/p>\n Example contents of \/root\/.my.cnf file:<\/p>\n Execute mysql_logrotate.sh from crontab regularly (for example once per day or once per week) on every cluster node.<\/p>\n The easiest way to add new instance is by using the script init_mysql_db.sh<\/em> and after that run init_grid.sh<\/em> to add the newly added instance to clusterware. The scripts do the followings tasks automatically:<\/p>\n All init_mysql_db.sh<\/em> options:<\/p>\n To create MySQL instance sample_instance<\/em> under \/instance\/sample_instance\/data<\/em>, config file located in \/instance\/sample_instance\/config\/my.cnf<\/em> and logs located under \/instance\/sample_instance\/logs<\/em> execute the following command:<\/p>\n Execute init_grid.sh<\/em> to add newly added instance to clusterware. You can skip this step to add the resources using default values by specifying -g<\/em> option to init_mysql_db.sh<\/em> previously:<\/p>\n All options you can use with the script:<\/p>\n To add the new instance using resource type mysql_instance<\/em> (there you can set check interval and other parameters) and to server pool pte<\/em> use the following command:<\/p>\n NB! All these tasks can be done automatically using script init_mysql_db.sh<\/p>\n Execute everything as user root. Create mysqld config file in \/instance\/sample_instance\/config\/my.cnf. This is regular mysql configuration file. Do not specify the following parameters in the configuration file (they will be ignored, because they’ll be set in the command line on startup):<\/p>\n Initialize MySQL data directory \/instance\/sample_instance\/data<\/p>\n Add instance configuration to \/u02\/app\/mysql\/instances.sh:<\/p>\n Symlink actionscript sample_instance.scr<\/p>\n Start mysql<\/p>\n Check MySQL log files and ensure that MySQL process started. Check pinging MySQL, if exit value is 0, then all is good and instance is working.<\/p>\n Create Virtual IP resource in clusterware<\/p>\n Create application resource in clusterware<\/p>\n Test…<\/p>\n The package includes script to automatically rotate and expire MySQL logfiles for all managed instances. It depends on the standard linux logrotate package (included in RHEL\/OEL distributions).<\/p>\n This script is expected to run on all cluster nodes at the same time from crontab. The script requires no parameters.<\/p>\n The collection of Bash scripts that help to manage multiple MySQL instances inside cluster managed by Oracle Clusterware 11gR2 in order to achieve high availability. Each MySQL instance running inside this cluster is configured with a dedicated IP address (Virtual IP resource in clusterware). The scripts manage: Initializing MySQL data directory and configuring resources in […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-5","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/pages\/5","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=5"}],"version-history":[{"count":26,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/pages\/5\/revisions"}],"predecessor-version":[{"id":294,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/pages\/5\/revisions\/294"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=5"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nView my usergroup presentation “Making MySQL highly available using Oracle Grid Infrastructure”<\/a><\/p>\nPurpose and requirements<\/h1>\n
Introduction<\/h2>\n
Design Requirements<\/h2>\n
\n
Software Requirements<\/h2>\n
\n
Architecture overview<\/h1>\n
Introduction<\/h2>\n
\nOracle Clusterware is included in the Oracle Unbreakable Linux Basic Support package.<\/p>\nSimple overview<\/h2>\n
\n
Details<\/h2>\n
Filesystem overview<\/h3>\n
Directory structure<\/h4>\n
\n * Shared filesystem for MySQL software and clustering scripts. This requires very little space, 10-15 GB filesystem is enough. Also take into account that each version of 5.5 MySQL advanced edition tar-ball is over 600MB.
\n * Shared filesystem for MySQL instance data-directories. This would store the databases. I’d like to use a separate LUN\/filesystem for each MySQL instance.<\/p>\n\n\n
\n \/u02<\/em><\/th>\n Mountpoint for software directory<\/th>\n<\/tr>\n<\/thead>\n \n\n \/u02\/app\/mysql\/<\/em><\/td>\n Location for scripts<\/td>\n<\/tr>\n \n \/u02\/app\/mysql\/product\/5.5.17\/advanced<\/em><\/td>\n Location for MySQL RDBMS software version 5.5.17 advanced edition<\/td>\n<\/tr>\n \n \/u02\/app\/mysql\/product\/5.5.16\/advanced<\/em><\/td>\n Location for MySQL RDBMS software version 5.5.16 advanced edition<\/td>\n<\/tr>\n \n \/u02\/app\/mysql\/product\/5.5.16\/community<\/em><\/td>\n Location for MySQL RDBMS software version 5.5.16 community edition<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n
\n \/instance\/instance_name<\/em><\/em><\/th>\n Mountpoint for MySQL data-directory filesystem<\/th>\n<\/tr>\n<\/thead>\n \n\n \/instance\/instance_name<\/em>\/config\/my.cnf<\/em><\/td>\n mysqld configuration file for instance instance_name<\/em><\/td>\n<\/tr>\n \n \/instance\/instance_name<\/em>\/data<\/em><\/td>\n Data directory for MySQL instance instance_name<\/em><\/td>\n<\/tr>\n \n \/instance\/instance_name<\/em>\/logs<\/em><\/td>\n Error\/general\/slow logs for instance instance_name<\/em>. Logs are rotated and archived by scripts automatically.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Creating and mounting ACFS volumes<\/h4>\n
-- todo\n<\/code><\/pre>\n-- todo\n<\/code><\/pre>\n-- todo\n<\/code><\/pre>\nInstallation<\/h1>\n
adduser mysql\npasswd -l mysql\n<\/code><\/pre>\n
\nSet execute flag on script files<\/p>\nchown a+x action_handler.scr\nchown a+x init_mysql_db.sh\nchown a+x mysql_handler.sh\n<\/code><\/pre>\n
\nAll managed MySQL instances are configured in instances.sh as a configuration block like this (NB! This is a BASH file, so all variables must follow BASH rules). Please take a look at AddNewInstance:<\/p>\nsample_instance=(\n DATADIR=\"\/instance\/sample_instance\/data\"\n CONFFILE=\"\/instance\/sample_instance\/config\/my.cnf\"\n BINDADDR=\"10.0.1.2\"\n SOFTWARE=\"\/u02\/app\/mysql\/product\/5.5.17\/advanced\"\n LOGDIR=\"\/instance\/sample_instance\/logs\"\n)\n<\/code><\/pre>\nmkdir -p \/u02\/app\/mysql\/product\/5.5.17\ncd \/u02\/app\/mysql\/product\/5.5.17\ntar xzf mysql-advanced-5.5.17-linux2.6-x86_64.tar.gz\nmv mysql-advanced-5.5.17-linux2.6-x86_64 advanced\n<\/code><\/pre>\ntouch \/root\/.my.cnf\nchmod 600 \/root\/.my.cnf\nvi \/root\/.my.cnf\n<\/code><\/pre>\n[client]\nuser=root\npassword=\"sOmepassw!ord\"\n\n[mysqldump]\nuser=root\npassword=\"sOmepassw!ord\"\n<\/code><\/pre>\nAdding a new node<\/h1>\n
2.1. Add new MySQL instance automatically<\/h2>\n
\n
This script initializes MySQL data directory and all cluster scripts.\nScript must be run as user root.\nUsage:\n-i name\n Instance name. Name should be 20 characters or less and only characters a-z, A-Z, 0-9 and _ are allowed\n (\\w character class)\n\nMySQL and instance settings:\n\n-s directory\n MySQL software location (where tar-edition is unpacked)\n-b ipaddr\n IP address to bind the instance\n-a basedirectory\n Instance base directory, this replaces options -d -c -l by setting the following values\n -d basedirectory\/data, -c basedirectory\/config\/my.cnf, -l basedirectory\/logs\n-d directory\n Empty MySQL data directory to initialize\n-c file\n MySQL config file for this instance\n-l directory\n MySQL error\/slow\/general logfile directory for this instance\n\nAdditional configuration:\n\n-x\n Do not update instances.sh\n-u\n Do not set mysql root password and clusterware user\n (creating user requires starting mysqld and default password specified in \/root\/.my.cnf)\n-g\n Add resources to clusterware (VIP and application)\n<\/code><\/pre>\n[root@dbmc1n01 mysql]# mkdir -p \/instance\/sample_instance\/data\n[root@dbmc1n01 mysql]# mkdir -p \/instance\/sample_instance\/config\n[root@dbmc1n01 mysql]# mkdir -p \/instance\/sample_instance\/logs\n[root@dbmc1n01 mysql]# touch \/instance\/sample_instance\/config\/my.cnf\n[root@dbmc1n01 mysql]# .\/init_mysql_db.sh -i sample_instance -a \/instance\/sample_instance \\\n> -b 127.0.0.1 -s \/u02\/app\/mysql\/product\/5.5.20\/advanced\nInstalling MySQL system tables...\nOK\nFilling help tables...\nOK\n\nTo start mysqld at boot time you have to copy\nsupport-files\/mysql.server to the right place for your system\n\nPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !\nTo do so, start the server, then issue the following commands:\n\n\/u02\/app\/mysql\/product\/5.5.20\/advanced\/bin\/mysqladmin -u root password 'new-password'\n\/u02\/app\/mysql\/product\/5.5.20\/advanced\/bin\/mysqladmin -u root -h 10.21.8.21 password 'new-password'\n\nAlternatively you can run:\n\/u02\/app\/mysql\/product\/5.5.20\/advanced\/bin\/mysql_secure_installation\n\nwhich will also give you the option of removing the test\ndatabases and anonymous user created by default. This is\nstrongly recommended for production servers.\n\nSee the manual for more instructions.\n\nYou can start the MySQL daemon with:\ncd \/u02\/app\/mysql\/product\/5.5.20\/advanced ; \/u02\/app\/mysql\/product\/5.5.20\/advanced\/bin\/mysqld_safe &\n\nYou can test the MySQL daemon with mysql-test-run.pl\ncd \/u02\/app\/mysql\/product\/5.5.20\/advanced\/mysql-test ; perl mysql-test-run.pl\n\nPlease report any problems with the \/u02\/app\/mysql\/product\/5.5.20\/advanced\/scripts\/mysqlbug script!\n\nStarting MySQL...\nCreating 'clusterware'@'localhost' user...\nChanging root password...\nStopping MySQL...\nUpdating cluster configuration \/u02\/app\/mysql\/instances.sh...\nSymlinking actionscript sample_instance.scr...\n<\/code><\/pre>\n[root@dbmc1n01 mysql]# .\/init_grid.sh\n\nThis script initializes MySQL instance in Oracle Clusterware, by creating virtual IP resource and application resource.\nScript must be run as user root.\nUsage:\n-i name\n Instance name. Name should be 20 characters or less and only characters a-z, A-Z, 0-9 and _ are allowed (\\w character class)\n-t type (default cluster_resource)\n Resource type\n-p pool name (default Generic)\n Server pool name\n-l number (default 10)\n Instance load attribute\n<\/code><\/pre>\n[root@dbmc1n01 mysql]# .\/init_grid.sh -i sample_instance -t mysql_instance -p pte\n<\/code><\/pre>\n2.2. Add new MySQL instance manually<\/h2>\n
\nI assume that the newly added instance is named sample_instance<\/em>.<\/p>\ndatadir\nbind_address\nsocket\npid-file\nlog-error\nslow-query-log-file\ngeneral-log-file\n<\/code><\/pre>\nchown mysql:mysql \/instance\/sample_instance\/data\n\/u02\/app\/mysql\/product\/5.5.17\/advanced\/scripts\/mysql_install_db \\\n --skip-name-resolve \\\n --basedir=\/u02\/app\/mysql\/product\/5.5.17\/advanced \\\n --datadir=\/instance\/sample_instance\/data \\\n --user=mysql\n<\/code><\/pre>\nsample_instance=(\n DATADIR=\"\/instance\/sample_instance\/data\"\n CONFFILE=\"\/instance\/sample_instance\/config\/my.cnf\"\n BINDADDR=\"10.0.1.2\"\n SOFTWARE=\"\/u02\/app\/mysql\/product\/5.5.17\/advanced\"\n LOGDIR=\"\/instance\/sample_instance\/logs\"\n)\n<\/code><\/pre>\ncd \/u02\/app\/mysql\nln -s action_handler.scr sample_instance.scr\n<\/code><\/pre>\n\/u02\/app\/mysql\/sample_instance.scr start\n<\/code><\/pre>\n
\nStart MySQL console and create clusterware user (reload privilege is needed for logrotate feature):<\/p>\n\/u02\/app\/mysql\/sample_instance.scr\n\n ******* Wed Nov 9 12:50:03 CET 2011 **********\nAction script '' for resource[] called for action\nHandler script: .\/mysql_handler.sh mysql instance sample_instance\nUsage: .\/mysql_handler.sh {start|stop|check|clean}. All other arguments execute MySQL Console.\nWelcome to the MySQL monitor. Commands end with ; or \\g.\nYour MySQL connection id is 7\nServer version: 5.5.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)\n\nCopyright (c) 2000, 2011, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql> create user 'clusterware'@'localhost' identified by 'cluster123';\nmysql> grant reload on *.* to 'clusterware'@'localhost';\n<\/code><\/pre>\n\/u02\/app\/mysql\/sample_instance.scr check\n\n ******* Wed Nov 9 13:06:03 CET 2011 **********\nAction script '' for resource[] called for action check\nHandler script: .\/mysql_handler.sh mysql instance sample_instance\nExit value: 0\n<\/code><\/pre>\nappvipcfg create -network=1 -ip=10.0.1.2 -vipname=sample_instance_vip -user=root\ncrsctl setperm resource sample_instance_vip -o root\ncrsctl setperm resource sample_instance_vip -u user:grid:r-x\n<\/code><\/pre>\ncrsctl add resource sample_instance -type cluster_resource \\\n-attr \"ACTION_SCRIPT=\/u02\/app\/mysql\/sample_instance.scr, PLACEMENT='balanced', LOAD=10, CHECK_INTERVAL='5', RESTART_ATTEMPTS='2', START_DEPENDENCIES='hard(sample_instance_vip)', STOP_DEPENDENCIES='hard(sample_instance_vip)'\"\ncrsctl setperm resource sample_instance -o root\ncrsctl setperm resource sample_instance -u user:grid:r-x\n<\/code><\/pre>\ncrsctl start resource sample_instance\ncrsctl stop resource sample_instance -f\ncrsctl relocate resource sample_instance -f\n<\/code><\/pre>\nLogrotate<\/h1>\n
\n