Head of Technology at Endemol
Not much for me to say here that isn’t covered in the following tutorial: http://www.howtoforge.com/mysql_master_master_replication
Aside from the comments I made there, which I hope they include, if not I’ll update this post with those comments in due course
I’m looking to setup some replication between two servers and server B has none of the databases server A has, so obviously I have to populate B with A’s data.
Easy you might think, simply start the slave and issue some like:
mysql> load data from master;
But actually, in my case, this fails because of the following bug when loading master data and the fact that views exist on server A
As it says here http://bugs.mysql.com/bug.php?id=20596
[30 Aug 2006 20:36] Trudy Pelzer
Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER is very limited, these statements are deprecated in versions 4.1, 5.0 and 5.1 of MySQL. We will introduce a more advanced technique (online backup) in a future (>5.1) version, that will have the added advantage of working with more storage engines.
Due to the deprecated status of the statements, this bug will not be fixed. For 5.1 and earlier, the recommended alternative solution to using LOAD DATA|TABLE FROM MASTER is mysqldump on the master piped (or output copied) to the mysql client on the slave. This also has the advantage of working for all storage engines.
A deprecation warning will be added for these statements in 4.1, 5.0 and 5.1 shortly.
Now there’s a number of ways around this e.g. exporting the views table, dropping them, doing load data from master then re-importing them or just copying the mysql data dir’s from A to B, or using mysql dump as suggested above, but this shouldn’t be necessary!
This is a bug that’s been known about by MySQL for almost 3 years, should be a fairly simple thing to fix really and hasn’t been. I don’t remember seeing any deprecation warning in the latest version of MySQL and have wasted more time as a result.
Hopefully now SUN seem to be taking a proper hold of MySQL some of these more minor but pain in the ass issues will start to be fixed!!!
So, I needed to upgrade MySQL on our development boxes today and I was met by a little surprise from the RPM program…
Basically it won’t do an upgrade as the vendor has changed from being MySQL AB to Sun Microsystems, and as a result I have to do a complete uninstall and re-install manually…
Ho hum, I know it’s a small issue and for the best, but it’s still a pain in the ass when something silly such as vendor name change wastes time in what would otherwise be a quick and simple upgrade.
So anyway as I’m going through it the following might be useful to you if you have to do the same any time soon.
First download all the current MySQL packages you need:
$hell> mkdir mysql-5.1.34
$hell> cd mysql-5.1.34
Then stop all running MySQL Process:
$hell> /etc/init.d/myst stop
Then find all the MySQL packages you need to remove by running:
$hell> rpm -qa | grep -i '^mysql-'
Then uninstall each e.g.:
$hell> rpm -e MySQL-client-community-5.1.29-0.rhel4
Then re-install all the new ones you just downloaded e.g.:
$hell> rpm -i MySQL-shared-community-5.1.34-0.rhel4.i386.rpm
Then run the MySQL upgrade program to do the final checks and upgrade the MySQL system database if necessary:
$hell> /usr/bin/mysql_upgrade -uroot -p
And that’s it, all should work nicely again 
Remember though, you shouldn’t upgrade between major versions that aren’t in sequence. i.e. Don’t upgrade from MySQL from 4.0 to 5.1 as the additions to the software made in 4.1, 5.0, etc. can be lost by skipping these intermediate upgrades.
I subscribe to the Security Focus web application security mailing list and as a result I get all kinds of mail through on various topics from across the field.
Recently (last 6-9 months or so) a few have caught my eye that I thought would be useful to forward to friends and colleagues also in the business, and similarly I thought I’d summarise those items here too:
Anti-fraud Image Solutions from Gunter Ollmann
Checklist for testing web apps from Paul Johnston
| Omaha Poker |