My favorites | Sign in
Project Home Downloads Wiki Issues Source
READ-ONLY: This project has been archived. For more information see this post.
Search
for
AddNewInstance  
How to add new MySQL instance.
Phase-Deploy
Updated Dec 13, 2012 by ilmar.kerm

1. Introduction

How to add new MySQL instance automatically and manually. Make sure you have read and followed Installation document before.

2. Details

2.1. Add new MySQL instance automatically

The easiest way to add new instance is by using the script init_mysql_db.sh and after that run init_grid.sh to add the newly added instance to clusterware. The scripts do the followings tasks automatically:

  1. Initializes MySQL data directory
  2. Creates MySQL user for clusterware monitoring
  3. Updates instances.sh configuration file
  4. Adds resources in clusterware - init_grid.sh

All init_mysql_db.sh options:

This script initializes MySQL data directory and all cluster scripts.
Script must be run as user root.
Usage:
-i name
        Instance name. Name should be 20 characters or less and only characters a-z, A-Z, 0-9 and _ are allowed
        (\w character class)

MySQL and instance settings:

-s directory
        MySQL software location (where tar-edition is unpacked)
-b ipaddr
        IP address to bind the instance
-a basedirectory
        Instance base directory, this replaces options -d -c -l by setting the following values
        -d basedirectory/data, -c basedirectory/config/my.cnf, -l basedirectory/logs
-d directory
        Empty MySQL data directory to initialize
-c file
        MySQL config file for this instance
-l directory
        MySQL error/slow/general logfile directory for this instance

Additional configuration:

-x
        Do not update instances.sh
-u
        Do not set mysql root password and clusterware user
        (creating user requires starting mysqld and default password specified in /root/.my.cnf)
-g
        Add resources to clusterware (VIP and application)

To create MySQL instance sample_instance under /instance/sample_instance/data, config file located in /instance/sample_instance/config/my.cnf and logs located under /instance/sample_instance/logs execute the following command:

[root@dbmc1n01 mysql]# mkdir -p /instance/sample_instance/data
[root@dbmc1n01 mysql]# mkdir -p /instance/sample_instance/config
[root@dbmc1n01 mysql]# mkdir -p /instance/sample_instance/logs
[root@dbmc1n01 mysql]# touch /instance/sample_instance/config/my.cnf
[root@dbmc1n01 mysql]# ./init_mysql_db.sh -i sample_instance -a /instance/sample_instance \
> -b 127.0.0.1 -s /u02/app/mysql/product/5.5.20/advanced
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/u02/app/mysql/product/5.5.20/advanced/bin/mysqladmin -u root password 'new-password'
/u02/app/mysql/product/5.5.20/advanced/bin/mysqladmin -u root -h 10.21.8.21 password 'new-password'

Alternatively you can run:
/u02/app/mysql/product/5.5.20/advanced/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /u02/app/mysql/product/5.5.20/advanced ; /u02/app/mysql/product/5.5.20/advanced/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /u02/app/mysql/product/5.5.20/advanced/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /u02/app/mysql/product/5.5.20/advanced/scripts/mysqlbug script!

Starting MySQL...
Creating 'clusterware'@'localhost' user...
Changing root password...
Stopping MySQL...
Updating cluster configuration /u02/app/mysql/instances.sh...
Symlinking actionscript sample_instance.scr...

Execute init_grid.sh to add newly added instance to clusterware. You can skip this step to add the resources using default values by specifying -g option to init_mysql_db.sh previously:

All options you can use with the script:

[root@dbmc1n01 mysql]# ./init_grid.sh

This script initializes MySQL instance in Oracle Clusterware, by creating virtual IP resource and application resource.
Script must be run as user root.
Usage:
-i name
        Instance name. Name should be 20 characters or less and only characters a-z, A-Z, 0-9 and _ are allowed (\w character class)
-t type (default cluster_resource)
        Resource type
-p pool name (default Generic)
        Server pool name
-l number (default 10)
        Instance load attribute

To add the new instance using resource type mysql_instance (there you can set check interval and other parameters) and to server pool pte use the following command:

[root@dbmc1n01 mysql]# ./init_grid.sh -i sample_instance -t mysql_instance -p pte

2.2. Add new MySQL instance manually

NB! All these tasks can be done automatically using script init_mysql_db.sh

Execute everything as user root. I assume that the newly added instance is named sample_instance.

  1. 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):
  2. datadir
    bind_address
    socket
    pid-file
    log-error
    slow-query-log-file
    general-log-file
  3. Initialize MySQL data directory /instance/sample_instance/data
  4. chown mysql:mysql /instance/sample_instance/data
    /u02/app/mysql/product/5.5.17/advanced/scripts/mysql_install_db \
      --skip-name-resolve \
      --basedir=/u02/app/mysql/product/5.5.17/advanced \
      --datadir=/instance/sample_instance/data \
      --user=mysql
  5. Add instance configuration to /u02/app/mysql/instances.sh:
  6. sample_instance=(
      DATADIR="/instance/sample_instance/data"
      CONFFILE="/instance/sample_instance/config/my.cnf"
      BINDADDR="10.0.1.2"
      SOFTWARE="/u02/app/mysql/product/5.5.17/advanced"
      LOGDIR="/instance/sample_instance/logs"
    )
  7. Symlink actionscript sample_instance.scr
  8. cd /u02/app/mysql
    ln -s action_handler.scr sample_instance.scr
  9. Start mysql
  10. /u02/app/mysql/sample_instance.scr start
  11. Check MySQL log files and ensure that MySQL process started.
  12. Start MySQL console and create clusterware user (reload privilege is needed for logrotate feature):
  13. /u02/app/mysql/sample_instance.scr
    
     *******   Wed Nov  9 12:50:03 CET 2011 **********
    Action script '' for resource[] called for action
    Handler script: ./mysql_handler.sh mysql instance sample_instance
    Usage: ./mysql_handler.sh {start|stop|check|clean}. All other arguments execute MySQL Console.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7
    Server version: 5.5.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
    
    Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> create user 'clusterware'@'localhost' identified by 'cluster123';
    mysql> grant reload on *.* to 'clusterware'@'localhost';
  14. Check pinging MySQL, if exit value is 0, then all is good and instance is working.
  15. /u02/app/mysql/sample_instance.scr check
    
     *******   Wed Nov  9 13:06:03 CET 2011 **********
    Action script '' for resource[] called for action check
    Handler script: ./mysql_handler.sh mysql instance sample_instance
    Exit value: 0
  16. Create Virtual IP resource in clusterware
  17. appvipcfg create -network=1 -ip=10.0.1.2 -vipname=sample_instance_vip -user=root
    crsctl setperm resource sample_instance_vip -o root
    crsctl setperm resource sample_instance_vip -u user:grid:r-x
  18. Create application resource in clusterware
  19. crsctl add resource sample_instance -type cluster_resource \
    -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)'"
    crsctl setperm resource sample_instance -o root
    crsctl setperm resource sample_instance -u user:grid:r-x
  20. Test...
  21. crsctl start resource sample_instance
    crsctl stop resource sample_instance -f
    crsctl relocate resource sample_instance -f
Powered by Google Project Hosting