wiki:db/obsolete/MySQLServerConfiguration

Version 46 (modified by mfreemon, 7 years ago) (diff)

--

THIS PAGE IS OBSOLETE. CURRENT INFORMATION CAN BE FOUND AT:
https://wiki.ncsa.illinois.edu/display/LSST/LSST+Build+and+Install+of+MySQL

MySQL installation

The current version of the MySQL server on LSST10 is 5.1.44.

To upgrade the version:

  • Stop the current MySQL server daemon (service mysqld stop)
  • Build and install from the newest source (in /home/dgehrig/mysql-5.1.44)
    configure --with-plugins=innobase
    make
    make install
    
  • upgrade the databases using mysql_upgrade -u root -p
  • restart the MySQL daemon using service mysqld start

The new binaries should now be at /usr/local/bin.

Add the non-standard mysql library location to the dynamic linker run time bindings.

  • Edit /etc/ld.so.conf.d/local.conf:
    # cat /etc/ld.so.conf.d/local.conf
    /usr/local/lib/mysql
    
  • Reload by executing /sbin/ldconfig.

Install MySQL-python from source as follows:

# cd /root
# tar xvfz MySQL-python-1.2.3c1.tar.gz
# cd MySQL-python-1.2.3c1
# python setup.py build
# python setup.py install

MySQL data files

MySQL currently locates its data files at /usr/data/mysql1/mysql_data.

~root/.my.cnf

The mysql root password is stored in root's home directory, file ~root/.my.cnf. Be sure this file is chmod 600. This allows us to avoid putting the password in various automated scripts (such as the backup script).

# cat /root/.my.cnf
[client]
password=xxxxxxxx

/etc/my.cnf.local

The configuration file is located at /etc/my.cnf.local (so that an rpm install or remove will not disturb it). A symlink for /etc/my.cnf points to this file.

[mysqld]
#datadir=/var/lib/mysql
datadir=/usr/data/mysql1/mysql_data
#socket=/var/lib/mysql/mysql.sock
socket=/tmp/mysql.sock
user=mysql
max_connections=500
open-files-limit=4096
general_log=1
log-output=TABLE
max_allowed_packet = 16M
key_buffer_size = 1GB

interactive_timeout=31449600

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
# Commented out.  See JIRA issue LSST-254
# old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.err
pid-file=/var/run/mysqld/mysqld.pid 

/etc/init.d/mysqld.local

The startup script, /etc/init.d/mysqld.local, contains a ".local" in order to avoid an rpm install or remove from disturbing it. A symlink for mysqld points to this file. Its contents are the same as the standard rpm-provided file, with the following modifications:

  • /usr/bin is changed to /usr/local/bin for these executables:
    • mysql_install_db
    • mysqld_safe
    • mysqladmin
    • my_print_defaults
  • The mysqld_safe option basedir is set to /usr/local

Starting and stoping MySQL

MySQL will automatically start on server boot for lsst10:

# grep mysqld /etc/rc.local
service mysqld start

To stop MySQL server:

service mysqld stop

To start MySQL server:

service mysqld start

Number of Connections

If you encounter errors in numerous Slices of the form:

File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/Slice.py", 
     line 440, in tryProcess stageObject.process()
File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/IOStage.py", 
     line 60, in process self._output()
File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/IOStage.py", 
     line 148, in _output logLoc)
File "/u/ncsa/rplante/stack/Linux64/daf_persistence/3.3.7/python/lsst/daf/persistence/
     persistenceLib.py", line 1349, in getPersistStorage 
return _persistenceLib.Persistence_getPersistStorage(*args)
LsstCppException: 0: lsst::pex::exceptions::RuntimeErrorException  
thrown at src/DbStorageImpl.cc:252 in void lsst::daf::persistence::DbStorageImpl::
error(const std::string&, bool) 0: Message: Unable to connect to MySQL database:  - * 
Too many connections

Increase the number of connections.

To check the current setting:

show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

To increase it, set:

max_connections = 500

to /etc/my.cnf under the [mysqld] section.

Then restart mysql.

If you want to avoid restarting the server, execute

SET GLOBAL max_connections = 500

inside mysql client (as superuser)

Open Files Limit

Increase the number of open files allowed for mysql so that the mysqldump backups will run successfully.

In the /etc/my.cnf.local file, add the following (1024 is the default):

open-files-limit=4096

This requires mysqld restart.

Ensure enough space for tmpdir

If there is not enough space in /tmp for mysql, then an alternate tmp directory can be set for mysql to use by specifying tmpdir in the my.cnf configuration file.

tmpdir=<the alternate tmp directory>

under [mysqld]

This requires mysqld restart.

Building the UDFs

source $LSST_HOME/loadLSST.csh
setup mysqlclient
cd /lsst/home/becla/qservWorker/udf
g++ -fPIC -shared -o qserv_udf.so -I$MYSQLCLIENT_DIR/include MySqlSpatialUdf.c
mysql -u adm_becla -p mysql < createMySqlUdfs_.sql

UTC on database backups

Due to a bug in the CSV engine, the timestamps on the general_log are incorrect. The option --skip-tz-utc is being used on the mysqldump command in the database backup script to circumvent this problem. The risk is that a backup/restore across a daylight saving time boundary will be screwed up. Hopefully, a future version of mysql will fix this problem (the current version as of this writing is 5.1.44).
http://bugs.mysql.com/bug.php?id=34612

Ignore the above (but retained for historical reference). We are migrating the two tables affected to another storage engine.