Technical Digital Media Consultant
MySQL
MySQL Seconds Behind Master Weirdness
0
So, I'm running show slave status and I'm seeing that my slave is around 14 days behind my master, which is weird since I the server's set to expire logs after 3 days.
After a bit of digging I found this: http://bugs.mysql.com/bug.php?id=2826
So I checked the system time on the slave and it was way out of whack with the current date.
So, I restarted ntpd (which causes it to re-sync), stopped and restarted the slave and there we go problem fixed.
Random fact of the day
0If you use an unsigned MySQL int to count seconds you can count up to roughly 136 years before the field is full 
On another note I also got my Google Wave invite today, so if anyone wants to connect just add chrisjohndean@googlewave.com to your list!
Master Master Replication on MySQL 5.1
0Not 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
Bloody MySQL Bugs
0I’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!!!
MySQL 5.1.34 Upgrades on CentOS 4 – ho hum
6So, 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.