I did a MySQL upgrade from 5.0.45 to 5.1.40 on two servers. Before starting the actual upgrade, I spent some time reading manuals and web postings. It turned out that the times were well spent. The upgraded version was Enterprise 5.1.40. However, the upgrade processes should be the same if it was upgraded to the same Community version.
These are some information on existing software:
· Server version: 5.0.45-log Source distribution
· OS: Redhat Linux 4. (2.6.18-92.el5), 64 bits.
Note that the existing installation was from the packages that were bundled with Redhat Linux packages.
Following were the steps I took to do the upgrade. I also included here all the issues that I had encountered in each step.
1. Obtain a full database dump (backup) and made a backup copy of the my.cnf file.
These two things are, no matter it was involving an upgrade or other major server works, important to do and should not skip.
#>mysqldump –u root –p dbs >dbs-dump.sql
#>cp /etc/my.cnf /tmp/my.cnf
2. Stop MySQL
#>./etc/init.d/mysqld stop
3. Upgrade (Install) MySQL Server
#> rpm -Uvh MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm
Here I got my first error:
error: Failed dependencies:
libmysqlclient.so.15()(64bit) is needed by (installed) perl-DBD-MySQL-3.0007-1.fc6.x86_64
libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by (installed) perl-DBD-MySQL-3.0007- 1.fc6.x86_64
Initially, I thought this error was due to some compatibility issues, so I decided to go ahead and to install the shared libraries (shared-compat package) first:
#> rpm -Uvh MySQL-shared-compat-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm
The package was installed without any error. I then tried to install the server package again.
#> rpm -Uvh MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm
This time, the server installation started without the dependency problems, but it gave me more warning and error messages:
Preparing... ########################################### [100%]
******************************************************************
A MySQL server package (mysql-server-5.0.45-7.el5) is installed.
The current MySQL server package is provided by a different vendor (Red Hat, Inc.) than MySQL AB or Sun Microsystems, Inc..
Some files may be installed to different locations, including log files and the service startup script in /etc/init.d/.
Upgrading directly from MySQL 5.0 to MySQL 5.1 may notbe safe in all cases. A manual dump and restore using mysqldump is recommended. It is important to review the MySQL manual's Upgrading
section for version-specific incompatibilities.
A manual upgrade is required.
- 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
- Install the new MySQL packages supplied by Sun Microsystems, Inc.
- Ensure that the MySQL server is started
- Run the 'mysql_upgrade' program
This is a brief description of the upgrade process. Important details can be found in the MySQL manual, in the Upgrading section.
******************************************************************
error: %pre(MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4.x86_64) scriptlet failed, exit status 1
error: install: %pre scriptlet failed (2), skipping MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4
Well, it turned out, there was no such thing as “Upgrade”. It required that we removed the existing packages, installed all the new packages, and then, run the upgrade script to upgrade the existing databases.
Following the instructions, I checked what was installed in the server:
#> rpm -qa grep -i '^mysql-'
mysql-5.0.45-7.el5
MySQL-shared-compat-advanced-gpl-5.1.40sp1-0.rhel4
mysql-server-5.0.45-7.el5
Since I just installed the MySQL-shared-compat-advanced-gpl-5.1.40sp1-0.rhel4 package, all I needed to remove were the following packages:
· mysql-5.0.45-7.el5
· mysql-server-5.0.45-7.el5
#> rpm -e mysql-server-5.0.45-7.el5.x86_64
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
# > rpm -e mysql-5.0.45-7.el5
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
Now, I was ready to install the new packages again:
# > rpm -Uvh MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm
The installation was successful with the following messages:
Preparing... ########################################### [100%]
1:MySQL-server-advanced-g########################################### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h mysql.domain.com password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
he latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/
Starting MySQL.[ OK ]
Giving mysqld 2 seconds to start
By checking the log and using ps –ef | grep mysql, I confirmed that the server was started.
4. Installed the client package.
Since the server was running, I wanted to install the client package so I could connect to the server.
#> rpm -Uvh MySQL-client-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm
No problem installing the client. So, I tried to connect to the server and it worked.
#>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40sp1-enterprise-gpl-advanced MySQL Enterprise Server - Advanced Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
Database
+--------------------+
information_schema
ysql
test
+--------------------+
3 rows in set (0.00 sec)
So far so good.
5. Installed other packages.
There were other packages, such as share libraries, embedded MySQL server library and devel libraries and include files (MySQL-devel package),etc, that needed to install in my case . I installed all of them without any problem.
6. Restarted the server with my.cnf file.
Since the server were started without the my.cnf file, I needed to stop the server, copy the my.cnf back to the /etc/ directory (see step 1) and restarted the server. While executing the rpm –e command to remove the old packages, the my.cnf file was renamed to my.cnf.rpmsave (see step 3)
#>./etc/init.d/mysql stop
#>cp /tmp/my.cnf /etc/my.cnf
#>./etc/init.d/mysql start
Checked the error log, I noticed the following error messages:
100602 13:05:34 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
100602 13:05:34 [ERROR] mysql.user has no `Event_priv` column at position 29
100602 13:05:34 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
100602 13:05:34 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.40sp1-enterprise-gpl-advanced-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Enterprise Server - Advanced Edition (GPL)
100602 13:44:57 [Warning] Statement may not be safe to log in statement format. Statement: ALTER TABLE general_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL
100602 13:44:57 [Warning] Statement may not be safe to log in statement format. Statement: ALTER TABLE slow_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL
These messages were expected because I had not run the mysql_upgrade script.
7. Executed the mysql_upgrade script
The script, in my case, was located in /usr/bin.
#>./usr/bin/mysql_upgrade
Again, it was a smooth run.
8. Loaded the database dump file
At this point, I could load the database back to the server.
#>mysql –u root –p < dbs-dump.sql
There was no error or warning message.
9. Restarted the server
Here, just to restart the server one more time to make sure there was no error message.
#>/etc/init.d/mysql stop
#>/etc/init.d/mysql start
Checked the error log and there was not error. I also ran some queries and connected an application to the database server. No error as well. So, Server was upgraded.
Summary: It is very important to read the MySQL manual, other articles such as those that listed under References, and search the web for any new information before doing the actual upgrade. Also, if it is possible, try it out in a development server first before doing it in a production environment. There are more than one way to do the upgrade, but each has its own upside and downside. I did the above upgrade on two QA servers and there was very little time pressure. One thing to keep in mind is that doing mysql dump and load takes time depends on the size of the database; therefore, a detail plan is need for large production system upgrade. There is other method, such as replication, that can be used to reduce times. It is also recommended that we should check for performance issues and query consistent issues after the upgrade. In some cases, mysql_upgrade may not run as smoothly as it was in my case. The MySQL manual also states some known bugs and issues which are worth to check out against the existing installation to make sure if or not they apply to the existing installation and there is a plan to due with them if they do.
Following are the summary of steps for the upgrade:
1. Backup databases and my.cnf file
2. Stop MySQL server
3. Remove existing MySQL packages
4. Install MySQL 5.1 server package
5. Install MySQL 5.1 client and other MySQL 5.1 packages
6. Restart MySQL with my.cnf file
7. Execute mysql_upgrade script
8. Load database backup to MySQL server (v.5.1)
9. Restart server and check error log.
References:
- MySQL Manual: Installing and Upgrade
- MySQL Performance Blog: Upgrading MySQL
- MySQL Performance Blog: Upgrading MySQL Reader’s command:
- High performance MySQL: Chapter 4 and 8
- Other Reference On the Web
Posts
No comments:
Post a Comment