MySQL product Installation

CONTENTS:
1. Requirements
2. Setup instructions to install the product.
3. Configure Mysql Server 
4. Configure another Mysql Server on the same machine 
5. Configure remote Mysql Cilent
6. Start and stop Mysql server
7. Start Mysql client 
8. Setup password for root
9. Adding new users
10.Automatic startup at boot time
11.Troubleshootings

Requirements:
1. Mysql product installer should be in the products groupname or root.
2. Group ownership for mysql data area should belong to mysql group (gid 9531).  
   Add mysql group to your systems /etc/group file or NIS group map, if it does not exist. 
   If you are setting up multiple databases to be managed by different UNIX acccounts,
   add these account names to the group entry. 
3. If you are going to install mysql product on AFS space be sure you have the 
   rights to work with AFS Read-only system! It is not recommended to create 
   mysql databases on AFS space! 
4. The base installation with an empty mysql databases requires 20MB.
5. Directory you are going to use to install mysql databases should be created 
   before you start ups mysql tailor.

Setup instructions to install the product:
1. Execute: newgrp products

2. Set $PRODUCTS to point to specific ups database on your machine.

3. Install mysql product in ups database:
     upd install mysql version -f Flavor

4. Configure mysql data area for mysql server:  ups tailor mysql    

5. Start mysql server: ups start mysql mysql>use mysqlmysql> select Host, user, password from user;
+-----------------+------+-------------------------------------------+
| Host | user | password |
+-----------------+------+-------------------------------------------+
| localhost | root | *C7096394471B2408C6EEC303106F9167CED6CFEA |
| odets2.fnal.gov | root | *C7096394471B2408C6EEC303106F9167CED6CFEA |
| odets2.fnal.gov | | |
| localhost | | |
+-----------------+------+-------------------------------------------+
to fix the blank user and pwd against hacking:IMMEDIATELY DO INSIDE MYSQL:mysql> update mysql.user set user='jtrumbo', password = PASSWORD('svetlana_julie') where 
-> user = ' ';mysql> update mysql.user set password=PASSWORD('newpwd') where user ='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> FLUSH PRIVILEGES;

mysql> select Host, user, password from user;
+-----------------+---------+-------------------------------------------+
| Host | user | password |
+-----------------+---------+-------------------------------------------+
| localhost | root | *C7096394471B2408C6EEC303106F9167CED6CFEA |
| odets2.fnal.gov | root | *C7096394471B2408C6EEC303106F9167CED6CFEA |
| odets2.fnal.gov | jtrumbo | *C7096394471B2408C6EEC303106F9167CED6CFEA |
| localhost | jtrumbo | *C7096394471B2408C6EEC303106F9167CED6CFEA |
+-----------------+---------+-------------------------------------------+
4 rows in set (0.00 sec)
 
6. Check if mysql server is running:  ups ping mysql
 
7. Set password for mysql root user: ups rootpass mysql

8. Start mysql client on local node: ups client mysql 

9. Shutdown mysql server:  ups stop mysql

Additional ups functions:
1. ups setup mysql 	- setup environment variables for mysql product.
2  ups variables mysql - check run-time server variables
3. ups status mysql    - short status message from mysql server

CONFIGURE MYSQL SERVER: run %ups tailor mysql
 
1. "The default mysql data directory = /usr/local/mysql"
   "Is this the place to configure mysql data area (y/n)? " n
   "Please, enter valid path:" 

2.  The default mysql temp directory = /tmp
    You can update tmpdir and other mysql server options in mysql configure file:
    /datadir/my.cnf before you start mysql server.

    NOTE: my.cnf file is config file with set of options for server and client user 
    can edit. If you would need to change port number or socket name for some reason, 
    then you have to update following files:
 
    In user datadir: 
        -  mysql.port (or/and mysql.socket)
        -  my.cnf in [client] and [mysqld] sections 
    In /path-to-ups-db/mysql/config directory:
        -  hostname.mysql-servers 
 
3.  Default username for mysqld = mysql.
    You can set another username or press Enter to accept default name:
   
    NOTE: this will be a Unix username to start mysql server. Later you can add 
    other users and set specific privileges and passwords for mysql server and client.  

4.  There are small,medium,large or huge cnf files MYSQL_DIR/share/mysql directory.
    Which one you would like to use (s/m/l/h)?
   
    NOTE: There are different levels of files depending on how big a machine you are
    using: from my-small.cnf to my-huge.cnf in MYSQL_DIR/share/mysql directory. 
    For most people, my-medium.cnf works well.
    Anyway, you have to pick one that fits better to your machine architecture and 
    program will create my.cnf file in your datadir ready to use.  

5.  print "You can on/off skip-innodb option in my.cnf file before starting mysqld. \n ";
    print "Disable InnoDB tables now (y/n)? " 

    NOTE: skip-innodb option will be added in my.cnf file to disable InnoDB tables. 
    To use InnoDB again - just comment skip-innodb option before you start the server 
    and configure datafiles for InnoDB tables. In my.cnf default configuration for 
    InnoDB tables is provided. 
 
6. Mysql_install_db will be executed creating mysql grand tables.

        Installing all prepared tables.
        ...............................
 	Mysql server successfuly configured.

    NOTE: Group ownership for files in configured data area should belong to mysql. 
    GID reserved for mysql is 9531. If mysql group does not exist on user's machine, 
    there will be message with request to add an entry for a mysql group to the user's 
    system /etc/group file or NIS group map.
    If there are going to be multiple databases to be managed by different 
    UNIX acccounts, these account names should be added to the group entry also.

7.  The user-defined datadir will be saved in UPS_THIS_DB/mysql/config/hostname. file.
    List configured mysql servers for specific machine will be created in
    UPS_THIS_DB/mysql/config/hostname.mysql-servers.If for some reason, you would 
    like to remove specific server from your machine, then 
     - shutdown this server; 
     - edit hostname.mysql-servers file in UPS_PROD_DB/mysql/config directory 
       removing all records related to this server;
     - remove your mysql datadir; 


CONFIGURE ANOTHER SERVER ON THE SAME MACHINE:
 
       run %ups tailor mysql 

       	Mysql server with server_id = 1,.. was already configured on this machine.
	Would you like to configure next server (y,n)? y

   NOTE: Program will check if there is attempt to use the same datatir: 

	You are trying to configure another mysql server with with the same datadir
	that was already configured for server_id = x
	Please, enter another data directory or press enter to quit: 

        Enter an idenifier to start this server as: ups start mysql -O "identifier": 

   NOTE: You can use any name to set unique tocken for each mysql server on your 
         machine. 
      
        Installing all prepared tables.
        ...............................
 	Mysql server successfuly configured.

   There are following ups function you can use:
   To start/stop mysql server      : ups start/stop mysql -O "identifier"
   To set root password            : ups rootpass mysql -O "identifier"
   To start mysql client           : ups client mysql -O "identifier"
   To see run-time server variables: ups variables mysql -O "identifier"
   To see short status message     : ups status mysql -O "identifier"
   To ping mysql server            : ups ping mysql -O "identifier"

   NOTE: ups tailor mysql can be repeated in case any of problem. 


CONFIGURE REMOTE MYSQL CLIENT:
 1 Install mysql product in ups database:
     upd install mysql Version -f Flavor

2. Setup mysql product environment:
    setup mysql 

3. Start mysql client: 
    shell>mysql -u user -D database -P port -S socket -p password

   Port number and socket name for the specific server should be provided by 
   mysql server sysadmin from corresponding my.cnf file on the server node

       
START and SHUTDOWN MYSQL SERVER:
   ups start mysql 
OR using mysql command:
setup mysql 
shell> mysqld_safe --defaults-name=/datadir/my.cnf --user=username &

There is server log file datadir/hostname.err you can use to check the server activity. 

To shutdown server:
ups stop mysql
OR 
using mysql command:
setup mysql 
shell> mysqladmin -u user -h host -P port -S socket -p password shutdown

START MYSQL CLIENT: 
To start mysql client on local node:
ups client mysql
OR 
using mysql command:
setup mysql 
shell> mysql  -D database -u user -h hostname -p password  

To start remote mysql client: 
setup mysql 
shell> mysql  -D database -u user -h hostname -p password -P port -S socket

When mysql clients starts there will be following messages:
        
  Welcome to the MySQL monitor.  Commands end with ; or \g.     
  Your MySQL connection id is 91 to server version: 3.23.51     
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 

To exit from mysql:
mysql> \q
Bye

 SETUP PASSWORD FOR root USER:
There are some options to change password for root user:
1. setup mysql
  $MYSQL_DIR/bin/mysqladmin -u root -h localhost password 'new-password'
  $MYSQL_DIR/bin/mysqladmin -u root -h 'hostname' password 'new-password

OR in mysql:
 
2. mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
   mysql> SET PASSWORD FOR root@%=PASSWORD('new_password');

3.Directly update user table in mysql database:
mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->     WHERE user='root';
mysql> FLUSH PRIVILEGES;


ADDING NEW USERS TO MYSQL
Full superuser who can connect to the server from anywhere:
----------------------------------------------------------
mysql> grant all privileges on *.* to @localhost
    ->     identified by 'password' with grant option;

mysql> grant all privileges on *.* to @"%"      
    -> identified by 'password' with grant option;

Directly update user table in mysql database:
------------------------------------------------
mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->     WHERE user='root';
mysql> FLUSH PRIVILEGES;


To get more information how to set up other databases, add users, 
etc see $MYSQL_DIR/Docs/manual.[ps, html, txt ]

 RESTART MYSQL SERVER AT BOOT TIME
To configure your system for automatic startup and shutdown mysql server 
add following commands to your system startup/shutdown scripts:

  1. To start mysql server with username "mysql": 
     su mysql -c "setup mysql; $MYSQL_DIR/bin/mysql.server start"  

  2  To stop mysql server with username "mysql": 
     su mysql -c "setup mysql; $MYSQL_DIR/bin/mysql.server stop"  

Mysql script mysql.server is designed for automatic startup/shutdown mysql server at boot time. 

 TROUBLESHOOTING:
1. Mysql product directory with mysql executables is lost and not recoverable, 
   but mysql data directory with mysql privileges tables and other mysql 
   databasea is available. 
   Solution: 
    - install mysql product
    - run mysql tailor with any fake datadir, later you can delete it. 
    - update 2 files in $THIS_UPS_DB/mysql/config directory 'machine'. and 
      'machine'.mysql-servers replacing fake dir name to your real datadir.
    - start mysql server as ups start mysql, it will start with  your real mysql datadir. 

2. ups start mysql  
  ERROR1:  Can't start server: Bind on TCP/IP port: Address already in use
           Do you already have another mysqld server running on port: 3306 ?

   It means another server is running on this machine with the same port number
   or you did not stop the server and try to start it again.
 
   Solution: 
   1. Check if your server is running, then no action. 
      You can stop server and restart it again. 
            OR 
   2. Change port number for your server updating following files:
    - in datadir mysql.port, my.cnf [client] and [mysqld] sections;
    - in $THIS_UPS_DB/mysql/config 'machine'.mysql-servers port=xxxx;
      Then start mysql server with new port number. 

  ERROR2: Can't start server : Bind on unix socket: Operation not permitted
          Do you already have another mysqld server running on socket: 
          /afs/fnal.gov/files/home/room3/..../mysql.sock?    Aborting.
   Mysql socket name cannot be configured on afs space!!!

   Solution: It is not recommended to use mysql datadir on afs space. 
   But if you still plan to use it for mysql datadir, then after ups tailor you have 
   to change location for mysql socket at anywhere else from afs. 

      To change path for mysql socket update following files:
         - in datadir mysql.socket, my.cnf [client] and [mysqld] sections;
        - in $THIS_UPS_DB/mysql/config 'machine'.mysql-servers socket=xxxx;

      Then start mysql server again. 

Mail comments to:css-dsg@fnal.gov
Last Modified  September 5, 2003