From owner-freebsd-questions@FreeBSD.ORG Sat Sep 2 19:20:16 2006 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 5EC2916A4E1 for ; Sat, 2 Sep 2006 19:20:16 +0000 (UTC) (envelope-from m.seaman@infracaninophile.co.uk) Received: from smtp.infracaninophile.co.uk (ns0.infracaninophile.co.uk [81.187.76.162]) by mx1.FreeBSD.org (Postfix) with ESMTP id 45C4343D45 for ; Sat, 2 Sep 2006 19:20:14 +0000 (GMT) (envelope-from m.seaman@infracaninophile.co.uk) Received: from [IPv6:::1] (localhost [IPv6:::1]) by smtp.infracaninophile.co.uk (8.13.8/8.13.8) with ESMTP id k82JJkFX039082; Sat, 2 Sep 2006 20:19:46 +0100 (BST) (envelope-from m.seaman@infracaninophile.co.uk) Authentication-Results: smtp.infracaninophile.co.uk from=m.seaman@infracaninophile.co.uk; sender-id=softfail; spf=softfail X-SenderID: Sendmail Sender-ID Filter v0.2.14 smtp.infracaninophile.co.uk k82JJkFX039082 Message-ID: <44F9D94B.8020102@infracaninophile.co.uk> Date: Sat, 02 Sep 2006 20:19:39 +0100 From: Matthew Seaman Organization: Infracaninophile User-Agent: Thunderbird 1.5.0.5 (X11/20060801) MIME-Version: 1.0 To: Dave References: <000301c6cea8$a5cf4650$0200a8c0@satellite> In-Reply-To: <000301c6cea8$a5cf4650$0200a8c0@satellite> X-Enigmail-Version: 0.94.0.0 Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------enig46B5DC3310C36DC8DD2C74AF" X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-2.0.2 (smtp.infracaninophile.co.uk [IPv6:::1]); Sat, 02 Sep 2006 20:20:09 +0100 (BST) X-Virus-Scanned: ClamAV 0.88.4/1786/Sat Sep 2 10:01:59 2006 on happy-idiot-talk.infracaninophile.co.uk X-Virus-Status: Clean X-Spam-Status: No, score=-2.6 required=5.0 tests=BAYES_00, DKIM_POLICY_TESTING, NO_RELAYS autolearn=ham version=3.1.4 X-Spam-Checker-Version: SpamAssassin 3.1.4 (2006-07-25) on happy-idiot-talk.infracaninophile.co.uk Cc: freebsd-questions@freebsd.org Subject: Re: upgrading mysql client and server X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 02 Sep 2006 19:20:16 -0000 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--