Upgrade MySQL from 4.1 to 5.1 Linux

How to Upgrade from MySQL 4.1 to MySQL 5.1

I downloaded the files.

MySQL-server-community-5.1.23-0.rhel4.i386.rpm
MySQL-client-community-5.1.23-0.rhel4.i386.rpm
MySQL-shared-compat-5.1.23-0.rhel4.i386.rpm
MySQL-devel-community-5.1.23-0.rhel4.i386.rpm
MySQL-shared-community-5.1.23-0.rhel4.i386.rpm

Put them in a directory. I put mine in /tmp/mysql directory

First create a backup of the current Data.

Note: When doing multiple databases be sure to include the “s” at the end of databases. When doing a single database then only type database without the “s”.

To create a complete backup of all the databases do this:

# mysqldump –u root –p –-all-databases> alldatabases.sql

Or to compress the file do this:

# mysqldump –u root –p –-all-databases |gzip > alldatabases.sql.gz

To create a backup of individual database then do this:

# mysqldump –u root –p -–database database > databasebackup.sql

# mysqldump –u root –p -–database database1 > databasebackup1.sql

# mysqldump –u root –p -–database database2 > databasebackup2.sql

Or to compress the file do this:

# mysqldump –u root –p -–database database |gzip > databasebackup.sql.gz

# mysqldump –u root –p -–database database1 |gzip > databasebackup1.sql.gz

# mysqldump –u root –p -–database database2 |gzip > databasebackup2.sql.gz

If you want to backup multiple database to one file then do this:

# mysqldump –u root –p -–databases database database1 database2 > databasebackup.sql

Or to compress the file do this:

# mysqldump –u root –p -–databases database database1 database2 |gzip > databasebackup.sql.gz

You can also just backup the database tables with NO DATA.  This will just keep the table structure, but NO DATA.   Don’t run this on “mysql” database.  You will need the users and authentication to get back in.   Use this command:

# mysqldump –u root –p -–no-data -–database database > databasestructure_nodata.sql

To do multiple database use this command:

# mysqldump –u root –p -–no-data -–databases database database1 database2 > database3structure_nodata.sql

Once the data is protected then you can do the upgrade.

service mysqld stop

rpm -Uvh --nodeps MySQL-server-community-5.1.23-0.rhel4.i386.rpm

rpm -Uvh MySQL-client-community-5.1.23-0.rhel4.i386.rpm

rpm -Uvh MySQL-shared-community-5.1.23-0.rhel4.i386.rpm

rpm –Uvh MySQL-shared-compat-5.1.23-0.rhel4.i386.rpm

rpm -Uvh MySQL-devel-community-5.1.23-0.rhel4.i386.rpm

vi /etc/my.cnf

# Comment out the base-dir line under [mysqld.server] - RHEL 4 / MySQL bug

adduser mysql

cd /var/run

mkdir mysqld

chown -R mysql:mysql /var/run/mysqld

chown -R mysql:mysql /var/lib/mysql

service mysql start

mysql

Now you will need to fix the table structure problem with this command:

# mysql_upgrade –u root –p

You will probably see some errors about our databases.  That should be ok.  

Then login with the credentials.

If things go wrong and you need to restore; here is how to restore the data.

# mysql -u [username] -p [database_to_restore] < [backupfile]

You have to create the database first if it doesn’t exist already.  Here is the command to do that:

# mysqladmin -u USERNAME -p create DATABASE 

OR you can create it within MySQL

# mysql –u root –p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 50

Server version: 5.1.23-rc-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE database database1;

Query OK, 1 row affected (0.02 sec)

To see all the databases in MySQL issue this command from within MySQL

Mysql> SHOW databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| database           |

| database1          |

| database2          |

| database3          |

| mysql              |

| test               |

+--------------------+

7 rows in set (0.00 sec)