Changes between Version 46 and Version 47 of db/obsolete/MySQLServerConfiguration


Ignore:
Timestamp:
12/19/2012 12:54:09 AM (6 years ago)
Author:
jbecla
Comment:

removed contents, (the page is obsolete)

Legend:

Unmodified
Added
Removed
Modified
  • db/obsolete/MySQLServerConfiguration

    v46 v47  
    11'''THIS PAGE IS OBSOLETE.'''   CURRENT INFORMATION CAN BE FOUND AT:[[BR]] 
    22https://wiki.ncsa.illinois.edu/display/LSST/LSST+Build+and+Install+of+MySQL 
    3  
    4 === MySQL installation  === 
    5  
    6 The current version of the MySQL server on LSST10 is 5.1.44. 
    7  
    8 To upgrade the version: 
    9  * Stop the current MySQL server daemon (`service mysqld stop`) 
    10  * Build and install from the newest source (in {{{/home/dgehrig/mysql-5.1.44}}}) 
    11 {{{ 
    12 configure --with-plugins=innobase 
    13 make 
    14 make install 
    15 }}} 
    16  * upgrade the databases using `mysql_upgrade -u root -p` 
    17  * restart the MySQL daemon using `service mysqld start` 
    18  
    19 The new binaries should now be at `/usr/local/bin`. 
    20  
    21 Add the non-standard mysql library location to the dynamic linker run time bindings. 
    22  * Edit `/etc/ld.so.conf.d/local.conf`: 
    23 {{{ 
    24 # cat /etc/ld.so.conf.d/local.conf 
    25 /usr/local/lib/mysql 
    26 }}} 
    27  * Reload by executing `/sbin/ldconfig`. 
    28  
    29 Install MySQL-python from source as follows: 
    30 {{{ 
    31 # cd /root 
    32 # tar xvfz MySQL-python-1.2.3c1.tar.gz 
    33 # cd MySQL-python-1.2.3c1 
    34 # python setup.py build 
    35 # python setup.py install 
    36 }}} 
    37  
    38 === MySQL data files === 
    39  
    40 MySQL currently locates its data files at `/usr/data/mysql1/mysql_data`. 
    41  
    42 == `~root/.my.cnf` == 
    43  
    44 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). 
    45 {{{ 
    46 # cat /root/.my.cnf 
    47 [client] 
    48 password=xxxxxxxx 
    49 }}} 
    50  
    51 == `/etc/my.cnf.local` == 
    52  
    53 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. 
    54 {{{ 
    55 [mysqld] 
    56 #datadir=/var/lib/mysql 
    57 datadir=/usr/data/mysql1/mysql_data 
    58 #socket=/var/lib/mysql/mysql.sock 
    59 socket=/tmp/mysql.sock 
    60 user=mysql 
    61 max_connections=500 
    62 open-files-limit=4096 
    63 general_log=1 
    64 log-output=TABLE 
    65 max_allowed_packet = 16M 
    66 key_buffer_size = 1GB 
    67  
    68 interactive_timeout=31449600 
    69  
    70 # Default to using old password format for compatibility with mysql 3.x 
    71 # clients (those using the mysqlclient10 compatibility package). 
    72 # Commented out.  See JIRA issue LSST-254 
    73 # old_passwords=1 
    74  
    75 [mysqld_safe] 
    76 log-error=/var/log/mysqld.err 
    77 pid-file=/var/run/mysqld/mysqld.pid  
    78 }}} 
    79  
    80 == `/etc/init.d/mysqld.local` == 
    81  
    82 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: 
    83  * `/usr/bin` is changed to `/usr/local/bin` for these executables: 
    84   * `mysql_install_db` 
    85   * `mysqld_safe` 
    86   * `mysqladmin` 
    87   * `my_print_defaults` 
    88  * The mysqld_safe option `basedir` is set to `/usr/local` 
    89  
    90 === Starting and stoping MySQL === 
    91  
    92 MySQL will automatically start on server boot for lsst10: 
    93 {{{ 
    94 # grep mysqld /etc/rc.local 
    95 service mysqld start 
    96 }}} 
    97  
    98 To stop MySQL server: 
    99 {{{ 
    100 service mysqld stop 
    101 }}} 
    102  
    103 To start MySQL server: 
    104 {{{ 
    105 service mysqld start 
    106 }}} 
    107  
    108 === Number of Connections === 
    109  
    110 If you encounter errors in numerous Slices of the form: 
    111 {{{ 
    112 File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/Slice.py",  
    113      line 440, in tryProcess stageObject.process() 
    114 File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/IOStage.py",  
    115      line 60, in process self._output() 
    116 File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/IOStage.py",  
    117      line 148, in _output logLoc) 
    118 File "/u/ncsa/rplante/stack/Linux64/daf_persistence/3.3.7/python/lsst/daf/persistence/ 
    119      persistenceLib.py", line 1349, in getPersistStorage  
    120 return _persistenceLib.Persistence_getPersistStorage(*args) 
    121 LsstCppException: 0: lsst::pex::exceptions::RuntimeErrorException   
    122 thrown at src/DbStorageImpl.cc:252 in void lsst::daf::persistence::DbStorageImpl:: 
    123 error(const std::string&, bool) 0: Message: Unable to connect to MySQL database:  - *  
    124 Too many connections 
    125 }}} 
    126  
    127 Increase the number of connections. 
    128  
    129 To check the current setting: 
    130 {{{ 
    131 show variables like 'max_connections'; 
    132 +-----------------+-------+ 
    133 | Variable_name   | Value | 
    134 +-----------------+-------+ 
    135 | max_connections | 100   | 
    136 +-----------------+-------+ 
    137 }}} 
    138  
    139 To increase it, set: 
    140 {{{ 
    141 max_connections = 500 
    142 }}} 
    143  
    144 to `/etc/my.cnf` under the `[mysqld]` section. 
    145  
    146 Then restart mysql. 
    147  
    148 If you want to avoid restarting the server, execute 
    149 {{{ 
    150 SET GLOBAL max_connections = 500 
    151 }}} 
    152  
    153 inside mysql client (as superuser) 
    154  
    155 === Open Files Limit === 
    156  
    157 Increase the number of open files allowed for mysql so that the mysqldump backups will run successfully. 
    158  
    159 In the /etc/my.cnf.local file, add the following (1024 is the default): 
    160 {{{ 
    161 open-files-limit=4096 
    162 }}} 
    163  
    164 This requires mysqld restart. 
    165  
    166 === Ensure enough space for tmpdir  === 
    167  
    168 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. 
    169 {{{ 
    170 tmpdir=<the alternate tmp directory> 
    171 }}} 
    172 under `[mysqld]` 
    173  
    174 This requires mysqld restart. 
    175  
    176 === Building the UDFs === 
    177  
    178 {{{ 
    179 source $LSST_HOME/loadLSST.csh 
    180 setup mysqlclient 
    181 cd /lsst/home/becla/qservWorker/udf 
    182 g++ -fPIC -shared -o qserv_udf.so -I$MYSQLCLIENT_DIR/include MySqlSpatialUdf.c 
    183 mysql -u adm_becla -p mysql < createMySqlUdfs_.sql 
    184 }}} 
    185  
    186 === UTC on database backups === 
    187  
    188 >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).[[BR]] 
    189 >http://bugs.mysql.com/bug.php?id=34612 
    190  
    191 Ignore the above (but retained for historical reference).  We are migrating the two tables affected to another storage engine.