Symfoware

Symfowareについての考察blog

FreeBSD 11.0にMariaDB 10.1をインストールし外部接続を許可する(pkg使用)

FreeBSD 11.0にMariaDB 10.1をインストールしてみます。

FreeBSD 11.0のインストール
FreeBSD 11.0-RELEASEを仮想環境(UbuntuのKVM)にインストール

MariaDBのインストール
FreeBSD 10.0にMariaDB 10をインストールする(pkg install使用)

それぞれ参考にしています。



MariaDB 10.1



pkg searchしてみると、mariadb101-serverの指定でインストールできそうです。


# pkg search mariadb
mariadb100-client-10.0.29     Multithreaded SQL database (client)
mariadb100-server-10.0.29     Multithreaded SQL database (server)
mariadb101-client-10.1.21     Multithreaded SQL database (client)
mariadb101-server-10.1.21     Multithreaded SQL database (server)
mariadb55-client-5.5.54_1     Multithreaded SQL database (client)
mariadb55-server-5.5.54_1     Multithreaded SQL database (server)





MariaDB 10.1をインストール。


# pkg install mariadb101-server




/etc/rc.confを編集。


# vi /etc/rc.conf




mysql_enable="YES"を追記し、起動許可を与えます。


hostname="freebsd11"
keymap="jp.106.kbd"
ifconfig_re0="inet 192.168.1.101 netmask 255.255.255.0"
defaultrouter="192.168.1.1"
sshd_enable="YES"
nginx_enable="YES"
php_fpm_enable="YES"
mysql_enable="YES"




mariadbを起動。
※コマンドはmysql-serverですが...


# service mysql-server start




デフォルトでrootパスワードは設定されていません。
ユーザー:root、パスワード無しで接続できます。


# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.21-MariaDB FreeBSD Ports

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>



10.1.21がインストールできたようです。





文字コードの変更



文字コードを調べてみると、デフォルトは相変わらずlatin1のようです。


MariaDB [(none)]> show variables like "char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database | latin1                         |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | latin1                         |
| character_set_system     | utf8                             |
| character_sets_dir     | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)




/usr/local/share/mysql/my-large.cnfを/usr/local/etc/my.cnfにコピー。
編集します。


# cp /usr/local/share/mysql/my-large.cnf /usr/local/etc/my.cnf
# vi /usr/local/etc/my.cnf




項目を追記します。


# The following options will be passed to all MariaDB clients
[client]
#password     = your_password
port            = 3306
socket         = /tmp/mysql.sock
default-character-set=utf8mb4


# The MariaDB server
[mysqld]
port            = 3306
socket         = /tmp/mysql.sock
character-set-server=utf8mb4
skip-character-set-client-handshake
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M




編集がおわったらMariaDBをリスタート。


# service mysql-server restart




ちゃんとutf8mb4に変更できました。


MariaDB [(none)]> show variables like "char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                         |
| character_set_connection | utf8mb4                         |
| character_set_database | utf8mb4                         |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                         |
| character_set_server     | utf8mb4                         |
| character_set_system     | utf8                             |
| character_sets_dir     | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)







外部接続の許可



別の端末から接続できるようにしておきます。


MariaDB [(none)]> grant all privileges on *.* to admin@'%' identified by 'P@ssw0rd' with grant option;
MariaDB [(none)]> grant all privileges on *.* to admin@localhost identified by 'P@ssw0rd' with grant option;



※2行めはローカルアクセス時の制限。
MariaDB 5.5の外部接続許可


これで、
ユーザー名:admin
パスワード:P@ssw0rd
で接続できるようになりました。


テーマ:サーバ - ジャンル:コンピュータ

  1. 2017/03/20(月) 18:24:27|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MariaDB 10.0で絵文字の寿司ビール問題に対応する

MySQL で utf8 と utf8mb4 の混在で起きること


MySQL の utf8 は4バイト文字を扱うことができません。



知らなかったです。
MariaDB 10.0でも同様の挙動なのか調べてみました。


テーブル



utf8とutf8mb4で同じレイアウトのテーブルを作成しました。


CREATE TABLE t_utf8 (
id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
value varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE t_utf8mb4 (
id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
value varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;







サンプルプログラム



CodeIgniterでデータベースを検索・登録するサンプルを作成してみます。

・application/controllers/Test.php


  1. <?php
  2. class Test extends CI_Controller {
  3.     
  4.     function __construct() {
  5.         parent::__construct();
  6.         
  7.         $this->load->database();
  8.         $this->load->helper('url_helper');
  9.         $this->load->library('form_validation');
  10.     }
  11.     
  12.     public function index() {
  13.         
  14.         $view_data = [];
  15.         
  16.         // 2つのテーブルを検索して、結果を表示
  17.         $view_data['utf8_rows'] = $this->db->get('t_utf8')->result_array();
  18.         $view_data['utf8mb4_rows'] = $this->db->get('t_utf8mb4')->result_array();
  19.         
  20.         $this->load->view('test', $view_data);
  21.         
  22.     }
  23.     
  24.     public function post() {
  25.         
  26.         // 入力チェックしつつ、入力が得られれば2つのテーブルにデータを登録
  27.         $this->form_validation->set_rules('input_text', 'text', 'required');
  28.         
  29.         if ($this->form_validation->run()) {
  30.             //検証通過ならデータベース登録
  31.             $data = [
  32.                 'value' => set_value('input_text')
  33.             ];
  34.             
  35.             $this->db->insert('t_utf8', $data);
  36.             $this->db->insert('t_utf8mb4', $data);
  37.         }
  38.         
  39.         $this->index();
  40.         
  41.     }
  42.     
  43. }





・application/views/test.php


  1. <!DOCTYPE html>
  2. <html lang="ja">
  3. <head>
  4.     <meta charset="utf-8">
  5.     <title>utf8テスト</title>
  6. </head>
  7. <body>
  8. <div>
  9.     <form method="post" action="<?php echo site_url('test/post'); ?>">
  10.         <?php echo form_error('input_text'); ?>
  11.         <input type="text" name="input_text">
  12.         <input type="submit" value="登録">
  13.     </form>
  14. </div>
  15. <div>
  16.     <h3>UTF8</h3>
  17.     <ul>
  18.         <?php foreach($utf8_rows as $row): ?>
  19.         <li><?php echo $row['value']; ?></li>
  20.         <?php endforeach; ?>
  21.     </ul>
  22.     <h3>UTF8MB4</h3>
  23.     <ul>
  24.         <?php foreach($utf8mb4_rows as $row): ?>
  25.         <li><?php echo $row['value']; ?></li>
  26.         <?php endforeach; ?>
  27.     </ul>
  28. </div>
  29. </body>
  30. </html>






utf8



データベースの設定ファイル
application/config/database.php
これを以下のように設定しました。


  1. $active_group = 'default';
  2. $query_builder = TRUE;
  3. $db['default'] = array(
  4.     'dsn'    => '',
  5.     'hostname' => 'localhost',
  6.     'username' => 'root',
  7.     'password' => 'P@ssw0rd',
  8.     'database' => 'sample',
  9.     'dbdriver' => 'mysqli',
  10.     'dbprefix' => '',
  11.     'pconnect' => FALSE,
  12.     'db_debug' => (ENVIRONMENT !== 'production'),
  13.     'cache_on' => FALSE,
  14.     'cachedir' => '',
  15.     'char_set' => 'utf8',
  16.     'dbcollat' => 'utf8_general_ci',
  17.     'swap_pre' => '',
  18.     'encrypt' => FALSE,
  19.     'compress' => FALSE,
  20.     'stricton' => FALSE,
  21.     'failover' => array(),
  22.     'save_queries' => TRUE
  23. );




utf8指定です。


日本語の登録、検索は問題なし。

729_01.png

729_02.png


絵文字を登録してみます。
入力は「🍣🍺は最高!」

729_03.png

729_04.png


utf8は空白、utf8mb4は文字化けした状態で登録されました。
utf8、入力チェックの検証を通過しつつ空白で登録されてしまいます。


MariaDB [sample]> select * from t_utf8;
+----+--------------------------+
| id | value                    |
+----+--------------------------+
| 1 | 日本語登録テスト         |
| 2 |                         |
+----+--------------------------+
2 rows in set (0.00 sec)

MariaDB [sample]> select * from t_utf8mb4;
+----+--------------------------+
| id | value                    |
+----+--------------------------+
| 1 | 日本語登録テスト         |
| 2 | ????????は最高!         |
+----+--------------------------+
2 rows in set (0.00 sec)








utf8mb4



データベース接続設定を変更します。


  1. $active_group = 'default';
  2. $query_builder = TRUE;
  3. $db['default'] = array(
  4.     'dsn'    => '',
  5.     'hostname' => 'localhost',
  6.     'username' => 'root',
  7.     'password' => 'P@ssw0rd',
  8.     'database' => 'sample',
  9.     'dbdriver' => 'mysqli',
  10.     'dbprefix' => '',
  11.     'pconnect' => FALSE,
  12.     'db_debug' => (ENVIRONMENT !== 'production'),
  13.     'cache_on' => FALSE,
  14.     'cachedir' => '',
  15.     'char_set' => 'utf8mb4',
  16.     'dbcollat' => 'utf8mb4_general_ci',
  17.     'swap_pre' => '',
  18.     'encrypt' => FALSE,
  19.     'compress' => FALSE,
  20.     'stricton' => FALSE,
  21.     'failover' => array(),
  22.     'save_queries' => TRUE
  23. );




日本語の登録は問題なし。

729_05.png

729_06.png


「🍣🍺は最高!」を登録してみます。

絵文字を含む文字列はutf8は絵文字の部分が文字化け。
utf8mb4は正常登録という結果になりました。

729_07.png

729_08.png



知らないって罪ですね...
MySQLだけでなく、MariaDBでもutf8mb4を使用したほうが良さそうです。

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2017/01/22(日) 23:06:40|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

HeidiSQL SSH tunnel経由でMySQL(MariaDB)に接続する

テスト時、MySQLに登録しているデータの確認はHeidiSQLで行っています。
http://www.heidisql.com/

とても便利なのですが、外部からのアクセスを許可していない
MySQLデータベースには接続できず困っていました。


MySQL(SSH tunnnel)



よく見てみると「ネットワーク種別」に「MySQL(SSH tunnnel)」という項目が。
対象のデータベースサーバーにSSH接続できればデータベースに接続できそうです。

plink.exeを使用して、ssh接続を行いデータベースとの通信をトンネルしてくれる模様。





plink.exeのダウンロードと設定



SSH tunnnelを行うためのplink.exeをダウンロードしておきます。
PuTTY Download Page

722_01.png


HeidiSQLの「設定」タブでは


ネットワーク接続:MySQL(SSH tunnel)
ホスト名:localhost
ユーザー:データベースのユーザー名
パスワード:データベースのパスワード
ポート:3306



を指定しておきます。

722_02.png


ここで指定した情報を使用し、ssh接続先でmysqlコマンドを実行すると考えると設定しやすいと思います。
上記の内容で指定した場合、データベースサーバー上でこんなコマンドを実行している感じになります。


$ mysql -uDB_user@localhost -pDB_Password




「SSHトンネル」のタブでは、データベースサーバーにssh接続する情報を指定します。


plink.exe:ダウンロードしたplink.exeのパス
SSHホスト:データベースサーバーのIP
SSHポート:22
ユーザー:ssh接続するときのユーザー
パスワード:ssh接続するときのパスワード
ポート:3307



722_03.png

上記のように指定すると、こんなオプションでplink.exeが実行されます。


C:\TOOL\plink.exe -ssh ssh_user@192.168.1.1 -pw "******" -P 22 -N -L 3307:localhost:3306





接続を実行すると、こんな詳細表示とともにデータベースサーバーに接続できました。


/* Delimiter changed to ; */
/* パスワード を使用してユーザー名 localhost : MySQL (SSH tunnel) ... */
/* 応答を 4 秒待って、 plink.exe プロセスの生成を試みます */
/* C:\TOOL\plink.exe -ssh ssh_user@192.168.1.1 -pw "******" -P 22 -N -L 3307:localhost:3306 */
SELECT CONNECTION_ID();
/* 接続しました。スレッド ID: 42957 */
/* Characterset: utf8mb4 */
SHOW STATUS;
SHOW VARIABLES;
SHOW DATABASES;



テーマ:サーバ - ジャンル:コンピュータ

  1. 2017/01/12(木) 21:48:11|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MariaDB 10のレプリケーション設定(Debian 8)

MariaDB 10系のレプリケーションを試してみます。
Debian 8 サーバーを2台仮想環境に用意しました。

マスター:192.168.1.101
スレーブ:192.168.1.103

MariaDBはapt-getでインストールした10.0.26です。


# apt-get install mariadb-server







レプリケーションするデータベースの用意



マスター(192.168.1.101)にレプリケーションするデータベースを作成します。

sampleというデータベースを作成。
testテーブルを用意し、適当にデータを入れておきます。


# mysql -uroot -pP@ssw0rd
MariaDB [(none)]> create database sample;
MariaDB [(none)]> use sample;
MariaDB [sample]> create table test (id int not null, item varchar(100) not null);
MariaDB [sample]> insert into test (id, item) values (1, 'test1');
MariaDB [sample]> insert into test (id, item) values (2, 'test2');
MariaDB [sample]> select * from test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)







マスター側での操作



こちらを参考に...というかそのまま試してみました。
[MySQL][MariaDB] DBのレプリケーション構築手順


レプリケーション用のユーザー「repl」をパスワード「P@ssw0rd」で作成。
接続可能なネットワークセグメントは「192.168.1.0/255.255.255.0」とします。


MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'P@ssw0rd';




作成できたかSHOW GRANTSで確認。


MariaDB [(none)]> SHOW GRANTS FOR 'repl'@'192.168.1.0/255.255.255.0';
+--------------------------------------------------------------------------------------------------+
| Grants for repl@192.168.1.0/255.255.255.0                                                        |
+--------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.0/255.255.255.0' IDENTIFIED BY PASSWORD 'XX' |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)




これでユーザーは準備出来ました。
次に、mariadb.cnfにレプリケーション用の設定を記載します。


# vi /etc/mysql/conf.d/mariadb.cnf




[mysqld]のセクションに以下の内容を追記。


# MariaDB-specific config file.
# Read by /etc/mysql/my.cnf

[client]
# Default is Latin1, if you need UTF-8 set this (also in server section)
#default-character-set = utf8

[mysqld]
#
# * Character sets
#
# Default is Latin1, if you need UTF-8 set all this (also in client section)
#
#character-set-server = utf8
#collation-server     = utf8_general_ci
#character_set_server = utf8
#collation_server     = utf8_general_ci


# スレーブからもアクセスできるように変更
bind-address = 0.0.0.0
# スレーブと重複しないIDを採番号
server-id = 1
# ログの出力パス
log_bin = /var/log/mysql/mariadb-bin.log
# ログを保存する日数
expire_logs_days = 10




編集がおわったらデータベースを再起動。


# service mysql restart





データベースの読み書きを「FLUSH TABLES WITH READ LOCK」でロック


# mysql -uroot -pP@ssw0rd
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)




バイナルログファイル名とポジションを「SHOW MASTER STATUS」で確認。
メモしておきます。


MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000002 |     314 |             |                 |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)




データベースのフルバックアップを取得します。


# mysqldump -uroot -pP@ssw0rd --all-databases --lock-all-tables > /tmp/dbdump.db




バックアップが終わったら「UNLOCK TABLES」でロックを開放します。


MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)




取得したバックアップファイルをスレーブにコピーします。
今回はsftpコマンドを使用しました。


# cd /tmp/
# sftp root@192.168.1.103
sftp> cd /tmp
sftp> put dbdump.db







スレーブの設定




マスターのバックアップを復元します。


# mysql -uroot -pP@ssw0rd < /tmp/dbdump.db




mariadb.cnfを編集。


# vi /etc/mysql/conf.d/mariadb.cnf




[mysqld]セクションにスレーブの設定を追記します。


[mysqld]
#
# * Character sets
#
# Default is Latin1, if you need UTF-8 set all this (also in client section)
#
#character-set-server = utf8
#collation-server     = utf8_general_ci
#character_set_server = utf8
#collation_server     = utf8_general_ci

# 確認用に外部からアクセスできるように変更
bind-address = 0.0.0.0
# スレーブと重複しないIDを採番号
server-id = 2
# ログの出力パス
log_bin = /var/log/mysql/mariadb-bin.log




編集がおわったらデータベースサービスを再起動。


# service mysql restart




マスターで確認したバイナリログファイル名とポジションを設定します。


CHANGE MASTER TO MASTER_HOST='192.168.1.101',
MASTER_PORT=3306, MASTER_USER='repl',
MASTER_PASSWORD='P@ssw0rd',
MASTER_LOG_FILE='mariadb-bin.000002',
MASTER_LOG_POS=314;




実際にコマンドを実行した時の様子はこんな感じでした。


MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.101',
    -> MASTER_PORT=3306, MASTER_USER='repl',
    -> MASTER_PASSWORD='P@ssw0rd',
    -> MASTER_LOG_FILE='mariadb-bin.000002',
    -> MASTER_LOG_POS=314;
Query OK, 0 rows affected (0.21 sec)




「START SLAVE」でスレーブとしての動作を開始します。


MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)






確認



マスターで「SHOW MASTER STATUS」を実行。
バイナリログのファイル名とポジションを確認します。


MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000002 |     314 |             |                 |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)





スレーブで「SHOW SLAVE STATUS\G」を実行。
Slave_IO_RunningとSlave_SQL_RunningがYes。
Master_Log_File と Read_Master_Log_Pos がマスターで確認したFile、Positionと一致していれば成功です。


MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.1.101
                 Master_User: repl
                 Master_Port: 3306
                Connect_Retry: 60
             Master_Log_File: mariadb-bin.000002
         Read_Master_Log_Pos: 314
             Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 537
        Relay_Master_Log_File: mariadb-bin.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: 314
             Relay_Log_Space: 835
             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_SSL_Crl:
         Master_SSL_Crlpath:
                 Using_Gtid: No
                 Gtid_IO_Pos:
1 row in set (0.00 sec)







動作確認



マスターのtestテーブル。


MariaDB [sample]> select * from test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+




スレーブのtestテーブル。


MariaDB [sample]> select * from test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+




マスター側でレコードを追加します。


MariaDB [sample]> insert into test (id, item) values (3, 'test3');
Query OK, 1 row affected (0.05 sec)




スレーブでselectするとちゃんとマスターで追加したレコードが表示されました。


MariaDB [sample]> select * from test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+----+-------+





再度マスターで「SHOW MASTER STATUS」


MariaDB [sample]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000002 |     845 |             |                 |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)




スレーブで「SHOW SLAVE STATUS\G」


MariaDB [sample]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.1.101
                 Master_User: repl
                 Master_Port: 3306
                Connect_Retry: 60
             Master_Log_File: mariadb-bin.000002
         Read_Master_Log_Pos: 845
             Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 1068
        Relay_Master_Log_File: mariadb-bin.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: 845
             Relay_Log_Space: 1366
             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_SSL_Crl:
         Master_SSL_Crlpath:
                 Using_Gtid: No
                 Gtid_IO_Pos:




ちゃんと同期されているようですね。



【参考URL】

[MySQL][MariaDB] DBのレプリケーション構築手順

MariaDB はじめてのMariaDB 【第10回 レプリケーションサーバの構築】


テーマ:サーバ - ジャンル:コンピュータ

  1. 2016/08/09(火) 23:09:34|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MariaDB 列にハッシュ形式でデータを格納する(Dynamic Columns)

MariaDBのドキュメントで、「Dynamic Columns」という機能を知りました。

Dynamic Columns

これは動的に項目を増減したい場合に使えそう。



Dynamic Columns Basics



ドキュメントに記載されている内容を試してみます。
Dynamic Columnsとして使用する列のデータ型に「blob」を指定してテーブルを作成。


  1. create table assets (
  2.     item_name varchar(32) primary key,
  3.     dynamic_cols blob
  4. );




データを登録する際、COLUMN_CREATEを使用して列名と値を指定します。
[フィールド1], [値1], [フィールド2], [値2] ...
の順で指定します。

以下のクエリーで、


color = blue
size = XL



という値を登録することになります。


  1. INSERT INTO assets VALUES
  2. ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));




同様にもう1レコード。


color = black
price = 500




  1. INSERT INTO assets VALUES
  2. ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));




データを検索してみると、dynamic_colsにバイナリな値が設定されています。

670_01.png


データの取り出しには「COLUMN_GET」を使用します。


COLUMN_GET([フィールド名], [COLUMN_CREATEで指定たフィールド名] as [データ型])





  1. SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets;



670_02.png

列が存在しない場合はNULLになります。


  1. SELECT item_name, COLUMN_GET(dynamic_cols, 'size' as char) AS size FROM assets;



670_03.png


カラムの削除は「COLUMN_DELETE」


  1. UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, "price")
  2. WHERE COLUMN_GET(dynamic_cols, 'color' as char)='black';




カラムの追加は「COLUMN_ADD」


  1. UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
  2. WHERE item_name='Thinkpad Laptop';








COLUMN_EXISTS



カラムが存在しているかをチェックするには「COLUMN_EXISTS」を使用します。
存在すれば1、存在しなければ0を返します。


  1. SELECT COLUMN_EXISTS(dynamic_cols, 'color') FROM assets;



670_04.png



  1. SELECT COLUMN_EXISTS(dynamic_cols, 'size') FROM assets;



670_05.png




SELECT COLUMN_LIST



存在しているカラム名を知りたい場合は「COLUMN_LIST」


  1. SELECT COLUMN_LIST(dynamic_cols) FROM assets;



このように、各々の行で存在しているカラム名が取得できます。

670_06.png

戻り値は文字列のようです。





COLUMN_CHECK



指定したblob列が有効なDynamic Columnsであるかチェックします。
有効であれば1を、無効であれば0を返します。

Dynamic Columns列というものがあるわけではなく、blob列をDynamic Columnsとして
使用しているため、普通の文字列などもデータとして登録可能です。

試しにこんなクエリーでデータを追加。


  1. INSERT INTO assets VALUES
  2. ('test', 'normal string');




カラムをチェックしてみます。


  1. SELECT item_name, COLUMN_CHECK(dynamic_cols) FROM assets;



670_07.png

もちろん、先ほど登録したデータは0(無効)となります。




COLUMN_JSON



データをJSON形式に変換します。


  1. SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;



これは便利。

670_08.png


だだし、Dynamic Columnsとして有効ではない列が存在する場合、
こんなエラーが発生します。


SQL エラー (1919): Encountered illegal format of dynamic column string




実際に使用する場合は、COLUMN_CHECKと組み合わせた
こんなクエリーになると思います。


  1. SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets
  2. where
  3. COLUMN_CHECK(dynamic_cols) = 1;




ちなみに、値が空白の場合はエラーにならず空の文字列が返されます。
値がNULLだとエラーになりました。





Nesting dynamic columns



値のネストもサポートされています。


COLUMN_CREATE('parent_column', COLUMN_CREATE('child_column', 12345))




  1. INSERT INTO assets VALUES('test', COLUMN_CREATE('parent_column', COLUMN_CREATE('child_column', 12345)));




670_09.png



テーマ:サーバ - ジャンル:コンピュータ

  1. 2016/02/21(日) 11:45:44|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
次のページ