Skip to content

107a_Aegir Setup

Jump to bottom
Luke Yang edited this page May 20, 2023 · 1 revision

Preface

About

This page provides instructions for setting up and Aegir front end for bulk hosting of WordPress + CiviCRM sites connected to the CiviPress infrastructure. Each step includes an identity that indicates which application should be used for the instructions that proceed. Examples are also provided for steps that have varying parameters depending on the context (server names, usernames, passwords, etc).

Note: Even though each new step indicates shelling into the server, it is not necessary often as you can continue on a previously open terminal that is already shelled into the server. The goal is to provide ease of understanding if a specific portion needs to be configured or changed in contrast from going through start to finish.

Example Values

The following values will be used for the examples:

Parameter Value
User on VPS servers luke
Primary Percona Server b13.jmaconsulting.biz
Front End Server b14.jmaconsulting.biz


Prerequisites

  • 6 VPS's configured


Main Documentation

Clone the repository from here

1. Percona Configuration for Aegir

Identity: Local Machine, Terminal

# shell into the Primary Percona server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]


Identity: Primary Percona Server, Terminal

# login into Percona MySQL as root user and type in MySQL root password
$ mysql -uroot -p


Identity: Primary Percona Server, MySQL Console

/* allows WordPress to use triggers in MySQL */
mysql> SET GLOBAL log_bin_trust_function_creators=1;


3. Create a WordPress Database and User

Identity: Primary Percona Server, MySQL Console

/* create an Aegir root user */
mysql> CREATE USER 'aegir_root'@'[civipress_front_end_host]' IDENTIFIED WITH mysql_native_password BY '[password]';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'wp_user'@'[civipress_front_end_host]';

/* ===== example ===== */
mysql> CREATE USER 'aegir_root'@'b14.jmaconsulting.biz' IDENTIFIED WITH mysql_native_password BY 'pass1234';
mysql> GRANT ALL PRIVILEGES ON wp_db.* TO 'aegir_root'@'b14.jmaconsulting.biz';


2. Configure Aegir Root Database User in ProxySQL

Identity: Local Machine, Terminal

# shell into the front-end server
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]


Identity: CiviPress Front-End Server, Terminal

# login into ProxySQL as admin user using the password that you set when configuring ProxySQL
$ mysql -u admin -p -h 127.0.0.1 -P 6032


Identity: CiviPress Front-End Server, ProxySQL Console

/* add WordPress and CiviCRM database user to ProxySQL */
mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('aegir_root','[password]',1);

/* save the users with hashed passwords */
mysql> LOAD MYSQL USERS TO RUNTIME;
mysql> SAVE MYSQL USERS FROM RUNTIME;
mysql> SAVE MYSQL USERS TO DISK;

/* configuration to make failover work */
mysql> SET mysql-set_query_lock_on_hostgroup=0;
mysql> LOAD MYSQL VARIABLES TO RUNTIME;
mysql> SAVE MYSQL VARIABLES TO DISK;

/* ===== example ===== */
mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('aegir_root','pass1234',1);

mysql> LOAD MYSQL USERS TO RUNTIME;
mysql> SAVE MYSQL USERS FROM RUNTIME;
mysql> SAVE MYSQL USERS TO DISK;

mysql> SET mysql-set_query_lock_on_hostgroup=0;
mysql> LOAD MYSQL VARIABLES TO RUNTIME;
mysql> SAVE MYSQL VARIABLES TO DISK;


3. Install Aegir

Clone Seamus' Aegir Ansible Script from here Identity: Local Machine, Terminal

# shell into the front-end server
$ git clone https://lab.jmaconsulting.biz/jma/ansible-configuration.git
$ cd ansible-configuration
$ ansible-playbook -K -i production -l [aegir_server] --tags aegir ./site.yml

# ===== example =====
$ git clone https://lab.jmaconsulting.biz/jma/ansible-configuration.git
$ cd ansible-configuration
$ ansible-playbook -K -i production -l b14.jmaconsulting.biz --tags aegir ./site.yml


4. Switch Aegir to Use ProxySQL

Identity: Local Machine, Terminal

$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]


Identity: Front End Server, Terminal

# run the drush command to get a one time reset password link
$ sudo su aegir -c 'drush @hm uli'

# Copy the link into the browser and reset password
# Navigate to the 'Hostmaster' tab located at the top right of the site
# Click on the database server (it should be the primary percona server)
# Click on the 'Edit' tab
# Change server hostname from the primary percona server to '127.0.0.1'
# Click on the 'Database' tab
# Click on the 'MySQL' radio button
# Change the port from 3306 to 6033
# Get the aegir root password from the primary percona server
# Type in the aegir root password
# Save


5. Make Changes to Allow ProxySQL and WordPress + CiviCRM to work

Identity: Local Machine, Terminal

$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]


Apply the following github changes for the aegir drush commands

Apply the following github changes for the hosting_wordpress module


6. Changing the Main Aegir Site to Connect to ProxySQL

Identity: Local Machine, Terminal

$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]


Identity: Front End Server, Terminal

# get the main aegir site database credentials
$ nano /var/aegir/hostmaster-7.x-3.x/sites/[aegir_site]/drushrc.php


Identity: Front End Server, Terminal

# log into proxysql
$ mysql -u admin -p -h 127.0.0.1 -P 6032


Identity: Front End Server, ProxySQL Admin Terminal

mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('[db_user]', '[db_passwd]', 1);
mysql> LOAD MYSQL USERS TO RUNTIME;
mysql> SAVE MYSQL USERS FROM RUNTIME;
mysql> SAVE MYSQL USERS TO DISK;


Identity: Local Machine, Terminal

# shell into primary Percona server to change aegir site user password plugin
$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]


Identity: Primary Percona Server, Terminal

# log into mysql
$ mysql -uroot -p


Identity: Front End Server, MySQL Terminal

mysql> DROP USER '[db_user]'@'[db_host]';
mysql> CREATE USER '[db_user]'@'[db_host]' IDENTIFIED WITH mysql_native_password BY '[db_passwd]';
mysql> GRANT ALL PRIVILEGES ON '[db_name]'.* to '[db_user]'@'[db_host]';


Identity: Local Machine, Terminal

$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]


Identity: Front End Server, Terminal

# get the main aegir site database credentials
$ nano /var/aegir/hostmaster-7.x-3.x/includes/database/mysql/database.inc


Identity: database.inc

# comment out the following line
sql_mode .= ',NO_AUTO_CREATE_USER';


Identity: Front End Server, Terminal

# Change the database host and port
$ nano /var/aegir/hostmaster-7.x-3.x/sites/[aegir_site]/drushrc.php


Identity: drushrc.php

# Change the existing db_host and db_port values to ProxySQL
$options['db_host'] = '127.0.0.1';
$options['db_port'] = '6033';


Go to the Aegir Site GUI and run the Verify task on the Aegir site again.


7. Enabling Backend SSL for the Main Aegir Site

Identity: Local Machine, Terminal

$ ssh [user]@[host]

# ===== example =====
$ ssh [email protected]


Identity: Front End Server, Terminal

# Add SSL certificate to Aegir Site
$ nano /var/aegir/hostmaster-7.x-3.x/sites/[aegir_site]/settings.php


Identity: settings.php

# add the following to the end of the array stored in $databases['default']['default']

'pdo' => [
    PDO::MYSQL_ATTR_SSL_CA => dirname(__DIR__) . '/b14.jmaconsulting.biz/cacert.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
],
Clone this wiki locally