MYSQL5.6がリリースされました!!

MYSQL5.6はこれまでのMYSQL5.5と比較してもパフォーマンスが良く、
レプリケーションも安定性と運用を考慮した改善がされているという
事でしたので、リリースを待っていました。

Web セミナー Web セミナー

MySQL Security Enhancements and Important Security Tips for ISVs & OEMs
Thursday, February 21, 2013  2013年02月22日 (金): 00:00 日本

MySQL 5.6 Performance & Scalability ~ Afina tu BD en este webinar gratuito!
Thursday, February 28, 2013  2013年03月01日 (金): 08:00 日本
  

MySQL Replication: An Introduction
http://www-jp.mysql.com/why-mysql/white-papers/mysql-replication-introduction/

The whitepaper discusses:
Replication concepts
Replication enhancements in MySQL 5.6
Replication use-cases
Replication topologies
Replication monitoring and management

MySQL Replication Tutorial: Configuration, Provisioning and Management
http://www-jp.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/

By reading this paper, you will be able to:
Configure and provision MySQL replication
Migrate to semi-synchronous replication
Administer and trouble-shoot MySQL replication

データベース管理者/開発者ガイド MySQL 5.6の新機能
http://www-jp.mysql.com/why-mysql/white-papers/whats-new-mysql-5-6-ja/

    以下抜粋

MySQL 5.6は、世界で最も普及しているオープンソース・データベースの、
これまでで最高のリリースです。次世代のWebシステムの実現や、組込みアプリケーション
およびサービスの構築 を可能にするために設計された先進的な新機能を提供します。
本書では、主要な機能と機能強化を項目ごとに紹介し、
詳細な技術情報や実装関連情報を提供する事例や参考資料を示します。本書によって、
以下に挙げるような、MySQL 5.6の以前のリリースより改善・強化した点を理解することができます。

パフォーマンスとスケーラビリティの向上
InnoDBストレージ・エンジンの改良によるトランザクション・スループットの向上
オプティマイザの改良によるクエリ実行時間と診断機能の改善
オンラインでのDDL/スキーマ操作によるアプリケーション可用性の向上
Memcached APIを用いたInnoDBへのNoSQLアクセスによる開発速度の向上
レプリケーションの改善による高いパフォーマンスと自己修復可能なクラスタ構成
パフォーマンス・スキーマの改良による性能統計情報管理と監視機能の改善
セキュリティの向上による容易なアプリケーション設計
その他の重要な機能強化


検証用のレプリケーションの環境にてスレーブのMYSQLを5.6にアップグレードしてみました。

レプリケーションの確認

マスター側にてデータをINSERTして確認。

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 0');
Query OK, 1 row affected (0.12 sec)

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 1');
Query OK, 1 row affected (0.45 sec)

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 2');
Query OK, 1 row affected (0.44 sec)

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+----------------+---------------+
| id | title          | comment       |
+----+----------------+---------------+
|  1 | Before upgrade | MYSQL 5.6 - 0 |
|  2 | Before upgrade | MYSQL 5.6 - 1 |
|  3 | Before upgrade | MYSQL 5.6 - 2 |
+----+----------------+---------------+
3 rows in set (0.00 sec)

mysql>

mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 5     |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

mysql>

スレーブにデータが同期されている事を確認。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+----------------+---------------+
| id | title          | comment       |
+----+----------------+---------------+
|  1 | Before upgrade | MYSQL 5.6 - 0 |
|  2 | Before upgrade | MYSQL 5.6 - 1 |
|  3 | Before upgrade | MYSQL 5.6 - 2 |
+----+----------------+---------------+
3 rows in set (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.08 sec)

MYSQL5.5.29で追加インストールしたプラグインが
MYSQL5.6で有効になっていない可能性があるのでアップグレードの
タイミングで事前に無効にしておく。

[root@HOME002 mysql]# cp -p /etc/my.cnf /etc/my.cnf.20130216
[root@HOME002 mysql]# vi /etc/my.cnf
[root@HOME002 mysql]# diff /etc/my.cnf /etc/my.cnf.20130216
68c68
< #rpl_semi_sync_slave_enabled=1
---
> rpl_semi_sync_slave_enabled=1
[root@HOME002 mysql]#

[root@HOME002 mysql]# /etc/init.d/mysql stop
Shutting down MySQL.                                       [  OK  ]
[root@HOME002 mysql]#

MYSQL56-0

MYSQL5.6のインストール開始。
基本的には、TARの入れ替えとシンボリックリンクの張替えで対応してます。

[root@HOME002 src]# tar zxvf mysql-5.6.10-linux-glibc2.5-i686.tar.gz
mysql-5.6.10-linux-glibc2.5-i686/COPYING
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlaccess.conf
mysql-5.6.10-linux-glibc2.5-i686/bin/resolveip
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqld_multi
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlshow
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlaccess
mysql-5.6.10-linux-glibc2.5-i686/bin/resolve_stack_dump
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqladmin
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqld

省略.........

mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/Memcache.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/File/Path.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/Exec.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/ConfigFactory.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/CoreDump.pm
[root@HOME002 src]#

[root@HOME002 src]# mv mysql-5.6.10-linux-glibc2.5-i686 /usr/local/
[root@HOME002 src]#


[root@HOME002 local]# ls -lh
合計 100K
drwxr-xr-x  2 root  root  4.0K  1月  8 14:59 bin
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 etc
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 games
drwxr-xr-x  2 root  root  4.0K  1月  7 19:58 include
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 lib
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 man
lrwxrwxrwx  1 mysql mysql   27 12月 22 14:43 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x 13 mysql mysql 4.0K  1月 25 15:14 mysql-5.5.29-linux2.6-i686
drwxr-xr-x 13 root  root  4.0K  2月 16 15:51 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4.0K  1月  7 19:58 share
drwxr-xr-x  2 root  root  4.0K  2月 16 15:53 src
[root@HOME002 local]#

[root@HOME002 local]# chown -R mysql:mysql mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME002 local]# chmod -R 775 mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME002 local]#


[root@HOME002 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME002 local]# ln -s mysql-5.6.10-linux-glibc2.5-i686/ mysql
[root@HOME002 local]# ls -lh
合計 100K
drwxr-xr-x  2 root  root  4.0K  1月  8 14:59 bin
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 etc
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 games
drwxr-xr-x  2 root  root  4.0K  1月  7 19:58 include
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 lib
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 man
lrwxrwxrwx  1 root  root    33  2月 16 15:56 mysql -> mysql-5.6.10-linux-glibc2.5-i686/
drwxrwxr-x 13 mysql mysql 4.0K  1月 25 15:14 mysql-5.5.29-linux2.6-i686
drwxrwxr-x 13 mysql mysql 4.0K  2月 16 15:51 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4.0K  1月  7 19:58 share
drwxr-xr-x  2 root  root  4.0K  2月 16 15:53 src
[root@HOME002 local]#


[root@HOME002 local]# cp -rp mysql-5.5.29-linux2.6-i686/data/* /usr/local/mysql/data/
[root@HOME002 local]#

[root@HOME002 bin]# ls -l /usr/local/mysql/bin/*up*
-rwxrwxr-x 1 mysql mysql 4471915  1月 23 02:13 /usr/local/mysql/bin/mysql_upgrade
[root@HOME002 bin]# /etc/init.d/mysql start
Starting MySQL......                                       [  OK  ]
[root@HOME002 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
test.TABLE001                                      OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
OK
[root@HOME002 bin]#

upgrade

アップグレード後の確認。
OFFにしてあったSEMI-SYNCを再度ONに設定しても問題無いか確認して元に戻す。

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> set GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql>

[root@HOME002 bin]# vi /etc/my.cnf
[root@HOME002 bin]# diff /etc/my.cnf /etc/my.cnf.20130216
[root@HOME002 bin]#

[root@HOME002 bin]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL...                                          [  OK  ]
[root@HOME002 bin]#

レプリケーションの動作確認
マスターでデータをINSERTしてみる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into TABLE001(title,comment) values('After Slave upgrade','MYSQL 5.6 - 0');
Query OK, 1 row affected (0.43 sec)

mysql> select * from TABLE001;
+----+---------------------+---------------+
| id | title               | comment       |
+----+---------------------+---------------+
|  1 | Before upgrade      | MYSQL 5.6 - 0 |
|  2 | Before upgrade      | MYSQL 5.6 - 1 |
|  3 | Before upgrade      | MYSQL 5.6 - 2 |
|  4 | After Slave upgrade | MYSQL 5.6 - 0 |
+----+---------------------+---------------+
4 rows in set (0.00 sec)

mysql>

スレーブにデータが同期されている事を確認。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+---------------------+---------------+
| id | title               | comment       |
+----+---------------------+---------------+
|  1 | Before upgrade      | MYSQL 5.6 - 0 |
|  2 | Before upgrade      | MYSQL 5.6 - 1 |
|  3 | Before upgrade      | MYSQL 5.6 - 2 |
|  4 | After Slave upgrade | MYSQL 5.6 - 0 |
+----+---------------------+---------------+
4 rows in set (0.00 sec)

mysql>

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 1506
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 270
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           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: 1506
              Relay_Log_Space: 437
              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: 1
                  Master_UUID:
             Master_Info_File: /usr/local/mysql-5.6.10-linux-glibc2.5-i686/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)

mysql>

insert-after-slave-upgrade

エラーログの確認
特に問題なさそうですが、ログを確認するとバージョンアップしたので
幾つかオプションファイルの設定やレプリケーションの設定も変更した方が良さそうです。
マスターをアップグレードするタイミングで設定変更してみます。

130216 16:16:21 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-02-16 16:16:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-02-16 16:16:21 13756 [Note] Plugin 'FEDERATED' is disabled.
2013-02-16 16:16:21 b7fe96d0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-02-16 16:16:21 13756 [Note] InnoDB: The InnoDB memory heap is disabled
2013-02-16 16:16:21 13756 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-02-16 16:16:21 13756 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-02-16 16:16:21 13756 [Note] InnoDB: CPU does not support crc32 instructions
2013-02-16 16:16:21 13756 [Note] InnoDB: Using Linux native AIO
2013-02-16 16:16:21 13756 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-02-16 16:16:21 13756 [Note] InnoDB: Completed initialization of buffer pool
2013-02-16 16:16:21 13756 [Note] InnoDB: Highest supported file format is Barracuda.
2013-02-16 16:16:22 13756 [Note] InnoDB: 128 rollback segment(s) are active.
2013-02-16 16:16:22 13756 [Note] InnoDB: 1.2.10 started; log sequence number 1727822
2013-02-16 16:16:22 13756 [Note] Server hostname (bind-address): '*'; port: 3306
2013-02-16 16:16:22 13756 [Note] IPv6 is available.
2013-02-16 16:16:22 13756 [Note]   - '::' resolves to '::';
2013-02-16 16:16:22 13756 [Note] Server socket created on IP: '::'.
2013-02-16 16:16:22 13756 [Note] Slave I/O thread: Start semi-sync replication to master 'slave_user@HOME001:3306' in log 'mysql-bin.000015' at position 1506
2013-02-16 16:16:22 13756 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-02-16 16:16:22 13756 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2013-02-16 16:16:22 13756 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000015' at position 1506, relay log '/usr/local/mysql/data/relay-bin.000006' position: 270
2013-02-16 16:16:22 13756 [Note] Slave I/O thread: connected to master 'slave_user@HOME001:3306',replication started in log 'mysql-bin.000015' at position 1506
2013-02-16 16:16:22 13756 [Note] Event Scheduler: Loaded 0 events
2013-02-16 16:16:22 13756 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2013-02-16 16:16:22 13756 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2013-02-16 16:16:22 13756 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master, maybe it is a *VERY OLD MASTER*. Error_code: 1193
[root@HOME002 data]#

Comments are closed.

Post Navigation