Difference between revisions of "NDPF Database Server"
From PDP/Grid Wiki
Jump to navigationJump to searchm (→Migration plan) |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 33: | Line 33: | ||
== About MySQL == | == 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. | 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 | ||
= Tools and scripts = | = Tools and scripts = | ||
| Line 55: | Line 86: | ||
# 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 | # 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 | ||
# change directory to the place where you copied the bedstee dumps (and ONLY those, and NO historic or old versions!) | # change directory to the place where you copied the bedstee dumps (and ONLY those, and NO historic or old versions!) | ||
| + | # copt the grants.sql file to this directory (source is in stolp:/root/ and in your mailbox) | ||
# 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 https://www.nikhef.nl/grid/ndpf/files/local/install/restoredb.sh]] | # 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 https://www.nikhef.nl/grid/ndpf/files/local/install/restoredb.sh]] | ||
# restart mysql for good measure | # restart mysql for good measure | ||
Latest revision as of 12:21, 7 September 2009
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
Tools and scripts
Migration plan
To migrate from bedstee to stolp in a safe way, try:
- 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.
- stop the mysql daemon on bedstee
- block inbound port 3306 on bedstee in iptables
- restart mysql on bedstee
- run the dump-mysql.sh script to generate the latest database dump
- copy the dump files for the content databases to stolp
- do NOT copy the mysql database itself (the layout has changed in 5.1)
- STOP the mysql server again
Then, on stolp:
- remove the MySQL-server rpm with "rpm -e MySQL-server"
- wipe all of /var/lib/mysql/ (note: this is a symlink to a dedicated partition)
- 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
- change directory to the place where you copied the bedstee dumps (and ONLY those, and NO historic or old versions!)
- copt the grants.sql file to this directory (source is in stolp:/root/ and in your mailbox)
- 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]
- restart mysql for good measure
- also for good measure
chkconfig mysql on chkconfig xinetd on
Then, on all services:
- change references from bedstee to stolp in Quattor
- push all changes and restart services
- see if it all works
Lastly:
- do a chkconfig mysql off on bedstee
- see if /etc/mysql exists on stolp
- run a test backup with dump-mysql.sh on stolp (source at [https://www.nikhef.nl/grid/ndpf/files/tmp/dump-mysql.sh]
- check if dump-mysql.sh is in the crontab for root
- run a backfrom from beerput to see if the mysql dump files make it to there
After one day:
- check if the mysql dump files made it to ADSM
