What could go wrong? If the way MyISAM changed the format of how something is stored in their files, it will break on 5.1. This will render your stats database on the master corrupt and unusable.
Even if the initial load went fine, it doesn't mean something won't come along later with the result listed above...a corrupt database. It's a risk, and if it can be avoided, I personally would do so.
And in your case, it can be avoided by dumping the data using mysqldump quickly and then load into the 5.1 instance.
Do not disable the triggers on your slaves, and do not configure your slaves to skip errors.
When replication is set up correctly with consistent data sets and identical configurations, including tables and their data, views, triggers, stored functions, and stored procedures, it just works. If it isn't "just working," then it was already not consistent in one of these ways. The duplicate key errors are a red flag that your data is not identical, and you don't want to suppress this.
If you aren't already, you should be using binlog_format = MIXED unless you have a specific reason not to. The default value prior to MySQL 5.6 was STATEMENT but this was largely for legacy reasons.
Using mixed logging allows the optimizer on the master to choose the best way to log each change to the master's data -- either by:
- logging the literal and exact query your client sent to the master, so that the slaves can execute that same query, thereby modifying their data (statement based logging), or
- logging the specific rows that were changed on the master so that the slave can change those same rows in the affected table without regard to the actual query that caused the changes (row based-logging).
If a statement is determined to be "unsafe" for statement-based logging (meaning that it could potentially cause the data to diverge from its initial identical state), it will be automatically logged as row-based, if you're using MIXED mode. Examples of this would be statements involving calls to non-deterministic functions like UUID() which would return different values on master and slave. Row-based logging handles this without a problem, since it passes the literal values across.
You might think NOW() might also be such a function, and that a similar limitation might apply to automatic timestamp columns, if the server clocks are not in sync, but that's not the case -- because with each binary (replication) log entry, the master logs what its current timestamp was at the time the query was executed -- allowing the slave to have a "pseudo-system clock" to actually fudge-in the master's clock's value at the time that statement was executed when needed. This log entry, incidentally, is how the slave calculates "seconds behind master," by comparing its system clock to the logged timestamps. (The exception to this is when the slave has executed everything in its relay logs, it always shows "0" seconds behind, because it doesn't know whether the former logs were delayed by clock skew or by an actual replication lag -- so if it has run out of things to execute, it is no longer technically "behind").
If a query is logged using row-based logging, then any data changed by triggers are also logged as row-based log entries, and the triggers on the slave will automatically not fire.
On the other hand, if the same query is logged using statement-based logging, then replication depends on the identical triggers being present on the slave, because the master will not log what the trigger did -- it only logs the query issued, and depends on the slave triggers firing exactly as they did on the master, assuming you want the data on all servers to be consistent.
With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
This behavior is by design. [...]
http://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html
The documentation often speaks of replication in what sounds like terms of either/or (statement or row) but for each query executed MIXED logging will choose the logging method on a per-query basis. You may also find statements to the effect that row-based logging is "required" in some conditions but this only means that it must be available (i.e., via MIXED); it does not mean that the server must be configured for ROW. DDL, of course, is always logged as statements, regardless of the binlog format.
Best Answer
I upgraded an old server from 5.1.34sp1 to 5.1.67 some time back... that was about the same amount of "jump" in minor versions as you are contemplating. In this case, the system worked without incident.
Within the same major release of MySQL (e.g. 5.1.x to 5.1.y), assuming the versions involved are after the GA milestone, upgrading should be relatively straightforward.
Nevertheless, a complete backup is essential.
Importantly, if you haven't tried restoring your backup on a different server, then for all practical purposes, you don't actually have a backup.
Giving you the "ssh commands" (actually, shell commands) to do the upgrade isn't possible, since there are variations in installation that make it impossible to provide a list of "type these things and it will work" commands. Assuming your current installation is using an Oracle tarball binary distribution, you'll need to download and extract the binaries into a new directory, stop the server, move or relink the data files where the new copy of MySQL can see them, and rename a couple of directories or change a couple of symlinks. There's not really a concept of "uninstalling" the old version, since the old version can live harmlessly on your server in a different directory. You just move it out of the way, and if the upgraded version doesn't start up properly, will often still be able to start the old version back up, if needed. Eventually, you can remove the old directory, with caution, to avoid removing the wrong things.
You'll need to read the documentation.
http://dev.mysql.com/doc/refman/5.1/en/upgrading.html
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html
After you do the upgrade, don't forget to run the
mysql_upgradeutility. This utility does not upgrade the version of your server; instead, it upgrades your data files to be fully compatible with the new version you've just installed. You run this after you do the upgrade, and after you restart the MySQL server daemon.http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html
Depending on how well the server was maintained in the past, if it was upgraded in the past, then it's possible that this was not done when it was upgraded previously. If so, it would actually be a good idea to run it before doing the upgrade, so that all of your structures are all consistent. Two important points, here: The version of the
mysql_upgradeutility that you run needs to be the version bundled with the currently running version of the server, and you need to make your backup before you run this. If you have some serious latent structural corruption that can't be automatically fixed, this can be theoretically be uncovered bymysql_upgradeand the server may shut down to prevent the detected corruption from causing even more serious problems. This is becausemysql_upgradeusesCHECK TABLEas part of its work, and:This is not a likely scenario, but it's a documented possibility of which you need to be aware.
As a general rule, the slave server should be upgraded before the master, not after. This is somewhat less critical when remaining within 5.1 (if you were upgrading to 5.5, you absolutely need to upgrade the slaves first) but upgrading the master last is most definitely the most correct approach.
Upgrading the slave first will also give you a better idea of what to expect, and if the slave's data is consistent with the master, you can always promote it to master if your upgrade of the actual master fails.