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
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 ) 😉