I completed an upgrade of Debian Lenny to Squeeze on a production database server over the weekend. It went quite well and I had zero downtime thanks to my secondary database servers running in-place. One of the biggest benefits to running Squeeze is that MySQL runs at version 5.1.49. Lenny only supports up to MySQL 5.0.
Row-based-replication is safer to replicate data to other servers since all changes are replicated. Prior to MySQL 5.1.14, updates to the mySQL database were not replicated. They were updated via statements (e.g. GRANT, REVOKE). This can potentially cause data-consistency between the master and slave servers, if one runs a INSERT and SELECT (w/o ORDER BY) as the order of the results will be different. If you had a SQL statement on the MySQL database that ends up erroring out, your slave connection to the master server would stop.
DISCLAIMER: This is for informational purposes only. We are providing absolutely no warranty for this information. Use this information at your own risk. Always perform upgrades on a test server before production and always have a full backup of the system.
Before performing the upgrade on the master database server, make sure you shutdown all the slave replication first. Run this command on all MySQL slave servers that connect to the master server.
mysql -u root -p
stop slave;
After the replication is turned off, shutdown the master MySQL server.
/etc/init.d/mysql stop
Backup the MySQL configuration file (my.cnf) along with any critical configuration files in /etc/
To upgrade Debian Lenny to Squeeze, follow the instructions on
http://www.cyberciti.biz/faq/apt-get-upgrade-debian-lenny-5-to-debian-6-squeeze/
Its basically involves changing the /etc/apt/sources.list to reflect the change from Lenny to Squeeze, running apt-get update, apt-get install, and finally the dist-upgrade. I recommend having console access just in case network connectivity is lost during the upgrade.
Upgrading Lenny to Squeeze does not automatically upgrade MySQL to 5.1. There will be separate package for MySQL server 5.1. I suggest backing up all databases and making a separate copy of the MySQL data directory prior to the next step.
apt-get install mysql-server-5.1
To enable Row-Based-Replication on MySQL, you will need to ensure my.cnf has the following line:
binlog_format = row
Start the MySQL server (if it wasn’t restarted in the upgrade process)
/etc/init.d/mysql start
If MySQL starts back up and errors out, it might be the following deprecated setting in your my.cnf. Comment out the skip-bdb line.
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
#skip-bdb
Run the MySQL upgrade command in shell to validate the upgrade on the master server:
mysql_upgrade -p
Once the MySQL server is running properly, start the replication on the MySQL slave servers. It would also be a good time to upgrade them to MySQL 5.1 if they haven’t been upgraded already.
mysql -u root -p
start slave;
show slave status\G
Check to see the Seconds_Behind_Master is 0 and the LAST_IO_Error is empty on the slave server.
Additional Sources:
http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html#replication-sbr-rbr-rbr-advantages
Pingback: Al