Madrid MySQL Users Group will have its next meeting on the 20th of November. Details can be found on the group’s Meetup page. We plan to talk about pstop, which I’ve announced earlier and also the latest changes in MariaDB and MySQL since our last meeting.  The meeting will be in Spanish. I hope to see … Continue reading MMUG9: Madrid MySQL Users Group meeting to take place on 20th Noevember 2014

November 14, 2014 By Severalnines

Thanks to everyone who attended and participated in this week's webinar on '9 DevOps Tips for Going in Production with Galera Cluster for MySQL'. If you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

In this webinar, Severalnines CTO Johan Andersson discussed 9 key aspects to consider before taking Galera Cluster for MySQL into production:

• 101 Sanity Check
• Operating System
• Backup Strategies
• Galera Recovery
• Query Performance
• Schema changes
• Security / Encryption
• Reporting
• Protecting from Disasters

Watch the replay 9 DevOps Tips for Going in …

Did your logging stop working after you set up logrotate? Then this post might be for you.

Archive your log files!

Some time ago, Peter Boros wrote about Rotating MySQL Slow Logs safely, explaining the steps of a “best practice” log rotate/archive. This post will add more info about the topic.

When running logrotate for MySQL (after proper setting the /etc/logrotate.d/mysql conf file) from anacron, there’s a situation that you might potentially face if the user and password used to execute the “flush logs” command is stored in, for example, /root/.my.cnf file.

The situation:

You might find out that you have a new MySQL log file ready to receive data, but nothing is being written to it.

Why did this happen?

The logrotate script is executed, but the postrotate …

Not a ground-breaking post here, but if you are interested in knowing more about the innodb_log_block_size variable, or if you use SSD cards and/or large InnoDB log files on ext4, then this is for you.

I’d read about it before briefly before, but didn’t give it too much thought until I ran across the following entry in an error log the other day:

InnoDB: Warning: innodb_log_block_size has been changed
from default value 512. (###EXPERIMENTAL### operation)

This got me wanting to know more.

Basically, this variable changes the size of transaction log records. Generally, the default of 512 is a good value. However, it has been found that setting it to 4096 has been beneficial when using SSD cards. (Note that while it is possible to set this to a value other than 512 or 4096, those are currently the only 2 values that make sense to use.)

Also, it has been found that 4096 is the best setting if you run …

A long-ago-discussed and much-requested feature, "dynamic compound statements", is working at last.

It's been eleven years since the original discussion of dynamic compound statements for MySQL, as you can see by looking at the worklog task on the wayback machine. (As usual, you have to click the "high level architecture" box to see the meat of the specification.) The essential idea is that one can directly enter compound statements like BEGIN ... END and conditional statements like "IF ... END IF" and looping statements like "WHILE ... END WHILE" without needing a CREATE PROCEDURE or CREATE FUNCTION statement.

The advantages are that one can run conditional or complex sequences of statements without needing an EXECUTE privilege, or affecting the database metadata. This has been a popular feature request, as …

There are a few circumstances where one will not want to run with only MyISAM tables. In this case, it can be beneficial to completely disable InnoDB.

As InnoDB has become more prevalent, disabling it in MySQL requires a little more effort than before.

In MariaDB 10.0, you can still completely disable it as you have done in the past (just add the –skip-innodb option, specify default-storage-engine=MyISAM, and comment out other InnoDB options):

[mysqld]
skip-innodb
default-storage-engine=MyISAM

Alternatively, instead of –skip-innodb, you can instead use “innodb=OFF”:

[mysqld]
skip-innodb
default-storage-engine=MyISAM

In MySQL 5.6, the –skip-innodb option has been deprecated (though still currently works), and since InnoDB is the new “default” storage engine, you must set both “default-storage-engine” and the new “default-tmp-storage-engine” options to “MyISAM”. If you …

I know, what, MyISAM-only? Yes, it’s true, and it occurs more than you might think.

If you do run MyISAM-only, it’s common to have disabled InnoDB altogether (–skip-innodb) to avoid allocating any RAM to it, and to avoid unnecessary files required for backups, and so forth. (See this post if interested in disabling InnoDB in MySQL 5.6 and/or MariaDB 10.0.)

One improvement in MySQL 5.6 and MariaDB 10.0 is the addition of some system tables. The 2 that are in both are `innodb_table_stats` and `innodb_index_stats`, both of which have an engine type of InnoDB. (The 1 additional system table in MariaDB 10.0 is `gtid_slave_pos`, and the 3 additional system tables in MySQL 5.6 are `slave_master_info`, `slave_relay_log_info`, and `slave_worker_info`.)

The issue, with regards to running MyISAM-only, is that these new …

November 7, 2014 By Severalnines

PrestaShop is a popular open source e-commerce software powering over 200,000 online stores, according to the company. We’ve seen a bit of interest into high availability PrestaShop setups, so this post will show you how to achieve that on multiple servers. Note that this setup not only caters for failures, but by load balancing traffic across multiple servers, it also allows the system to scale and handle more users.

This post is similar to our previous posts on web application scalability and high availability:

• Magento - nginx, MySQL Galera Cluster, OCFS2
• Drupal - Apache, MySQL Galera Cluster, csync2 with …

I ran into this the other day, and while the solution is documented in the manual, it was kind of buried, so I thought I’d mention it here (and hopefully make it easier for others searching for this in the future).

In this specific instance, one was running a huge LOAD DATA (10 hours into it) and had to terminate the LOAD DATA command.

Terminating the command is one thing, but preventing InnoDB from trying to undo the rows that were inserted is another.

In this case, the LOAD DATA will easily re-create the table in question, so the existing table can be “thrown out”, so to speak. This, of course, is a *critical* requirement for this operation (i.e., that you can easily re-create the table – either from a load, backup, dump, etc.).

Per the manual:

“If you know that a given table is causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a failing …

Facebook recently made opensource, osquery. It gives you operating system data via SQL queries! Its very neat, and you can test this even on MacOSX (it works on that platform & Linux). It is by far the project with the most advanced functionality, linked here in this post.

I noticed that rather quickly, there was a PostgreSQL project, called pgosquery, based on Foreign Data Wrappers with a similar idea. (apparently it was written in less than 15 minutes; so a much lower learning curve than the regular MySQL storage engine interface)

I immediately thought about an older MySQL project, by Chip Turner (then at Google, now at Facebook), called …