Date: Sat, 02 Sep 2006 20:19:39 +0100 From: Matthew Seaman <m.seaman@infracaninophile.co.uk> To: Dave <dmehler26@woh.rr.com> Cc: freebsd-questions@freebsd.org Subject: Re: upgrading mysql client and server Message-ID: <44F9D94B.8020102@infracaninophile.co.uk> In-Reply-To: <000301c6cea8$a5cf4650$0200a8c0@satellite> References: <000301c6cea8$a5cf4650$0200a8c0@satellite>
next in thread | previous in thread | raw e-mail | index | archive | help
This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig46B5DC3310C36DC8DD2C74AF
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable
Dave wrote:
> I've got mysql server and client v4.1 running on a FreeBSD6.x box.
> They were installed via ports not packages using custom make options
> which i have in portupgrade's pkgtools.conf file. I now would like to
> upgrade both of them to v5, without causing dependency issues with
> installed apps or with my databases. What i was wondering is is there a=
n
> easy way of doing this? I am concerned about my databases, i've done
> mysqldumps on them, but i have to manually go in and create information=
=2E
> I'd rather feed the mysql command a complete file and have it
> automatically regenerate everything. I'm also concerned about any
> compatibility issues. Any help appreciated.
This isn't an amazingly difficult upgrade. However there are a few
gotchas you should be aware of.
*) MySQL 5.0.x is pretty much backwards compatible with 4.1.x -- ie.
everything that's in 4.1.x is also in 5.0.x (but the converse is
not true). On disk data formats may be slightly changed, but you
should be able to run the mysql_upgrade script to convert everythi=
ng
to 5.0.x style. mysql_upgrade comes as part of the mysql-client-5=
=2E0.x
port/package
You should certainly take good backups of your 4.1.x databases,
but if everything goes according to plan, you won't actually need
to load them into the upgraded MySQL server.
*) Upgrade the MySQL client port and its dependencies first. MySQL
client 5.0 will interoperate pretty well with MySQL server 4.1 but=
not necessarily vice versa. You will also need to recompile
anything that links against the MySQL shlibs, as the ABI has
changed between the versions. Using portupgrade makes that fairly=
simple, and portupgrade's feature of preserving any shlibs removed=
from old ports in /usr/local/lib/compat/pkg means the old 4.1
dependent stuff will still continue to work even after you've upda=
ted --
again, not that you should be planning to use mysql dependent
programs while upgrading, but having that capability is useful if
you run into problems.
*) Make sure to shut down the mysql daemon before attempting to upgra=
de
it. I know this is bleeding obvious, but it's also easy to forget=
=2E
Not doing this can lead to data corruption, and in fact the port w=
ill
automatically kill any running mysql at re-install time. But do be=
sure
to double check.
The sequence of thing you'll need to do is as follows:
0) You've got good backups, haven't you?
i) Examine the output of 'pkg_info -R mysql-client-\*' and note down
any daemon processes that depend on MySQL. You should shut those =
down
for the duration of the upgrade process. Alternatively you could
drop to single user mode to carry out this whole procedure.
ii) Edit /etc/make.conf and insert in it:
WITH_MYSQL_VER=3D 50
You can put extra compile time flags for the mysql port into
/etc/make.conf at the same time. eg. I tend to use something
like this, which you should modify to suit your own needs:
.if ${.CURDIR:M*/databases/mysql*}
WITH_CHARSET=3Dlatin1
WITH_XCHARSET=3Dnone
WITH_COLLATION=3Dlatin1_swedish_ci
WITH_OPENSSL=3Dyes
BUILD_OPTIMIZED=3Dyes
WITH_INNODB=3Dyes
WITH_ARCHIVE=3Dyes
WITH_FEDERATED=3Dyes
WITH_NDB=3Dyes
.endif=20
Or you can use the MAKE_ARGS array in pkgtools.conf to achieve a s=
imilar
effect. (Note that pkgtools.conf only applies to portupgrade et a=
l,
/etc/make.conf applies generally to any way of using the ports)
iii) Make sure the database is shut down:
/usr/local/etc/rc.d/mysql-server stop
iv) Upgrade mysql-client, replacing mysql-client-4.1.x in the dependen=
cies
between your installed ports:
portupgrade -o databases/mysql50-client -f mysql-client-\*
iv) Upgrade mysql-server, replacing mysql-server-4.1.x in the dependen=
cies
between your installed ports:
portupgrade -o databases/mysql50-server -f mysql-server-\*
v) Force a recompile of everything else that depends on mysql-client =
so
that they all link against the upgraded shlib:
portupgrade -Nfi -r mysql-client-\* -x mysql-client -x mysql-serv=
er
Certain MySQL related ports are specific to one mysql version, suc=
h
as eg. p5-DBD-mysql50-3.0006 or mysql-scripts-5.0.24 and you may h=
ave
to use the 'portupgrade -o port/name -f pkg' style of command to g=
et
them upgraded properly.
vi) Restart mysql-server. Immediately run the mysql-upgrade program:
mysql_upgrade -v -b /usr/local -d /var/db/mysql -u root
(Will prompt you for the root@localhost password to the DB)
That should fix up anything that needs fixing, especially the user=
grant tables.
Examine /var/db/mysql/$HOSTNAME.err to see if there are any obviou=
s
problems, and do whatever tests you need to on the contents of you=
r
DB to assure yourself it has come through intact.
vii) Restart all mysql client programs. Test that they function correc=
tly.
viii) Make haste to nearest public house and order yourself a pint of
your favourite beer. You've earned it.
Cheers,
Matthew
--=20
Dr Matthew J Seaman MA, D.Phil. 7 Priory Courtyard
Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate
Kent, CT11 9PW
--------------enig46B5DC3310C36DC8DD2C74AF
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFE+dlS8Mjk52CukIwRCHwaAJ49ZAAD/foCavRtFPN7XYYKZguuigCdFOUs
vS4evHklryb0Jbu+g0TLsiQ=
=LsYY
-----END PGP SIGNATURE-----
--------------enig46B5DC3310C36DC8DD2C74AF--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?44F9D94B.8020102>