This product was packaged by S.Lebedeva 02.06.03 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 user 9. Adding new users 10.Automatic startup at boot time 11.Troubleshootings 1. Requirements: ---------------- 1. Mysql product installer should be in the products group or root. 2. If mysql installer is a root user then it has to be mysql groupname on this machine: ups tailor will change groupname to mysql to have an access to the privilege tables for mysql server. 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 for mysql databases should be created before you start ups mysql tailor. 2. 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 [-z /path-to-upsdb] 4. Configure mysql data area for mysql server: ups tailor mysql 5. Start mysql server: ups start mysql 6. Check if mysql server is running: ups ping mysql OR if mysql server owner has a username and password in mysql database: shell> setup mysql shell> mysqladmin ping -p Enter password: 7. Set password for mysql root user: ups rootpass mysql This password is for mysql's root user only not for mysql server owner, who configured mysql server in ups tailor. 8. Start mysql client on local node: ups client mysql 9. Start mysql client on remote node: > setup mysql > mysql -u user -h hostname [-D database] -P port -S socket -p password 10. 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 3. 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 UPS_THIS_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 name 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; 4. 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 = n1,n2,... 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. 5. 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 -h hostname -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. 6. 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 7. 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: shell> 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 8. 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; 9. Adding New Users to MySQL Server ----------------------------------- Full superuser who can connect to the server from anywhere: mysql> grant all privileges on *.* to @localhost -> identified by 'password' with grant options; mysql> grant all privileges on *.* to @"%" -> identified by 'password' with grant options; 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 about mysql commands, how to set up other databases, add users, etc see: $MYSQL_DIR/Docs/manual.[ps, html, txt ] 10. Restart Mysql Server at boot time --------------------------------------- To configure your system for automatic startup and shutdown mysql server there are should be 3 scripts in your system: 1. two scripts are supplied by UPS ups_startup and ups_shutdown. 2 A third script, called ups, must be supplied by user and placed in the init.d directory where it will be executed at boot time. UPS script is used to configure your machine for automatic startup/shutdown and to call the first two scripts. When UPS gets installed and configured on a system, ups_startup and ups_shutdown get copied into separate directories under $PRODUCTS/.upsfiles, as follows: $PRODUCTS/.upsfiles/startup/ups_startup $PRODUCTS/.upsfiles/shutdown/ups_shutdown It is recommended to use the sample ups script given below, with no changes except the database path (defined by upsdb). It works for all supported UNIX flavors. The UPS Script. -------------- 1. The script must be called ups. The location of the init.d directory in which it must reside is OS-specific, as follows: Operating System Directory ------------------------------------------- IRIX, SunOS /etc/init.d Linux /etc/rc.d/init.d OSF11 /sbin/init.d 2. Set the ups file ownership and permissions properly by running: % chown 0 ups % chgrp 0 ups 3. The ups Script Contents #!/bin/sh upsdb=/local/ups/db state=$1 case $state in 'start') start=$upsdb/.upsfiles/startup/ups_startup (while [ ! -f $start ]; do sleep 5; done; $start) & ;; 'stop') $upsdb/.upsfiles/shutdown/ups_shutdown ;; 'config') case $0 in /*) initd=$0;; *) initd=`pwd`/$0;; esac sfile=`echo $initd | sed -e 's;init.d/;rc3.d/S99;'` kfile=`echo $initd | sed -e 's;init.d/;rc0.d/K01;'` ln -s $initd $sfile ln -s $initd $kfile sfile=`echo $initd | sed -e 's;init.d/;rc5.d/S99;'` kfile=`echo $initd | sed -e 's;init.d/;rc6.d/K01;'` ln -s $initd $sfile ln -s $initd $kfile ;; *) echo "usage: $0 {start|stop|config}" ;; esac The automatic startup/shutdown feature is enabled if and only if these three conditions are met: the ups_startup and ups_shutdown files exist the appropriate system startup files on your machine are configured: (ups scripts). an appropriate control file exists Control files: --------------- In the directory $PRODUCTS/.upsfiles/startup directory you may find one or more files with the name .products. Edit the file appropriate to that node, or create the file if it doesn't exist. Add a line of the following format to the appropriate file(s): /bin/su - -c "ups start [] []" Example:fnod.products file looks like: echo "ups starting juke" ups start -z /local/ups/db juke echo "ups starting windriver" ups start windriver There is might be following instance for mysql as: echo "ups starting mysql" ups start mysql ups start mysql -O identifier In the directory $PRODUCTS/.upsfiles/shutdown you will find files named similarly to the startup control files. Edit one or more of them. Add a line of the following format to the appropriate file(s): /bin/su - -c "ups stop [] []" Make sure the permissions of all the .products and .products files are set to 744. A line for a particular product can exist in more than one file. The system runs all these files at boot time and uses only the first ups start command it finds for a product. More details in: Chapter 15: Automatic UPS Product Startup and Shutdown http://www.fnal.gov/docs/products/ups/ReferenceManual/html/autostart.html 11. 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. Solution: 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 again. 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.