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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13
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:
On the primary in a second shell:
On the primary:
On the secondary: