Installing MySQL under RHEL

See also A clean install or upgrade under Solaris.

See http://www.howtoforge.com/quick-n-easy-lamp-server-centos-rhel.

1. Install mysql:

   yum install mysql mysql-server mysql-devel
   /etc/init.d/mysqld start
   

2. Secure the server. Starting the server prints out a message suggesting setting the password. The root mysql password is in ~mysql/.my.cnf:

   [client]
   user=root
   password=XXXXXX
   

   Secure the database:

   /usr/bin/mysql_secure_installation
   

3. Set up access for the apache user. Note that /usr/sfq/bin/mysql is not the binary to run, instead run /opt/mysql/mysql/bin/mysql
   The xxxxxxx password is the same as above, which is in ~mysql/.my.cnf
   The yyyyyyy password is found in ~apache/.mysqlpwd
   The zzzzzzz password is found in ~apache/conf.php
   The aaaaaaa password is found in ~apache/conf.php

   root@moog www]# /usr/bin/mysql --user=root -p mysql
   /usr/bin/mysql --user=root -p mysql
   Enter password:
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Welcome to the MySQL monitor.  Commands end with ; or g.
   Your MySQL connection id is 10
   Server version: 5.1.61 Source distribution
   
   Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
   
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'apache'@'localhost'
   GRANT ALL PRIVILEGES ON *.* TO 'apache'@'localhost'
       -> IDENTIFIED BY 'yyyyyyy' WITH GRANT OPTION;
   IDENTIFIED BY 'yyyyyy' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'apache'@'%'
   GRANT ALL PRIVILEGES ON *.* TO 'apache'@'%'
       -> IDENTIFIED BY 'yyyyyyy' WITH GRANT OPTION;
   IDENTIFIED BY 'yyyyyyy' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.00 sec)
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'dbreader'@'localhost'
       -> IDENTIFIED BY 'zzzzzzzz' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'dbapplyuser'@'localhost'
       -> IDENTIFIED BY 'aaaaaaaa' WITH GRANT OPTION;
   
   mysql> FLUSH PRIVILEGES 
   FLUSH PRIVILEGES
       -> ;
   ;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> exit
   exit
   Bye
   [root@moog www]# 
   

4. Verify that your changes are in the db by running

   /usr/bin/mysqldump -u root -p mysql
   

   The output will be voluminous, look for apache in the output.
5. Set up auto start by editing /etc/my.cnf:

   # Configuration file for MySQL
   [client]
   
   [mysqld]
   datadir=/home/www/mysql
   log=/home/www/mysql/mysql_log
   log-update=/home/www/mysql/mysql_update_log
   socket=/var/lib/mysql/mysql.sock
   user=mysql
   symbolic-links=0
   
   [mysqldump]
   
   #[mysqld]
   #datadir=/var/lib/mysql
   #socket=/var/lib/mysql/mysql.sock
   #user=mysql
   # Disabling symbolic-links is recommended to prevent assorted security risks
   #symbolic-links=0
   
   [mysqld_safe]
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid
   

   At this point, do:

   mkdir -p /home/www/mysql
   chown mysql /home/www/mysql
   

6. Start up mysql:

   /etc/init.d/mysqld start
   

7. Verify that you can connect:

   [root@moog www]# mysql --user=root -p mysql
   mysql --user=root -p mysql
   Enter password:
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Welcome to the MySQL monitor.  Commands end with ; or g.
   Your MySQL connection id is 14
   Server version: 5.1.61 Source distribution
   
   Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
   
   mysql> exit
   exit
   Bye
   [root@moog www]# 
   

8. Go back to the Apache page and install PhP, but be sure to come back and Install PhPMyAdmin.
9. How do I copy the database to a new machine

A clean install or upgrade under Solaris

1. If you are doing a clean install, then you may want to use the Solaris packages, but you can also use the tar files. I think the Solaris packages updated /etc/my.cnf.
2. If you are doing an upgrade, then use the tar files so that you can have multiple installations so that it is easy to fall back if there are problems.

1. Clean install: Set up a mysql user and group.
2. Clean install: remove vestiges of the old install. NOTE THAT THIS WILL REMOVE YOUR DATABASE

   pkgrm mysql
   rm -rf /opt/mysql /var/lib/mysql
   mv /etc/my.cnf /etc/my.cnf.old
   

3. Upgrade: BACK UP THE DATABASE

   su - mysql
   mkdir /home/www/mysqlarchive/mysqlupgrade
   cd /home/www/mysqlarchive/mysqlupgrade
   /home/www/php/util/mysqldumpall
   exit
   
   cp /etc/my.cnf /etc/my.cnf.old
   
   

   Note that /etc/my.cnf will control the directories in which the mysql_install_db command works.

1. Download the source distribution
2. Need Cmake

1. Under Solaris, there are two methods, pkgadd and tar. It is probably better to use tar because you can have multiple installations of mysql.

   pkgadd method: For Solaris, download the 32 bit package from http://www.mysql.com. I downloaded mysql-5.1.53-solaris10-sparc.tar.gz.

   gunzip mysql-5.0.67-solaris10-sparc.pkg.gz
   pkgadd -d mysql-5.0.67-solaris10-sparc.pkg.gz
   

   This will create /opt/mysql

   tar method: For Solaris, download the 32 bit .tar.gz from http://www.mysql.com. I downloaded mysql-5.1.53-solaris10-sparc.tar.gz.

   cd /opt/mysql
   gtar -zxf /home/tools/downloads/mysql-5.1.53-solaris10-sparc.tar.gz
   rm mysql
   ln -s mysql-5.1.53 mysql
   

   This will create /opt/mysql See /opt/mysql/mysql/INSTALL_BINARY for the steps necessary:

   cd /opt/mysql/mysql 
   chown -R mysql .
   chgrp -R mysql .
   

2. Initialize or Upgrade.

   If the next steps fail, it could be that /etc/my.cnf or ~mysql/.my.cnf are causing problems. You might want to temporarily move /etc/my.cnf. Initial Install:Try running:

   /opt/mysql/mysql/scripts/mysql_install_db --user=mysql --ldata=/home/www/mysql --datadir=/home/www/mysql --force
   chown -R root .                             
   chown -R mysql data
   /opt/mysql/mysql/bin/mysqld_safe --user=mysql --datadir=/home/www/mysql  --verbose &
   

Or Upgrade: Upgrade the server

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
su - mysql
cd /opt/mysql/mysql
bin/mysql_upgrade

Try out the website, bring it down and back up

/etc/init.d/gsrc stop
/etc/init.d/gsrc start

3. Once the mysqld_safe is running, set the password. The ~mysql/.my.cnf should look like

   [client]
   user=root
   password=xxxxxxx
   

   /opt/mysql/mysql/bin/mysqladmin -u root password 'xxxxxxx'
   /opt/mysql/mysql/bin/mysqladmin -u root -h carson.EECS.Berkeley.EDU password 'xxxxxxx'
   

4. Set up access for the apache user.
   Note that /usr/sfq/bin/mysql is not the binary to run, instead run /opt/mysql/mysql/bin/mysql
   The xxxxxxx password is the same as above, which is in ~mysql/.my.cnf
   The yyyyyyy password is found in ~apache/.mysqlpwd

   carson.EECS.Berkeley.EDU:root: %C2> /opt/mysql/mysql/bin/mysql --user=root -p mysql
   Enter password: xxxxxx
   
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Welcome to the MySQL monitor.  Commands end with ; or g.
   Your MySQL connection id is 10
   Server version: 5.0.45 MySQL Community Server (GPL)
   
   Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
   
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'apache'@'localhost'
       -> IDENTIFIED BY 'yyyyyy' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'apache'@'%'
       -> IDENTIFIED BY 'yyyyyy' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> FLUSH PRIVILEGES                                                         
       -> ;                                                                        
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> exit
   Bye
   carson.EECS.Berkeley.EDU:root: %C2> 
   

5. Verify that your changes are in the db by running

   /usr/local/mysql/bin/mysqldump -u root -p mysql
   

   The output will be voluminous, look for apache in the output.
6. Set up auto start by creating /etc/my.cnf:

   # Configuration file for MySQL
   [client]
   
   [mysqld]
   datadir=/home/www/mysql
   log=/home/www/mysql/mysql_log
   log-update=/home/www/mysql/mysql_update_log
   
   [mysqldump]
   

7. Then run /etc/init.d/mysql start
8. Verify that you can connect:

   carson.EECS.Berkeley.EDU:root: %C2> /opt/mysql/mysql/bin/mysql --user=root -p mysql
   Enter password:  yyyyyyy                                                     
   
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Welcome to the MySQL monitor.  Commands end with ; or g.
   Your MySQL connection id is 2
   Server version: 5.0.45-log MySQL Community Server (GPL)
   
   Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
   
   mysql> exit                                                                     
   Bye
   carson.EECS.Berkeley.EDU:root: %C2> 
   

9. Install phpMyAdmin.
10. See How do I copy the database from another machine

Notes from October, 2007

Select only database user

In phpMyAdmin, from the home page, go to "Privileges" to add the user.
Then edit /home/apache/conf.php.
Then cvs-update php/include/sql.inc.php, which uses the select-only user for all select-only functions.

$db_select_user = 'dbreader';
$db_select_pass = 'Thepassword';

mysqli_connect() not found

Solution: edit /usr/local/lib/php.ini and uncomment

mysql won't start

070509 19:03:27 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

These are allenh's notes from upgrading MYSQL in June, 2002. Download MySQL's Solaris 2.8 binary package and simply copy the whole directory structure to be /usr/local/mysql-3.23.51.

Rename /usr/local/mysql/data to "data-" and make /usr/local/mysql/data a symbolic link to /home/www/mysql.

Mkdir /usr/local/mysql/lib/mysql, and copy /usr/local/mysql-3.22.23/lib/mysql/libmysqlclient.so.6 there. (I think this file will not be required by the upgraded Apache.)

Bring the website down as root:
$ /etc/init.d/gsrc stop

Create a symbolic link named /usr/local/mysql that points to /usr/local/mysql-3.23.51.

Bring the website up as root:
$ /etc/init.d/gsrc start

Check the website functionality. Should be unchanged.

MySQL is an open-source SQL database that consistently receives high recommendations for its reliability, performance, and of course, price. It is the most commonly-used database with PHP-enabled Web servers (like ours), which makes writing and porting PHP code simpler.

• http://www.mysql.com
• Local copy of documentation

Installation Slightly updated in June, 2005. Solaris 10 binaries of 3.23 are not available, so we rebuild from source

1. Download from http://www.mysql.com or http://downloads.mysql.com/archives.php
2. Unpack MySQL in the directory /home/tools/mysql-3.23.58.

3. Create the directory /usr/local/mysql-3.23.58
   Create a link from /usr/local/mysql to /usr/local/mysql-3.23.58
   If /usr/local/mysql exists, then the server is probably running, so shut it down with:

       /usr/local/mysql/bin/mysqladmin -h localhost -u root -p shutdown
   

   See MySQL passwords FAQ for information about passwords.

   Create a mysql user and group. /etc/passwd:

   mysql:x:2813:104:MySQL user at www.gigascale.org:/home/mysql:/bin/sh
   

   Update /etc/shadow:

   mysql:xxxxxxxxx:12299::::::
   

   Update /etc/group:

   mysql::104:
   

   Create the dot files for the mysql user

   mkdir /home/mysql
   cp /usr/cluster/lib/userhome/.??* /home/mysql
   chown -R mysql /home/mysql
   

4. Create the directory /home/www/mysql if it doesn't exist. (/home/www/ is where the Apache data files are kept. If it doesn't exist, create it, it will be set up properly in the Apache installation.)

       cd /home/www
       mkdir mysql
       chown mysql mysql
       chgrp mysql mysql
       chmod 700 mysql
   

   From now on, you should not need to be root to install.

5. Configure and build MySQL.

   See the Solaris Notes section of the mysql manual

   You should be able to complete this installation if you are in the permissions group staff.

       cd /home/tools/mysql-3.23.xxx
       CC=gcc CFLAGS="-O3" 
       CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" 
       ./configure 
           --prefix=/usr/local/mysql-3.23.58 
           --localstatedir=/home/www/mysql 
   	 --with-low-memory 
            --enable-assembler
       make
       make install
   

   Note: if you get an error complaining about /usr/include/term.h, comment out the line

       #define HAVE_TERM_H 1
   

   from config.h and try again.

6. Create the grant tables. You may need to be in the webmastr permissions group to do this.

       /usr/local/mysql/bin/mysql_install_db
   

7. Start the server. You may need to be in the webmastr permissions group to do this.

       /usr/local/mysql/bin/safe_mysqld &
   

   Change the password. See MySQL Password FAQ for the password.

   /usr/local/mysql/bin/mysqladmin -u root  password "xxxx"
   /usr/local/mysql-3.23.58/bin/mysqladmin -u root -h gigascale.EECS.Berkeley.EDU  password "xxxx"
   

8. Add the apache user to mysql. Note that the yyyyyyy password is found in ~apache/.mysqlpwd

   andrews.EECS.Berkeley.EDU:root: /home/apache> mysql --user=root -p mysql
   Enter password: XXXX
   
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Welcome to the MySQL monitor.  Commands end with ; or g.
   Your MySQL connection id is 2 to server version: 3.23.58-log
   
   Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
   
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'apache'@'localhost'
       -> IDENTIFIED BY 'yyyyyyy' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.01 sec)
   
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'apache'@'%'
       -> IDENTIFIED BY 'yyyyyyy' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.01 sec)
   
   mysql> exit
   Bye
   andrews.EECS.Berkeley.EDU:root: /home/apache>
   

   To verify that the apache user has been added run:

   /usr/local/mysql/bin/mysqldump -u root -p mysql
   

9. Under Solaris, we automatically start up MySQL at boot time:
   1. /etc/rc3.d/S51mysql.server is a link to /etc/init.d/mysql.server. S51mysql.server gets run when Solaris goes to Init level 3.
   2. /etc/init.d/mysql.server reads in /etc/my.cnf which as of 6/05 contained the following:

      # Configuration file for MySQL
      [client]
      
      [mysqld]
      log=/home/www/mysql/mysql_log
      log-update=/home/www/mysql/mysql_update_log
      
      [mysqldump]
      
      

   3. and then runs /usr/local/mysql/bin/safe_mysqld, which will restart mysqld if it exits.

10. To run the test suites for MySQL you need to have the Perl database modules installed. This is probably a good idea anyway, as it means that we can write or modify Perl cgi-bin scripts to access MySQL (as well as doig it through PHP). Follow the Perl instructions to install the needed Perl modules.

    You may need to be root to do this, unless your system is configured to give write permission to staff in /usr/local/bin, /usr/local/lib/perl5, and /usr/local/man. See How do I add a package to Perl

    Note that to build DBI, I had to

    perl -MCPAN -e shell
       install DBI
       install DBD
    cd /export/home1/root/.cpan/build/DBI-1.48
    make OPTIMIZE=-O3 CCCDLFLAGS=-fPIC CCFLAGS="-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_TS_ERRNO"
    make install
    cd ../DBD-mysql-2.9007
    make OPTIMIZE=-O3 CCCDLFLAGS=-fPIC CCFLAGS="-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_TS_ERRNO"
    make install
    

    cd /export/home/tools/mysql-3.23.58/sql-bench
    ./run-all-tests -user root -pass "xxxxx"
    

11. Go to the Post-installation setup and testing page to make sure that the server is working OK. (Start at item 3.) For the moment, you may need to specify the option -u root to the SQL commands, otherwise you will not be permitted to access the database.
12. We use phpMyAdmin to administer the MySQL via http://www.gigascale.org/mysql.
    It gets installed in ~www/php/mysql.
    The RewriteRules in /usr/local/apache/conf/rewrite.conf rewrite urls like http://www.gigascale.org/mysql to /home/www/php/mysql/. Note that there are several rewrite rules to handle the various file types.

    Below are the instructions on how to update the phpMyAdmin

    1. On Markov, the backup server, download phpMyAdmin from http://phpwizard.net/phpMyAdmin/
    2. Check for files that have been modified since the last release:

        cd ~www/php/mysql
        cvs update
        cvs log
        

       config.inc.php3 is probably the only modified file
    3. make a copy of the directory

        cd ~www/php
        cp -r mysql mysql_old_version_number
        

    4. Copy in the new version

        cd ~www/php/mysql
        cp -r /home/tools/phpMyAdmin 
        

    5. Copy the config.inc.php3 file from the saved version

        cp ../mysql_old_version_number/config.inc.php3 .
        

    6. Try it out by going to http://markov.eecs.berkeley.edu/mysql
    7. Check in the changes with

        cvs commit -m "Upgraded to version new_version_number"
        

    8. As the www user on gigascale check out the new version

        cd ~www/php/mysql
        cvs update