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 中文
Showing entries 1 to 7

Displaying posts with tag: binary logs (reset)

Binary log file size matters (sometimes)
+2 Vote Up -0Vote Down

I used to think one should never look at max_binlog_size, however last year I had a couple of interesting cases which showed that sometimes it may be very important variable to tune properly. I meant to write about it earlier but never really had a chance to do it. I have it now!

One of our customers was complaining that the database would lock up at random times and then it would go back to normal in just a few seconds. This was MySQL 5.0 running MyISAM/InnoDB mix, not heavily loaded. We used pt-stalk (at that time it was aspersa stalk) trying to figure out what is happening, however all we found was a spike in writes, many queries piled up and looking at the system process list it was quite obvious that page flush daemon was acting out. I/O Pattern was rather awkward – here is an

  [Read more...]
Setting up Master-Slave Replication with MySQL
+1 Vote Up -1Vote Down
Replication enables data from one MySQL server to be replicated on one or more other MySQL servers. Replication is mostly used as scale-out solution. In such a solution, all writes and updates take place on the master server, while reads take place on one or more slaves. This model is actually known as master-slave replication and this is the kind of replication that I will be setting up in this post.
Statement-based vs Row-based Replication
+2 Vote Up -0Vote Down
Replication as most people know it, has mostly been SQL statement propagation from master to slave. This is known as "statement-based" replication. But there is also another kind of replication that is available, "the row-based replication" and that has quite a lot of benefits. In this post I intend on highlighting the advantages and disadvantages of both the types of replication to help you choose the best one. I also follow up with my own recommendation.
Purging binary logs.
+2 Vote Up -0Vote Down
Being a MySQL DBA , one faces a common issue in replication environment -> Disk space issue on master, since the number of binary logs have increased.Now, one of the solution to this would be using expire_logs_days parameter in your mysql config file. But what if, the slave is lagging by few hours or if the slave is broken since few days and the binary logs are removed due to the parameter set. Whenever the salve comes up, it will go bonkers, knowing that the binary log where it last stopped no more exists.
I faced this issue a couple of times until I decided to automate it using a script. Herewith I am attaching the URL to my python script which can run regularly in cron.Features :
  • Checks the slaves connected to the master (I have limit it to 3 for now.)
  • Checks the last binary log file which is being used by the

  [Read more...]
Just how useful are binary logs for incremental backups?
+1 Vote Up -0Vote Down

We've written about replication slaves lagging behind masters before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup.  Let me make up some numbers for the purposes of this example:

  • We have 2 Servers in a Master-Slave topology.
  • The database size is 100 GB (same tables on each).
  • The slave machine barely keeps up with the master (at 90% capacity during peak, 75% during offpeak)
  • The peak window is 12 hours, the offpeak window is 12 hours.

Provided that the backup method was raw data files, it shouldn't take much more than 30 minutes to restore 100GB (50MB/s), but to replay one day of binary

  [Read more...]
How to use MySQL binlogs to undo a DROP statement
+0 Vote Up -0Vote Down
This post is for people who are trying to roll back unwanted modifications to their MySQL database. You cannot use the binary logs to undo unwanted changes to your data. The binary logs are for redoing statements, not undoing them. If you have a backup, you may be able to restore the backup and [...]
Mycat beta 0.3.0 released
+0 Vote Up -0Vote Down
After a very long time distracted with other projects, I finally added the third component of the MyCAT project: binlog_mon, a binary log manager for MySQL.

The main feature of this tool is that it has two disk usage thresholds which determine when it purges your binary logs:
  • a lower, "nominal", threshold above which binary logs will be purged if-and-only-if none of the replication slaves are still reading it,
  • and a higher, "critical", threshold at which the behavior is configurable.
It can simply send you an alert if disk usage is above critical and the oldest file is still needed - or it can purge 1 file, all files until usage below critical, or all files until usage below nominal levels. (Other options could be added fairly easily.) The "critical" option is so configurable because



  [Read more...]
Showing entries 1 to 7

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.