I wanted to do some experimentation with Percona XtraDB Cluster (Galera) and I didn't want to use virtual machines. I'm already using MySQL Sandbox for many other projects so that's the natural choice. I've downloaded the tarball for Percona XtraDB Cluster 5.5.29 and I've extracted it to ~/opt/mysql/5.5.29-pxc. Then I've installed 3 nodes: make_sandbox 5.5.29-pxc -- --sandbox_port 4551 \ --sandbox_directory msb_5_5_29-pxc01  make_sandbox 5.5.29-pxc -- --sandbox_port 4552 \ --sandbox_directory msb_5_5_29-pxc02  make_sandbox 5.5.29-pxc -- --sandbox_port 4553 \ --sandbox_directory msb_5_5_29-pxc03  But when I try to start a node this error happens: 130327 14:21:03 [Note] WSREP: wsrep_load(): loading provider library '/home/dvee den/mysql/5.5.29-pxc/lib/libgalera_smm.so' 130327 14:21:03 [ERROR] WSREP: wsrep_load(): dlopen(): libssl.so.10: cannot open shared object file: No such file or directory 130327 14:21:03 [ERROR] WSREP: wsrep_load(/home/...

With MySQL 5.5 the default storage engine was changed to InnoDB. This was a good step as most users expected MySQL to support transactions, row level locking and all the other InnoDB features, but with 5.1 they sometimes forgot to choose the right storage engine. As most databases don't have multiple storage engines and many MySQL users changed the default storage engine to InnoDB this made the switch to MySQL easier and the default behaviour more in line with what people expect from a relational database. Changing the storage engine can of course be done on a per table or per instance basis (default-storage-engine in my.cnf). The temporary tables created with 'CREATE TEMPORARY TABLE ...' should not be forgotten. The performance of InnoDB or MyISAM for temporary tables can have quite some impact, especially with slow storage, a buffer pool which is too small to hold the complete dataset or very small temporary tables. In MySQL 5.6 there is a new variable introduced to se...

One of the many reasons to switch from MyISAM to InnoDB (or NDB) is the support of foreign keys which helps to guarantee referential integrity. This is how it's supposed to work: mysql> CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL, -> pname VARCHAR(100) NOT NULL, PRIMARY KEY(`id`)); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE child (id INT AUTO_INCREMENT NOT NULL, cname VARCHAR(100) NOT NULL, -> pid INT NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY `fk_parent_child` (`id`) REFERENCES parent (`id`)); Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(100) NOT NULL, `pid` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `parent` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec...

The recently released MySQL 5.6 gets a lot of attention, but for those who are still on 5.5 there is also good news: There are two new features in 5.5. The first feature is that there are more INFORMATION_SCHEMA tables for InnoDB. This means that it's possible to 'see' what's in the buffer pool. It also makes it possible to get more information about the LRU list. From the 5.5.28 changelog : InnoDB: Certain information_schema tables originally introduced in MySQL 5.6 are now also available in MySQL 5.5 and MySQL 5.1: INNODB_BUFFER_PAGE , INNODB_BUFFER_PAGE_LRU , and INNODB_BUFFER_POOL_STATS . (Bug #13113026) This is in the "Bugs Fixed" section instead of the "Functionality Added or Changed" section, which is a bit weird in my opinion. The second feature is a variable which makes it possible to get more information about the deadlocks. From the 5.5.30 changelog : InnoDB: The innodb_print_all_deadlocks configuration option from MySQL 5.6 ...

There are many thing changed in MySQL 5.6 which are related to passwords: There is a new password hash algorithm (SHA-256) There is obfuscation for passwords with the .mylogin.cnf file. The option to store slave passwords in a database table. It's now possible to supply a password to START SLAVE. But that's not what this blog post is about. This blog post is a great new feature: Hiding passwords from your log files, automatically. MySQL 5.6 will by default hide passwords from the general log. This is not just obfuscation as only the one-way hash will be put in the log files. By setting log-raw=OFF you can disable password hiding for the general log. The log-raw setting will only influence the general log, so the passwords in the slow query log and the binary logs will still be hidden. With MySQL 5.5 this could be done manually by first storing the hash in a variable, but like many other work-arounds this is not needed anymore. But it doesn't stop there. Accor...