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 edp@172.88.1.2:/home/edp
Password:
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

mysql_enable="YES"

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#

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.

mysql>

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
proxy#

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

proxy#

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 )