MySQL 5.6'da Çok Ustalı Yineleme (=Multi-Master Replication)

Yineleme yeteneği MySQL'in 5.0 sürümünden itibaren sahip olduğu bir yetenektir. 5.6 sürümünde yamaklarda birden  fazla iş parçası tanımlamak mümkün olabilmektedir. Böylelikle usta ile aradaki gecikme düşük değerlerde tutulabilinir. 5.6 öncesinde yamaklarda iki adet iş parçacığı vardı:

1. G/Ç iş parçası: Ustanın binlog kayıtlarını okumaktan ve yerel diske yazmaktan sorumludur.
2. SQL iş parçası: G/Ç iş parçasının yerel diske yazdığı SQL cümlelerini okuyup çalıştırmaktan sorumludur. 

Artık SQL cümlelerini çalıştırmak için birden fazla iş parçası tanımlayabiliyoruz. Çok çekirdekli sistemlerde çalışan MySQL sunucusu için başarımın artacağı anlamına gelmektedir. Bunun için iş parçası sayısını slave_parallel_workers değişkenine atamak yeterli olmaktadır. Aksi belirtilmez ise varsayılan değeri sıfırdır. Bu durumda 5.6 öncesinde olduğu gibi birer iş parçası çalışır. Makinadaki çekirdek sayısı kadar değer vermek uygun olur:

mysql> set global slave_parallel_workers=4;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'slave_parallel_workers';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| slave_parallel_workers | 4     |

+------------------------+-------+

1 row in set (0.00 sec) 

MySQL'de çok ustalı yineleme yapmak mümkündür. Ancak çok ustalı yineleme ile ilgili birkaç problemin olduğunu bilmenizde fayda var. Öncelikle çok ustalı yinelemenin nasıl kurulacağını göstereceğim. Ardından karşılaşılan problemleri ve olası çözümlerine değineceğim. Çok ustalı yinelemede ikiden fazla düğüm kurmak anlamlı değildir. Bir halka yapısında oluşturulacak bu mimaride düğümlerden herhangi biri erişilemez olursa yineleme kesintiye uğrar.

İki düğümlü çok ustalı yineleme için öncelikli olarak sunucuların yapılandırma dosyalarında bir iki tanımlama yapmak gerekir. IP adresleri 192.168.1.66 (Sunucu 1) ve 192.168.1.67 (Sunucu 2) olan iki makinamız bulunsun. Sunucu 1 için yapılandırma dosyasında aşağıdaki tanımlamalar bulunmalıdır:

[mysqld]

log_bin=masterlog

log-slave-updates

server_id = 1

...

Sunucu 2 için yapılandırma dosyasında aşağıdaki tanımlamalar bulunmalıdır:

[mysqld]

log_bin=masterlog

log-slave-updates

server_id = 2

...

Bir numaralı sunucuda yinelemede kullanılacak kullanıcı oluşturulur:

mysql> grant replication slave on *.* to 'repuser'@'192.168.1.%' identified by 'secret';
Query OK, 0 rows affected (0.02 sec)

mysql> show global variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| masterlog.000001 |       473 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.14 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| masterlog.000001 |       520 |
| masterlog.000002 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> change master to
    -> master_host='192.168.1.67',
    -> master_user='repuser',
    -> master_password='secret',
    -> master_log_file='masterlog.000002',
    -> master_log_pos=120,
    -> master_port=3306;
Query OK, 0 rows affected, 2 warnings (0.30 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.67
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: masterlog.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: masterlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 456
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 55458b84-9063-11e2-ab0e-00ff10207b07
             Master_Info_File: C:\opt\mysql-5.6.10\data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

İki numaralı sunucuda (192.168.1.67) yinelemede kullanılacak kullanıcı oluşturulur:

mysql> grant replication slave on *.* to 'repuser'@'192.168.1.%' identified by 'secret';

Query OK, 0 rows affected (0.02 sec)

mysql> show global variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| masterlog.000001 |       473 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.14 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| masterlog.000001 |       520 |
| masterlog.000002 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> change master to

    -> master_host='192.168.1.66',

    -> master_user='repuser',

    -> master_password='secret',

    -> master_port=3306,

    -> master_log_file='masterlog.000002',

    -> master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.30 sec)

mysql> start slave;

Query OK, 0 rows affected (0.05 sec)

mysql> show slave status \G
*************************** 1. row ***************************

               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.66
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: masterlog.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: aa-PC-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: masterlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 0

                  Master_UUID:

             Master_Info_File: C:\opt\mysql-5.6.10\data\master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp: 130319 09:34:00

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

1 row in set (0.00 sec)

Çok ustalı yinelemede temel problem CAP teoremi ile açıklanabilir. Eğer tek başına çalışan bir sunucudaki yazılımı bilgisayar ağı ile biri birine bağlı dağıtılmış bir sisteme dönüştürüyorsanız eskiden olduğu gibi davranmasını beklememelisiniz. Genelde yapılan hatalardan biri de testlerin sadece tek başına bir sistemde yapılmasıdır. Tek başına çalışan sistemde düzgün çalışan bir uygulama dağıtık bir sistemde istenilen biçimde çalışmayabilir. Brewer'in CAP teorimi de bununla ilgilidir. Teorem ile ilgili detayları bu bağlantıdan okuyabilirsiniz. Özetle teorem dağıtık bir sistemde aşağıdaki üç özelliğin tümünün birden sağlanamayacağını söyler: 

1. Tutarlılık
2. Her zaman erişilebilirlik
3. Bölünme bağışıklığı

Bu özelliklerden birinden ödün vermeniz gerekir. Değişik veritabanı ürünleri için CAP teoremine uygun olarak bir sınıflandırma yapan çalışmaya bakmanızda bir fayda var. MySQL çok ustalı yineleme tutarlıktan ödün veriyor. Şimdi tutarlık ile ilgili oluşabilecek problemlere bir kaç tane örnek vereceğim:

Birinci problem:

Sunucu 1	Sunucu 2
mysql> use test; Database changed mysql> create table t1 (      -> id int not null auto_increment,     -> value varchar(30),     -> primary key(id)      -> ) engine=innodb ; Query OK, 0 rows affected (0.44 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (NULL,'jack'); Query OK, 1 row affected (0.00 sec)	mysql> insert into t1 values(NULL,'jack'); Query OK, 1 row affected (0.01 sec)
mysql> commit; Query OK, 0 rows affected (0.03 sec)
	mysql> commit; Query OK, 0 rows affected (0.03 sec)

2013-02-20 13:07:38 1956 [ERROR] Slave SQL: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t1 values(NULL,'jack')', Error_code: 1062
2013-02-20 13:07:38 1956 [Warning] Slave: Duplicate entry '1' for key 'PRIMARY' Error_code: 1062 
2013-02-20 13:07:38 1956 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'masterlog.000003' position 1687973

İkinci problem: Bu problem 'lost update' problemi olarak bilinir. Sunucularda aynı veritabanın tablolarında aynı kimlikli kayıtları üzerinde işlem yapılırken,iki farklı istemci kayıtlarda kilit olmadığı için bu kaydı değiştirmeye çalışabilir. Böyle bir durumunda kaydı en son değiştiren (commit gönderen) kazanır. 

Sunucu 1	Sunucu 2
mysql> use test; Database changed mysql> create table t2 (      -> id int not null auto_increment,     -> value varchar(30),     -> unique key(value),     -> primary key(id)      -> ) engine=innodb ; Query OK, 0 rows affected (0.44 sec) mysql> insert into t2 values (1,'jack'); Query OK, 1 row affected (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> update t2 set value='jack bauer' where id=1; Query OK, 1 row affected (0.00 sec)	mysql> update t2 set value='jack shephard' where id=1; Query OK, 1 row affected (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.03 sec)
	mysql> commit; Query OK, 0 rows affected (0.03 sec)

Üçüncü problem: 

Sunucu 1	Sunucu 2
mysql> use test; Database changed mysql> create table t2 (      -> id int not null auto_increment,     -> value varchar(30),     -> unique key(value),     -> primary key(id)      -> ) engine=innodb ; Query OK, 0 rows affected (0.44 sec) mysql> insert into t2 values (1,'jackb'),(2,'jacks'); Query OK, 1 row affected (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> update t2 set value='jack bauer' where id=1; Query OK, 1 row affected (0.00 sec)	mysql> update t2 set value='jack bauer' where id=2; Query OK, 1 row affected (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.03 sec)
	mysql> commit; Query OK, 0 rows affected (0.03 sec)

Olası çözümler

1. auto_increment_offset tanımlaması
2. Çok Ustalı Yineleme Yöneticisi kullanımı
3. MySQL 5.5 ile beraber gelen yarı-eş zamanlı yineleme 
4. MySQL 5.6 ile gelen atomik yineleme 

MySQL 5.6'da Bölümleme

Uzunca bir süredir beklediğimiz MySQL 5.6 sürümü nihayet yayınlandı. Bu sürüm Oracle'ın satın almadan sonra çıkardığı ikinci sürüm olma özelliğini taşıyor. Yeni sürüm birçok alanda iyileştirmeler ve yenilikler içeriyor. Bu sürümde de İyileştirmelerin ve yeniliklerin ağırlıklı olarak InnoDB depolama motoru üzerinde gerçekleştirildiğini rahatlıkla söyleyebiliriz. Yeni sürümü bu adresten indirebilirsiniz.
5.6 ile gelen önemli yeniliklerden biri bölümleme ile ilgili. Hem başarımı iyileştirilmiş hem de yönetimini kolaylaştırılmış. Örneğin artık belirli bir bölümü seçip üzerinde SELECT, INSERT, UPDATE ve DELETE cümlelleri çalıştırabiliyoruz. Bir bölümü başka bir tablo ile değiş tokuş edebiliyoruz. Bunlara ilişkin örnekleri aşağıda bulabilirsiniz.

mysql> create table employees (
    -> id int not null auto_increment primary key,
    -> first_name varchar(30),
    -> last_name varchar(30),
    -> salary float(8,2) )
    -> partition by hash(id) partitions 5;     


1 row in set (0.00 sec)

mysql> show table status like 'employees'\G

*************************** 1. row ***************************
           Name: employees
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5
 Avg_row_length: 16384
    Data_length: 81920
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: partitioned
        Comment:
1 row in set (0.00 sec)

mysql> show create table employees\G

*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(30) DEFAULT NULL,
  `salary` float(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (id)
PARTITIONS 5 */
1 row in set (0.00 sec)

mysql> select partition_name,table_rows from information_schema.partitions where table_name='employees';

+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          0 |
| p4             |          0 |
+----------------+------------+
5 rows in set (0.01 sec)

mysql> insert into employees values
    -> (1,'jack','bauer',24000),
    -> (2,'jack','shephard',32000),
    -> (3,'kate','austen',27500),
    -> (4,'ben','linus',21750),
    -> (5,'claire','littleton',43000),
    -> (6,'jin','kwon',35000),
    -> (7,'sun','kwon',26500),
    -> (8,'john','locke',43500),
    -> (9,'hugo','reyes',125000),
    -> (10,'sayid','jarrah',12500),
    -> (11,'james','ford',33500),
    -> (12,'desmond','hume',25000),
    -> (13,'charlie','pace',45000),
    -> (14,'micheal','dawson',47000),
    -> (15,'juliet','burke',37000);
Query OK, 15 rows affected, 15 warnings (0.07 sec)
Records: 15  Duplicates: 0  Warnings: 15

mysql> select partition_name,table_rows from information_schema.partitions where table_name='employees';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |          3 |
| p1             |          3 |
| p2             |          3 |
| p3             |          3 |
| p4             |          3 |
+----------------+------------+
5 rows in set (0.01 sec)

mysql> select * from employees partition(p0);
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |




+----+------------+-----------+----------+
|  5 | claire     | littleton | 43000.00 |
| 10 | sayid      | jarrah    | 12500.00 |
| 15 | juliet     | burke     | 37000.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)

mysql> select * from employees partition(p1);
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  1 | jack       | bauer     | 24000.00 |
|  6 | jin        | kwon      | 35000.00 |
| 11 | james      | ford      | 33500.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)

mysql> select * from employees partition(p2);
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  2 | jack       | shephard  | 32000.00 |
|  7 | sun        | kwon      | 26500.00 |
| 12 | desmond    | hume      | 25000.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)

mysql> select * from employees partition(p3);
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  3 | kate       | austen    | 27500.00 |
|  8 | john       | locke     | 43500.00 |
| 13 | charlie    | pace      | 45000.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)

mysql> select * from employees partition(p4);
+----+------------+-----------+-----------+
| id | first_name | last_name | salary    |
+----+------------+-----------+-----------+
|  4 | ben        | linus     |  21750.00 |
|  9 | hugo       | reyes     | 125000.00 |
| 14 | micheal    | dawson    |  47000.00 |
+----+------------+-----------+-----------+
3 rows in set (0.00 sec)


mysql> select * from employees partition(p1,p3);
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  1 | jack       | bauer     | 24000.00 |
|  6 | jin        | kwon      | 35000.00 |
| 11 | james      | ford      | 33500.00 |
|  3 | kate       | austen    | 27500.00 |
|  8 | john       | locke     | 43500.00 |
| 13 | charlie    | pace      | 45000.00 |
+----+------------+-----------+----------+
6 rows in set (0.00 sec)


mysql> insert into employees partition(p1) values (NULL,'shannon','rutherford',75000);
Query OK, 1 row affected (0.03 sec)

mysql> select * from employees partition(p1);
+----+------------+------------+----------+
| id | first_name | last_name  | salary   |
+----+------------+------------+----------+
|  1 | jack       | bauer      | 24000.00 |
|  6 | jin        | kwon       | 35000.00 |
| 11 | james      | ford       | 33500.00 |
| 16 | shannon    | rutherford | 75000.00 |
+----+------------+------------+----------+
4 rows in set (0.00 sec)

mysql> insert into employees partition(p2) values (NULL,'ana','cortez',51250);
Query OK, 1 row affected (0.06 sec)

mysql> select * from employees partition(p2);
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  2 | jack       | shephard  | 32000.00 |
|  7 | sun        | kwon      | 26500.00 |
| 12 | desmond    | hume      | 25000.00 |
| 17 | ana        | cortez    | 51250.00 |
+----+------------+-----------+----------+
4 rows in set (0.00 sec)

mysql> create table emp_backup like employees;

Query OK, 0 rows affected (1.61 sec)

mysql> alter table emp_backup remove partitioning;
Query OK, 0 rows affected (1.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table employees exchange partition p0 with table emp_backup ;

Query OK, 0 rows affected (0.52 sec)

mysql> select * from employees partition (p0);
Empty set (0.00 sec)

mysql> select * from emp_backup;
+----+------------+-----------+----------+
| id | first_name | last_name | salary   |
+----+------------+-----------+----------+
|  5 | claire     | littleton | 43000.00 |
| 10 | sayid      | jarrah    | 12500.00 |
| 15 | juliet     | burke     | 37000.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)