Wake up European DBA, call for papers for Percona Live London 2014 is open!

Call for papers for Percona Live London 2014 is open. For the fourth consecutive year, PLUK is going to be one of the best community event in Europe.
I have the honour of being conference committee chairman and the hard task to reviewing the talks with my colleagues of the committee.

First, let me introduce the committee members:

  • Art van Scheppingen (Spil Games)
  • Nicolai Plum (Booking.com)
  • Luis Motta Campos (Ebay Classifieds Group)
  • Colin Charles (MariaDB)
  • David Busby (Percona)
  • Morgan Tocker (Oracle)
  • Cédric PEINTRE (Dailymotion)

Amazing, isn’t it?! I think we couldn’t have a better committee for a community event.
I’m very glad to take part in the adventure with you guys!

And if you wonder what the committee does, please, read this review from Shlomi.
You should know that the committee is fully independent. We are working hard to offer you the best conference and being as fair as possible, with our beliefs, with fun.

Now, it’s time to submit your talks and tutorials. Like I said last year, If you are using databases, you might have something to say.
We are looking for the best use cases in relation to these topics, surprise us!

  • High Availability
  • DevOps
  • Programming
  • Performance Optimization
  • Replication & Backup
  • MySQL in the Cloud
  • MySQL and NoSQL
  • MySQL Case Studies
  • Security
  • What’s New in MySQL

Tell us about your life, I’m sure you have a ton of stories to tell us.
We would love to have new faces, new companies and new topics this year again.
Don’t be shy, everyone is eager to drink your words.

Read the guidelines and submit your talk now!

Looking forward to meet you in London in November.
Cheers

[Tuto] A real life Multi-Master use case with MariaDB

The goal of this tutorial is to show you how to use Multi-Master and aggregate databases with the same name but with different data from different masters.

Example:

  • master1 => a French subsidiary
  • master2 => a British subsidiary

Both have the same database (PRODUCTION) but the data are totally different:

ScreenShot038We will start with 3 servers (2 masters and 1 slave), you can add more master if needed.

Informations

  •  10.10.16.231: first master (aka ”’master1”’) => a French subsidiary
  • 10.10.16.232: second master (aka ”’master2”’) => a British subsidiary
  • 10.10.16.233: slave (multi-master) (aka ”’slave”’)

If you have already your 3 servers correctly installed go directly to: “Dump your databases”

Default installation on 3 servers

apt-get -y install python-software-properties
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

The main reason why I put the apt configuration in a different file is that we use a configuration manager and this one overwrite /etc/apt/sources.list.
Also, if any trouble occurs just remove this file and you restart with the default configuration.

echo "deb http://mirror.stshosting.co.uk/mariadb/repo/10.0/ubuntu precise main" > /etc/apt/sources.list.d/mariadb.list
apt-get update
apt-get install mariadb-server

The goal of this small script is to get the IP of the server and make a CRC32 from this IP to generate one unique server-id.
Generally the crc32 command isn’t installed, so, we will use the MySQL function instead.
Even if your server have more interface you should have no trouble because the IP should be unique.

user=`egrep user /etc/mysql/debian.cnf | tr -d ' ' | cut -d '=' -f 2 | head -n1 | tr -d '\n'`
passwd=`egrep password /etc/mysql/debian.cnf | tr -d ' ' | cut -d '=' -f 2 | head -n1 | tr -d '\n'`
ip=`ifconfig eth0 | grep "inet addr" | awk -F: '{print $2}' | awk '{print $1}' | head -n1 | tr -d '\n'`
crc32=`mysql -u $user -p$passwd -e "SELECT CRC32('$ip')"`
id_server=`echo -n $crc32 | cut -d ' ' -f 2 | tr -d '\n'`

Find out a minimalist configuration file. Consider to use your own configuration.
If you are interested by my default MariaDB 10 configuration at Photobox, just click here

cat >> /etc/mysql/conf.d/mariadb10.cnf << EOF
[mysqld]
 character-set-client-handshake = FALSE
 character-set-server = utf8
 collation-server = utf8_general_ci
 bind-address = 0.0.0.0
 external-locking = off
 skip-name-resolve
 #make a crc32 of ip server
 server-id=$id_server
 #to prevent auto start of thread slave
 skip-slave-start
[mysql]
 default-character-set = utf8
EOF

Then, restart the server:

/etc/init.d/mysql restart
* Stopping MariaDB database server mysqld [ OK ]
* Starting MariaDB database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.

Repeat these actions on the 3 servers.

Create users on both master

Create replication’s user on both master

On master1:

mysql -u root -p -e "GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'%' IDENTIFIED BY 'passwd'"

On master2:

mysql -u root -p -e "GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'%' IDENTIFIED BY 'passwd'"

Create an user for external backup

On master1 and on master2:

mysql -u root -p -e "GRANT SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT, SUPER ON *.* TO 'backup'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION"

Only for testing

Considering you didn’t have a such configuration and you want to test it

Create a database on master1:

 master1 [(none)]> CREATE DATABASE PRODUCTION;

Create a database on master2:

 master2 [(none)]> CREATE DATABASE PRODUCTION;

Load slave with master data

All the commands until the end have to be done on the slave server:

* –master-data=2: Get the binary log and the position, and put it at the beginning of the dump in comment
* –single-transaction: This option issues a BEGIN SQL statement before dumping data from the server (works only with InnoDB tables)

mysqldump -h 10.10.16.231 -u root -p --master-data=2 --single-transaction PRODUCTION > PRODUCTION_10.10.16.231.sql
mysqldump -h 10.10.16.232 -u root -p --master-data=2 --single-transaction PRODUCTION > PRODUCTION_10.10.16.232.sql

Create both new databases:

slave[(none)]> CREATE DATABASE PRODUCTION_FR;
slave[(none)]> CREATE DATABASE PRODUCTION_UK;

Then load the data:

mysql -h 10.10.16.233 -u root -p PRODUCTION_FR < PRODUCTION_10.10.16.231.sql
mysql -h 10.10.16.233 -u root -p PRODUCTION_UK < PRODUCTION_10.10.16.232.sql

Set up replication on slave

Edit both dump files and get the name and the position of the binlog and replace it as following:

French subsidiary:

less PRODUCTION_10.10.16.231.sql

Get the line: (file and position will be different)

-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000010', MASTER_LOG_POS=771;

Replace name and position in this command:

CHANGE MASTER 'PRODUCTION_FR' TO MASTER_HOST = "10.10.16.231", MASTER_USER = "replication", MASTER_PASSWORD ="passwd", MASTER_LOG_FILE='mariadb-bin.000010', MASTER_LOG_POS=771;

English subsidiary

less PRODUCTION_10.10.16.232.sql

Get the line: (file and position will be different)

-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000010', MASTER_LOG_POS=771;

Replace name and position in this command:

CHANGE MASTER 'PRODUCTION_UK' TO MASTER_HOST = "10.10.16.232", MASTER_USER = "replication", MASTER_PASSWORD ="passwd", MASTER_LOG_FILE='mariadb-bin.000010', MASTER_LOG_POS=771;

Rules of replication on config file

Unfortunatly the option replicate-rewrite-db doesn’t exist as a variable and we can’t set up this configuration without restarting the slave server.
Add the following lines to /etc/mysql/conf.d/mariadb10.cnf on the slave:

PRODUCTION_FR.replicate-rewrite-db="PRODUCTION->PRODUCTION_FR"
PRODUCTION_UK.replicate-rewrite-db="PRODUCTION->PRODUCTION_UK"
PRODUCTION_FR.replicate-do-db="PRODUCTION_FR"
PRODUCTION_UK.replicate-do-db="PRODUCTION_UK"

Then you can restart MySQL (Don’t forget to launch the slaves because we skipped it at start):

/etc/init.d/mysql restart

Start replication:

* one by one:

START SLAVE 'PRODUCTION_FR';
START SLAVE 'PRODUCTION_UK';

* all together:

START ALL SLAVES;

Check the replication:

slave[(none)]>SHOW SLAVE 'PRODUCTION_UK' STATUS;
slave[(none)]>SHOW SLAVE 'PRODUCTION_FR' STATUS;
slave[(none)]>SHOW ALL SLAVES STATUS;

Tests

On slave:

slave [(none)]> use PRODUCTION_FR;
Database changed
slave [PRODUCTION_FR]> show tables;
Empty set (0.00 sec)
slave [(none)]> use PRODUCTION_UK;
Database changed
slave [PRODUCTION_UK]> show tables;
Empty set (0.00 sec)

On master1:

master1 [(none)]> use PRODUCTION;
Database changed
master1 [PRODUCTION]>CREATE TABLE `france` (id int);
Query OK, 0 rows affected (0.13 sec)
master1 [PRODUCTION]> INSERT INTO `france` SET id=1;
Query OK, 1 row affected (0.00 sec)

On master2:

master2 [(none)]> use PRODUCTION;
Database changed
master2 [PRODUCTION]>CREATE TABLE `british` (id int);
Query OK, 0 rows affected (0.13 sec)
master2 [PRODUCTION]> INSERT INTO `british` SET id=2;
Query OK, 1 row affected (0.00 sec)

On slave:

-- for FRANCE
slave [(none)]> use PRODUCTION_FR;
Database changed
slave [PRODUCTION_FR]> show tables;
 +-------------------------+
 | Tables_in_PRODUCTION_FR |
 +-------------------------+
 | france |
 +-------------------------+
 1 row in set (0.00 sec)
slave [PRODUCTION_FR]> SELECT * FROM france;
 +------+
 | id |
 +------+
 | 1 |
 +------+
 1 row in set (0.00 sec)
 
-- for British
 slave [(none)]> use PRODUCTION_UK;
 Database changed
 slave [PRODUCTION_UK]> show tables;
 +-------------------------+
 | Tables_in_PRODUCTION_UK |
 +-------------------------+
 | british |
 +-------------------------+
 1 row in set (0.00 sec)
slave [PRODUCTION_UK]> SELECT * FROM british;
 +------+
 | id |
 +------+
 | 2 |
 +------+
 1 row in set (0.00 sec)

It works!

Limitations

WARNING: it doesn’t work with the database specified in query.
This work fine:

 USE PRODUCTION;
 UPDATE `ma_table` SET id=1 WHERE id =2;

This query will break the replication:

 USE PRODUCTION;
 UPDATE `PRODUCTION`.`ma_table` SET id=1 WHERE id =2;

=> database `PRODUCTION` does not exist on this server.

Real examples

Missing update

On master1:

master1 [(none)]>UPDATE `PRODUCTION`.`france` SET id=3 WHERE id =1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
master1 [(none)]> select * from `PRODUCTION`.`france`;
 +------+
 | id |
 +------+
 | 3 |
 +------+
 1 row in set (0.00 sec)

On slave:

slave [PRODUCTION_FR]> SELECT * FROM france;
 +------+
 | id |
 +------+
 | 1 |
 +------+
 1 row in set (0.00 sec)

In this case we missed the update.
it’s a real problem because the replication should crash, the slave is desynchronized with master1 and we didn’t know it.

Replication crash

On master1:

master1[(none)]> use PRODUCTION;
Database changed
master1 [PRODUCTION]> SELECT * FROM`PRODUCTION`.`france`;
 +------+
 | id |
 +------+
 | 3 |
 +------+
 1 row in set (0.00 sec)
master1 [PRODUCTION]>UPDATE `PRODUCTION`.`france` SET id=4 WHERE id =3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
master1 [PRODUCTION]> SELECT * FROM `PRODUCTION`.`france`;
 +------+
 | id |
 +------+
 | 4 |
 +------+
 1 row in set (0.01 sec)

On pmacli:

ScreenShot039

On slave:

slave [PRODUCTION_FR]> show slave 'PRODUCTION_FR' status\G
*************************** 1. row ***************************
...
Slave_IO_State: Waiting for master to send event
Last_SQL_Error: Error 'Table 'PRODUCTION.france' doesn't exist' on query. Default database: 'PRODUCTION_FR'. Query: 'UPDATE `PRODUCTION`.`france` SET id=4 WHERE id =3'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2370966657
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)

We got the error!

 


 

PhotoBox_NewLogo_jan07_1

Author: Aurélien LEQUOY
License: This article is under : ”’The GNU General Public License v3.0”’ http://opensource.org/licenses/GPL-3.0

FOSDEM slides ok, but where are your slides from PLUK?

As usual, you can find the slides from PLUK 2013 and FOSDEM 2014 on [Plus].
You have just to access to the slides page and free up some time to discover and read all the referenced content.

Remember the programs of these fantastic events :

Now, I’ve just discovered that many slides are missing for PLUK 2013 on the perconalive website.

For latecomers, it’s still time to upload your slides from your dedicated speaker page.
Please, do it quickly, the attendees, the community, the committee and myself will be grateful to enjoy your slides!

Cheers

How do you want to get [Plus] in 2014!

2013 was and we wish you a happy new year 2014!
Last year was amazing and we are looking forward to live 2014.

I’m honored to take part of two amazing events in 2014, the FOSDEM and PLMCE.
By my lowly contribution as committee member of these two events, I wanted to promote open source in general and MySQL in particular.
And the schedule of these events tells me that we won the bet!
(PLMCE schedule will be published next week)

MySQL raises interest more and again in 2014 and is still in the heart of major architectures ;-)
We are proud to promote community events, community projects and open source initiatives for many years!

Also, for several months we have been working hard on glimpsee.net.
We hope to offer you a private beta release of our elegantly simple dashboard in the next few months.
Glimpsee has been already tested by privileged users since last summer.
And I heard they couldn’t live without our dynamic dashboard…

Hope to meet you somewhere on earth in 2014.

Cheers

Percona Live London 2013 : My Chairman point of view!

I would like to write a short and dislocated post on my condition of Chairman of the PLUK this year. When I accepted this mission, I had no clue what was going to be my job and I have no regret about this decision.
Because it was a fantastic experience and a wonderful conference.

So, I would like to thanks Kortney and Percona again for trusting me.
My committee fellows, David, Todd, Kenny, Ben and Ivan for their involvement.
All those of you who have submitted talks and tutorials for their high level and quality.
And of course all the attendees, we have cooked up this conference for you, thank you for being there.

I jumped into this adventure with my beliefs and I wanted to do it as honestly as possible and as humbly as possible.
I attended all the last PLUK conferences and I quickly knew what I wanted for this new event.

My priority was to have full day tutorials and some new faces, and I think we had both of them.
I wanted some stories, Etsy, Spil Games, WordPress, eBay and Bodybuilding.com told us their stories.
The opening keynotes from Peter, Monty and Tomas have been the icing on the cake!

As you can imagine, manage a committee with members from Percona, Oracle and SkySQL was a bit tricky. But I was very impressed by the loyalty and passion to achieve this mission together.
No, the real difficulty for us was to rate and choose only 24 among more than 100 submitted talks.

I’m not really the best person to say that but I think we did the job and I hope you enjoyed the conference.
Please, share the sessions you loved on your favorite social network and post a comment with your thoughts about the conference.

Now, it’s time to move forward :

  • CfP for FOSDEM 2014 (MySQL devroom) is still open until December 8th, submit ASAP!
  • CfP for PLMCE 2014 is still open until December 7th, you know what you have to do!
  • You can also register right know for PLMCE 2014 and enjoy a very sweet price

To be continued…