CS MySQL Database Overview for System Administrators

Public MySQL servers, available across campus

Public Alias Current Hostname OS MySQL Apps Accounts
mysql.student.cs mysql-002.student.cs ubuntu-14.04 5.5 cs338/webdb CS students, on request
mysql.cs mysql-10[246].cs ubuntu-14.04 5.5 Marmoset, ST, inventory, faculty recruiting ... CS faculty, grads and staff, on request - See EnterpriseMysqlClusterProject and MySQLHAOperationsManual.

Other MySQL servers

Public Alias Current Hostname OS MySQL Apps Accounts
marmoset-mysql.student.cs marmoset_db-002.student.cs ubuntu-14.04 5.5 proposed dedicated Marmoset Marmoset
was database.cs mysql170.cs ubuntu-10.10 5.1 retired -
- mysql-172.cs ubuntu-14.04 5.5 was mysql.cs, then just Marmoset now mostly retired
- mysql-270.cscf ubuntu-12.04 5.5 hot spare, testing, mostly retired -
- mysql-272.cscf ubuntu-12.04 5.5 hot spare, testing, mostly retired -

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:

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

Topic revision: r17 - 2019-03-04 - FraserGunn
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback