Database environment

Author: Cathal O'Keeffe

Our applications are backed by MySQL databases. Each instance of our applications (production and QA) are backed by a database server that is shared between handlers. Most of our non DB servers also have MySQL installed as it is required for the Watchdog process to run.

Depending on the machine the MySQL server instance is installed in, either /var/lib/mysql or /mysql-data/database; this directory is typically mounted on a dedicated partition with a Watchdog check to monitor when the partition starts running out of free space.

Server settings can be found in /etc/my.cnf though any changes to these settings will not take effect until after the server is restarted. The mysql server can be shut down or started up or restarted using sudo service mysqld stop, sudo service mysqld start and sudo service mysqld restart respectively.

The production database servers are replicated to slave databases. This provides many uses. For instance the databases on ccicerrigaldb1.crc is replicated to three different servers.

1. ccicerrigaldb2.crc is used to run read-only commands against in order to lighten the load on the main database server and acts as a fallback server for the applications when we need to perform maintenance on ccicerrigaldb1.crc.

1. ccicerrigaldbdr1.crc is a disaster recovery server and is part of a set of servers designed to be started up in the event of catastrophic failure on the main Crown Castle production servers so that the applications can continue running

1. qadb2.err is a slave that's running on a server on our own internal network. This allows us to take database copies when we need them (transferring such a large amount of data from the Crown Castle network to our own would take a very long amount of time)

Replication GOTCHA: Replication is command-based, not row-based. This is an important distinction. Each command run against a database that is being replicated is copied by each slave and run on them as well. Consider the following example

USE database_a

INSERT INTO database_b.table_one VALUES ('foo', 'bar', 123);

In this case even though the command is inserting data into database_b it is actually being run against database_a. This means that the command be replicated to slaves if and only if database_a is replicating. If database_b is replicating and database_a is non-replicating then the master database will now have data in it that doesn't exist on the slaves! You should also never perform updates on the slave database as this will bring them out of sync with the master, making them effectively useless.

Our databases typically have at least three users defined - reader, writer and root. The reader users have read-only privileges and should be used when querying the servers. When doing updates or inserts into the database use the writer account. In general always use the account with the minimum privileges you need at a given time. This saves on accidents.

When doing updates and inserts on the database avoid writing ad hoc queries. All updates you plan on doing should be planned beforehand and written to a single text file that is double checked by you and at least one other person. All commands should be done in a transaction so that if it fails it will all roll back. Pipe the file into mysql using standard input and run it with the -v option so you can monitor the commands as they run. Commands should also be run against a test database if possible before being run on production and if feasible backups should be taken of any tables being updated before running updates. In general just take every precaution you can! An innocent mistake on the database can lead to major headaches trying to fix it!

1. Using mysql through either the command line or workbench log into one of the QA servers with a read only account. Ask for the hostnames and password if you don't know them.
2. In the ticketer database have a look at ticket table. Try a couple of SELECT queries to familiarise yourself with the data in this table a little. E.g. try counting the number of ticket entries for specific date ranges.
3. Try and perform a small update on the table on a particular ticket ID. It should fail due to insufficient permissions (if it does not fail please raise this with your mentor immediately!)
4. Open an SSH connection to one of the master DB servers and one of the slave servers. Again ask someone if you need hostnames and passwords.
5. On each server open /etc/my.cnf in a text viewer/editor and compare the configurations for the slave and master. See how they differ and what settings are the same.
6. Log into mysql on both a master and slave server. Run the command SHOW MASTER STATUS and SHOW SLAVE STATUS on them respectively. Run them a few times and make note of how the log file position increases on both over time.