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 91 to 120 of 32789 Next 30 Older Entries
How to Configure ClusterControl to run on nginx
+0 Vote Up -0Vote Down
July 10, 2014 By Severalnines

ClusterControl uses the Apache HTTP Server to serve its web interface, but it is also possible to use nginx. nginx + PHP fastcgi is well-known for its capabilities to run on a small memory footprint compared to standard Apache + PHP DSO.

 

In this post, we will show you how to run ClusterControl on nginx web server by swapping out the default Apache web server installed during the initial deployment. This blog post does not mean that we officially support nginx, it just an alternative way that a portion of our users have been interested in. For instance, Phil Bayfield

  [Read more...]
Recover after DROP TABLE. Case 1
+0 Vote Up -0Vote Down

Introduction

Human mistakes are inevitable. Wrong “DROP DATABASE” or “DROP TABLE” may destroy critical data on the MySQL server. Backups would help however they’re not always available. This situation is frightening but not hopeless. In many cases it is possible to recover almost all the data that was in the database or table.
Let’s look how we can do it. The recovery plan depends on whether InnoDB kept all data in a single ibdata1 or each table had its own tablespace . In this post we will consider the case innodb_file_per_table=OFF. This option assumes that all tables are stored in a common file, usually located at /var/lib/mysql/ibdata1.

Wrong action – table deletion

For our scenario we will use


  [Read more...]
A common problem
+0 Vote Up -0Vote Down

With the majority of the clients where I perform server audits I find an issue where both the host server and MySQL are not configured with anything other than the default values for the open files limit. This can cause a system stalling event where users are not able to access the server. What do we do to keep this from happening? I'm glad you asked! But first a little background...

 

The open_files_limit configuration value is used to specify the number of open files a user is allowed to have at any one time. This is done both in the operating system, and in the MySQL configuraition. The reason that it matters to MySQL is that every table consists of multiple files. When the process user, typically mysql, is accessing tables for a query then really the user is accessing files. Since there can be hundreds of users at any one times the number of open files can quickly add up. With a default of

  [Read more...]
4 Things To Know About MySQL Prepared Statements
+0 Vote Up -0Vote Down

While writing our TCP stream reassembly and MySQL protocol reverse-engineering algorithms, a few finer points of the MySQL protocol and internals came up. None of this is new information, but you may not have stumbled upon it before.

Prepared Statement IDs

Prepared statements are generated by the COM_STMT_PREPARE protocol command, with the statement text as an argument. The server prepares the statement, and assuming all goes well, returns a statement ID. This is a number that the client needs to remember for future executions of the prepared statement. The ID increments with each new prepared statement.

Statement Scope

The statement is scoped to the connection that created it. It's not visible or valid for any other connection. Statement IDs are private to the connection too, so server-wide you will not have

  [Read more...]
TokuDB gotchas: slow INFORMATION_SCHEMA TABLES
+0 Vote Up -0Vote Down

We are using Percona Server + TokuDB engine extensively in Percona Cloud Tools and getting real usage operational experience with this engine. So I want to share some findings we came across, in hope it may help someone in their work with TokuDB.

So, one problem I faced is that SELECT * FROM INFORMATION_SCHEMA.TABLES is quite slow when I have thousands tables in TokuDB. How slow? For example…

select * from information_schema.tables limit 1000;
...
1000 rows in set (18 min 31.93 sec)

This is very similar to what InnoDB faced a couple years back. InnoDB solved it by adding

  [Read more...]
Check for MySQL slave lag with Percona Toolkit plugin for Tungsten Replicator
+0 Vote Up -0Vote Down

A while back, I made some changes to the plugin interface for pt-online-schema-change which allows custom replication checks to be written. As I was adding this functionality, I also added the --plugin option to pt-table-checksum. This was released in Percona Toolkit 2.2.8.

With these additions, I spent some time writing a plugin that allows Percona Toolkit tools to use Tungsten Replicator to check for slave lag, you can find the code at

  [Read more...]
MySQL Community Dinner 2014
+1 Vote Up -0Vote Down
Hello all,

I hope you're doing well. After the successful event last year it's a no brainer to arrange another community dinner when we're all together again in London this November. We had a fantastic turn-out for the meal last year, seating over 100 MySQLers in a venue whom were expecting only 60. So this year join the MySQL community again for a bite to eat and a beverage or two and discuss life, love and data.

The When
Monday, November 3rd 2014

The Where 
Masala Zone, Covent Garden

The Why
We have a vibrant community surrounding the MySQL ecosystem. Whether you want to talk about the meaning of life with Colin Charles or find out how Facebook take their logical backups even quiz Shlomi Noach about where thinks up his latest and greatest MySQL tools; why not do it










  [Read more...]
FromDual.en: FromDual Performance Monitor for MySQL 0.9.3 has been released
+0 Vote Up -0Vote Down
Taxonomy upgrade extras: mysqlperformancemonitormonitoringmpmmaasperformance monitor

FromDual has the pleasure to announce the release of the new version 0.9.3 of its popular Database Performance Monitor for MySQL, Galera Cluster, MariaDB and Percona Server mpm.

This release contains various minor bug fixes and improvements.

You can download mpm from

  [Read more...]
Anemometer: MySQL Slow Query Monitor
+0 Vote Up -0Vote Down

Anemometer is a MySQL slow query monitoring tool. It's used to analyze/visualize slow query log, collected from MySQL instance to identify the problematic queries. Also, makes it easier to figure out what to optimize and how to track performance over time.

Required:

  • MySQL database to store query
  • pt-query-digest from Percona
  • slow query log from MySQL server
  • Web server with php

Setup:

Configure, webserver with php, get aneommeter code from github and place into the document root of the webserver.

$ sudo git clone git://github.com/box/Anemometer.git
  [Read more...]
Fine-Tuning MySQL Full-Text Search with InnoDB
+0 Vote Up -0Vote Down

If you are using FULLTEXT indexes in MySQL and plan to switch from MyISAM to InnoDB then you should review the reference manual section on Fine-Tuning MySQL Full-Text Search to see what configuration changes may be required. As I mentioned in yesterday's post when comparing query results on my database with FULLTEXT indexes in MyISAM versus InnoDB I got different results. Specifically, the InnoDB tables were returning fewer results for certain queries with short FULLTEXT search terms. Here's an example of a query that returned fewer results on InnoDB:

select id from flite.ad_index where match(name,description,keywords) against('+v1*' IN BOOLEAN MODE);

  [Read more...]
Keeping your data work on the server using UNION
+0 Vote Up -0Vote Down
I have found myself using UNION in MySQL more and more lately. In this example, I am using it to speed up queries that are using IN clauses. MySQL handles the IN clause like a big OR operation. Recently, I created what looks like a very crazy query using UNION, that in fact helped our MySQL servers perform much better.

With any technology you use, you have to ask yourself, "What is this tech good at doing?" For me, MySQL has always been excelent at running lots of small queries that use primary, unique, or well defined covering indexes. I guess most databases are good at that. Perhaps that is the bare minimum for any database. MySQL seems to excel at doing this however. We had a query that looked like this:

select category_id, count(*) from some_table
where
    article_id in (1,2,3,4,5,6,7,8,9) and



  [Read more...]
TIMESTAMP Columns, Amazon RDS 5.6, and You
+1 Vote Up -0Vote Down

This comes from an issue that I worked on recently, wherein a customer reported that their application was working fine under stock MySQL 5.6 but producing erroneous results when they tried running it on Amazon RDS 5.6. They had a table which, on the working server, contained two TIMESTAMP columns, one which defaulted to CURRENT_TIMESTAMP and the other which defaulted to ’0000-00-00 00:00:00′, like so:

CREATE TABLE mysql56 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ts2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
);

However, under Amazon RDS, the same table looked like this:

CREATE TABLE rds56 ( 
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT NULL, 
);

They

  [Read more...]
MySQL Central @ OpenWorld Keynotes
Employee_Team +2 Vote Up -0Vote Down

A few weeks ago we announced the availability of the MySQL Central @ OpenWorld content catalog.


We're now pleased to announce additional keynotes delivered by MySQL power users. They will follow the "State of The Dolphin" keynote address by Oracle's Chief Corporate Architect Edward Screven and VP of MySQL Engineering Tomas Ulin, and include:

High Speed Event Logging at Booking.com
Nicolai Plum, Senior Systems Architect, will provide an



  [Read more...]
Testing MySQL FULLTEXT indexes in InnoDB using pt-upgrade
+0 Vote Up -0Vote Down

As I prepare to convert some MySQL tables with FULLTEXT indexes from MyISAM to InnoDB I want to verify that running a standard production query set against the tables will return the same results with InnoDB that it did with MyISAM. Since I read Matt Lord's blog post about the document relevancy rankings used for InnoDB full-text searches I knew to expect some differences when sorting by relevancy, so I want to focus on getting the same set of rows back, mostly ignoring the order in which the rows are returned.

Percona toolkit has a tool called pt-upgrade that works well for this purpose. I used 2 test servers with a copy of my production database. On one of the servers I left the

  [Read more...]
Munin graphing of MySQL
+0 Vote Up -0Vote Down

While there are many graphing tools out there and we’ve used Munin for a while now.

The MySQL plugin for Munin had fallen out of date and the show engine innodb status output changed in 5.5 making some bits of the plugin simply not work any more. Also the show global status has some extra variables so there was a need to create new graphs.

All of these are now in the 2.1.8+ development releases of Munin.

Here are samples of the new/updated graphs.

Tables

  [Read more...]
#DBHangOps 07/10/14 -- MySQL Index Types and more!
+0 Vote Up -0Vote Down
#DBHangOps 07/10/14 -- MySQL Index Types and More!

Check out the recording below!

Hello everybody!

Join in #DBHangOps this Thursday, July, 10, 2014 at 11:00am pacific (18:00 GMT), to participate in the discussion about:

  • Indexes in MySQL
    • PRIMARY, UNIQUE, FULLTEXT and more
    • Geo-spatial
    • Hash indexes
    • Fractal Indexes (TokuDB)

Be sure to check out the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Thursday!

See all of you on Thursday!

Show notes

Indexes

Spatial index resources

  [Read more...]
MaxScale 1.0-beta is out - Happy Birthday MaxScale!
+2 Vote Up -0Vote Down
It was a year ago, on a nice Sunday night of the English Summer (apologies for the oxymoron), that Mark Riddoch came to see me and together we headed to the Vansittart Arms, our local family pub round the corner. A pint of London Pride on one side and a Honey Dew on the other were the perfect add-on to Mark’s MacBook Pro, on which Mark was showing me the 0.1 version of MaxScale. It was the result of the joint efforts of Mark’s team, Massimiliano and Vilho, who had worked hard to bring to life the first version of something that I believe will be a natural addition to clusters of  [Read more...]
Sphinx in Docker. The basics.
+0 Vote Up -0Vote Down
With an ear to the interwebs, you’ll hear a few things about Docker. Docker is an open platform for developers and sysadmins to build, ship, and run distributed applications. In this blog post, I’m going to outline a very basic example of how to use Sphinx from within a Docker container. What is Docker? This [...]
ClusterControl Module for Puppet
+0 Vote Up -0Vote Down
July 7, 2014 By Severalnines

If you are automating your infrastructure using Puppet, then this blog is for you. We are glad to announce the availability of a Puppet module for ClusterControl. For those using Chef, we already published Chef cookbooks for Galera Cluster and ClusterControl some time back.  

 

 

ClusterControl on Puppet Forge

 

The ClusterControl module initial release is available on Puppet Forge, installing the

  [Read more...]
Looking out for max values in integer-based columns in MySQL
+0 Vote Up -0Vote Down

Yay! My first blog post! As long as at least 1 person finds it useful, I’ve done my job. Recently, one of my long-term clients was noticing that while their INSERTs were succeeding, a particular column counter was not incrementing. A quick investigation determined the column was of type int(11) and they had reached the maximum value of 2147483647. We fixed this by using pt-online-schema-change to change the column to int(10) unsigned, thus allowing values up to 4294967295.

My client was now concerned about all his other integer-based columns and wanted me to check them all. So I wrote a

  [Read more...]
MySQL Cluster High Availability Through Data Replicas
Employee_Team +0 Vote Up -0Vote Down

MySQL Cluster enables high availability by storing data replicas on multiple hosts. MySQL Cluster maintains connections between data nodes by using high-speed interconnects over TCP/IP - standard or direct connections - or SCI (Scalable Coherent Interface) sockets.

To learn more about MySQL Cluster, take the MySQL Cluster training course.

This course is currently scheduled for the following locations:

 Location

 Date

 Delivery Language

 Sao

  [Read more...]
The MySQL 6.0 goodybag
+1 Vote Up -0Vote Down
After MySQL 5.1 was released work started on MySQL 5.2, but then this was renamed to MySQL 6.0. There were many new features introduced in 6.0. But then stabilizing this branch became as huge task. Eventually the decision was made to start of with a stable branch and backport the new features from 6.0. This is how many of the 6.0 features landed in 5.5 and 5.6.

So let's see which features 6.0 brought and were they landed. I'll use the What Is New in MySQL 6.0 section of the MySQL 6.0 Reference Manual for this.

  • The Falcon storage engine. This never landed anywhere as far as I know. It's not even included in the




  [Read more...]
Setting up authentication en masse
+0 Vote Up -0Vote Down

Managing many hosts is quite challenging task. There are many tools to solve the problem. My favorite is pdsh.

Running a command across a set of hosts is as simple as following:

# pdsh -w 192.168.177.[201-208] -R ssh reboot

Together with dshbak (which is a part of pdsh package) you can do even cooler things. Like, check which systems have yum:

# pdsh -w 192.168.177.[201-208] -R ssh "which yum" | dshbak -c
----------------
192.168.177.[201-203]
----------------
/usr/bin/yum 

Or which systems run older version of MySQL

# pdsh -w 192.168.177.[201-208] -R ssh "mysql -e \"SHOW VARIABLES LIKE 'version'\""  | dshbak -c
----------------
192.168.177.[201-203]
----------------
  [Read more...]
Percona XtraDB Cluster 5.6 Setup
+0 Vote Up -0Vote Down

Percona XtraDB Cluster (PXC) is a high availability and high scalability, solution for MySQL clustering. Percona Server, Percona Xtrabackup and Galera library for high availability integrated together as PXC. It provides synchronous, multi-master, and parallel replication. All the nodes of this cluster are ready to serve read/write request simultaneously.

 

VMs used for illustration. Here, I've used 1 centos node and 2 ubuntu nodes. In real world don't mix different OS. This is just for testing purpose. All nodes should be of the same configuration hardware/software.

1 Centos12        192.168.56.12           node1
2 Ubuntu13        192.168.56.13           node2
3 Ubuntu14
  [Read more...]
Abdel-Mawla Gharieb: Replication Troubleshooting - Classic VS GTID
+6 Vote Up -0Vote Down

In previous posts, I was talking about how to set up MySQL replication, Classic Replication (based on binary logs information) and Transaction-based Replication (based on GTID). In this article I'll summarize how to troubleshoot MySQL replication for the most common issues we might face with a simple comparison how can we get them solved in the different replication methods (Classic VS GTID).

There are two main operations we might need to do in a replication setup:

  • Skip or ignore a statement that causes the replication to stop.
  • Re-initialize a slave when the Replication is broke and could not be started anymore.

Skip or Ignore statement


Basically, the slave should be


  [Read more...]
Percona XtraDB Cluster 5.6 Setup
+0 Vote Up -0Vote Down

Percona XtraDB Cluster (PXC) is a high availability and high scalability, solution for MySQL clustering. Percona Server, Percona Xtrabackup and Galera library for high availability integrated together as PXC. It provides synchronous, multi-master, and parallel replication. All the nodes of this cluster are ready to serve read/write request simultaneously.

VMs used for illustration. Here, I've used 1 centos node and 2 ubuntu nodes. In real world don't mix different OS. This is just for testing purpose. All nodes should be of the same configuration hardware/software.

1 Centos12        192.168.56.12           node1
2 Ubuntu13        192.168.56.13           node2
3 Ubuntu14        192.168.56.14           node3

Adding repo's to the requisite

  [Read more...]
[Tuto] A real life Multi-Master use case with MariaDB
+1 Vote Up -0Vote Down

The goal of this tutorial is to show you how to use Multi-Master and aggregate databases with the same name but with different data from different masters.

Example:

  • master1 => a French subsidiary
  • master2 => a British subsidiary

Both have the same database (PRODUCTION) but the data are totally different:

We will start with 3 servers (2 masters and 1 slave), you can add more master if needed.

Informations
  •  10.10.16.231: first master (aka ”’master1”’) => a French subsidiary
  • 10.10.16.232: second master (aka ”’master2”’) => a British subsidiary
  • 10.10.16.233: slave (multi-master) (aka
  [Read more...]
db4free.net Relaunch
Employee +0 Vote Up -0Vote Down

Finally! The new db4free.net Website has launched and along with it comes a new (slightly stronger) server and some simplifications with the service itself.

So what is new? Of course the new website which now is up to modern standards again, using a responsive design. While the old site was only designed for desktop (no surprise if you consider it was created in 2005 and remained more or less unchanged since), the new site adopts to small screen devices like mobile phones and tablets as well. Also the look is totally different:

db4free.net old

  [Read more...]
YAML code in Markdown
+0 Vote Up -0Vote Down

My friend Brian Aker noticed my post from the other day on Ansible and image building had some things in the YAML Ansible playbooks that looked broken. Sure enough, I didn't notice when proofreading that double {{ and }}

curly braces weren't showing up! That was definite breakage but upon verification of the playbook in my repo, it was not broken and I realized that my markdown in my Jekyll-based github pages website/blog had manged the YAML I inteded to talk about.

  [Read more...]
Installing Vagrant and Virtual box on Ubuntu 14.04 LTS
+0 Vote Up -0Vote Down

Vagrant is an open source tool for building complete virtual development environment. Very often, test environment is required for testing latest release and new tools. Also, reduces the time spent in re-building OS. By default, it uses virtualbox for managing the Virtualization. Vagrant act as a central place configuration for managing/deploying multiple reproducible virtual environment with the same configuration.

Installing Virtualbox:

$ sudo apt-get install virtualbox

Installing Vagrant:

$ sudo apt-get install vagrant

Install the dkms package to ensure that the VirtualBox host kernel modules (vboxdrv, vboxnetflt and vboxnetadp) are properly updated if the

  [Read more...]
Previous 30 Newer Entries Showing entries 91 to 120 of 32789 Next 30 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.