Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 82 Next 22 Older Entries

Displaying posts with tag: mysql replication (reset)

MySQL Replication – Creating a New Master/Slave Topology with or without Virtual Machines
+4 Vote Up -0Vote Down

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...]
MySQL 5.6 GA – Replication Enhancements
Employee +5 Vote Up -0Vote Down

Multi-Threaded Slave

MySQL 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:

  • Global Transaction Identifiers (GTIDs) – a unique identifier that is used accross your replication topology to identify a transaction. Makes setting up and managing your cluster (including the promotion of a new master) far simpler and more reliable.
  • Multi-threaded slaves (MTS) – Increases the performance of replication on the slave;
  [Read more...]
Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication
+4 Vote Up -0Vote Down

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...]
Percona MySQL University in Montevideo and Buenos Aires
+2 Vote Up -0Vote Down

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...]
MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves – Part One
+3 Vote Up -1Vote Down

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
+0 Vote Up -0Vote Down

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...]
Retrieving List of MySQL Users and Grants with Perl
+0 Vote Up -0Vote Down

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...]
MySQL Replication – Multi-Threaded Slaves (Parallel Event Execution)
+3 Vote Up -1Vote Down

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...]
MySQL 5.6 Delayed Replication – Making a Slave Deliberately Lag Behind a Master
+2 Vote Up -0Vote Down

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...]
PARALLEL SLAVE in MySQL REPLICATION
Employee +5 Vote Up -0Vote Down

Overview


MySQL replication does not always scale up well. A common reason for
that is the single-threaded nature of the slave server applier.
Indeed the performance race between the master and the slave
has been unfair. Contrary to the master, which executes transactions
concurrently by multiple threads, the standard MySQL slave is limited
to install changes sequentially by the only thread.
However, if logical partitioning of data per database
takes place, the 5.6 server's Multi-Threaded Slave (MTS) framework may
be found helpful. It allows to install changes done to different
databases in parallel.
For instance the simplest use case would be when the master server has
just two databases and transactions against the master server update
only one of them at a time. Those transactions when












  [Read more...]
Replication and auto-failover made easy with MySQL Utilities
Employee +3 Vote Up -1Vote Down

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:

  • Set up replication from a single master to multiple slaves
  • Automatically detect the failure of the master and promote one of the slaves to be the new master
  • Introduce the old master back into the topology as a new slave and
  [Read more...]
Improving MySQL Slave Performance with Batch Operations
Employee +0 Vote Up -0Vote Down


The problem

Sometime back a member of the MySQL support team informed us that the slaves are very sluggish, when he tried to run a delete query on the master. "What may be the problem?" we wondered. This is in RBR and its pretty usual that we have slower slaves when using RBR as compared to SBR. We requested for some more details and found that it is something else. It's a problem when a lot of rows are getting modified in a table without PRIMARY KEY.

"UPDATE t1 set a = a+1;" and other queries such as this are generally found to make slaves sluggish if the table does not have proper index. Is it a problem with the fact that a lot of data is being transferred over the network? After all the total amount of data that will be transferred over the network will be more than twice (before and after image + some



  [Read more...]
MySQL 5.6 Replication – webinar replay
Employee +4 Vote Up -0Vote Down

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...]
Upcoming conferences to learn more about MySQL Cluster & Replication
Employee +5 Vote Up -0Vote Down

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...]
Upcoming webinar: MySQL 5.6 Replication – For Next Generation of Web and Cloud Services
Employee +5 Vote Up -0Vote Down

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:

  • High performance with Multi-Threaded Slaves and
  [Read more...]
Bulletproofing MySQL replication with checksums
+0 Vote Up -0Vote Down

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...]
    5 Ways to fortify MySQL replication
    +1 Vote Up -2Vote Down

    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...]
    New MySQL 5.6 Replication Utilities – mysqlfailover and mysqlrpladmin
    +3 Vote Up -0Vote Down

    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...]
    “Stay hungry, stay foolish” ...... but have a backup :)
    +2 Vote Up -0Vote Down
    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...]
    MySQL 5.6 Replication Enhancements – webinar replay
    Employee +6 Vote Up -0Vote Down

    Global Transaction IDs - simplifying replication management

    The replay has now been released for the MySQL 5.6 replication enhancements replay where you can get the latest information on all of the great new content that has been included in the MySQL 5.6 Development Releases as well as some features that are still being developed. You can view the replay here (http://www.mysql.com/news-and-events/on-demand-webinars/display-od-677.html" target="_blank).

    Some of the topics discussed are:

    • Enhanced data integrity: Global Transactions
      [Read more...]
    Vote for MySQL[plus] awards 2011 !
    +5 Vote Up -0Vote Down

    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...]
    Walking on Cloud 9
    +1 Vote Up -0Vote Down

    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...]
    What’s new in MySQL 5.6 Replication – free webinar
    Employee +6 Vote Up -0Vote Down

    Global Transaction IDs - simplifying replication management

    There will be a webinar this Wednesday where you can get the latest information on all of the great new content that has been included in the MySQL 5.6 Development Releases as well as some features that are still being developed. As always, the webinar is free but you need to register here (http://www.mysql.com/news-and-events/web-seminars/display-677.html" target="_blank) in advance. Even if you can’t attend the live event it’s worth registering so that you get sent the replay.

    Some of the topics

      [Read more...]
    MySQL HA Solutions – webinar replay
    Employee +1 Vote Up -0Vote Down

    If you were unable to attend the live webinar (or you want to go back and listen to it again) then it’s now available to view on-line here.

    Databases are the center of today’s web and enterprise applications, storing and protecting an organization’s most valuable assets and supporting business-critical applications. Just minutes of downtime can result in significant lost revenue and dissatisfied customers. Ensuring database highly availability is therefore a top priority for any organization. Tune into this webcast to learn more.

    The session discusses:

      [Read more...]
    Enhanced conflict resolution with MySQL Cluster active-active replication
    Employee +4 Vote Up -0Vote Down

    Detecting conflicts

    Part of the latest MySQL Cluster Development Milestone Release (MySQL Cluster 7.2.1 – select the “Development Release” tab at http://dev.mysql.com/downloads/cluster/#downloads) is a couple of enhancements to the conflict detection and resolution mechanism for active-active (multi-master) replication. While MySQL Cluster has had conflict detection for years it has now been made much more complete and a lot easier to

      [Read more...]
    London 18th October: Oracle Technology Network MySQL Developer Day
    Employee +5 Vote Up -0Vote Down

    I will be presenting on MySQL Cluster and MySQL Replication at the Oracle Technical Network MySQL Developer day in London on Tuesday, 18 October 2011 (8:30 AM – 4:00 PM). It’s free but you need to register here while there are still places (attendance has been extremely high at other locations).

    The MySQL Developer Day is a one-stop shop for you to

      [Read more...]
    5 Things That Are Toxic to Scalability
    +0 Vote Up -0Vote Down
    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...]
    5 Ways to Boost MySQL Scalability
    +0 Vote Up -0Vote Down

    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...]
    slow queries are not logged if they're replicated slow queries
    +1 Vote Up -0Vote Down
    I was debugging a performance problem and was shocked that while overnight we had the same slow query run on a replica, it wasnt logged to the slow log that I set up the day prior.
    It seems slow queries that are executed from the replication SQL thread are not logged. I did not know this. This is how I confirmed this is the case:

    *****SLAVE SERVER*****enable slow log and confirm its working
    mysql> set @@global.slow_query_log=ON;Query OK, 0 rows affected (0.00 sec)
    mysql> set @@global.long_query_time=1;Query OK, 0 rows affected (0.00 sec)
    mysql> flush logs;Query OK, 0 rows affected (0.12 sec)
    mysql> select sleep(10);+-----------+| sleep(10) |+-----------+| 0 |+-----------+1 row in set (10.00 sec)
    mysql> Ctrl-C -- exit!Abortedslave:~# cd /databases/logsslave:/databases/logs# tail mysqld-slow.log







      [Read more...]
    MySQL Development Milestone 5.6.2: Taking MySQL Replication to the Next Level
    Employee_Team +6 Vote Up -0Vote Down
    The announcements at the O'Reilly MySQL and Oracle Collaborate conferences last week mark an exciting milestone in the development of MySQL replication.  The purpose of this blog is to summarize those announcements and provide links to further reading, published directly by the engineers who have been working hard to take MySQL replication to the next level.

    A number of significant replication enhancements were released as part of the MySQL 5.5 GA just a few months ago. 
    We are always listening to our customers and the community.  And, based on their input, the MySQL engineering team has continued to rapidly evolve


      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 82 Next 22 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.