Showing entries 1 to 10 of 14
4 Older Entries »
Displaying posts with tag: Advanced (reset)
MySQL 5.7.6 is out. Be prepared for big changes

Today Oracle released MySQL 5.7.6 milestone 16. With this, MySQL 5.7 has been in development for over 2 years.
Compared to MySQL 5.6, the changes are quite extensive. The main effort of the team has been focused on speed, with performance reportedly improved from 2 to 3 times compared to previous releases.
A full list of what is new would take too much space here, but I would like to mention some key points:

  • Oracle has spent a considerable amount of energy in the improvement of MySQL security and safety. You will see many new features, but even more old features that were deprecated and more that were removed after deprecation in 5.6.
  • The installation process has been changing in every …
[Read more]
Making mk-table-checksum less intrusive

About a month ago I needed to compare tens of thousands of tables in hundreds of databases between a few different servers. The obvious choice was, mk-table-checksum! The only problem was, that the tool needs to know the minimum and maximum value of the column by which each table is to be subdivided into chunks and checksummed. This select min(col), max(col) from table locks all write operations on the table and on a big table it meant downtime.

Looking at the source it was clear we could make mk-table-checksum run the select min(col), max(col) from table on the read-only slave and use the values to checksum the master.

It was subtle code changes in function:
get_range_statistics adding

my $cxn_string_dc = “DBI:mysql:;host=slavehost;port=3306;mysql_read_default_group=client”;
my $user = ‘user’;
my $pass = ‘password’;
my $dbh_slave = DBI->connect($cxn_string_dc, $user, $pass); …

[Read more]
Advanced replication for the masses - Part III - Replication topologies

After part I: the basics, and part II: parallel apply, we deal now with some more mundane topic, or how to deploy replication services in a way that they fit our business, covering from the basic master/slave to the most advanced multi-source scheme.

Master/slaveThe most common topology is master/slave. One master, many slaves. This topology is equivalent to MySQL native replication. The differences are in the additional features. Tungsten supports seamless failover and parallel replication in all topologies.

Figure 1. Tungsten master/slave replication
Unlike MySQL, and unlike previous versions of Tungsten, the implementation of this topology uses a dedicated service for the …

[Read more]
Advanced replication for the masses - Part II - Parallel replication
I hope you liked the first part of this series of lessons. And I really hope that you have followed the instructions and got your little replication cluster up and working.

If you haven't done that, thinking that you would spare your energies for more juicy matters, I have news for you. What I explained in the previous part is exactly what you need to do to set up parallel replication. With just a tiny additional detail.
For the sake of the diligent readers who have followed the instructions with the first lessons, I won't repeat them, but I'll invite you to set the environment as explained in the first part.
Once you have a cluster up and …

[Read more]
Advanced replication for the masses - Part I - Getting started with Tungsten Replicator
MySQL DBAs and developers: oil your fingers and get ready to experience a new dimension of data replication. I am pleased to announce that Continuent has just released Tungsten Replicator 2.0, an open source data replication engine that can replace MySQL native replication with a set of advanced features.
A note about the source code. The current version of Tungsten Replicator available in the web site is free to use, but it is not yet the open source version. We need a few weeks more to extract the code from the enterprise tree and make a new build. But we did not want to delay the user experience. So everything …
[Read more]
Setting up slave, stripping indexes and changing engines, on the fly

Warning, the following is quite ugly, but does the job :)

A while back I needed to create an archive slave database from a half a terabyte myisam master and had space restrictions. I could not dump the db, load it, then drop keys (archive doesn’t support keys apart from a primary key on one column as of 5.1), alter engine etc (would take even longer than it took either way). So an ugly single liner came to mind and worked nicely too.

mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname --master-data=1 | sed 's/ENGINE=MyISAM/ENGINE=archive/g' | grep -v '^ UNIQUE KEY' | grep -v '^ KEY' | perl -p0777i -e 's/,\n^\)/\n\)/mg' | mysql -uuser -ppassword -h127.0.0.1 -P3307 dbname

So what is it doing?
Broken down:
mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname --master-data=1 –> extract the database with the master position
sed …

[Read more]
MySQL stored procedure debugging, can I sue for going insane?

Lets paint the picture:

Scenario part 1 : Migrating a couple thousand stored procedures from database technology X to mysql
Scenario part 2 : Legacy system where the people who wrote it left a long time ago
Scenario part 3 : Developers sure can get real creative and invent all kinds of ways to get data (eg: having a stored proc which formulates a big query using concat after going through a bunch of conditions (fair enough), BUT the parts making up the different queries are stored in a table on a database rather than within the stored proc itself) … talk about KIS – Keep it simple!!
Scenario part 4 : This stored proc references 18 tables, 4 views, and another two stored procedures on 5 databases

Now close your eyes and try to imagine that for a few seconds, nah kidding don’t want you to hurt yourself.

I wonder, who’s gonna cover my health insurance if i go crazy? :)

mysql 02:55:47 DEV …

[Read more]
Three Editions of MySQL are Available

Yes, you read the title correctly — there are three editions of MySQL available, according to Well, that page names two, and then of course there is the community edition….

From the manual page:

MySQL Enterprise Server is available in the following editions:

* MySQL Enterprise Server – Pro is the world’s most popular open source database that enables you to rapidly deliver high performance and scalable Online Transaction Processing (OLTP) applications.
* MySQL Enterprise Server – Advanced is the most comprehensive edition of MySQL. It provides all the benefits of MySQL Enterprise Server Pro and adds horizontal table and index partitioning for improving the performance and management of VLDBs (Very Large Databases).

How is “horizontal table …

[Read more]
Datetime & Timestamp manipulation / migration explained

Are you doing some datetime manipulation or maybe you are migrating from some database technology to MySQL or possibly using milliseconds?
Here is an example on how to go about it:

Say you have the following date: MAR 16 2008 09:12:51:893AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893AM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 2008031691251.893000

What if its PM rather than AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 20080316211251.893000

Ok so this is just simple string manipulation where:
%M is the month name
%d is day number
%Y is the year
%h is the hour
%i is the minute
%s is the second
%f is the microsecond
%p is the period: ante or post meridiem

In the DATE_FORMAT part we se a %k which is …

[Read more]
Further Thoughts on MySQL Upgrades

I have been upgrading more MySQL database instances recently and have found a few more potential gotchas, which if you are not careful, can potentially be rather nasty. These are not documented explicitly by MySQL, so it may be handy for you to know if you have not come across this type of thing before.

Most of the issues are those related to upgrading MySQL instances which are replicated, either the master servers or the slaves. Some seem specific to the rpm packages I am using (MySQL enterprise or MySQL advanced rpms), though others are not.

Take care upgrading a 5.0 master when you have 5.1 slaves

It is not a good idea to run a mixed major version of mysql in a replicated environment so why would I be doing this? If you work in a replicated environment and have several slaves then it is recommended that you upgrade the slaves first. I work with quite a few slaves so the process of upgrading them all takes longer …

[Read more]
Showing entries 1 to 10 of 14
4 Older Entries »