FromDual TechFeed (en)

We recently run into some troubles with max_allowed_packet size problems during backups with the FromDual Backup/Recovery Manager and thus I investigated a bit more in the symptoms of such problems.

Read more about: max_allowed_packet.

A general rule for max_allowed_packet size to avoid problems is: All clients and the server should have set the same value for max_allowed_packet size!

I prepared some data for the test which looked as follows:

mysql> SELECT id, LEFT(data, 30), LENGTH(data), ts FROM test; …

Hello oli,

every user/process on a UNIX system has some user limits. There are some soft (S) and hard (H) user limits. Soft limits a user can change himself up to the hard limits.

You can find you own user limits as follows:

shell> ulimit -aS | grep 'open'
open files                      (-n) 1024

shell> ulimit -aH | grep 'open'
open files                      (-n) 4096

and the user limits of an already running process as follows:

shell> cat /proc/`pidof mysqld`/limits | egrep 'Limit|open …

Hello oli,

it looks like you have enabled skip_name_resolve in your my.cnf.
This prevents MySQL to do (DNS) host lookups. If you have created some users with explicit host names MySQL wants to tell you now that you cannot use these users any more with this configuration. Example:

mysql> GRANT ALL ON *.* TO 'root'@'master';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS; …

Looking at Percona Server source code we can find the following:

storage/innobase/srv/srv0srv.cc

export_vars.innodb_checkpoint_age     = (log_sys->lsn - log_sys->last_checkpoint_lsn);
export_vars.innodb_checkpoint_max_age = log_sys->max_checkpoint_age;

Looking at the code we can see how output of SHOW ENGINE INNODB STATUS\G is produced:

storage/innobase/log/log0log.cc

void log_print(FILE* file)
{

 fprintf(file,
         "Log sequence number " LSN_PF "\n"
         "Log flushed up to   " …

For processing SELECT queries MySQL needs some times the help of temporary tables. These temporary tables can be created either in memory or on disk.

The number of creations of such temporary tables can be found with the following command:

mysql> SHOW GLOBAL STATUS LIKE 'created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 4     |
| Created_tmp_tables      | 36    |
+-------------------------+-------+ …

After properly installing and testing a Galera Cluster we see that the set-up is not finished yet. It needs something in front of the Galera Cluster that balances the load over all nodes.
So we install a load balancer in front of the Galera Cluster. Typically nowadays HAProxy is chosen for this purpose. But then we find, that the whole Galera Cluster is still not high available in case the load balancer fails or dies. So we need a second load balancer for high availability.
But how should we properly …

We have recently seen a case where the following command was executed on a Galera Cluster node:

SQL> GRANT SUPER ON userdb.* TO root@127.0.0.111;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

2014-12-09 14:53:55 7457 [Warning] Did not write failed 'GRANT SUPER ON userdb.* TO root@127.0.0.111' into binary log while granting/revoking privileges in databases.
2014-12-09 14:53:55 7457 [ERROR] Slave SQL: Error 'Incorrect usage of DB GRANT and GLOBAL PRIVILEGES' on query. Default …

Sometimes, MySQL tablespace file(s) might be deleted by mistake, e.g. delete the shared tablespace (ibdata1) or an individual tablespace (table_name.ibd).

In this post I will show you how to recover those files (on Linux OS) having only one condition, MySQL service should still be running. If MySQL service stopped after deleting that file, this method will not work, so it is extremely important to act as quick as possible to avoid data loss.

The following is a simple table creation (innodb_file_per_table is …

Global Transaction ID (GTID) is one of the major features that were introduced in MySQL 5.6 which provides a lot of benefits. I have talked about the GTID concept, implementation and possible troubleshooting at Percona Live London 2014, you can download the slides from our presentations repository or from my session at Percona Live.

On the other hand, there are some important things you should consider before deploying GTID in production, I’m going to list them here in this blog post.

Table of …

A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.

After some investigations, we found a lot of insert queries in state “query end” and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of “XA COMMIT”:

SQL> SHOW PROCESSLIST;

27  user    host:33214  foodmart …

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.

Situation

A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.*

Analysis

We do some analysis first:

mysql> DROP TABLE test;
ERROR 1051 (42S02): Unknown table 'test'

mysql> CREATE TABLE test (id INT) ENGINE = InnoDB;
ERROR 1050 (42S01): Table '`test`.`test`' already exists

The MySQL error log shows us the following …

Global Transaction ID (GTID) is a new feature coming with MySQL 5.6 which introduced a new MySQL replication method called Transaction-based Replication that is depend on GTID.

In a few weeks, I will be speaking at Percona Live London 2014 (November 3-4) about “Transaction-based REPLICATION (GTID) - IMPLEMENTATION AND TROUBLESHOOTING”. I’ll talk about how to implement GTID replication in different scenarios and how to troubleshoot most of the common issues in GTID replication.

Anyone …

We have been asked several times by MySQL users about how to install multiple MySQL instances on a single host.
Typically, this is required when testing different MySQL versions or MySQL servers (MySQL server, Percona server and MariaDB server) while no available resources are available.
Sometimes, it is even required to install multiple MySQL instances on a single production server.

In this article, I’ll go through the steps needed to install multiple MySQL instances on a single host (using the tar …

This week, we did some migrations from MariaDB 10.0 to Percona Server 5.6 at the IT department of a big German bank.

We were perfectly aware that since version 10.0 the MariaDB code base started to diverge slightly away from the MySQL and Percona Server code base which are still pretty close to each other.

Because of the Percona Server option enforce_storage_engine we wanted to do this migration.

We stopped the MariaDB 10.0 server, replaced the binaries by the Percona Server 5.6 binaries, and started the …

It is not clear for many MySQL users that Percona XtraDB Cluster (PXC) and MariaDB Galera Cluster depend on the same Galera library i.e used in Galera Cluster for MySQL which is provided by Codership team:

• Galera Cluster: MySQL Server (by Oracle) + Galera library.
• Percona XtraDB Cluster: Percona Server + Galera library.
• MariaDB Galera Cluster: MariaDB Server + Galera library.

But the question is, are there any performance differences between the three of them ?

Let’s discover that by doing some …

If you used the combination of Percona tools (pt-table-checksum and pt-table-sync) - as I mentioned - to get the data synchronized after injecting an empty transaction and starting the slave you won’t face that problem. Otherwise, yes this could be dangerous if that slave promoted to be master AND the other slaves didn’t replicate that transaction yet or when you take a database backup from that slave as well.

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 …

Just to confirm, the above channel failover steps are valid in Galera Cluster for both MySQL versions 5.5 and 5.6. Enjoy!!

Sometimes it could be desirable to replicate from a Galera Cluster to a single MySQL slave or to an other Galera Cluster. Reasons for this measure could be:

• An unstable network between two Galera Cluster locations.
• A separation of a reporting slave and the Galera Cluster so that heavy reports on the slave do not affect the Galera Cluster performance.
• Mixing different sources in a slave or a Galera Cluster (fan-in replication).

This article is based on earlier research work (see MySQL Cluster - Cluster …

In a previous post I was talking about How to Setup MySQL Replication using the classic method (based on binary logs information). In this article I’ll go through the transaction-based replication implementation using GTID in different scenarios.

The following topics will be covered in this blog:

• What is the concept of GTID protocol?
• GTID Replication Implementation
  • Fresh Installations
  • Adding New Slave
• Migration from classic replication to GTID replication
  • How to perform the migration?
  • Is online …