MySQL¶

Default values for MySQL configuration: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

Use http://www.mysqlcalculator.com to calculate settings depending on your host configuration.

Configure Backups¶

By default we install automatic MySQL backups pre-configured with rotating daily, weekly and monthly backups which will be stored in /var/backups/mysql. Those backups will be done at 2am every day by a cron task.

In defaults/main.yml you'll find a variable mysqlbackup with all the default values being defined and if you want to change those, copy that into your inventory and make the changes there.

Special note on excluding tables¶

By default no tables are excluded. You can define a list of excluded tables in mysqlbackup.exclude.table where each item in the list must be defined in the format dbname.tablename and the tablename accepts wildcards, e.g. mydb.cache* to exclude all tables that start with cache in their name.

Configuring Drupal databases¶

The Drupal role defines databases for each domain in a variable named drupal_settings.ITEM.domains.DOMAIN.db and inside of this dictionary you can either turn off MySQL backup for that database completely by adding backup: false or you can exclude certain tables by adding a list in backup_exclude with table names supporting wildcards as well. Note, you don't have to provide the db name here as we have already defined that once before.

Examples:

drupal_settings:
  - ...
    domains:
      - ...
        db:
          ...
          backup: false
      - ...
        db:
          ...
          backup_exclude:
            - cache*
            - access*

Replication¶

Setting up a new replication¶

Starting from here there are basically these steps:

Create a DB dump: lock tables, remember bin log position, dump db (see Choosing a Method for Data Snapshots) and copy to secondary host
Unlock the tables again
Configure secondary for new master with Ansible playbook
On the secondary, start the MySQL server and turn off replication
Import dump file and then start replication again

On the primary:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

On the primary in a second shell:

mysqldump --all-databases --master-data > dbdump.db
scp dbdump.db >[SECONDARY]:

On the primary:

UNLOCK TABLES;

With Ansible:

apb mysqlsecondary --company=[COMPANY] --tags=changemaster --extra-vars="port=[PORT]" --extra-vars="binpos=[BINPOS]" --extra-vars="binlog=/var/log/mysql/[BINPOS]"

On the secondary:

1	`mysql < dbdump.db`

START SLAVE;
SHOW SLAVE STATUS;