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.
If 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 firstname.lastname@example.org to your list!
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.