Migrating from mysql 4.0 to mysql 5.0 in FreeBSD

Final decision has been made, upgrade to mysql 5.0 is a must.

But how to do that?

How about user data?

After doing it by myself I found it’s easy. I try to share my experience in sbs (step by step) form 🙂

Situation :

I have two server kongja and proxy :

Server kongja has mysql 4.0.27 installed

Server proxy don’t have mysql server installed.

I wanna try migration in server proxy before doing it in kongja.

Here’s the steps :

1. Install mysql40-server in proxy

cd /usr/ports/databases/mysql40-server/ && make install

2. Compress mysql directory in kongja

cd /var/db

tar cvzf mysql.tar.gz mysql

export this file (mysql.tar.gz) to proxy.

kongja# scp mysql.tar.gz [email protected]:/home/edp
mysql.tar.gz                                                                                100%  249MB  13.8MB/s   00:18

3. login to proxy , make sure new installed  mysql server functional 🙂

3.1 add this line in rc.conf


3.2 start mysql server

/usr/local/etc/rc.d/mysql-server start

3.3 Check the service

proxy# ps -ax | grep mysql
81946  p0  I      0:00.01 /bin/sh /usr/local/bin/mysqld_safe –defaults-extra-file=/var/db/mysql/my.cnf –user=mysql –datadi
81963  p0  S      0:00.32 /usr/local/libexec/mysqld –defaults-extra-file=/var/db/mysql/my.cnf –basedir=/usr/local –datadir
81967  p0  DL+    0:00.00 grep mysql

4. raname mysql database ( fresh install ) to mysql_old

proxy# cd /var/db
proxy# ls
entropy                 ipf                     mountdtab               ports
entropy-file            locate.database         mysql                   portsnap
freebsd-update          mergemaster.mtree       pkg                     sup

mv mysql mysql_old

Extract mysql.tar.gz (compressed file from kongja)

proxy# tar xvzf mysql.tar.gz

proxy# ls -l
total 257080
drwx——     2 operator  operator        512 Jul 25 14:22 entropy
-rw——-     1 root      wheel          4096 Apr 22 20:38 entropy-file
drwxr-xr-x     2 root      wheel           512 Jan 12  2007 freebsd-update
drwx——     2 root      wheel           512 Jan 12  2007 ipf
-r–r–r–     1 nobody    wheel       1575035 Jul 21 04:17 locate.database
-rw-r–r–     1 root      wheel        154619 Jun 13 20:47 mergemaster.mtree
-rw-r–r–     1 root      wheel             0 Jan  1  2002 mountdtab
drwxr-xr-x  3706 mysql     mysql         73728 Jul 25 13:42 mysql
-rw-r–r–     1 edp       wheel     261231714 Jul 25 14:06 mysql.tar.gz
drwx——   273 mysql     mysql          5632 Jul 25 14:01 mysql_old

proxy# /usr/local/etc/rc.d/mysql-server start
Starting mysql

proxy# ps -ax | grep mysql
81946  p0  IW     0:00.00 /bin/sh /usr/local/bin/mysqld_safe –defaults-extra-file=/var/db/mysql/my.cnf –user=mysql –datadi
81963  p0  S      0:01.20 /usr/local/libexec/mysqld –defaults-extra-file=/var/db/mysql/my.cnf –basedir=/usr/local –datadir
82455  p0  R+     0:00.00 grep mysql

make sure to kill all mysql service
proxy# kill -9 81946
proxy# kill -9 81963

Checking again
proxy# ps -ax | grep mysql
82457  p0  R+     0:00.00 grep mysql

Start the service

proxy# /usr/local/etc/rc.d/mysql-server start

Starting mysql.
proxy# /usr/local/etc/rc.d/mysql-server start
mysql already running? (pid=82488).

try login using user and password that proven to work in kongja

proxy# mysql -uxxx -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.27

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


great, till this step. Moving file from kongja to proxy that run same mysql version successfully accomplished 🙂

5. Upgrading to mysql 5.0 steps , start by deleting all installed mysql related ports.

proxy# pkg_info | grep mysql
mysql-client-4.0.27 Multithreaded SQL database (client)
mysql-server-4.0.27 Multithreaded SQL database (server)
proxy# pkg_delete mysql-server-4.0.27
Stopping mysql.
Waiting for PIDS: 82488.
proxy# pkg_delete mysql-client-4.0.27

6. Install mysql50-server and use upgrade script

proxy# whereis mysql50-server
mysql50-server: /usr/ports/databases/mysql50-server
proxy# cd /usr/ports/databases/mysql50-server && make install

Using mysql_upgrade script

proxy# mysql_upgrade –datadir=/var/db/mysql
Looking for ‘mysql’ in: mysql
FATAL ERROR: Can’t find ‘mysql’

7. Try other script :

proxy# mysqlcheck -uuser -p –check-upgrade –all-databases –auto-repair

proxy# mysql_fix_privilege_tables –user=zzzzz –password=xxxxxyyyy
This script updates all the mysql privilege tables to be usable by
the current version of MySQL


In this steps tables and privileges has been updated to mysql 5.0 style.

Very neat, I’m lucky with these steps only I can doing migration from mysql 4.0.27 to mysql 5.0.45 easily with FreeBSD.

Enough for experiment. Time to do the steps in production server ( kongja )  😉