Jean-François talks about binlog servers. Take a look here: http://blog.booking.com/mysql_slave_scaling_and_more.html
|Showing entries 1 to 30 of 1145||Next 30 Older Entries|
Jean-François talks about binlog servers. Take a look here: http://blog.booking.com/mysql_slave_scaling_and_more.html
My article on how to make the real-time processing of information from traditional transactional stores into Hadoop a reality has been published over at TDWI:
I think that getting 128-bit types into MySQL would be good. There are a few use cases for this and right now we have to work around them. That should not be necessary. While not essential they would make things easier.
The headline is easy to understand, but is this really needed?
First we need to look to see where this might be used. I can think of three different 128-bit types which are missing at the moment:
IPv6 addresses are 128-bit numbers, and having a native way to store them would be really helpful. Given this also includes an IPv4 representation then for those people who store IP addresses (client connections and other things) such a native type would be much better than the typical unsigned int or[Read more...]
We had really great webinar on Replicating to/from Oracle earliest this month, and you can view the recording of that Webinar here.
A good sign of how great a Webinar was is the questions that come afterwards, and we didn’t get through them all. so here are all the questions and answers for the entire webinar.
Q: What is the overhead of Replicator on source database with asynchronous CDC?
A: With asynchronous operation there is no substantial CPU overhead (as with synchronous), but the amount of generated redo logs becomes bigger requiring more disk space and better log management to ensure that the space is used effectively.
Q: Do you support migration from Solaris/Oracle to Linux/Oracle?
A: The replication is[Read more...]
An article about moving data into Hadoop in real-time has just been published over at DBTA, written by me and my CEO Robert Hodges.
In the article I talk about one of the major issues for all people deploying databases in the modern heterogenous world – how do we move and migrate data effectively between entirely different database systems in a way that is efficient and usable. How do you get the data you need to the database you need it in. If your source is a transactional database, how does that data get moved into Hadoop in a way that makes the data usable to be queried by Hive, Impala or HBase?
You can read the full article here: Real-Time Data Movement: The Key to[Read more...]
If you run multiple MySQL environments on multiple servers it’s a good habit to set your MySQL prompt to double check which server you are on.
however, using the MYSQL_PS1 environment variable I found this does not work under sudo (the normal way people run sudo).
I.e., the following syntax’s work.
$ mysql $ sudo su - -c mysql $ sudo su - ; mysql
but the following does not.
$ sudo mysql
The trick is actually to ensure via /etc/sudoers you inherit the MySQL_PS1 environment variable.
echo "export MYSQL_PS1=\"`hostname` [\d]> \"" | sudo tee /etc/profile.d/mysql.sh echo 'Defaults env_keep += "MYSQL_PS1"' | sudo tee /tmp/mysql sudo chmod 400 /tmp/mysql sudo mv /tmp/mysql /etc/sudoers.d
So I’ve submitted my talks for the Tech14 UK Oracle User Group conference which is in Liverpool this year. I’m not going to give away the topics, but you can imagine they are going to be about data translation and movement and how to get your various databases talking together.
I can also say, after having seen other submissions for talks this year (as I’m helping to judge), that the conference is shaping up to be very interesting. There’s a good spread of different topics this year, but I know from having talked to the organisers that they are looking for more submissions in the areas of Operating Systems, Engineered Systems and[Read more...]
The German Unix User Group (GUUG) will hold their annual conference "Frühjahrsfachgespräch" on September 23-26 this year (I know, not really "Frühjahr" anymore, but this is how it is).
The Call for Presentations is still open until May 31st. Talks can be proposed in German and English, and there are slots for longer tutorials as well.
The range of possible topics is broad, so if you think you have anything interesting to share with a very passionate and technical audience of sysadmins and developers, here are some suggestions:
Madrid MySQL Users Group will have its next meeting on the 24th of April. Details can be found on the group’s Meetup page.
We plan to talk about WebScaleSQL and I will give a short presentation on how to build WebScaleSQL RPMs on CentOS 6. The meeting will be in Spanish.
We’ve changed the place that we’ll be holding the meeting. See the Meetup URL for details. Looking forward to seeing you there.
La próxima reunión de Madrid MySQL Users Group tendrá lugar el jueves 24 de abril. Se puede encontrar más detalles en la página del grupo. Hablaremos sobre WebScaleSQL y ofreceré una breve presentación sobre como[Read more...]
Data types make all the difference in the world when you’re designing your database. The choices you make now will affect the quality of your data, as well as application performance. I’m going to focus on one issue in this article: why you should always use decimals to represent money. Let’s jump in and see why that’s true.
Let’s use a really, really simplified accounting ledger. It’s just three fields, an entry id, a customer id, and an amount, which has a float for a data type.
Create Table LedgerEntries ( LedgerEntryID Int Primary Key Auto_Increment Not Null ,CustomerID Int Not Null ,Amount Float Not Null );
Now let’s load some data.
Insert Into LedgerEntries (CustomerID, Amount)[Read more...]
Looks like this post was rather unclear. See the bottom for how to build the rpms quickly.
WebScaleSQL was announced last week. This looks like a good thing for MySQL as it provides a buildable version of MySQL which includes multiple patches from Facebook, Google, LinkedIn, and Twitter needed by large users of MySQL, patches which have not been incorporated into the upstream source tree. Making this more visible will possibly encourage more of these patches to be brought into the code sooner.[Read more...]
Hopefully by now you have already seen that we are working on Hadoop replication. I’m happy to say that it is going really well. I’ve managed to push a few terabytes of data and different data sets through into Hadoop on Cloudera, HortonWorks, and Amazon’s Elastic MapReduce (EMR). For those who have been following my long association with the IBM InfoSphere BigInsights Hadoop product, and I’m pleased to say that it’s working there too. I’ve had to adapt Robert’s original script to work with the different versions of the underlying Hadoop tools and systems to make it compatible. The actual performance and process is unchanged; you just use a different JS-based batchloader script to work with different tools.
Robert has also been simplifying some of the core functionality, such as configuring some fixed pre-determined[Read more...]
A colleague and I have been looking at GTID on MySQL recently and you may be interested in the blog post that results from that. You can see it here. http://blog.booking.com/mysql-5.6-gtids-evaluation-and-online-migration.html.
I work for a firm that’s heavily invested in SQL – a team that needs to have developers who know their way around relational databases and MySQL in particular. I want to show you how I run interviews for our development positions.
Everybody has their own methods and opinions on how to conduct technical interviews. I’ve found that I generally dislike interviews that focus either on whiteboard puzzles or obscure technical details, since they don’t really show how well the candidate is at what really matters: building functioning, quality apps. I really like running the interview like we’re talking about the design for a new product. I want to figure out the requirements, mull over the data model, and write some simple queries to make sure we can show the[Read more...]
To follow-up and describe some of the methods and techniques behind replicating into Hadoop from MySQL in real-time, and how this can be combined into your data workflow, Continuent are running a webinar with me presenting that will go over the details and provide a demo of the data replication process.
Real-Time Data Loading from MySQL to Hadoop with New Tungsten Replicator 3.0
Hadoop is an increasingly popular means of analyzing transaction data from MySQL. Up until now mechanisms for moving data between MySQL and Hadoop have been rather limited. The new Continuent Tungsten Replicator 3.0 provides enterprise-quality replication from MySQL to Hadoop. Tungsten Replicator 3.0 is 100% open source, released under a GPL V2 license, and available for download at
Coming up as a new feature in Tungsten Replicator (and written by our replicator expert Stephane Giron) is the ability to provision a new database by using data from an existing database. This new feature comes in the form of a tool called the Parallel Extractor.
The principles are very simple. On the master side:
On the slave side, the THL events are read[Read more...]
Now I’m back in the MySQL fold, I’ve got the opportunity to speak at Percona Live again. I’ve always enjoyed speaking at this conference (back when it was known by another name…), although I need to up my game and do the 6 talks I did back in 2009.
On the Tuesday afternoon, tutorials day, I’m running a half-day session with my replication colleague Linas Virbalas. This will be similar to the session I did at Percona Live London, and cover some of the more advanced content on replication, including, but not limited to:
I might even choose to demo[Read more...]
I want to teach you the difference between an inner and an outer join. We first need to think about what a join is. Simply, it’s when you combine two tables to make a new one. You’re not physically creating a new table when you join them together, but for the purposes of the query, you are creating a new virtual table. Every row now has the columns from both tables. So if TableA has columns Col1 and Col2 and TableB has columns Col3 and Col4, when you join these two tables, you’ll get Col1, Col2, Col3, and Col4. Just as with any query, you have the option of including all columns or excluding some, as well as filtering out rows.
Inner join. A join is combining the rows from two tables. An inner join[Read more...]
Unless you’ve been living under a rock, you’ve probably heard about driverless cars. Google and the large car makers have been creating cars that doen’t need a human driver. This is tremendous news. Over 30,000 people die in car crashes every year in the US. While not all of those could be prevented by removing the meatbag driver, a lot of them could. Driverless cars are the right way forward and I can’t wait until they come. Every time I see a car wreck or hear about a drunk driver, I wish we had this[Read more...]
We had a great webinar on Thursday about replicating from MySQL to Hadoop (watch the whole thing). It was great, but one of the questions at the end was ‘is there an easy way to test’.
Sadly we can’t go giving out convenient ready-to-run downloads of these things because of licensing and and other complexities, so I want to try and make it as simple and straightforward as possible by giving you the directions to complete. I’m going to be point to the Continuent Documentation every now and then so this is not too crowded, but we should get through it pretty easily.
For this to work:
Madrid MySQL Users Group will have its next meeting on 20th March. Details can be found on the group’s Meetup page.
I will be giving a presentation on MySQL replication hopefully aimed at all levels, but covering some details relevant to larger setups. The meeting will be in Spanish.
Look forward to seeing you there.
La próxima reunión de Madrid MySQL Users Group tendrá lugar el jueves 20 de marzo. Se puede encontrar más detalles en la página del grupo. Ofreceré una presentación sobre replicación de MySQL dirigido a gente de todos los niveles, pero incluirá información relevante a entornos más grandes. La presentación será en español.
Espero veros allí.
MySQL has default behavior that’s just plain wrong. I’ve covered some quirks with MySQL not null behavior and offered up using the SQL Mode strict_all_tables as a solution.
As a review, SQL Mode changes the way MySQL executes SQL statements and it’s often used to make MySQL behave. There are several switches that I would turn on by default, like only_full_group_by. You can change settings for yourself (just your connection) or for everybody (change it at the server level).
There’s another SQL Mode option, strict_trans_tables, that’s similar in intent but not in behavior to[Read more...]
Databases store information, right? Well, what if they could store information about your information so you could query it? Good news! Most database engines such as MySQL implement Information Schema, or a set of views that describe your tables and columns. If you’ve ever used a language like Java or C#, you might be familiar with reflection: the ability to read an object’s metadata. This enables you to do meta-programming, or writing logic about the program itself.
The idea of Information Schema is like reflection: they are views in your database that you can use in your programs or scripts when you need to know what the schema looks like or check the health of the instance. Unlike Show Processlist or Desc Table commands, the results are in tabular format.
I’m going[Read more...]
One of the new features of MySQL 5.6 is the optimizer trace. What does it do? Well, you know Explain, right? Just add Explain before a query and MySQL will print out how it would execute the query. The optimizer trace does that, except it gives you a little more background into to how it makes its decisions. Think of it as “It’s a Wonderful Life” for indexes – it shows you what the world would be like without that primary key or index.
If you ask, why did MySQL choose to run a full table scan vs. an index seek, the optimizer trace can help you get a better picture of the situation. The other big deal with the optimizer trace is that it’ll show you the relative costs of operations. Yeah, MySQL already tells you how many rows it’ll have to read, but now you know how much those cost[Read more...]
Moving data between databases is hard. Without ever intending it, I seem to have spent a lifetime working on solutions for getting data into and out of databases, but more frequently between. In fact, my first job out of university was migrating data from BRS/Text, a free-text database (probably what we would call a NoSQL) into a more structured Oracle.
Today I spend some of my time working in Big Data, more often than not, migrating information from existing data stores into Big Data so that they can be analysed, something I covered in more detail here:[Read more...]
One of the first tasks when setting up a new MySQL server is creating users and granting them permissions, or giving them the ability to do stuff in MySQL. By default, a single user, the root user, is created when you setup MySQL. This user is granted all privileges on the entire system, which means you should create separate logins for administrators and applications. Let’s walk through all the basic steps you need to create a new user and give them permissions.
The first command I’ll show you is pretty simple:
Create User bob@'%' Identified By 'Astr0ngPhr@$e';
Before we move on, let’s check that user’s permissions:
Show Grants for bob@'%';
Which should show something like:[Read more...]
In this post, I’m going to briefly cover the signs that you’re doing multi-tenancy wrong. Some of these practices are entrenched in software: there are gems in Ruby on Rails, for instance, use the first anti-pattern to achieve multi-tenancy. Listen, you can drive a car with a flat tire and you can eat yogurt with a fork. People have made these solutions work, but there’s a better way.
If you find yourself running DDL (Create Table…) for each new company or user that you add to your system, most likely you’re committing a pretty big anti-pattern. Now every time you update the[Read more...]
If you happen to work with personal data, chances are you are subject to SOX (Sarbanes-Oxley) whether you like it or not.
One of the worst aspects of this is that if you want to be able to analyse your data and you replicate out to another host, you have to find a way of anonymizing the information. There are of course lots of ways of doing this, but if you are replicating the data, why not anonymize it during the replication?
Of the many cool features in Tungsten Replicator, one of my favorites is filtering. This allows you to process the stream of changes that are coming from the data extracted from the master and perform operations on it. We use it a lot in the replicator for ignoring tables, schemas and columns, and for ensuring that we have the correct information within the THL.
Given this, let’s use it to anonymize the data as it is being[Read more...]
MySQL 5.6 has been GA for just over a year now. See MySQL 5.6.10 Release Notes. Congratulations on your birthday! That is quite a long time. I was using it earlier in production because it worked and could do things that 5.5 could not do, but earlier versions were to use at your own risk, and indeed if prodded incorrectly would fall on the floor. That is fair enough because they were work in progress, yet if you poked them the right way they did a very good job. Those dev versions have been long since upgraded which is good so they do not need quite as much care and attention.
So from where I see 5.6 it works very well. One big change that has made a large difference but which I think a lot of people may not really understand or use is the[Read more...]
I want to give you tools that you can use for building databases that can handle complex relationships. In the previous article in this series, I looked at hierarchical data. The classic example of a hierarchy is an org chart. On most org charts I’ve seen, an employee has only one boss, which is a one-to-many relationship. The other kind of segmentation is many-to-many. A good instances of this is your social circle. Most people have many friends, and those friends have multiple friends themselves.
This is a common pattern. When developing a multi-tenanted application, users[Read more...]
|Showing entries 1 to 30 of 1145||Next 30 Older Entries|