Skip to content

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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:

1
2
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

On the primary in a second shell:

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

On the primary:

1
UNLOCK TABLES;

With Ansible:

1
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
1
2
START SLAVE;
SHOW SLAVE STATUS;

Last update: February 20, 2021 08:25:27