I was rooting through past blog entries and I stumbled accross a draft post on setting up multi-master (update anywhere) asynchronous replication for MySQL Cluster. The post never quite got finished and published and while the material is now 4 years old it may still be helpfull to some and so I’m posting it now. Note that a lot has happened with MySQL Cluster in the last 4 years and in this area, the most notable change has been the Enhanced conflict resolution with MySQL Cluster active-active replication feature introduced in MySQL Cluster 7.2 and if you’re only dealing with a pair of Clusters, that’s your best option as it removed the need for you[Read more...]
On 17th October I’ll be running a hands-on workshop on the various technologies available to provide High Availability using MySQL. The workshop is being held on 17th October (the day before the All Your Base conference) in Oxford (UK). The cost is £250 + VAT and you can register here.
This workshop provides an introduction to what High Availability (HA) is; what technology options are available to achieve it with MySQL and how to actually implement your own HA solutions. The session will be a mixture of presentations,[Read more...]
With the General Availability of the standalone MySQL Utilities it now makes sense to use these to simplify (and optionally automate) your MySQL Replication and High Availability solutions. In light of that, 4 of our MySQL white papers have been updated to reflect the new opportunities:
MySQL Guide to High Availability Solutions (http://www.mysql.com/why-mysql/white-papers/mysql-guide-to-high-availability-solutions/" target="_blank). Data[Read more...]
With the release of MySQL Utilities 1.3.4, the standalone (not bundled with the MySQL WorkBench GUI) package is now Generally Available and fully supported. This post will focus on a very important change (the ability to run as a daemon rather than in a terminal) to the mysqlfailover utility which allows you to build a light-weight HA database solution using MySQL Replication.
For a general overview of MySQL Utilities,[Read more...]
On Thursday, Dr Charles Bell and I will be presenting a webinar on MySQL Utilities; there will be a heavy focus on what you can acheive with them and how you should use them. As well as listening to the presentation, this is a great chance to get your questions answered by the experts (Israel Gomez from the engineering team will also be on-line to help with the questions). As always, the webinar is free but you should register in advance here (http://www.mysql.com/news-and-events/web-seminars/focus-on-mysql-utilities/" target="_blank). If the time isn’t convenient, register anyway and you’ll be sent a link to the replay when[Read more...]
Update – the recording of this webinar is now available here.
This Wednesday (27th March) Mat Keep and I will be presenting a free, live webinar on MySQL 5.6 Replication. You need to register here (http://www.mysql.com/news-and-events/web-seminars/mysql-5-6-replication-enabling-next-generation-of-web-mobile-social-and-cloud-services/" target="_blank) ahead of the webinar – worth doing even if you can’t attend as you’ll then be sent a link to the replay when it’s available. We’ll also have some of the key MySQL replication developers[Read more...]
In my last few posts, I wrote about “How to install MySQL replication using GTID’s” (Part One, Part Two). In this post, I will show you how to install MySQL 5.6 and set up replication between two MySQL servers the “old fashioned way” using the binary log and binary log position.
I am going to create some virtual machines instead of using individual servers. But, you can[Read more...]
Multi-Threaded SlaveMySQL 5.6 has now been declared Generally Available (i.e. suitable for production use). This is a very exciting release from a MySQL replication perspective with some big new features. These include:
In my last two posts, I wrote about setting up replication with MySQL 5.6 using Global Transaction Identifiers. Even when I set up replication “the old-fashioned way“, one thought always enters my mind – did all of the data copy over to the slave? And, even after the master/slave has been running for a while, I am always wondering if the data in the slave matches the master. Or did the change that I made to that table make it over to the slave? It is probably more of a case of paranoia on my part, as[Read more...]
Following our Percona MySQL University event in Raleigh,NC Percona MySQL University comes to South America! We’ll have a Full day FREE MySQL Technical Educational events in Montevideo on February 5th, 2013 and Buenos Aires on February 7th.
I’m very excited to bring these events to MySQL Community in Uruguay and Argentina. This is my first trip to South America and it looks like it is going to be a lot of fun!
With Percona MySQL University events, we focus on MySQL Education for broad group of users. We’ve specially prepared talks that will be interesting for people just[Read more...]
One of my favorite features of MySQL (http://mysql.com) is replication. Replication provides you with the ability to have MySQL automatically copy data from one MySQL instance to another. There are many benefits to using replication, but I just like having an extra copy of my data on another server in case the main server crashes. But if the master crashes, I can then use the MySQL mysqlfailover script to automatically failover from the master to the slave. (see my earlier post – Using the MySQL Script mysqlfailover for Automatic Failover[Read more...]
Before I upgrade MySQL to the latest and greatest version, one of the first things that I do is export the user and grant information. In the past, I would keep all of my user information (user name, password, grants) in a text file, with the SQL for each user/grant ready to be executed on the upgraded server. I did use my own form of “mental encryption” for my passwords, so the passwords weren’t in plain English. But then I would have to decode my passwords each time before I executed the SQL statements.
When I upgrade, I usually like to dump all of the data and import it into the new version, so I have a fresh copy of the database. The MySQL server that I have is for my personal use and the data size is relatively small, so for my case it doesn’t take long to import the data.
But there were times when I[Read more...]
If you aren’t familiar with MySQL replication, “Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default – slaves need not to connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.” (From: http://dev.mysql.com/doc/refman/5.5/en/replication.html).
I use MySQL replication on my home office server. I don’t really have much data to store, but it[Read more...]
In the majority of MySQL replication scenarios, you want your slave databases to be a mirror of your master databases. You usually don’t want your slave to be behind your master by more than a few seconds – and your main goal is for your slave to always be in sync with your master. Would you ever want your slave to deliberately be a few seconds, minutes or even hours behind your master? There have been several suggestions from MySQL users over the years regarding this functionality as “feature request” (even though most of the requests were submitted as MySQL “bugs”, which was the easiest way to submit such a request).
The first request (that I could find) was by Jason Garrett, back in August of 2006, and was logged as “bug[Read more...]
If you’re a user of MySQL Workbench then you may have noticed a pocket knife icon appear in the top right hand corner – click on that and a terminal opens which gives you access to the MySQL utilities. In this post I’m focussing on the replication utilities but you can also refer to the full MySQL Utilities documentation.
What I’ll step through is how to uses these utilities to:
MySQL 5.6 Replication - Global Transaction IDs
On Wednesday (16th May 2012), Mat Keep and I presented on the new replication features that are previewed as part of the latest MySQL 5.6 Development Release.
The replay for that webinar (together with the chart deck) is now available from here (http://www.mysql.com/news-and-events/on-demand-webinars/display-od-711.html" target="_blank).
In addition, there were a huge number of great questions raised and we had a couple of key engineers answering them on-line –[Read more...]
There are a couple of conferences coming up where you can expect to learn about the latest developments in MySQL Cluster and MySQL Replication (as well as what else is happening in MySQL 5.6).
The first is the Oracle MySQL Innovation Day which is being held in Oracle HQ at Redwood Shores. This is an all-day event on 5th June – unfortunately I won’t be able to attend this one but there will be lots of great Cluster and replication sessions. If you can’t make it out to California then there will be a live Webcast. You can register here to attend in person or join the[Read more...]
MySQL 5.6 Replication - Global Transaction IDs
On Wednesday (16th May 2012), Mat Keep and I will be presenting the new replication features that are previewed as part of the latest MySQL 5.6 Development Release. If you’d like to attend then register here (http://mysql.com/news-and-events/web-seminars/display-711.html" target="_blank).
MySQL 5.6 delivers new replication capabilities which we will discuss in the webinar:
Read the original article at Bulletproofing MySQL replication with checksums
Your MySQL replications running well? You might not even know if they aren’t. One of the scariest things about MySQL replication is that it can drift out of sync with the master “silently”. No errors, no warnings.
MySQL’s replication solution evolved as a statement based technology. Instead of sending actual block changes, MySQL just has to log committed transactions, and reapply those on the slave side. This affords a wonderful array of topologies and different uses, but[Read more...]
Read the original article at 5 Ways to fortify MySQL replication
MySQL replication technology is powerful and flexible. But it doesn’t do everything perfectly all the time. You may experience trouble with the slaves falling behind the master, or want to scale horizontally by building new slaves automatically. Or you might need to build a slave without blocking the master database.
All of these goals can be achieved using some powerful tools. Here’s a quick guide to those tools and how to use them.
Something we’re sure you need to do quite often is to build new slaves. You can[Read more...]
With all of the new news coming out right now, it can be easy to miss or overlook some of the new features.
While there’s been a lot of talk about MySQL 5.6 Replication, I specifically wanted to mention the new ‘mysqlfailover’ and ‘mysqlrpladmin’ utilities.
These are two new MySQL replication utilities (results of the new Global Transaction Identifiers (GTIDs) in MySQL 5.6).
Let me quote the MySQL 5.6 Replication article for both of these utilities:
“Provides continuous monitoring of the replication topology, enabling failover to a slave in the event of an outage on the master.
The default behavior is to[Read more...]