Mani's Blog

November 10, 2011

Upgrade MySQL 5.5.16

Filed under: MySQL,Upgrade — mani @ 6:52 am
Tags:

Overview

We upgraded our environment to Enterprise Edition.  Upgrading MySQL process pretty much simple process.   However there are some know issues, even there is bug in upgrading performance schema.

  1. Download binaries from http://www.oracle.com
    MySQL-*-5.5.16*
  2. copy them to local Filesystems.
    cd /usr/local/src
    cp /local/mysql/backups/MySQL_Software/5.5.16_ent/* .
  3. Stop the mysql
    /etc/init.d/mysql stop
  4. Make sure mysql is not running
    ps -ef |grep mysqld |grep -v grep
  5. Uninstall existing MySQL binaries
    for x in `rpm -qa |grep MySQL |grep "5.5"`;
    do
    echo "rpm -e ${x}";
    done
  6. Install new 5.5.16 binaries
    rpm -ivh --force --nodeps MySQL-client-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-shared-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-devel-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
  7. Start Mysql
    /etc/init.d/mysql start
  8. Run mysql_upgrade
    mysql_upgrade --skip-write-binlog -uroot -p
  9. Stop the mysql
    /etc/init.d/mysql stop
  10. Copy 5.5.16 performance_schema directory from one of the upgraded database.
    rm -rf /local/mysql/data/performance_schema
    cp -rp /local/mysql/backups/performance_schema /local/mysql/data
    chown -R mysql:mysql /local/mysql/data/performance_schema
  11. Start Mysql
    /etc/init.d/mysql start
  12. Check Performance_schema
    use performance_schema;
    select * from cond_instances;
  13. check the error log.
    tail -f /var/log/mysqld/mysql.err
  14. validate the replication.
    show slave status\G

Know issues and fixes

  1. Failed to uninstall
    Issue:
    root@@xnomdb912n1:/usr/local/src#rpm -e MySQL-shared-5.5.6_rc-1.rhel5
    error: Failed dependencies:
    libmysqlclient.so.16()(64bit) is needed by (installed) php-mysql-5.2.10-11.0.x86_64
    Fix: Just Ignore
  2. While running mysql_upgrade
    Issue: Error : Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
    Error : Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
    error : Corrupt
    Cause: .frm file is there for the table, not data file
    Fix: touch the file and rerun the upgrade
    touch /local/mysql/data/mysql/general_log.CSV
  3. While running mysql_upgrade
    Issue:
    ERROR 1194 (HY000) at line 719: Table 'general_log' is marked as crashed and should be repaired
    ERROR 1194 (HY000) at line 730: Table 'slow_log' is marked as crashed and should be repaired
    FATAL ERROR: Upgrade failed
    Fix: Run repair on these tables.
    repair table general_log;
    repair table slow_log;
  4. During the install
    Issue:
    root@@xnomdb912n1:/usr/local/src#rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
    Preparing... ########################################### [100%]
    Some previous upgrade was not finished:
    -rw-r--r-- 1 root root 214 Oct 10 21:42 /var/lib/mysql/RPM_UPGRADE_MARKER
    Please check its status, then do
        rm /var/lib/mysql/RPM_UPGRADE_MARKER
    before repeating the MySQL upgrade.
    error: %pre(MySQL-server-advanced-5.5.16-1.rhel5.x86_64) scriptlet failed, exit status 1
    error: install: %pre scriptlet failed (2), skipping MySQL-server-advanced-5.5.16-1.rhel5
    Fix: Remove the upgrade marker and run the install again.
    rm /var/lib/mysql/RPM_UPGRADE_MARKER
    rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
  5. While start up mysql
    Issue: 
    root@@xnamdb01n2:/usr/local/src#/etc/init.d/mysql start
    Starting MySQL. ERROR! The server quit without updating PID file (/local/mysql/data/xnamdb01n2.ningops.com.pid).
    Error logs says:
    /usr/sbin/mysqld: File '/local/mysql/mojo/binary-logs.index' not found (Errcode: 13)
    111017 18:32:25 [ERROR] Aborting
    Fix: correct the privileges
    example:
    root@@xnamdb01n2:/local/mysql/mojo#chown mysql:mysql *
    /local/mysql/mojo#/etc/init.d/mysql start
  6. Replication error
    Issue: replication broken since we touched the file *.CSV
    Fix: skip the counter and start the slave.
    stop slave; set global sql_slave_skip_counter=1;start slave;
    show slave status\G
  7. Performance_schema issue
    Issue: wrong structure error when you read Performance_schema table.
    Fix: No fix yet.  Bug #13146778 is filed my Oracle support.
    Workaround: Copy upgraded performance-schema directory to the database and start MySQL.
    Installation steps are modified accordingly.
  8. Issue while MySQL startup on one or 2 databases.
    Issue: Error log shows.
    111024 23:16:04 InnoDB: Completed initialization of buffer pool
    InnoDB: Unable to lock /local/mysql/data/ibdata1, error: 11
    InnoDB: Check that you do not already have another mysqld process
    Solution: killall -9 mysqld_safe mysqld
    then /etc/init.d/mysql start
  9. PID file couldn’t be found
    Issue: I was trying to stop MySQL, but error reports as below.
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql stop
     ERROR! MySQL server PID file could not be found!
    Fix: Only option worked was  
    root@@xnomdbcmn011n1:/usr/local/src#killall -9 mysqld_safe mysqld
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql start
    Starting MySQL.......... SUCCESS!
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql stop
    Shutting down MySQL......... SUCCESS!
Advertisements

Create a free website or blog at WordPress.com.