Upgrade to MySQL5 1 - Upgrading MySQL3 to MySQL4 - Red Hat Enterprise Linux 3 RHEL

Here comes the time to upgrade from MySQL 3.23 to the latest MySQL 5.0. In the first part, I will guide you through the MySQL 3 to MySQL 4 upgrade.

MySQL recommends we upgrade to next series available.

As a general rule, we recommend that when upgrading from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 rather than to 4.1 or 5.0.

• Read upgrading section for the release series to which you are upgrading, change notes to learn about new features.
• Backup your databases.
• Making changes to the grant tables. Read “Upgrading the Grant Tables”.
• Read MySQL replication upgrade info
• MySQL-Max distribution: According to MySQL, you will need to " upgrade later to a non-Max version of MySQL, mysqld_safe still attempts to run the old mysqld-max server. If you perform such an upgrade, you should manually remove the old mysqld-max server to ensure that mysqld_safe runs the new mysqld server."

Moving data files and Table Character Sets
For this MySQL says that you can move MySQL format and data files "between different versions on the same architecture as long as you stay within versions for the same release series of MySQL". If character set of a table needs to be changed, then run

charsetmyisamchk -r -q --set-character-set=

on all MyISAM tables. The character sets need to be changed because other indexes that you created on MySQL may not work because the sort order can be changed if the character set is changed.

Renaming old MySQL

At this point you may want to rename MySQL directory for backup. mysqldump can be used to do this job. Then mysql or mysqlimport can be invoked to read the database dumps created by MySQL.

MySQL says

If, after an upgrade, you experience problems with recompiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs.
....
If problems occur, such as that the new mysqld server does not want to start or that you cannot connect without a password, verify that you do not have some old

my.cnf

file from your previous installation. You can check this with the

--print-defaults

option (for example, mysqld --print-defaults). If this displays anything other than the program name, you have an active

my.cnf

file that affects server or client operation.

It is a good idea to rebuild and reinstall the Perl

DBD::mysql

module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP

mysql and mysqli extensions or the Python MySQLdb module.

Convert old ISAM tables to MyISAM

format
You have the following options:

1. Use mysql_convert_table_formatscript as described here. Then use the following command only if all tables in the database are ISAM or MyISAM tables

   shell>mysql_convert_table_format database db_name

2. To convert tables individually, use

   mysql> ALTER TABLE tbl_name  TYPE=MyISAM;
   

To know the table type for any MySQL table, use

mysql> SHOW TABLE STATUS LIKE 'tbl_name';

Make sure you are not using shared MySQL libraries (Perl DBD::mysql module, MySQLdb). Otherwise according to MySQL, "you should recompile them, because the data structures used in libmysqlclient.so."

MySQL UPGRADE GUIDE 1

MySQL Server Changes with MySQL4 onwards

1. server by default no longer loads user-defined functions. “User-Defined Function Security Precautions”.
2. many new privileges in the

   mysql.user

   table : “Privileges Provided by MySQL”.
3. safe_mysqld has been renamed to mysqld_safe
4. "InnoDB support is included by default" (binary)
5. mysqld option --temp-pool enabled by default(better performance with Linux).
6. Startup options --skip-locking and --enable-locking have been renamed to --skip-external-locking and--external-locking
7. External system locking of MyISAM/ISAM files is turned off by default. To turn it on, use

   -external-locking

Renamed startup variables and options:

Name in 3.23	Name in 4.0 (and above)
myisam_bulk_insert_tree_size	bulk_insert_buffer_size
query_cache_startup_type	query_cache_type
record_buffer	read_buffer_size
record_rnd_buffer	read_rnd_buffer_size
sort_buffer	sort_buffer_size
warnings	log-warnings
--err-log	--log-error (for mysqld_safe)

Why should you use truncate instead of delete

You should use TRUNCATE TABLE when you want to delete all rows from a table and you do not need to obtain a count of the number of rows that were deleted. (DELETE FROM tbl_name returns a row count in 4.0 and does not reset the AUTO_INCREMENT counter, and TRUNCATE TABLE is faster.)

For Full Text indexes

To use MATCH ... AGAINST (... IN BOOLEAN MODE) full-text searches, you must rebuild existing table indexes using REPAIR TABLE tbl_name USE_FRM. If you attempt a boolean full-text search without rebuilding the indexes in this manner, the search returns incorrect results. See “Fine-Tuning MySQL Full-Text Search”.

Also see: “How to Make a Threaded Client”
Comments and Errors

.
Download MySQL from here.

wget http://dev.mysql.com/..../MySQL-server-5.0.15-0.i386.rpm

Now lets verify the integrity of the package using md5sum

[root@plain install]# md5sum MySQL-server-5.0.15-0.i386.rpm
13971fd27ff2dc38537ff157ef6b715a MySQL-server-5.0.15-0.i386.rpm

Now verify that the MD5 checksum listed matches the MD5 Checksum on MySQL site.

In our case the md5 checksums do match.

13971fd27ff2dc38537ff157ef6b715a == 13971fd27ff2dc38537ff157ef6b715a

Therefore we can continue with the installation.

MySQL won't let you have both servers on one machine as we get the following error when installing MySQL 5 on top of MySQL 3

[root@plain install]# rpm -ivh MySQL-server-5.0.15-0.i386.rpm
warning: MySQL-server-5.0.15-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
MySQL conflicts with mysql-3.23.58-2.3
MySQL-server conflicts with mysql-server-3.23.58-1

We get the following errors when trying to uninstall MySQL

[root@plain install]# rpm -ev mysql
error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3
libmysqlclient.so.10 is needed by (installed) libdbi-dbd-mysql-0.6.5-5
libmysqlclient.so.10 is needed by (installed) MySQL-python-0.9.1-6
libmysqlclient.so.10 is needed by (installed) MyODBC-2.50.39-12
libmysqlclient.so.10 is needed by (installed) mysql-server-3.23.58-1
mysql = 3.23.58 is needed by (installed) mysql-devel-3.23.58-2.3
mysql is needed by (installed) MySQL-python-0.9.1-6
mysql = 3.23.58 is needed by (installed) mysql-server-3.23.58-1

To fix this, we need to uninstall MySQL 3.23 and all dependencies

rpm -ev mysql perl-DBD-MySQL-2.1021-3 libdbi-dbd-mysql-0.6.5-5 MySQL-python-0.9.1-6 MyODBC-2.50.39-12 mysql-server-3.23.58-1 mysql-devel-3.23.58-2.3 MySQL-python-0.9.1-6 mysql-server-3.23.58-1

Now lets get the other MySQL utilities

Benchmark suits

Downloading

wget http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-server-standard-5.0.15-0.rhel3.i386.rpm/from/ftp://ftp.orst.edu/pub/mysql/

wget http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-client-standard-5.0.15-0.rhel3.i386.rpm/from/ftp://ftp.orst.edu/pub/mysql
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm/from/pick/ftp:/ftp.orst.edu/pub/mysql
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-devel-standard-5.0.15-0.rhel3.i386.rpm/from/pick/ftp:/ftp.orst.edu/pub/mysql/

You should have the following RPMs now for MySQL

[root@plain mysql]# ls
MySQL-bench-5.0.15-0.i386.rpm MySQL-server-standard-5.0.15-0.rhel3.i386.rpm
MySQL-client-standard-5.0.15-0.rhel3.i386.rpm MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm

Calculate MD5


Verify all the packages using MD5

[root@plain mysql]# md5sum MySQL-*
3b02c71d5d5354bcd8ce9a534a999d74 MySQL-bench-5.0.15-0.i386.rpm
c7a0047f24d2ba0494829eb5a9c572bf MySQL-client-standard-5.0.15-0.rhel3.i386.rpm
b13df3d95bb8fca02d23546b84931140 MySQL-server-standard-5.0.15-0.rhel3.i386.rpm
109ca0273a6a7c460e4afaef3def88b3 MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm
51772ca969fa08a13064410469d79191 MySQL-devel-standard-5.0.15-0.rhel3.i386.rpm

Now lets install the MySQL 5 RPM

[root@plain mysql]# rpm -ivh MySQL-client-standard-5.0.15-0.rhel3.i386.rpm
warning: MySQL-client-standard-5.0.15-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-client-standard ########################################### [100%]
[root@plain mysql]# rpm -ivh MySQL-server-standard-5.0.15-0.rhel3.i386.rpm
warning: MySQL-server-standard-5.0.15-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-server-standard ########################################### [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 server password 'new-password'
See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
Starting MySQL.[ OK ]
[root@plain mysql]# rpm -ivh MySQL-devel-standard-5.0.15-0.rhel3.i386.rpm
warning: MySQL-devel-standard-5.0.15-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-devel-standard ########################################### [100%]
[root@plain mysql]#
[root@plain mysql]# rpm -ivh MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm
warning: MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-shared-standard ########################################### [100%]
[root@plain mysql]# rpm -ivh MySQL-bench-5.0.15-0.i386.rpm
warning: MySQL-bench-5.0.15-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
MySQL-client is needed by MySQL-bench-5.0.15-0
[root@plain mysql]#

Notice we got an error for the benchmarks one. We will investigate this later.

Now we can confirm that the MySQL RPMs have been installed.

[root@plain mysql]# rpm -qa | grep -i mysql
MySQL-client-standard-5.0.15-0.rhel3
MySQL-server-standard-5.0.15-0.rhel3
MySQL-shared-standard-5.0.15-0.rhel3
MySQL-devel-standard-5.0.15-0.rhel3

END

EV1 - MySQL4