NDPF Database Server

Server Essentials

The current server details are:

• hosted at stolp.nikhef.nl, located in rack C09U18
• database server supported: MySQL 5.1
• databases hosted:

itb_cns
itb_dpm
itb_lfc
itb_lfc_db
mysql
ndpf_cns
ndpf_dpm
ndpf_lfc
voms_dteam
voms_GIN_GGF_ORG
voms_TEST

Configuring a new server

Installed as a database server with the following configuration:

• Use new PE2960 from Generics 2008/A series: 4x500GB SATA on PERC6/i controller, 24 GB RAM, 2x4-core 2.83 GHz E5440
• RAID-5 over disks 1,2,3, HS (global, proximity) on disk 4
• installed with CentOS5U3 with patches (including latest kernel for 2692), see [https://www.nikhef.nl/grid/ndpf/files/local/install/stolp.nikhef.nl.ks]
• installed MySQL 5.1.37 Community edition (pick the latest 5.1 series), e.g. from

http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-server-community-5.1.37-0.rhel5.x86_64.rpm/from/http://mirror.leaseweb.com/mysql/

and download/mirror all of 'server', 'client', 'shared', 'shared-compat', and maybe debuginfo as well

• allow all hosts in the farm to talk through deel/nikopn to 3306 in farmnet-grid (194.171.97.0/24)
• configure iptables to allow 3306 connections from selected hosts. The default configuration is triggered by the kickstart file from the scripts in [https://www.nikhef.nl/grid/ndpf/files/local/install], but these make it a gridSrv default node
• change the root ssh affiliation to ndpfPrivilegedUsers (from gridSrvPrivilegedUsers)
• install xinetd and include host in rsync-backup on beerput

About MySQL

The downloaded MySQL 5.1.37 EL5/x86-64 RPMs in /www/grid/ndpf/files/MySQL-5.1.37-RHEL5-x86-64/ should NOT be linked to the packages/ repository, as by default the MySQL.com client will provide a version of MySQL that will replace the OS-provided one. This triggers subsequent errors in php-mysql and will block any future yum auto-updates.

Our my.cnf file

After installing MySQL (and killing the server), put in the proper my.cnf file to be copied by restoredb.sh and wipe the /var/lib/mysql directory. An example my.cnf file is

[mysqld]

innodb_file_per_table
innodb_open_files=1024

set-variable=max_connections=1000

log-slow-queries = /var/log/mysql-slow.log
long_query_time = 4

key_buffer_size = 2048M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
tmp_table_size=512M
thread_concurrency = 8

innodb_buffer_pool_size = 3072M
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Migration plan

To migrate from bedstee to stolp in a safe way, try:

1. stop all services that depend on the database. In ELPROD: dpns, dpm (both tbn18), lfc (klomp), and voms (karnton); in the ITB also dpm and lfc.
2. stop the mysql daemon on bedstee
3. block inbound port 3306 on bedstee in iptables
4. restart mysql on bedstee
5. run the dump-mysql.sh script to generate the latest database dump
6. copy the dump files for the content databases to stolp
7. do NOT copy the mysql database itself (the layout has changed in 5.1)
8. STOP the mysql server again

Then, on stolp:

1. remove the MySQL-server rpm with "rpm -e MySQL-server"
2. wipe all of /var/lib/mysql/ (note: this is a symlink to a dedicated partition)
3. put in place the right my.cnf, my.cfg files (latter should be symlink to former). They can be found in the home directory of root
4. change directory to the place where you copied the bedstee dumps (and ONLY those, and NO historic or old versions!)
5. copt the grants.sql file to this directory (source is in stolp:/root/ and in your mailbox)
6. re-install MySQL-server using the restoredb.sh script, found in "~root/" on stolp. Use the specific database root password when requested. The source of the script is at [https://www.nikhef.nl/grid/ndpf/files/local/install/restoredb.sh]
7. restart mysql for good measure
8. also for good measure

chkconfig mysql on
chkconfig xinetd on

Then, on all services:

1. change references from bedstee to stolp in Quattor
2. push all changes and restart services
3. see if it all works

Lastly:

1. do a chkconfig mysql off on bedstee
2. see if /etc/mysql exists on stolp
3. run a test backup with dump-mysql.sh on stolp (source at [https://www.nikhef.nl/grid/ndpf/files/tmp/dump-mysql.sh]
4. check if dump-mysql.sh is in the crontab for root
5. run a backfrom from beerput to see if the mysql dump files make it to there

After one day:

1. check if the mysql dump files made it to ADSM