Adding your own collation to MySQL

Send to Kindle

I’m the kind of DBA that prefers to keep everything simple, BUT, sometimes it’s not possible. Few days ago I’ve faced an issue where none of the collations shipped by default with MySQL would guarantee integrity of my database, and to avoid a massive re-write of application code, we have explored an option that up to the day, I didn’t know about.
Add your own collation to MySQL.

This option is described on this section of MySQL documentation . On this post I will show how to make MySQL identify volves with acute accent (fada) as a different letter:

First, let’s find out where is our character-set folder:

mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'character_sets_dir';
+--------------------+---------------------------------------+
| Variable_name | Value |
+--------------------+---------------------------------------+
| character_sets_dir | /mysql/sources/5.6.26/share/charsets/ |
+--------------------+---------------------------------------+
1 row in set (0.00 sec)

In this case, I’ll be working on /mysql/sources/5.6.26/share/charsets/ it may be different on your environment.
You will find a file named Index.xml inside your character_sets_dir.

On this file, we will be using the locate data markup language syntax (LDML), more specifically reset, p and t elements.
You can have more information about LDML and the complete list of elements LDML Syntax Supported in MySQL.

Let’s edit this file and add our collation inside :

<charset name="utf8">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
 . . .
  <collation name="utf8_test_ci" id="1122" version="5.2.0">
    <rules>
      <reset>A</reset>
      <p>\u00c1</p>
      <t>\u00e1</t>
      <reset>E</reset>
      <p>\u00c9</p>
      <t>\u00e9</t>
      <reset>I</reset>
      <p>\u00cd</p>
      <t>\u00ed</t>
      <reset>O</reset>
      <p>\u00d3</p>
      <t>\u00f3</t>
      <reset>U</reset>
      <p>\u00da</p>
      <t>\u00fa</t>
    </rules>
  </collation>
</charset>

So, let’s explain the first rule:

<reset>A</reset>
<p>\u00c1</p>
<t>\u00e1</t>
  • reset – is reseting the ordering an rules for the A (and a) character
  • p – is saying that \u00c1 (Á) will be treated as a different letter
  • t – is saying that \u00e1 (á) will be treated as the lower case version of \u00c1 (Á)

The same applies for the other letters (E / I / O / U). Now all we need is to restart MySQL service.

Let’s do some testing to find out how it works, I’ll create 2 tables one with utf8 default collation and 1 with our test collation:


mysql [localhost] {msandbox} (test) > CREATE TABLE `utf8` ( `name` varchar(50), PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('A');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('Á');
ERROR 1062 (23000): Duplicate entry 'Á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('á');
ERROR 1062 (23000): Duplicate entry 'á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > SELECT * FROM utf8;
+------+
| name |
+------+
| A |
+------+
1 row in set (0.00 sec)

MySQL only allowed 1 variation of the letter A, all subsequent failed due to duplication of the PRIMARY KEY , let’s see how it looks on our test collation:


mysql [localhost] {msandbox} (test) > CREATE TABLE `utf8_test` ( `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_test_ci , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('A');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('Á');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('á');
ERROR 1062 (23000): Duplicate entry 'á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > SELECT * FROM utf8_test;
+------+
| name |
+------+
| A |
| Á |
+------+
2 rows in set (0.00 sec)

In this case, MySQL allowed 2 entries, A and Á, it blocked a because it’s a duplicate of A and it blocked á because it’s a duplicate of Á.

That is it, I hope it will be of any usage for you.

Send to Kindle
Posted in Charset, Collation, MySQL | Leave a comment

MySQL & NoSQL – Memcached Plugin

Send to Kindle

Many of you have already heard about NoSQL databases and one of the the most used tool is Memcached, where you add a cache layer between the application and database. Since MySQL version 5.6, a new plugin is available to do the integration between MySQL and Memcached. On this article, we will learn how to install it on linux, and some basic configurations of it.

Pre-requirements:
Install libevent

Installation:
To install memcached support we will need to create a few tables responsible for MySQL and memcached integration. MySQL already includes the file which creates those tables (innodb_memcached_config.sql), you can find this file in a sub folder of your basedir. To discover where is your basedir, run the bellow command:

mysql> SHOW VARIABLES LIKE 'basedir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr  |
+---------------+-------+
1 row in set (0.00 sec)

If you have installed MySQL via your distro repository, the path will be as follow:

$basedir/share/mysql/innodb_memcached_config.sql

In case you have used MySQL binaries, the path will be as follow:

$basedir/share/innodb_memcached_config.sql

Now we will run this .sql file. By default, the script creates a test table on test database, but on our tests we will use the memcached db:

mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql> source /usr/share/mysql/innodb_memcached_config.sql
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

mysql>

Now let’s create our table to store memcached data:

mysql> CREATE DATABASE IF NOT EXISTS memcached;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE memcached.dados LIKE test.demo_test;
Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE innodb_memcache.containers SET db_schema = 'memcached', db_table = 'dados' WHERE name = 'aaa' LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DROP DATABASE test;
Query OK, 1 row affected (0.07 sec)

Next step is to install memcached plugin on MySQL. To do it we will use INSTALL PLUGIN command:

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.01 sec)

To verify if the plugin was successful installed, we can run the bellow command:

mysql> \! netstat -tunap | grep LIST | grep mysql
tcp        0      0 0.0.0.0:11211               0.0.0.0:*                   LISTEN      2032/mysqld         
tcp        0      0 :::11211                    :::*                        LISTEN      2032/mysqld         
tcp        0      0 :::3306                     :::*                        LISTEN      2032/mysqld         
mysql> 

Configuring and Using:

Now we will test memcached via some programming language, PHP in this case:

 [root@localhost memcache]# cat test1.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$m->set('key1', 'Testing memcached');
echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>
[root@localhost memcache]# php test1.php 
Value of key1 is:Testing memcached
[root@localhost memcache]# 

Now, let’s see what is stored on MySQL?

mysql> SELECT * FROM memcached.dados;
+------+-------------------+------+------+------+
| c1   | c2                | c3   | c4   | c5   |
+------+-------------------+------+------+------+
| key1 | Testing memcached |    0 |   19 |    0 |
+------+-------------------+------+------+------+
1 row in set (0.00 sec)

mysql> 

What happens if we change an entry manually on MySQL?

mysql> UPDATE memcached.dados SET c2 = 'Entry modified  directly on MySQL';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


[root@localhost memcache]# cat test2.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>

[root@localhost memcache]# php test2.php 
Value of key1 is:Entry modified  directly on MySQL

[root@localhost memcache]# 


What about if we want to store the entries on a different MySQL table?
We just need to create a new table, add a new container and use the delimiter which is configured on config_options table from innodb_memcache database:

mysql> SELECT * FROM innodb_memcache.config_options WHERE name = 'table_map_delimiter';
+---------------------+-------+
| name                | value |
+---------------------+-------+
| table_map_delimiter | .     |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE memcached.dados2 LIKE memcached.dados;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('bbb', 'memcached', 'dados2', 'c1', 'c2', 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.01 sec)

We have created a new table named dados2 and added a new container to refer to that table, which we named as bbb, now we just need to use that as prefix on memcached:

 [root@localhost memcache]# cat test3.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$m->set('@@bbb.key1', 'Should be stored on dados2 table');
echo 'Value of bbb.key1 is:' . $m->get('@@bbb.key1') . "\n";
?>
[root@localhost memcache]# php test3.php 
Value of bbb.key1 is:Should be stored on dados2 table
[root@localhost memcache]# 


mysql> SELECT * FROM memcached.dados2;
+------+----------------------------------+------+------+------+
| c1   | c2                               | c3   | c4   | c5   |
+------+----------------------------------+------+------+------+
| key1 | Should be stored on dados2 table |    0 |   22 |    0 |
+------+----------------------------------+------+------+------+
1 row in set (0.00 sec)

We also can map the table to store values into separate fields on the table.
Verify that on the config_options table we already have a separator configured:

mysql> SELECT * FROM innodb_memcache.config_options WHERE name = 'separator';
+-----------+-------+
| name      | value |
+-----------+-------+
| separator | |     |
+-----------+-------+
1 row in set (0.00 sec)

This character we will use to store values into multiple columns. Let’s create a table and add it to a new container(we will specify the columns we want the values separated by comma ‘,’):

mysql> CREATE TABLE products (id varchar(128), name varchar(255), value varchar(15), c3 int, c4 bigint, c5 int, PRIMARY KEY(id));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('products', 'memcached', 'products', 'id', 'name,value', 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.00 sec)

Now, lets create an array of products and add them to memcached:

[root@localhost memcache]# cat test4.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$products = array(
array('1', 'TV', '1999,00'),
array('2', 'Hack', '399,00'),
array('3', 'Table', '599,00'),
array('4', 'Chair', '99,00')
);

foreach($products as $product)
{
	$key = '@@products.' . $product[0];
	$value = $product[1] . '|' . $product[2];
	$m->set($key, $value);
}

?>
[root@localhost memcache]# php test4.php 



mysql> SELECT * FROM memcached.products;
+----+----------+---------+------+------+------+
| id | nome     | valor   | c3   | c4   | c5   |
+----+----------+---------+------+------+------+
| 1  | TV       | 1999,00 |    0 |   23 |    0 |
| 2  | Hack     | 399,00  |    0 |   24 |    0 |
| 3  | Table    | 599,00  |    0 |   25 |    0 |
| 4  | Chair    | 99,00   |    0 |   26 |    0 |
+----+----------+---------+------+------+------+
4 rows in set (0.00 sec)

Server/Service restart:

Let’s see what happens if we have to restart MySQL service(Same apply to server reboot)?
Will the data remain available on memcached after it?

[root@localhost memcache]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@localhost memcache]# cat test5.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>

[root@localhost memcache]# php test5.php 
Value of key1 is:Entry modified  directly on MySQL

[root@localhost memcache]# 

That is it! Entries will remain available even after reboot’s/restart’s.

SELinux:

On environments where we have SELinux enabled, it can block memcached integration because MySQL is not allowed to listen on memcached port, where is a tip on how to allow it(I’m using CentOS as linux distro):

Search for entries containing mysqld and dinied keywords on /var/log/audit/audit.log , if you find any, type the bellow commands to create a new SELinux module to allow it:

type=AVC msg=audit(1421080542.433:31): avc:  denied  { name_bind } for  pid=1360 comm="mysqld" src=11211 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:memcache_port_t:s0 tclass=tcp_socket
type=SYSCALL msg=audit(1421080542.433:31): arch=c000003e syscall=49 success=no exit=-13 a0=2d a1=7f5fcc0409a0 a2=10 a3=7f5fe0ee275c items=0 ppid=1123 pid=1360 auid=0 uid=27 gid=27 euid=27 suid=27 fsuid=27 egid=27 sgid=27 fsgid=27 tty=pts0 ses=2 comm="mysqld" exe="/usr/sbin/mysqld" subj=unconfined_u:system_r:mysqld_t:s0 key=(null)


audit2why < /var/log/audit/audit.log

cd /root/
mkdir selinux-custom
cd selinux-custom
audit2allow -a -M mysql-memcache
semodule -i mysql-memcache.pp

Opcoes do memcached: In case you want to modify any memcached specific option, you can add the daemon_memcached_option to MySQL config file, for example, to change memcached port:

#no arquivo de configuração (normalmente my.cnf)
daemon_memcached_option="-p11222"

That is it, I hope you’ve learned how to install and configure MySQL and memcached plugin integration.

Send to Kindle
Posted in InnoDB, install, MySQL, optimize, performance | Leave a comment

MySQL Backup Strategy – Slides

Send to Kindle

Hi there, it’s been a few months since Percona Live London has finished, but I managed just now to get sometime to post the slides from my presentation about backups. Please, take a look and feel free to comment.

Pdf Version


Send to Kindle
Posted in backup, MySQL, Security | Leave a comment

MySQL Fabric – Part 1 – Installing

Send to Kindle

MySQL Fabric is a tool included on MySQL Utilities that helps you to manage your MySQL instances.
It works by basically adding a new layer between your application and MySQL instances, which can provide an easy way to use sharding and build a high available system.

For More information about what is MySQL Fabric, please follow the documentation.

To install our Fabric environment, we will have to configure 4 servers, I will use the follow names and IP on this tutorial:

fabric1 (192.168.0.200) - fabric
mysql1 (192.168.0.201) - mysql master
mysql2 (192.168.0.202) - mysql slave
mysql3 (192.168.0.203) - mysql slave

Note: I’m running CentOS 6.5 on all servers.

1. Add mysql repo on all 4 machines, please read Installing latest version of MySQL via yum for more info:

rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum update

2. Install mysql mysql-server mysql-utilities:

yum install mysql mysql-server mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start

3. On mysql1,mysql2,mysql3 add the follow to my.cnf:

[mysqld]
...
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=192.168.0.201
report-port=3306
server-id=1
log-bin=mysql1-bin.log

4. On mysql1 add a replication user for each mysql ip:


GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "

5. On mysql1 add the privileges to fabric user from fabric node ip:


GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"

6. On mysql2 and mysql3, setup replication:

CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

7. On fabric1, add the fabric MySQL user:


GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';

[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"

8. On fabric1,, configure user and password on [storage] and [servers] group on /etc/mysql/fabric.cfg :


[storage]
...
password = fabricpwd
...
[servers]
password = reppwd
user = replication

9. On fabric1, start the fabric db, it will ask to create a password, this password will be used on all next mysqlfabric commands:


mysqlfabric manage setup

[root@fabric1 ~]# mysqlfabric manage setup
[INFO] 1406131468.176740 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc: 
Repeat Password:
Password set.

10. On fabric1, start fabric:


mysqlfabric manage start &

11. On fabric1,, add a group:

mysqlfabric group create GLOBAL1

[root@fabric1 ~]# mysqlfabric group create GLOBAL1
Password for admin: 
Procedure :
{ uuid        = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

12. On fabric1,, add mysql1, mysql2 and mysql3 to GLOBAL1 group:


mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203

[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201
Password for admin: 
Procedure :
{ uuid        = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202
Password for admin: 
Procedure :
{ uuid        = c8babfb9-d836-44c0-b4fd-015cd1df8298,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203
Password for admin: 
Procedure :
{ uuid        = c86bba70-69ac-4923-9c54-1a8aaab6d97e,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

13. On fabric1, get the uuid of your master:

mysqlfabric group lookup_servers GLOBAL1

[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}

14. On fabric1, add your master as master on GLOBAL1 group:

mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'

[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'
Password for admin: 
[WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)).
[INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242.
Procedure :
{ uuid        = 733ae69d-fb12-447b-b86b-041703491315,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}
[root@fabric1 ~]# 

That is it, we now have our MySQL Fabric environment working and ready.
Watch out for my next few posts to learn more about MySQL Fabric.

Send to Kindle
Posted in install, MySQL, performance, replication | 2 Comments

MySQL Sandbox

Send to Kindle

Hi there, today we will learn about an amazing tool that every single MySQL dba must know, I’m talking about MySQL Sandbox.

MySQL Sandbox is developed by Giuseppe Maxia (The Data Charmer), it’s a tool that make the installation of MySQL servers very easy. If you need to quickly create a MySQL instance for test or a replication setup(it supports master slave, circular and master master replication), this it the tool.

INSTALLATION:

Go to http://mysqlsandbox.net/ and get the latest version (I got from launchpad):

yum install perl perl-ExtUtils-MakeMaker perl-Test-Simple
wget https://launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.44.tar.gz
tar -zxvf MySQL-Sandbox-3.0.44.tar.gz
cd MySQL-Sandbox-3.0.44
perl Makefile.PL
make
make test
make install

CREATING A SINGLE SANDBOX:

To create a single sandbox, all you need is the mysql package that you want install and the make_sandbox command:

[root@localhost ~]# make_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
unpacking /root/mysql-5.6.17-linux-glibc2.5-i686.tar.gz
Executing low_level_make_sandbox --basedir=/root/5.6.17 \
	--sandbox_directory=msb_5_6_17 \
	--install_version=5.6 \
	--sandbox_port=5617 \
	--no_ver_after_name \
	--my_clause=log-error=msandbox.err
    The MySQL Sandbox,  version 3.0.44
    (C) 2006-2013 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /root/sandboxes
sandbox_directory              = msb_5_6_17
sandbox_port                   = 5617
check_port                     = 
no_check_port                  = 
datadir_from                   = script
install_version                = 5.6
basedir                        = /root/5.6.17
tmpdir                         = 
my_file                        = 
operating_system_user          = root
db_user                        = msandbox
remote_access                  = 127.%
bind_address                   = 127.0.0.1
ro_user                        = msandbox_ro
rw_user                        = msandbox_rw
repl_user                      = rsandbox
db_password                    = msandbox
repl_password                  = rsandbox
my_clause                      = log-error=msandbox.err
master                         = 
slaveof                        = 
high_performance               = 
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > 
force                          = 
no_ver_after_name              = 1
verbose                        = 
load_grants                    = 1
no_load_grants                 = 
no_run                         = 
no_show                        = 
do you agree? ([Y],n) 
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_6_17

To use it you can call the use script inside the sandbox folder:

[root@localhost ~]# $HOME/sandboxes/msb_5_6_17/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > 

You can manage your sandbox by calling the start / stop / restart / status script inside the sandbox folder

CREATING A MASTER SLAVE REPLICATION:

To create a master slave replication topology (by default is set to 1 master and 2 slaves but it can be changed passing the --how_many_nodes parameter) we will use the make_replication_sandbox command:

[root@localhost ~]# make_replication_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
installing and starting master
installing slave 1
installing slave 2
starting slave 1
.... sandbox server started
starting slave 2
.. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_6_17

To use it we can call the use script, for replication, the use script will be located inside the nodeN/Master folder:

[root@localhost ~]# #MASTER
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/master/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 1
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node1/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave1 [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 2
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node2/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave2 [localhost] {msandbox} ((none)) > 

On replication sandbox, you can manage the individual sandbox by calling the start / stop / restart / status script inside the node / master folder or you can call the scripts ending with _all located on the sandbox folder (start_all / stop_all / restart_all / status_all).

CREATING A MULTI MASTER REPLICATION:

To create a multi-master sandbox we will use the make_replication_sandbox with --master_master option:

[root@localhost ~]# make_replication_sandbox --master_master mysql-5.6.17-linux-glibc2.5-i686.tar.gz
installing node 1
installing node 2
# server: 1: 
# server: 2: 
# server: 1: 
# server: 2: 
Circular replication activated
group directory installed in $HOME/sandboxes/rcsandbox_mysql-5_6_17

That is it, you can find more information using the help parameter on make_multiple_custom_sandbox make_multiple_sandbox make_replication_sandbox make_sandbox make_sandbox_from_installed and make_sandbox_from_source

Send to Kindle
Posted in install, MySQL, replication | 1 Comment

Warm-up InnoDB Buffer Pool

Send to Kindle

As we know, one of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed).

As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer.
To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6 (it was presented on previous versions of Percona Server and MariaDB).

If you have your production server already running, we are going to set it to dump the content every time it shutdown:

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

Now, the next time we stop MySQL, a file named ib_buffer_pool will be created under the MySQL datadir (you can change the file name by using the innodb_buffer_pool_filename config).

Next step is to tell MySQL to read the this file and load the actual page data into the buffer pool, we will do it by adding a new entry named innodb_buffer_pool_load_at_startup on my.cnf (and also add innodb_buffer_pool_dump_at_shutdown to it, this way it will automatically dump it every time we shutdown the server):

[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Now we can restart our mysql server, as mention before, it will create a file with a content similar to the above:

[root@marcelodb data]# tail ib_buffer_pool
0,69618
0,69619
0,69620
0,69621
0,69622
0,69623
0,69624
0,69625
0,69626
0,69627

When we restart our server, we can see 2 new messages being written to MySQL logs:

2014-01-07 19:53:54 7fad34bc7700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2014-01-07 19:53:54 7fad34bc7700 InnoDB: Buffer pool(s) dump completed at 140107 19:53:54

This means that MySQL have wrote the file with the buffer pool pages.

2014-01-07 19:54:01 7f68eee67700 InnoDB: Buffer pool(s) load completed at 140107 19:54:01

This means that MySQL have finished to reload the pages content back to the InnoDB buffer pool.

You can controller the dump / reload progress through the Innodb_buffer_pool_dump_status and Innodb_buffer_pool_load_status:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+------------------------+
| Variable_name                  | Value                  |
+--------------------------------+------------------------+
| Innodb_buffer_pool_load_status | Loaded 5121/6441 pages |
+--------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140108 16:55:05 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

If you wish, you can tell MySQL to dump/reload the buffer pool right now using innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now.

If you use InnoDB as storage engine of any of you tables, that is a very cool practice to put in place to minimize the side effects of a server/service restart.

Send to Kindle
Posted in InnoDB, MySQL, optimize, performance | 5 Comments

Installing latest version of MySQL via yum

Send to Kindle

Usually when you try to install MySQL via yum, by default, the version that comes with your OS is always an old version (not the latest version available from mysql.com/downloads).
In order to have the latest version, you always needed to go to mysql.com/downloads and manually download and install MySQL packages right? Not anymore, MySQL recently released the MySQL YUM Repository.
Basically, it will allows you to install the latest GA version of MySQL Community Server, Workbench and Connector/ODBC.

HOW TO INSTALL?
You can basically install it in 2 ways:

1. Using the RPM package
Download the .rpm from dev.mysql.com/downloads/repo/ and install it:

wget http://dev.mysql.com/get/mysql-community-release-el6-3.noarch.rpm/from/http://repo.mysql.com/
sudo yum localinstall -y mysql-community-release-el6-3.noarch.rpm

2. Manually adding a file under /etc/yum.repos.d/ folder
Add the bellow content to a new file named mysql-community.repo :

[mysql-community]
name=MySQL Community Server 
baseurl=URL
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Replace baseurl=URL according to your OS:
EL6 (Centos6 / Redhat 6):

baseurl=http://repo.mysql.com/yum/mysql-community/el/6/$basearch/

Fedore 18 and 19:

baseurl=http://repo.mysql.com/yum/mysql-community/fc/$releasever/$basearch/

Note that we have gpgcheck=1, if we use it as 1 in this install method, we will need to add the GnuPG key (see http://dev.mysql.com/doc/refman/5.6/en/checking-gpg-signature.html for more details).

INSTALLING PACKAGES:
Now that we have our repo enabled, it’s just a normal yum command to install the latest version of MySQL Community Server:

yum install mysql-server

LISTING AVAILABLE PACKAGES:
To list the packages available in the yum repo, use the bellow command:

sudo yum --disablerepo=\* --enablerepo=mysql-community list available
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
mysql-community-client.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-common.i686                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-common.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-devel.i686                                                                                                                                        5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-devel.x86_64                                                                                                                                      5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded.i686                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded.x86_64                                                                                                                                   5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded-devel.i686                                                                                                                               5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded-devel.x86_64                                                                                                                             5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs.i686                                                                                                                                         5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs.x86_64                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs-compat.i686                                                                                                                                  5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs-compat.x86_64                                                                                                                                5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-server.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-test.x86_64                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-connector-odbc.x86_64                                                                                                                                       5.2.6-1.el6                                                                                                                               mysql-community
mysql-workbench-community.x86_64                                                                                                                                  6.0.7-4.el6                                                                                                                               mysql-community

Remember, before change anything on a production server, test, test, test first :)

Send to Kindle
Posted in install, MySQL | 1 Comment

Auditing MySQL With Mcafee Audit Plugin

Send to Kindle

mysql-audit

Audit MySQL isn’t an easy task by default, you can use some technics like tcpdump, write a parser for general log, use MySQL proxy, or you can use some of audit plugins available out there(Mcafee MySQL Audit Plugin or MySQL Enterprise Audit Log Plugin for example).

On this post I’ll cover the Mcafee MySQL Audit Plugin (https://github.com/mcafee/mysql-audit), on a follow-up post I’ll talk about MySQL Enterprise Audit Log Plugin.

The installation is easy and require just a few steps, I’m using MySQL 5.5 32 bits, so I’ll download the files for my MySQL version and architecture from https://github.com/mcafee/mysql-audit/downloads

[root@mysql-audit marcelo]# wget https://github.com/downloads/mcafee/mysql-audit/audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
[root@mysql-audit marcelo]# unzip audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
Archive:  audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
   creating: audit-plugin-mysql-5.5/
   creating: audit-plugin-mysql-5.5/lib/
  inflating: audit-plugin-mysql-5.5/lib/libaudit_plugin.so  
  inflating: audit-plugin-mysql-5.5/COPYING  
  inflating: audit-plugin-mysql-5.5/THIRDPARTY.txt  
  inflating: audit-plugin-mysql-5.5/README.txt

Next step is to copy the libaudit_plugin.so to MySQL plugin dir:

mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'plugin_dir';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| plugin_dir    | /var/marcelo/5.5.33/lib/plugin/ |
+---------------+---------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > quit
Bye
[root@mysql-audit audit-plugin-mysql-5.5]# cp lib/libaudit_plugin.so /var/marcelo/5.5.33/lib/plugin/

In order to work, Mcafee MySQL Audit Plugin needs to extract some offsets from MySQL server, some of them are build-in the the Audit code, but some aren’t (see https://github.com/mcafee/mysql-audit/wiki/Troubleshooting for more information).
GDB will be necessary to extract the offsets, use yum install gdb or apt-get install gdb

[root@mysql-audit marcelo]# wget https://raw.github.com/mcafee/mysql-audit/v1.0.3/offset-extract/offset-extract.sh
[root@mysql-audit marcelo]# ./offset-extract.sh 5.5.33/bin/mysqld
//offsets for: 5.5.33/bin/mysqld (5.5.33)
{"5.5.33","3172729c5bf6e81c8d87fe26fe248204", 3816, 3844, 2368, 2700, 44, 1656},

Now we are going to change our .cnf file to load and enable the plugin by default:

# add under the [mysqld] section of your .cnf
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=3816, 3844, 2368, 2700, 44, 1656
audit_json_file=1

Restart the server, by default, a file named mysql-audit.json is created on MySQL datadir, from now one, you can parse the file to extract the information that you think is relevant to you, see bellow some examples:

Get failed attempts to connect to MySQL server:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"cmd\":\"Failed Login\"'
{"msg-type":"activity","date":"1381764013653","thread-id":"65","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764016225","thread-id":"66","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764026005","thread-id":"67","query-id":"0","user":"root","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}

Get sucessful attempts to connect to MySQL server:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"cmd\":\"Connect\"'
{"msg-type":"activity","date":"1381763915626","thread-id":"60","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763915626","thread-id":"61","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763915626","thread-id":"62","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763916006","thread-id":"64","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}

Get all activities from a specific host

[root@mysql-audit data]# cat mysql-audit.json | grep '\"ip\":\"10.10.2.68\"'
{"msg-type":"activity","date":"1381763919836","thread-id":"60","query-id":"4798","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"SELECT SUM(K) FROM sbtest8 WHERE id BETWEEN 151257 AND 151257+99"}
{"msg-type":"activity","date":"1381763919844","thread-id":"62","query-id":"4799","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest6","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest6 WHERE id BETWEEN 141568 AND 141568+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919847","thread-id":"47","query-id":"4800","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"update","objects":[{"db":"test","name":"sbtest3","obj_type":"TABLE"}],"query":"UPDATE sbtest3 SET k=k+1 WHERE id=150189"}
{"msg-type":"activity","date":"1381763919848","thread-id":"60","query-id":"4801","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest8 WHERE id BETWEEN 175916 AND 175916+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919869","thread-id":"64","query-id":"4803","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest10","obj_type":"TABLE"}],"query":"SELECT DISTINCT c FROM sbtest10 WHERE id BETWEEN 132850 AND 132850+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919881","thread-id":"57","query-id":"4784","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"commit","query":"COMMIT"}
{"msg-type":"activity","date":"1381763919930","thread-id":"37","query-id":"4802","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"commit","query":"COMMIT"}
{"msg-type":"activity","date":"1381764013653","thread-id":"65","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764016225","thread-id":"66","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764026005","thread-id":"67","query-id":"0","user":"root","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}

Get all DELETES on a specific table(sbtest8) from a specific host

[root@mysql-audit data]# cat mysql-audit.json | grep -i '\"cmd\":\"delete\"' | grep -i '\"name\":\"sbtest8\"' | grep '\"ip\":\"10.10.2.68\"'
{"msg-type":"activity","date":"1381763914163","thread-id":"53","query-id":"3366","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=150652"}
{"msg-type":"activity","date":"1381763914947","thread-id":"44","query-id":"3513","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=194099"}
{"msg-type":"activity","date":"1381763915718","thread-id":"36","query-id":"3630","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=151405"}
{"msg-type":"activity","date":"1381763916273","thread-id":"48","query-id":"3799","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=148546"}
{"msg-type":"activity","date":"1381763918698","thread-id":"40","query-id":"4437","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=149492"}

As you can see, you can extract and adjust the log to fit your needs, there is also some configurations that you can enable to change the way the plugin works (https://github.com/mcafee/mysql-audit/wiki/Configuration for more info) :

audit_record_cmds – list of commands that you would like to record, for example, if you want to record only failed connections and connections, change it to : audit_record_cmds=”Failed Login,Connect”
audit_record_objs – list of objects/tables that you would like to record activity, for example, if you want to record only activity on test database, change it to audit_record_objs=”test.*”, if you want to record activity only for table sbtest1 and sbtest2 change it to audit_record_objs=”test.sbtest1,test.sbtest2″

That is it, use you imagination and start to audit your MySQL server.

Send to Kindle
Posted in install, mysqld, Security | 7 Comments

Multi-Source Replication with MySQL 5.7 – example

Send to Kindle

Complementing this previous post, today we are going to configure 1 slave receiving updates from 2 master with Multi-source replication.
As mention before, this feature is only available on labs.mysql.com.

To configure it, is very simple, we are going to nees 2 masters with GTID enabled (see this post to know how to configure) and a slave with crash save options enabled.

Master 1 e 2:

gtid-mode=on
enforce-gtid-consistency

Slave

master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid-mode=on
enforce-gtid-consistency

Let’s first create our replication user:

master1 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'127.0.0.1' IDENTIFIED BY '123';
master2 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'127.0.0.1' IDENTIFIED BY '123';

Now we are going to configure our slave normally, there is a new option named FOR CHANEL, that will identify which master are we connecting to:

slave [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_PORT=7773 FOR CHANNEL="channel1";
slave [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_PORT=7774 FOR CHANNEL="channel2";

Now, we just need to star the replication:

slave [localhost] {msandbox} ((none)) > START SLAVE FOR ALL CHANNELS;

Let’s insert some data in both master to see if we can retrieve it on our slave:

master1 [localhost] {msandbox} ((none)) > CREATE TABLE test.tst1 (ID INT);
Query OK, 0 rows affected (0.12 sec)

master1 [localhost] {msandbox} ((none)) > INSERT INTO test.tst1 SET ID=1;
Query OK, 1 row affected (0.01 sec)

master2 [localhost] {msandbox} ((none)) >  CREATE TABLE test.tst2 (ID INT);
Query OK, 0 rows affected (0.06 sec)

master2 [localhost] {msandbox} ((none)) > INSERT INTO test.tst2 SET ID=2;
Query OK, 1 row affected (0.01 sec)

slave [localhost] {msandbox} ((none)) > USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
slave [localhost] {msandbox} (test) > SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tst1           |
| tst2           |
+----------------+
2 rows in set (0.00 sec)

slave [localhost] {msandbox} (test) > SELECT * FROM tst1;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

slave [localhost] {msandbox} (test) > SELECT * FROM tst2;
+------+
| ID   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Some useful commands:

START | STOP SLAVE [SQL_THREAD | IO_THREAD] FOR CHANNEL=”channel name";
START | STOP SLAVE [SQL_THREAD | IO_THREAD] FOR ALL CHANNELS;
SHOW SLAVE STATUS FOR CHANNEL='channel name'\G
FLUSH RELAY LOGS FOR CHANNEL="channel name";
RESET SLAVE FOR CHANNEL="channel name";

That’s it, we now have our slave receiving updates from multiple masters with Multi-source replication.

Send to Kindle
Posted in replication | 11 Comments

MySQL Multi Source Replication

Send to Kindle

Last week, durring MySQL Connect, MySQL 5.7.2 DMR was launched, one of the new functionality is the multi source replication. At the moment, MySQL can have only one master per slave (you can archive multi source replication via some hacks, but like the name says, it’s a hack).

See how to configure here

To clarify, there is a difference between multi-master replication and multi source replication, see the bellow pictures to understand the difference:

MySQL Multi Master Replication

MySQL Multi Master Replication


Multi Master Replication – In the above picture, we have 2 master’s and 1 slave, where, master 1 is master of master 2, master 2 is master of master 1 and also of slave, in order to slave receive updates from master 1, all this updates must past to master 2 to later one be replied to slave.
MySQL Multi Source Replication

MySQL Multi Source Replication


Multi Source Replication – In the above picture, we have 2 master’s and 1 slave, master 1 is only master of slave, and master 2 is only master of slave. The slave receive updates from both master’s directly.

In the next post, I’ll explain how to configure 1 slave and 2 masters in multi source replication.
The test version can be downloades via labs.mysql.com, go there, download and try for yourself.
Remember, this is a test version, so, don’t use in production !!!

Send to Kindle
Posted in MySQL, replication | Leave a comment