Setup MySQL DB on EC2 Amazon Linux for Confluence, JIRA & Fisheye


Change MySQL Configuration

  1. Stop MySQL
    sudo service mysqld stop
    
  2. sudo vi /etc/my.cnf
    • Change datadir location to
      [mysqld]
      datadir=/data/mysql
      socket=/data/mysql.sock
      ...
      
      [client]
      socket=/data/mysql.sock
      
    • Add MySQL Configuration
      [mysqld]
      ...
      character-set-server=utf8
      default-storage-engine=INNODB
      binlog_format="ROW"
      log-bin=log-bin
      key_buffer_size = 64M
      table_open_cache = 256
      read_buffer_size = 1M
      read_rnd_buffer_size = 4M
      myisam_sort_buffer_size = 16M
      thread_cache_size = 8
      query_cache_size= 16M
      innodb_buffer_pool_size = 128M
      innodb_additional_mem_pool_size = 32M
      innodb_log_file_size = 32M
      innodb_log_buffer_size = 8M
      transaction_isolation=READ-COMMITTED
      max_allowed_packet=32M
      

      ...

      [mysqld_safe]

      ...

      log-slow-queries=/var/log/slow_query.log myisam_recover_options

      ...

      Note the memory allocation above may need further tuning subject to the size of the physical memory

    • READ-COMMMITTED is needed for Confluence because of the following errors: MySQL isolation level 'REPEATABLE-READ' is no longer supported. Isolation level must be 'READ-COMMITTED'

  3. Move the data directory
    sudo mv /var/lib/mysql /data
    
  4. Remove the binary log file so it can be resized
    sudo rm /data/mysql/ib_logfile0
    sudo rm /data/mysql/ib_logfile1
    
  5. Start MySQL
    sudo service mysqld start
    

Schema Changes

Confluence

CREATE DATABASE confluence CHARACTER SET utf8;

GRANT ALL ON confluence.* to 'confluenceuser'@'localhost' identified by 'changethissecret';
FLUSH PRIVILEGES;

JIRA

CREATE DATABASE jiradb CHARACTER SET utf8;

GRANT ALL ON jiradb.* to 'jirauser'@'localhost' identified by 'changethissecret';
FLUSH PRIVILEGES;

FishEye

CREATE DATABASE fisheye CHARACTER SET utf8 COLLATE utf8_bin;

GRANT ALL ON fisheye.* to 'fisher'@'localhost' identified by 'changethissecret';
FLUSH PRIVILEGES;