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...]

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...]

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...]

Postgres replication for MySQL DBA's

Working a with Postgers and MySQL replication I noticed that many MySQL DBA's have hard time grasping Postgres replication and how it works,  you may ask why would you need to know how to setup and use Postgres replication - but lets face it Postgres is becoming ever more popular and many MySQL shops also have Postgres databases. Overall a lot of startups are using a collection of databases  that serve various purposes. I"ll attempt to explain Postgres replication in plain English and use MySQL terms to help adsorb the seemingly complex subject. 
Postgres  "streaming replication" was introduced since Rel. 9.0 of Postgres and is a very useful addition that many of us were waiting for years, personally I strongly believe that the ack of replication kept Postgres from wide adoption unlike MySQL
  [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...]

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.

What and Why?

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.

Build new Replicas without Downtime

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:

mysqlfailover

“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...]

As we all settle into 2012, I remember the great saying “Stay hungry, stay foolish.”

The famous“Stay hungry, stay foolish” reference is from the last Whole Earth Catalog and also made popular via the Steve Jobs 2005 Commencement Address at Stanford, for those that do not know. Computers today would be a very different place without the numerous people that did stay hungry and  foolish as it all came together.  How does that apply to the DBA of today?

Hopefully we are all still hungry but not foolish with our data. MySQL is running most of the databases that power the web these days and we cannot afford to be foolish with that data.  When it comes to data



  [Read more...]

First of all, I wish you a happy new year.
Many things happened last year, it was really exciting to be involved in the MySQL ecosystem.
I hope this enthusiasm will be increased this year, up to you !

To start the year, I propose the MySQL[plus] Awards 2011
It will only take 5 minutes to fill out these polls.
Answer with your heart first and then with your experience with some of these tools or services.

Polls will be closed January 31, so, vote now !
For “other” answers, please,  let me a comment with details.

Don’t hesitate to submit proposal for tools or services in the comments.

  [Read more...]

As the saying goes, we at Severalnines have been walking on several clouds this year, 9 to be precise!

Today, we are proud to say that we are on walking on Cloud 9!

And in the spirit of celebration, we would like to announce our:

Top 9 Clouds of the Year 2011 for Severalnines

Cloud 1 – releasing ClusterControl™ - our first commercial product in April!

ClusterControl™ is our flagship product. It enables developers and database administrators to Deploy, Manage, Monitor and Scale their clustered database platforms, free from the complexity and learning curves

  [Read more...]

Scalability is about application, architecture and infrastructure design, and careful management of server components.

1. Object Relational Mappers

ORMs are popular among developers but not among performance experts.  Why is that?  Primarily these two engineers experience a web application from entirely different perspectives.  One is building functionality, delivering features, and results are measured on fitting business requirements.  Performance and scalability are often low priorities at this stage.  ORMs allow developers to be much more productive, abstracting away the SQL difficulties of interacting with the backend datastore, and allowing them to concentrate on building the features and functionality.

On the performance side the picture is a bit different.  By leaving SQL query writing to an ORM, you are faced with complex queries that

  [Read more...]

There are a lot of scalability challenges we see with clients over and over. The list could easily include 20, 50 or even 100 items, but we shortened it down to the biggest five issues we see.

1. Tune those queries

By far the biggest bang for your buck is query optimization. Queries can be functionally correct and meet business requirements without being stress tested for high traffic and high load. This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn't necessarily be a good use of time to tune a query for some page off in a remote corner of your site, that didn't receive

  [Read more...]