[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

Why I do what I do?

I was sincerely affected by this last MySQL post and this other very long post from Jeremy Cole.
Yes, these two guys are MySQL rock stars and they are really impressives, their involvement in the MySQL community is utter!

I don’t want to write a long long speech about my simple life…
I just want to clarify why I do what I do.

Many people have asked or wondered without asking why I do what I do
(Jeremy Cole - 2013)

A few years ago Ashley Unitt asked me what I was most proud of, and now, I can make a complete answer. I’m very proud to take part of a community, MySQL has transformed my job into a passion and an incredible desire to share this passion, as honestly as possible.

What does it mean in real life?

If I can help in decision making or take part of a discussion, without any bad motivations, independently, I do it!

If the opportunity arises to have a good time over a glass of rosé and discussing open source with Stéphane Varoqui, I do it!

If I have suddenly an idea and a friend with me for several nights and weekends to achieve it, oh yes, I do it!

I do it because it’s terribly exciting and fun, even if I’ve just did it for myself… on a whim.

Yes, I’m very proud when we made MYXPLAIN.net with Max, I’m proud to work for an amazing company, I’m proud to advise someone who challenge me from the other side of the world, I’m proud to read an email from Rick James in my inbox…

I’m proud to be honest!

An incomplete list of what your developers would like to know before migrating to MySQL 5.5

A few years ago, I asked to check with me in the long (very long) change history of MySQL 5.5 documentation what are the changes in relation to the SQL syntax.
Chris Calender helped me to retrieve a list of the main changes, thanks again Chris.

Today, I would like to share this list with you.
It is simply a curated transcript of what you might find in the documentation but I’m sure it can help some of you.
 

INTO clause in nested SELECT statements

 
Previously, the parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.
 

Table aliases in DELETE statements

 
In MySQL 5.5.3, several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases.

In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for the USING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table.

As of MySQL 5.5.3, alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part.

Incorrect:

  • DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;
  • DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;

Correct:

  • DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2;
  • DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;

Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1:

  • DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;

To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database:

  • DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;

As of MySQL 5.5.3, alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases.

Statements containing alias constructs that are no longer permitted must be rewritten.
 

New reserved words

 
There are several new reserved words in 5.5 what were not reserved in 5.1.
So if you are using any of these as table names, etc., then you’ll need to surround them with backticks (`)

  •  GENERAL
  • IGNORE_SERVER_IDS
  • MASTER_HEARTBEAT_PERIOD
  • MAXVALUE
  • RESIGNAL
  • SIGNAL
  • SLOW

 

FLUSH TABLES and LOCK TABLES

 
As of MySQL 5.5.3, due to work done for Bug #989, FLUSH TABLES is not permitted when there is an active LOCK TABLES … READ.
To provide a workaround for this restriction, FLUSH TABLES has a new variant, FLUSH TABLES tbl_list WITH READ LOCK, that enables tables to be flushed and locked in a single operation.
As a result of this change, applications that previously used this statement sequence to lock and flush tables will fail:

  • LOCK TABLES tbl_list READ;
  • FLUSH TABLES tbl_list;

Such applications should now use this statement instead:

  •  FLUSH TABLES tbl_list WITH READ LOCK;

 

Fast truncation

 
As of MySQL 5.5.7, InnoDB always uses the fast truncation technique, equivalent to DROP TABLE and CREATE TABLE.
It no longer performs a row-by-row delete for tables with parent-child foreign key relationships.
TRUNCATE TABLE returns an error for such tables.
Modify your SQL to issue DELETE FROM table_name for such tables instead.
 

TIMESTAMP display width

 
In very old versions of MySQL (prior to 4.1), the TIMESTAMP data type supported a display width, which was silently ignored beginning with MySQL 4.1.
This is deprecated in MySQL 5.1, and removed altogether in MySQL 5.5.

The display width is no longer supporter for TIMESTAMP data type in MySQL 5.5
 

Aliases and wildcards

 
Aliases for wildcards (as in SELECT t.* AS 'alias' FROM t) are no longer accepted and result in an error.
Previously, such aliases were ignored silently. (Bug #27249)
 

Scientific notation

 
In addition to these official parts of the MySQL release notes, Sheeri write a post about floats, doubles ans scientific notation between MySQL 5.1 and MySQL 5.5 :

http://blog.mozilla.org/it/2013/01/17/mysql-5-1-vs-mysql-5-5-floats-doubles-and-scientific-notation/

 

This list is incomplete because I’m sure you have your own tips to share.

Source : http://dev.mysql.com/doc/relnotes/mysql/5.5/en/

The strange commit behavior and the invisible Xid_log_event

Did you see this when you are migrating from your lovely MySQL 5.1 to MySQL 5.5?
Oh, sorry, you remain attached to your pretty 4.1. Yes, I know, MyISAM has become so important in your life…

Ok, seriously, I would like to share this little observation I made recently when switching to MySQL 5.5 on one slave.
You can see below two graphs for the transactional activity, there is exactly the same volume of update, delete and insert queries :

MySQL 5.1

Screenshot 2013-04-15 at 06.34.23 PM

MySQL 5.5Screenshot 2013-04-15 at 06.32.42 PM

But the gray area represents the number of commit per second.
I find that I have much more commit with MySQL 5.5, why?
The first question I asked to myself was whether MySQL was telling the truth…

And I had three options to know the truth :

  • The com_commit counter : I compared the number of begin and commit during ten minutes
    • MySQL 5.1 : I got much more begin than commit
    • MySQL 5.5 : I got exactly the same result between begin and commit
  • The relay logs : In a sample of logs, I compared the number of begin and commit 
    • MySQL 5.1 : I got exactly the same result between begin and commit
    • MySQL 5.5 : I got exactly the same result between begin and commit
  • The genaral log : I compared the number of begin and commit during ten minutes
    • MySQL 5.1 : I got exactly the same result between begin and commit
    • MySQL 5.5 : I got exactly the same result between begin and commit

Hum, ok, the com_commit counter want to play with me!

Let’s go in the general log, I had two kinds of commit output :

Query COMMIT
Query COMMIT /* implicit, from Xid_log_event */

So, I called a friend : Hey Google, what is this (f…) Xid_log_event?

http://ftp.nchu.edu.tw/MySQL/sources/doxygen/mysql-5.1/classXid__log__event.html 
http://bugs.mysql.com/bug.php?id=54605

Hum, it smells replication…

Yes, you guessed it, the commit statements from the replication stream are not recorded in the com_commit counter in 5.1.
But the begin statements are recorded in the com_begin counter…

Look at your own slave servers and compare!