Place of all tech articles

MySQL Version Upgrade in Redhat/CentOS from 5.1 to latest

If you follow traditional procedure for “MySQL Version Upgrade in Redhat/CentOS from 5.1 to latest” , you need a lot of time and patience. You have to upgrade current version to next version and so on to reach the final desired version. But follow the simple, first method for “MySQL Version Upgrade in Redhat/CentOS from 5.1 to latest”. This procedure will work not only from version 5.1 to latest but also from all versions.

1. Check the version of mysql
   mysql –version
   
2. Check the version of Operating System
   uname -a
   Red circle means Redhat 6 and blue circle means 64 bit machine.

 

• Download the latest version according to server OS and architecture from http://dev.mysql.com/downloads/mysql/ and choose Red Hat Enterprise Linux
  At the time of writing the blog the latest version is 5.6.15. Earlier version of MySQL you can download from http://downloads.mysql.com/archives/community/. You need to download only four packages :

 

1. MySQL-server-5.6.15-1.el6.x86_64.rpm
2. MySQL-client-5.6.15-1.el6.x86_64.rpm
3. MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm
4. MySQL-devel-5.6.15-1.el6.x86_64.rpm

In the below image from MySQL website, red circle(5.6.15) indicates MySQL version , blue circle(x86,64-bit) indicates server architecture model and black circle indicates Redhat/CentOs version.

For 32 bit machine the packages would be

1. MySQL-server-5.6.15-1.el6.i686.rpm
2. MySQL-client-5.6.15-1.el6.i686.rpm
3. MySQL-shared-compat-5.6.15-1.el6.i686.rpm
4. MySQL-devel-5.6.15-1.el6.i686.rpm

 

• Take a full backup of the database
  mysqldump -u root –all-databases –lock-all-tables –routines -u root -p > backup.sql
  *For more safely upgradation , dump all databases except mysql( holds user ,performance and system variables) . Because if there is a huge version gap between original version and upgraded version , some unexpected problem may occur. Problem is that you will be required to create all users and permissions again. Run the following command to take backup all databases except mysql
  mysqldump -u root –databases database1 database2–lock-all-tables –routines -u root -p > backup.sql

 

 

• Shutdown MySQL daemon
  /etc/init.d/mysqld stop
  
• Move current /etc/my.cnf file to /etc/my.cnf.back
  mv /etc/my.cnf /etc/my.cnf.back

 

 

• Query the current installed RPM.
  Here we upgrade MySQL version 5.1.52 to 5.6.15
  rpm -qa | grep -i mysql
  
• Remove all packages except
  • mysql-libs-5.1.52-1.el6_0.1.x86_64
  • perl-DBD-MySQL-4.013-3.el6.x86_64
  • php-mysql-5.3.3-3.el6_1.3.x86_64

   

• Removing Packages as following order
  1. rpm -e MySQL-python-1.2.3-0.3.c1.1.el6.x86_64
  2. rpm -e mysql-connector-odbc-5.1.5r1144-7.el6.x86_64
  3. rpm -e mysql-bench-5.1.52-1.el6_0.1.x86_64
  4. rpm -e mod_auth_mysql-3.0.0-11.el6_0.1.x86_64
  5. rpm -e mysql-test-5.1.52-1.el6_0.1.x86_64

  mysql-libs-5.1.52-1.el6_0.1.x86_64 is actully a client library and many packages depend on it. When you install MySQL-shared-compat-5.6.15-1.el6.i686.rpm the library will be updated . Don’t need to remove dependent package . But sometimes , it may happen that you can’t install MySQL-shared-compat due to conflicting with library then you need to remove mysql-libs-5.1.52-1.el6_0.1.x86_64. Before that you must remove perl-DBD-MySQL-4.013-3.el6.x86_64 , php-mysql-5.3.3-3.el6_1.3.x86_64 and any other package that depends on mysql-libs-5.1.52-1.el6_0.1.x86_64. After upgrading mysql you will require to install again perl-DBD( to access MySQL from Perl) and php-mysql ( to access mysql from PHP) .

 

 

• Install downloaded packages as following order of commands

 

1. rpm -Uvh MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm
2. rpm -UvhMySQL-client-5.6.15-1.el6.x86_64.rpm
3. rpm -Uvh MySQL-server-5.6.15-1.el6.x86_64.rpm
4. rpm -Uvh MySQL-devel-5.6.15-1.el6.x86_64.rpm

 

• Check the new installed version
  mysql –version

 

 

• Copy a sample my.cnf file and configure necessary mysql parameters.
  cp /usr/share/mysql/my-default.cnf /etc/my.cnf

 

 

• Start MySQL server with new version

 

/etc/init.d/mysql start

After upgraded to version 5.6.15 mysql instance has changed from mysqld to mysql. So now you need to stop,start or restart MySQL by /etc/init.d/mysql command.
try to access mysql without password
mysql

if you get error “Access denied for user ‘root'” then stop MySQL(/etc/init.d/mysql stop) and start it in safe mode

start mysql in safe mode to change password
mysqld_safe –skip-grant-tables &

Now you can access without password. Change the password as required
mysql> use mysql;
mysql> update user set password=PASSWORD(‘your root password’) where user=’root’;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Stop MySQL server again (/etc/init.d/mysql stop) and start again (/etc/init.d/mysql start)

Try to access now using your password
mysql -u root -p

If you get the following error after accessing
mysql> use mysql;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
run the following command
mysql> set password=PASSWORD(‘your root password’);
Query OK, 0 rows affected (0.00 sec)

 

• Import the backup you taken before upgradation
  mysql -u root -p < backup.sql
  Now you should get access using your old password if you have taken fullbackup including mysql database. If face any problem start mysql server in safe mode and change the root password.
  If your backup didn’t include mysql database you need to create other again old users and grant permissions.

 

 

• For checking uploaded database consistency run the command
  mysql_upgrade -u root -p

 

 

• For ensure security run the following command and remove anonymous users
  mysql_secure_installation

 
* You can follow the above procedure of “MySQL version upgrade in Redhat/CentOS from 5.1 to latest ” for other version upgrade. As instance , from version 5.5 to 5.6.15.