Bookmark and Share
Upgrade from MySQL 5.0 to MySQL 5.1
(Publish Date: 2009-9-27 12:54pm, Total Visits: 2424, Today: 1, This Week: 1, This Month: 5)

Upgrade from MySQL5.0 to MySQL5.1

1. backup databases first.

2. $rpm -Uvh MySQL5.1.rpm

3. If 2 doesn't work, we need to manual upgrade MySQL

- Ensure that you have a complete, working backup of your data and my.cnf files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages.  Usually this command will list the packages you should remove:
  rpm -qa | grep -i '^mysql-'

  You may choose to use 'rpm --nodeps -ev ' to remove the package which contains the mysqlclient shared library.  The
  library will be reinstalled by the MySQL-shared-compat package.
- Install the new MySQL packages supplied by Sun Microsystems, Inc.
- Ensure that the MySQL server is started
- Run the 'mysql_upgrade' program to check tables for MySQL Upgrade
- Sometimes, the output of 'mysql_upgrade' shows that we need to run mysql_fix_privilege_tables to fix privileges

4. please refer the following link

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html

5. run the following scripts to check the tables' status after upgrade.

#!/bin/bash
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# sbray@csc.uvic.ca, UVic Fine Arts 2004.
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/

# system variables (change these according to your system)
USER=root
PASSWORD=xxxxxxx
DBHOST=localhost
LOGFILE=./mysql_check.log
MAILTO=xxx@xxx.com
TYPE1='FOR UPGRADE' # extra params to CHECK_TABLE e.g. FAST
TYPE2=FAST
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space

# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE

echo -n "Logfile: "
date
echo "---------------------------------------------------------"
echo

# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi

# Run through each database and execute our CHECK TABLE command for each table...
for i in $DBNAMES
do
# to fancy up our log file
echo ""
echo "Database: $i"
echo "---------------------------------------------------------"

DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show tables"`"

for j in $DBTABLES
do
echo "CHECK TABLE $j $TYPE1 $TYPE2" | mysql -u$USER -p$PASSWORD $i
done
echo ""
done

exec 1>&6 6>&- # Restore stdout and close file descriptor #6

# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done

# send off our results...
# if test $CORRUPT = "yes" ; then
# cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
# else
# cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
# fi