Running Percona XtraDB Cluster in a Sandbox on Ubuntu

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/dveeden/mysql/5.5.29-pxc/lib/libgalera_smm.so) failed: Invalid argument (22). Reverting to no provider.

There is no libssl.so.10 on my Ubuntu 12.10 system, but there is a libssl.so.1.0.0.

The fix for this issue is:
cd ~/opt/mysql/5.5.29-pxc/lib/
ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 libssl.so.10
ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 libcrypto.so.10

The symlinks are created inside the library directory for Percona XtraDB Cluster, this way I don't need to tinker with the global library directories.

MyISAM in a MySQL 5.6 InnoDB Only instance

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 set the default storage engine for temporary tables: default_tmp_storage_engine. This makes it possible to use MyISAM or MEMORY as default temporary storage engine, which could benefit performance in some cases.

A great new feature of MySQL 5.6 is the full text indexing support for InnoDB. Now you can switch your fulltext tables to InnoDB! (after careful testing of course as results may differ)
So do we still need MyISAM? The answer is Yes.

The system tables (mysql.*) are mostly MyISAM. The general log and slow query log tables are in CSV format. In 5.6 there are also some InnoDB tables: innodb_index_stats, innodb_table_stats and optionally slave_master_info and slave_relay_log_info. It's not supported to change these MyISAM tables to InnoDB.

mysql> SELECT engine, COUNT(*) FROM information_schema.tables
    -> WHERE table_schema='mysql' GROUP BY engine;
+--------+----------+
| engine | COUNT(*) |
+--------+----------+
| CSV    |        2 |
| InnoDB |        5 |
| MyISAM |       21 |
+--------+----------+
3 rows in set (0.00 sec)

The implicit temporary tables as created by MySQL (e.g. not with CREATE TABLE) are still in MyISAM format. This can be shown by running some query which uses 'Using temporary; Using filesort' and while the query runs looking in the tmpdir location:

Session1:

select a.user,a.host,b.user from mysql.user a, mysql.user b, mysql.user c,
mysql.user d ,mysql.user e,mysql.user f, mysql.user g, mysql.user h order by a.user,b.user

Session2:

mysql> select @@tmpdir;
+----------------------------------------+
| @@tmpdir                               |
+----------------------------------------+
| /home/dveeden/sandboxes/msb_5_6_10/tmp |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> \! ls -l /home/dveeden/sandboxes/msb_5_6_10/tmp
total 4044
-rw-rw---- 1 dveeden dveeden 4120272 Mar 18 13:30 #sql_69cd_0.MYD
-rw-rw---- 1 dveeden dveeden    1024 Mar 18 13:30 #sql_69cd_0.MYI

And the third one: mysqldump uses MyISAM.
A view is first restored as a MyISAM table and after all views are restored then they are converted to 'real' views. This is needed as views might be dependent on each other.
With performance_schema it's possible to 'see' the usage of MyISAM. With 5.6 not everything is enabled by default, so first check the setup_* tables to see what's enabled and what's not. In setup_objects the system tables are disabled, which can easily give wrong results if you're interested in MyISAM usage.

mysql> SELECT * FROM performance_schema.events_waits_summary_global_by_event_name 
    -> WHERE event_name LIKE 'wait/io/file/myisam/%'\G
*************************** 1. row ***************************
    EVENT_NAME: wait/io/file/myisam/data_tmp
    COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0
*************************** 2. row ***************************
    EVENT_NAME: wait/io/file/myisam/dfile
    COUNT_STAR: 145525
SUM_TIMER_WAIT: 4793712275400
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 32940608
MAX_TIMER_WAIT: 2244115016
*************************** 3. row ***************************
    EVENT_NAME: wait/io/file/myisam/kfile
    COUNT_STAR: 329
SUM_TIMER_WAIT: 17011553368
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 51706768
MAX_TIMER_WAIT: 236143040
*************************** 4. row ***************************
    EVENT_NAME: wait/io/file/myisam/log
    COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0
4 rows in set (0.00 sec)

mysql> SELECT event_name,object_name,COUNT(*),SUM(TIMER_WAIT) FROM performance_schema.events_waits_history_long 
    -> WHERE event_name LIKE 'wait/io/file/myisam/%' GROUP BY event_name,object_name\G
*************************** 1. row ***************************
     event_name: wait/io/file/myisam/dfile
    object_name: /home/dveeden/sandboxes/msb_5_6_10/tmp/#sql_69cd_0.MYD
       COUNT(*): 84
SUM(TIMER_WAIT): 1120085576
*************************** 2. row ***************************
     event_name: wait/io/file/myisam/kfile
    object_name: /home/dveeden/sandboxes/msb_5_6_10/tmp/#sql_69cd_0.MYI
       COUNT(*): 6
SUM(TIMER_WAIT): 501999104
2 rows in set (0.02 sec)

Some possible sources of high(er) MyISAM usage:

• The mysql.proc table might be read often if you're often calling stored procedures. (seen with 5.5). This might also be true for events.
• Monitoring which checks things (too) often.
• Usage of server side help

And InnoDB doesn't support spatial indexes, so if you need them you still need MyISAM. Storing spatial information can be done with InnoDB, so if you can do without index you don't need MyISAM:

mysql> CREATE TABLE geo_m (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i),
    -> SPATIAL KEY (g)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE geo_i (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i),
    -> SPATIAL KEY (g)) ENGINE=InnoDB;
ERROR 1464 (HY000): The used table type doesn't support SPATIAL indexes
mysql> CREATE TABLE geo_i (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

If you're interested in performance_schema, make sure to attend the MySQL Virtual Developer Day.

So MyISAM is still a critical part of MySQL and you should not just forget about it, even if you're only using InnoDB.

How to lose your (foreign) key?

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)

But unfortunately it sometimes doesn't work as we expect:

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 REFERENCES parent(`id`), PRIMARY KEY(`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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

This is a known bug: Bug #17943.

But there is yet another way of losing a foreign key:

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 COMMENT 'Parent\'s name', 
    -> pid INT NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY `fk_parent_child` (`id`) REFERENCES parent (`id`));
Query OK, 0 rows affected (0.00 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 COMMENT 'Parent''s name',
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

This is also a known bug: Bug #68367

And the third way to lose a foreign key:

mysql> CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL, 
    -> pname VARCHAR(100) NOT NULL, PRIMARY KEY(`id`));                                                    
Query OK, 0 rows affected (0.00 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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE child ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

This is not a bug, but it sometimes happens (especially with 5.1 and earlier). The developer creates a table and the realized he/she didn't specify the correct storage engine. This is well documented and well known.

So be careful and check if the key is where you expect it to be. With information_schema the information about foreign keys is not only available with SHOW statements but also as a table.

mysql> SELECT * FROM information_schema.referential_constraints WHERE CONSTRAINT_SCHEMA='fktest'\G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: fktest
          CONSTRAINT_NAME: child_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: fktest
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: RESTRICT
              DELETE_RULE: RESTRICT
               TABLE_NAME: child
    REFERENCED_TABLE_NAME: parent
1 row in set (0.00 sec)

MySQL 5.5's new features

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 was backported to MySQL 5.5. This option records each deadlock condition in the MySQL error log, allowing easier troubleshooting if frequent deadlocks point to application coding issues. (Bug #14515889)

So updating to the latest 5.5 (or even  5.1) is not only good for security, stability and performance but it also brings some nice new features.

How MySQL 5.6 handles passwords more securely

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. According to the manual the mysql client now won't log statements which match "*IDENTIFIED*:*PASSWORD*".

But don't forget that MySQL Server logfiles and MySQL Client logfiles should still be protected with the correct file permissions and the same is true for all other files like master.info. And if you store slave credentials inside the database these should be protected by using the MySQL privileges system, but that's even true if master.info is used as someone with too many database privileges might just load the contents with LOAD DATA INFILE.

Of course there there is still room for improvement:
It would be nice if we could easily enable connection security. MySQL and many other applications use TLS, but that has some performance overhead and isn't really easy to setup. And MySQL is still not secure-by-default, but easy-by-default...

So MySQL 5.6 improves security without needing any special configuration.