Wed, 2015-03-04 10:26stephanevaroqui

Every organization needs a good database back-up plan, to include plans for data restoration. Such a plan should include, of course making proper back-ups and doing in a way that won't disturb user traffic. It should also include making sure that you're ready to restore back-ups quickly, so that your organization can operate with minimal downtime and without loss of data when a problem occurs. There are many things such as these to consider for developing a good back-up and restoration plan.

Full Back-Ups

It's strongly recommended to make a back-up of all of the databases in the same dump. This will allow for a consistent view of the global state of the databases. Plus, a full back-up can be used for provisioning a new replication when needed. A full back-up will save the system tables and stay consistent with multiple database queries and transactions. There are …

In MySQL Cluster 7.4 we have worked on improving the speed of restarts.
In addition we have made it easier to track how fast the restarts are
proceeding and to see which phases of the restart take a long time.

For this purpose we have added a new Ndbinfo table in MySQL Cluster 7.4.
I will try to explain the restart phases in a series of blogs and how they map
to the columns in this Ndbinfo table.

First some short intro to concepts in MySQL Cluster. LCP stands for local
checkpoints and this is where the main memory content of tables are
written to disk at regular intervals. There is also a GCP, global checkpoint,
this is a protocol to synchronise the REDO log writes and thus about
durability of transactions. LDM stands for Local Data Manager and is
the thread that runs all the code that maintains the actual data in MySQL
Cluster. This includes row …

Last week I had to update several million rows in a single MySQL table. Rather that executing the update directly in the database, I decided to use common_schema's split() function.

There are two reasons I chose to use common_schema for this task:

Chunking

Chunking breaks a single update statement into multiple statements, each updating at most a certain specified number of rows. The default chunk size is 1000, and I changed it to 2000 by setting the size parameter.

Throttling

The Practical Guide to Query Optimization for MySQL and PostgreSQL is a must read for any DBA or developer who wants to maximize resources, ship code faster, and do more to delight users.

Abstract

Profiling is the science of categorizing, sorting, and ranking system work to determine where time, money, and resources are spent within the system. Profiling database workload is an essential skill for any database administrator or developer and is the first step to optimizing performance. This book is the first in a series. It teaches you the tools and techniques you’ll need to profile one or many MySQL or PostgreSQL databases. Further books will explain how to speed up the queries that you select for optimization as a result of profiling.

Check out the table of contents and download the full edition …

Full text is a critical point when it comes to mysql. It used to have that feature in MyISAM but that’s not really maintained anymore nor it is advised to use unless you have a very specific use case in which it might make sense. There are 3rd party solution which takes the problem away (Lucene, Sphinx, Solr, ElasticSearch) but all bring extra complexity to your setup which has its own cost. So do you need to give up on fulltext search if you’re using MySQL + InnoDB? No! Definitely not.

Alternatives for full-text search in MySQL

I have an live database with 9000+ products with category, brand and short description which is perfect to test my searches on. I’m going to run the queries many times and use profile information to collect more granular and accurate timing information.

LIKE

Well… It’s not a real alternative but just for a sec see how well it behaves.

SELECT SQL_NO_CACHE b.name, …

Talking with Percona Live attendees last year I heard a couple of common themes. First, people told me that there is a lot of great advanced content at Percona Live but there is not much for people just starting to learn the ropes with MySQL. Second, they would like us to find a way to make such basic content less expensive.

I’m pleased to say we’re able to accommodate both of these wishes this year at Percona Live! We have created a two-day intensive track called “MySQL 101” that runs April 15-16. MySQL 101 is designed for developers, system administrators and DBAs familiar with other databases but not with MySQL. And of course it’s ideal for anyone else who would like to expand their professional experience to include MySQL. The sessions are designed to lay a solid foundation on many aspects of MySQL development, design and …

In the MySQL team, we are currently discussing deprecating several of the SQL mode options which are used by mysqldump to change the output format. From the mysqldump command:

$ mysqldump --help
..
--compatible=name
 Change the dump to be compatible with a given mode. By
 default tables are dumped in a format optimized for
 MySQL. Legal modes are: ansi, mysql323, mysql40,
 postgresql, oracle, mssql, db2, maxdb, no_key_options,
 no_table_options, no_field_options. One can use several
 modes separated by commas. Note: Requires MySQL server
 version 4.1.0 or higher. This option is ignored with
 earlier server versions.

To explain the rationale for this proposal:

1. The options mysql323, mysql40 are designed to allow mysqldump to create an output format that can be restored on a MySQL Server of version 3.23 or 4.0. While we aim to support the upgrade case from these versions, supporting a downgrade is not something we …

As the need for information grows so does the size of data we need to keep in our databases. SST is unavoidable for spinning up new nodes in a PXC cluster and when datasets reach the “terra-byte” range this becomes ever more cumbersome requiring many hours for a new node to synchronize.

More often that not, it is necessary to implement custom “wsrep_sst” scripts or resort to manual synchronization processes. Luckily cloud providers provide convenient methods to leverage disk snapshots that can be used to quickly transfer data between nodes.

This article deals with the actions needed to perform a snapshot on Google’s Compute Engine (GCE) infrastructure. A similar method can be used on AWS EC2 instances using EBS snapshots or any other form of snapshots such as LVM, ZFS or SAN. The steps described can be …

In MySQL Character encoding – part 1 we stated that the myriad of ways in which character encoding can be controlled can lead to many situations where your data may not be available as expected.

UTF8 was designed on a placemat in a New Jersey diner one night in September or so 1992.

Setting MySQL Client and Server Character encoding.

Lets restart MySQL with the correct setting for our purpose, UTF8. Here we can see the setting in the MySQL configuration file, in this case /etc/mysql/my.cnf.

character-set-server = utf8

This change is then reflected in the session and global variables once the instance is restarted with the new configuration parameter.

mysql> SELECT …

It's been some time since I wrote my last blog. As usual this means that I have
been busy developing new things. Most of my blogs are about describing new
developments that happened in the MySQL Server, MySQL Cluster, MySQL
partitioning and other areas I have been busy developing in. For the last year I have
been quite busy in working with MySQL Cluster 7.4, the newest cluster release. As
usual we have been able to add some performance improvements. But for
MySQL Cluster 7.4 the goal has also been to improve quality. There are a number
of ways that one can improve quality. One can improve quality of a cluster by making
it faster to restart as problems appear. One can also improve it by improving code
quality. We have done both of those things.

In order to improve my own possibilities to test my new developments I decided to
invest in a "Windows computer". This …