Internal Errigal Collaboration Wiki

User Tools

Site Tools

Trace:
Writing /app/www/public/data/meta/introduction/databaseenv/start.meta failed
introduction:databaseenv:start

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
introduction:databaseenv:start [2016/11/11 15:15] – edillonintroduction:databaseenv:start [2021/06/25 10:09] (current) – external edit 127.0.0.1
Line 1: Line 1:
 +====== 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 [[watchdogs:watchdog_overview|Watchdog]] process to run.
 +
 +
 +----
 +
 +
 +===== MySQL server setup =====
 +
 +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.
 +
 +
 +----
 +
 +
 +===== Replication =====
 +
 +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.
 +
 +  - 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.
 +
 +  - 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
 +
 +  - 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.
 +
 +
 +----
 +
 +
 +===== Accessing the Server =====
 +
 +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!
 +
 +
 +----
 +
 +
 +===== Onboarding Self Exercise =====
 +  - 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.
 +  - 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.
 +  - 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!)
 +  - 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.
 +  - 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.
 +  - 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.

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki