The Galera series

Overview and some history

Some years ago (2011), I was presenting in one article what at that time I have call “A dream on MySQL parallel replication” (http://www.tusacentral.net/joomla/index.php/mysql-blogs/96-a-dream-on-mysql-parallel-replication).

At that time I was dreaming about having a replication mechanism working on the commit operations, and was also trying to explain how useless is to have parallel replication by schema. I have to say that I got a lot of feedback about that article, directly and indirectly, most of it telling me that I was crazy and that what I was looking for cannot be done.

The only crazy guy that was following me in my wandering was Herik Ingo, who mentions to me Codership and Galera.

After …

With one of my previous post I have run through the steps involve in setting up Jenkins and today I’m going to discuss about the how to get a PHP project integrated with Jenkins on a Mac Mini server(It’s a pain in the ass compared to a Linux box when it comes to setting up […]

A tip for all those cloud users that like cloning database servers (as reported in my book Effective MySQL – Replication Techniques in Depth).

Starting with MySQL 5.6, MySQL instances have a UUID. Cloning servers to quickly create slaves will result in the following error message.

mysql> SHOW SLAVE STATUSG
...
  Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
...

The solution is simple. Clear our the file based configuration file (located in the MySQL datadir) and restart the MySQL instance.

$ rm -f /mysql/data/auto.cnf
$ service mysql restart

MySQL5.6 introduced a new system variable eq_range_index_dive_limit. This may significantly affect query execution plans. Here I show a typical example.

There is a table "t". Primary key consists of multiple columns starting from "id1". There are 1.67M rows in table t, and cardinality of id1 is 46K (These numbers can be collected by SHOW TABLE STATUS / SHOW INDEX). So there are 36 rows (1.67M/46K=36) per id1 on average, but actual id1 distribution is not even. There are nearly 1M rows where id1 between 1 and 10.

mysql> explain select count(*) from t force index (PRIMARY) where id1 in (1,2,3,4,5,6,7,8,9)\G

*************************** 1. row ***************************

…

MySQL5.6 introduced a new system variable eq_range_index_dive_limit. This may significantly affect query execution plans. Here I show a typical example.

There is a table "t". Primary key consists of multiple columns starting from "id1". There are 1.67M rows in table t, and cardinality of id1 is 46K (These numbers can be collected by SHOW TABLE STATUS / SHOW INDEX). So there are 36 rows (1.67M/46K=36) per id1 on average, but actual id1 distribution is not even. There are nearly 1M rows where id1 between 1 and 10.

mysql> explain select count(*) from t force index (PRIMARY) where id1 in (1,2,3,4,5,6,7,8,9)\G

*************************** 1. row ***************************

…

This week we start talking about the Python MySQL Utilities. Ear Candy is a pitfall when importing a mysqldump export and At the Movies is "Deploying MySQL in AWS and OpenStack" by Mark Riddoch of SkySQL.

Part 2 of MySQL Utilities
Part 3 of MySQL Utilities
Part 4 of MySQL Utilities
Part 5 of MySQL Utilities

MySQL Utilities
MySQL Utilities documentation

read more

Oracle Linux had a lot of engineering hours inside it to drive the performance to new levels. But currently oracle Linux 6.4 comes with a dated version of MySQL, version 5.1.69. So how can you upgrade to 5.6 without losing your data?

First, make a backup just in case Murphy’s Law has a tort waiting for you.

Remove the old files. The rpm -qa | grep -i ^mysql command will show you the packages to be removed. Then rpm -e mysql-server-5.1.69-1.el6_4.x85_64 mysql-libs-5.1.69-1.el6_4.x86_64 and mysql-5.1.69-1.el6_4.x85_64 to remove them.

Thirdly, download the big MySQL 5.6 bundle for Oracle Linux. Untar the bundle and then a quick rpm -Uhv MySQL-*.rpm will install the files.

There is ONE big difference. MySQL as of 5.6, forces a default for the MySQL server and places it in /root/.mysql_secret and you will see to set a new password upon …

Send to Kindle

Desde a versão 5.6.6 MySQL introduziu o habilidade the forcar usuários a trocar suas senhas.
Tu pode fazer isso digitando ALTER USER PASSWORD EXPIRE.

ALTER USER 'marcelo'@'localhost' PASSWORD EXPIRE;

Na próxima vez que marcelo tentar conectar ao MySQL via localhost, o servidor ira bloquear todos os comandos até que o usuario use SET PASSWORD;

mysql> \s
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Para alterar a senha, e utilizar o server normalmente, basta digitar:

SET PASSWORD = PASSWORD('senha');
#Ou utilizando o hash gerado por SELECT PASSWORD('senha');
SET PASSWORD = '*AD77F56D2FD78299B87609DCC0423260B5AADB03';

Mas tome cuidado, se tu marcar a senha de um usuário como expirada, ele não poderá conectar via clientes com versão anterior a 5.6.10:

ERROR 1862 (HY000): Your password has expired. To log in you must …

Send to Kindle

Since version 5.6.6 MySQL has introduced the capability to force an user to change their password.
You can archive that by issue ALTER USER PASSWORD EXPIRE statement.

ALTER USER 'marcelo'@'localhost' PASSWORD EXPIRE;

Next time marcelo tries to login from localhost it will block all statements rather then SET PASSWORD;

mysql> \s
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

To remove this block, just issue an :

SET PASSWORD = PASSWORD('pwd');
#Or use the hash received from SELECT PASSWORD('pwd');
SET PASSWORD = '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD';

But be careful, if you expire the password for an account, you won’t be able to login with any client version before 5.6.10(GA):

ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

As Peter …

This Log Buffer Edition starts with an awesome post by Dana Pylayeva from Rakuten LinkShare, a long term Pythian client, about her deep and insightful reflections about security. And as usual, it also brings together all the sparkling gems of Oracle, SQL Server, and MySQL. Enjoy!

Oracle:

Alberto illustrates the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only the SUM aggregate function.

Tim Hall writes about Oracle 12c Multitenant Option : CDBs and PDBs. …