Project:MariaDB-K8s

MariaDB configuration

Set up the DB

The main database of the portal is based on MariaDB. Our MariaDB instance is deployed an managed using mariadb-operator, which allows to use Custom Resource Definitions (CRDs) in Kubernetes to declaratively manage our database. In simple terms, this allows us to define all our DB resources (databases, uses, grants...) using YAML files.

The MariaDB operator takes care of the DB replication to spin up a main node and several other read replicas.

The following definition in mariadb.yaml assigns an external IP to access the DB from outside the cluster.

primaryService:
  type: LoadBalancer

secondaryService:
  type: LoadBalancer

primaryService represents the write node whereas secondaryService refers to the read-only replicas. With

kubectl get svc

it is possible to see which specific IP was assigned to these services. These IPs are necessary to set up the connection between the MediaWiki container and the DB.

MediaWiki configuration

MediaWiki requires its SQL user to have the SLAVE MONITOR and BINLOG MONITOR privileges in order for replication to work. These privileges are thus defined in grant.yaml.

The connection between MediaWiki and the different DB nodes can be specified with the following parameter in LocalSettings.php, substituting primaryService-IP and secondaryService-IP by the right values:

$wgLBFactoryConf = array(

    'class' => 'LBFactoryMulti',

    'sectionsByDB' => array(
        'my_wiki' => 's1', 
    ),

    'sectionLoads' => array(
        's1' => array(
            '<primaryService-IP>'   => 0,
            '<secondaryService-IP>' => 50,
        ),
    ),


    'serverTemplate' => array(
        'dbname'     => $wgDBname,
        'user'       => $wgDBuser,
        'password'   => $wgDBpassword,
        'type'       => 'mysql',
        'flags'      => DBO_DEFAULT,
        'max lag'    => 30,
    ),
);

Backup

• Backups for the database are scheduled every 24h. We simultaneously keep 7 copies for the last 7 days.
• MariaDB-operator takes care of creating the backup and pushing them into an S3 bucket, as defined in backup.yaml.
• Production backups are prefixed with production, staging backups use the prefix staging.
• The files in the S3 bucket can examined using s3cmd:

s3cmd --host=hsm-test-09.zib.de:9001 --host-bucket=hsm-test-09.zib.de:9001 --region=us-east-1 --access_key=<access_key> --secret_key=<secret_key> ls s3://

Restore a database to the cluster

To restore or load a backup file once the DB is running.

1) Copy the backup file inside the main DB pod. The main pod is the only one with write rights. The file can be copied into the volume attached to the mariadb-0 pod, which will have enough space for it. This is mounted to /var/lib/mysql:

kubectl cp ./portal_db_backup.gz (production|staging)/mariadb-0:/var/lib/mysql

2) Unzip and load the file into the database:

Unzipping directly will probably fail with out of memory errors when the backup file is big.

cd /var/lib/mysql
gzip -d portal_db_backup.gz

Therefore it might be better to stream the compressed file into a new file:

gunzip -c portal_db_backup.gz > portal_backup

3) The backup can then be loaded into the database using:

mariadb --max_allowed_packet=1G -u root -p my_wiki < portal_backup

The --max_allowed_packet can be tweaked to avoid memory erors that could crash the pod.

4) During the restoration of the database it is important to keep an eye on the available disk space. To carry out the replication there will be several log files like prod-mariadb-bin.000001 that can quickly fill up the disk space.

To check whether these files can already be deleted one can log into the replica and run

SHOW ALL SLAVES STATUS\G

to check whether the log file has already been processed. For this, check the both current Master_Log_File or Relay_Master_Log_File. Any log files below the number mentiond in these two fields can be already deleted with:

PURGE BINARY LOGS TO 'prod-mariadb-bin.0000XX';