HOWTO: freebsd mysql migration to ports version

[paix]

This instruction describes how to migrate to latest mysql5 using freebsd ports.

first, install mysql and backup all databases.

#make install clean -C /usr/ports/databases/mysql50-server/

By default in freebsd the mysql data dir is "/var/db/mysql"
In my case, it's "/home/mysql"

so

#ln -s /home/mysql /var/db/mysql

also you could use mysql_dbdir="/home/mysql" vars in rc.conf

#cp /etc/my.cnf /home/mysql
#chown mysql:mysql /home/mysql/my.cnf

Stopping DA mysql version:

#/usr/local/etc/rc.d/mysqld stop
#chmod -x /usr/local/etc/rc.d/mysqld

Starting freebsd mysql ports version:

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

To ensure please see:

#tail -f /var/db/mysql/`uname -n`.err

In my case:

080708 9:05:17 InnoDB: Starting shutdown...
080708 9:05:20 InnoDB: Shutdown completed; log sequence number 0 1559936068
080708 9:05:20 [Note] /usr/local/mysql-5.0.22-freebsd6.0-i386/bin/mysqld: Shutdown complete
080708 09:05:20 mysqld ended

080708 09:06:58 mysqld started
080708 9:06:59 InnoDB: Started; log sequence number 0 1559936068
080708 9:06:59 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.51a' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.51a

All works fine!

Warning!!
Please keep in mind that changing mysql dirs could cause php-mysql extension work broken, so you could want recompile php-extensions.
(Probably you should correct mysql dir in /usr/local/directadmin/customapache/configure.php and rebuild the php)

I've solve this problem completely moving to php5 and php5-extensions installed through ports to. Php is running as fastcgi.

Some addons (250708):

cd /usr/local/etc/rc.d 
mv mysqld mysqld_da_old
ln -s mysql-server mysqld

thanks to tsiou!

 

[tsiou]

also do a :

cd /usr/local/etc/rc.d

rm mysqld
ln -s mysql-server mysqld


so you can restart mysql via DA admin panel (and let DA restart mysql when it wants)

 

[Chrysalis]

I will add my 2 cents.

1 - I still use /etc/my.cnf on the ports version of mysqld and works fine.
2 - If you dont replace the binaries in /usr/local/mysql/bin/ then directadmin will still report the old version in the control panel.

 

[tsiou]

I will add my 2 cents.

1 - I still use /etc/my.cnf on the ports version of mysqld and works fine.

Yes me too...

2 - If you dont replace the binaries in /usr/local/mysql/bin/ then directadmin will still report the old version in the control panel.

You can do a locate mysql_safe etc and link da's binaries to freebsd's
like ln /wherefreebsdhas/mysql_safe /wheredahas/mysql_safe

this directory in my system is :
[root@pindos /home/zam]# ls -la /usr/local/mysql/
total 6
drwxr-xr-x 3 root mysql 512 Apr 23 00:36 .
drwxr-xr-x 25 root wheel 512 Jul 7 14:59 ..
drwxr-xr-x 2 root mysql 512 Apr 23 00:37 bin
[root@pindos /home/zam]# ls -la /usr/local/mysql/bin/
total 4
drwxr-xr-x 2 root mysql 512 Apr 23 00:37 .
drwxr-xr-x 3 root mysql 512 Apr 23 00:36 ..
lrwxr-xr-x 1 root mysql 24 Apr 23 00:37 mysqldump -> /usr/local/bin/mysqldump

You have to delete DA's binaries and DA's startup script. DA do some service restarts for log rotates etc. What if DA's do a mysql restart and stops freebsd's mysql and start the old binaries? Or by mistake you do a mysqld restart instead of mysql-server restart (I did it many times )

I tell you so because i did what you did for all DA's services (apache, dovecot, exim etc) and for some of it you have to keep the name of DA's script and ln it to freebsd's script to have it shown working in control panel and restart every time DA does.

 

[Chrysalis]

yep I forgot to mention I overwrite the DA script with the port version.

 

[tsiou]

It's best though to have the link..
In your rc.conf you have a mysqld=yes, when your server is going to reboot freebsd will try to start it by running /usr/local/etc/rc.d/mysql-server start and
you 'll get an error on that. Mysql will start via the local.sh which contains DA's commnad i thing ./mysqld start
When the time comes to portupgrade mysql it will remake freebsd's mysql-server startup script. If you forget to delete it after a reboot freebsd will start mysql and DA will try via the local.sh a bit later giving you an error...
These aren't something bad just errors like cant bind to port, but the best way for you to not loose time (think a year after when you do a restart, see the error, don't remember from where it comes from and spent some hours searching) is to leave freebsd's scripts.
do a ln -s mysql-server mysqld
edit local.sh, remove mysqld d line (or something like that) and when you are portupgrading you dont have to worry...

 

[paix]

Thanks for notes, guys!

DA is great thing, but i think is not a good to don't use the power of freebsd, particularly it ports system.

Also, there are some nice tools:
sysutils/monit
sysutils/monitord

that could be used for advanced management and monitoring.

 

[Chrysalis]

so where is this local.sh then, doesnt exist.

 

[tsiou]

sorry i meant boot.sh

remove mysqld d

sorry for some mistakes but its been over a year that i'm not using da's services and i can't remember it all.
I would like also to write some how too's for all of the services, but i can't remember the details of how i did it and i
don't have a second box to play. If you do, or want to move to the other services, i'd like to help you
by chatting the time that you're doing it, so to write a full how for all services.

It's better though to not try in a production server..