CS MySQL Database Overview for System Administrators

Public MySQL servers, available across campus

Other MySQL servers

Backups

See MySQLBackup for backup and recovery information.

Monitoring

For mysql.cs and mysql.student.cs:

• Use Inventory to enable nagios services ssh and "inv-mysql".
• /var/log/mysql/error.log* contains errors and other events like backup and shutdown.
  • It also contains periodic "mysqladmin status" checks.
  • mysqlqps calculates queries per second from status in error.log.
• /var/log/mysql/slow_queries.log logs queries that take a long time (currently > 10 seconds).

Configuration (for ubuntu mysql)

• Central configuration is in /etc/mysql/my.cnf plus /etc/mysql/conf.d/*.cnf.
• Mysql clients also use ~/.my.cnf.
• Debian maintenance (and other automated root tasks) get connection parameters (especially password) from debian.cnf, e.g.: mysqladmin --defaults-file=/etc/mysql/debian.cnf status

Creation (for a CS Infrastructure LXC container)

• If this is a new mysql service and it will have a well known public hostname, e.g. mysql.cs, make the public hostname an alias. Or use some other technique that allows the public hostname to be quickly moved to a new machine.
• If this is a limited service, e.g. it is used by one application for a security function, consider using an IP address that is not routed beyond CS nets. If it could be used outside of CS, use an IP address that is not routed off campus.
• Estimate the amount of disk space needed for the database. Multiply by 4 to account for local backups and binary logs.
• Request an LXC Container (or similar virtual machine) from the CS Infrastructure group. Provide estimates of the disk space needed. The current default is 100GB. If the database will have a heavy write load, ask for SSD space.
• The container should come with these packages already installed: uwcs-container, uwcs-system-utils.
• Install python-mysqldb. Local mysql utilities may need it in the future.
• Install mysql-server. It will ask for a new mysql root password during installation. Use a random hard password, then forget it. After mysql-server is installed, delete all root users from the mysql.user table (and flush privileges). For database administration, we usually need only the automatically created debian-sys-maint@localhost.
• Install the Percona PPA in e.g. /etc/apt/sources.list.d/percona-trusty.list.
• Install percona-toolkit and percona-xtrabackup.
• Start backups before the service goes into production.
• Start monitoring before the service goes into production.

Old notes

PhpMyAdmin is available to work with these database servers:

• mysql.cs https://www.cs.uwaterloo.ca/phpMyAdmin/

PostgreSQL has advantages and should be considered for new applications. However, many important applications continue to use MySQL.