Upgrading to MySQL 5.1

From UFP-UV Sakai Wiki

Jump to: navigation, search

Contents


Before upgrade

Determine current installed RPMs

rpm -qa | grep MySQL
  • On production and development servers this produces the following list:
    • MySQL-client-standard-4.1.15-0.rhel3
    • MySQL-server-standard-4.1.15-0.rhel3
    • MySQL-devel-standard-4.1.15-0.rhel3
    • MySQL-shared-standard-4.1.15-0.rhel3

Notes


Upgrade

Phase I: 4.1 to 5.1

  • Backup
  • Create upgrade folder:
mkdir -p /servicos/install/mysql-upgrade
  • Backup configuration
cp /etc/my.cnf /servicos/install/mysql-upgrade/my-4.1.cnf
  • Backup all databases:
mysqldump -u root -p --all-databases --lock-all-tables --quote-names > /servicos/install/mysql-upgrade/all_dbs_4-1.sql
mysqldump documentation.
  • Backup privileges (mysql db):
mysql -u root -Bse "SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user" | mysql -u root -Bs | sed 's/$/;/g'
Additional information: [2]
  • Shutdown Apache httpd:
service httpd stop
  • Shutdown any other service dependent on MySQL
  • Shutdown MySQL:
service mysql stop
  • Backup data and binlog folders
cp -R /servicos/mysql /servicos/install/mysql-upgrade/mysql-data
cp -R /servicos/mysql_bin_log /servicos/install/mysql-upgrade/mysql_bin_log
  • Remove MySQL 4.1 RPMs:
rpm -e --nodeps MySQL-devel-standard-4.1.15-0.rhel3 MySQL-server-standard-4.1.15-0.rhel3 \
                MySQL-client-standard-4.1.15-0.rhel3 MySQL-shared-standard-4.1.15-0.rhel3
  • Clean mysql & binlog folders, move binlog out of SAN:
rm -rf /emcf/mysql/*; rm -rf /emcf/mysql_bin_log; rm -rf /servicos/mysql_bin_log
mkdir /servicos/mysql_bin_log; chown -R mysql.mysql /servicos/mysql_bin_log
  • Edit /etc/my.cnf:
Remove innodb_log_arch_dir setting
Change to innodb_flush_log_at_trx_commit = 1
Add binlog_format = MIXED (make sure there are no bin log files!)
Note: Unused settings will cause MySQL to fail on startup with "Manager of pid-file quit without updating file"
  • Install MySQL 5.1 RPMs:
rpm -ivh /servicos/install/mysql-upgrade/packages/5.1/MySQL*
Server will startup automatically
  • Upgrade: mysql_upgrade -u root -p --force ([3])

Phase II: Sakai

  • As mysql 5.1 supports using the query cache with server-side prepared statements, connector configuration can be changed to use server-side prepared statements with these parameters:
useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096
  • Use MySQL Connector/J (5.1.12+) for 5.1: [4]


Phase III: Rebuild/reinstall 3rd party clients

  • Perl DBD::mysql module:
"Does not need any update!"
  • PHP mysql extensions:
Recompile:
make clean
./configure --with-apxs2=/usr/local/apache2/bin/apxs \
            --with-mysql \
            --enable-mbstring \
            --with-gd \
            --with-zlib \
            --with-zlib-dir=/usr/include \
            --with-png-dir=/usr/include/libpng12 \
            --with-freetype-dir=/usr/include/freetype2/freetype \
            --enable-gd-native-ttf \
            --with-ldap
make
make test
Install:
su make install
libtool --finish /servicos/install/php-5.2.4/libs

Phase IV: Start remaining servers

  • service httpd start
Personal tools