くりーむわーかー

プログラムとか。作ってて ・試しててハマった事など。誰かのお役に立てば幸いかと。 その他、いろいろエトセトラ。。。

MariaDB

MariaDB rootのパスワードの話 再設定とか

最近?のMariaDBはインストールした後にセキュアインストールっていうのがある。

ルートのパスワード設定とか対話形式でやってくれるので、ありがたや。rootのパスワード再設定とかもしてくれる。rootの再設定って昔はセーフモードで上げて~、あれこれのテーブルを直でUpdateして~みたいな感じだったので、コレできるとだいぶありがたや。

インストール後に↓

#~~インストール

#終わったらサービス上げる
sudo systemctl start mariadb

#↓のコマンド
sudo '/usr/bin/mysql_secure_installation'

#そしたら対話形式に色々効いてくるので答えてくだけ
#最初に現在のルートのパスワード聞いてくるけど
#本当に初回は空欄(エンターのみ)でOK

Enter current password for root (enter for none): 

#本当に初回は↓
Set root password? [Y/n] y
#2回目以降は↓
Change the root password? [Y/n] y

New password: 
Re-enter new password: 

Remove anonymous users? [Y/n] n
Disallow root login remotely? [Y/n] n
Remove test database and access to it? [Y/n] n
Reload privilege tables now? [Y/n] n

パスワード以外の質問は自分は基本nでやってる。ローカルで試してる程度だったら、特に消す必要もないので。

昔のやり方より断然こっちの方が楽でよい。

CentOS7+MariaDB+GaleraCluster+MaxScale+Python

AWS上のCentOS7でMariaDBをGaleraClusterでクラスタ構成して、MaxScaleを使ってPythonから呼ぶ。(って書いてて、俺何語だ書いてんだって感じになった。

クラスタ構成のDBを使う必要が出てきたので、今まで話半分に聞いてたクラスタを真面目に調べた。クラスタ構成で、自分みたいなしがないSEには一個だけ腑に落ちない点があったんだけど、この点を明確に書いてるのってあんまないのよね。当たり前だろって話なんだろうけど。なので先にそれについて。

クラスタ構成のDBって、ノードが何個かあって、それぞれレプリケーション(複製?同期?)みたいな事してるイメージ。ほいで、マスタ落ちたらスレーブが昇格して~みたいな感じ。

でだ、ノードが複数あるってことはサービスがそれぞれいるって事。じゃーどのノードにアプリ側からアクセスすればいいの?ノード落ちたら、設定ファイル手で書きかえるの?アホなの?って感じだった。まー自分がアホなんですけど。

結論から言うと、クラスタ構成のDBを使う場合はアプリ側で何かしらの仕込み(orそれに代わるもの)が絶対に必要になる。具体的には下の感じの機能を持ったDBアクセスするためのモジュールが必要になる。

  • クライアント(アプリ)からの接続先(呼び出し先)は一か所
  • その一か所で登録されているノードにランダムにアクセス
  • 別途、各ノードが生きてるかどうかの死活監視をする
  • 死んでたら登録リスト的何かから除外
  • 復活したら登録リスト的何かに復活させる
  • マスタとかスレーブを切り替えてるならその辺も

クラスタ構成の何かを使う場合は必ず上記の様なものが必要。Webサーバで考えればロードバランサみたいなやつ。クラスタとかHA構成の話ってだいたい、クラスタそのものの機能ばっかりの話で、実際、具体的にどう使うか的な話があまり無いので、いっつも腑に落ちなかった。プロキシみたいな機能はどれなのって事かな。

Djangoのアプリでやろうとして、きっとそういうモジュールがあるんだろうなと思ってたら公式にこんな問答があった。アプリ側で上述の機能を実装しとけって事らしい。マジか・・・。

って思ったら、MariaDBの場合、上述のプロキシみたいな機能をMaxScaleというの使ってやれる。MariaDBはなかなかいい感じですね。使ってみて、機能的にも割とよかった。

という事で、CentOS7でMariaDBをGaleraClusterでクラスタ構成にして、MaxScaleのサービスを通してPythonから呼んで、アプリ側には変更を加えずにクラスタを使えるようにする。公式にもチュートリアルあるし、色々記事もあるんだけど、古かったりやり方書かれ過ぎてパニックなので、まとめ的にも。あと、実際にどう繋げばいいの?ってとこも。

まず、AWSで3つインスタンスを作る。マーケットプレイスの「CentOS 7 (x86_64) - with Updates HVM」で作った。同じVPCのサブネット内にインスタンス作って、プライベートIPでPING通るようにする。とりあえず、セキュリティで全てトラフィックを例えば「10.0.0.0/16」とかにしとけば行けると思う。

そしたら前準備。これは全インスタンスでやっておく。

#全体的に更新
sudo yum -y update

#時刻を日本の時間にしておく(これはやらなくてもいいかな)
sudo vi /etc/sysconfig/clock
#中身は↓#################
ZONE="Japan"
#中身は↑#################

sudo ln -sf /usr/share/zoneinfo/Japan /etc/localtime
sudo reboot

そしたら、最初のノードを作る。MariaDBは10.3。

#MariaDB レポジトリ設定
#https://mariadb.com/kb/en/library/mariadb-package-repository-setup-and-usage/

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

#MariaDB Serverのインストール(↓でMariaDB-client,galeraも入るらし)
sudo yum -y install MariaDB-server

#設定ファイル
sudo vi /etc/my.cnf.d/server.cnf
#中身は↓#################
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider='/usr/lib64/galera/libgalera_smm.so'
wsrep_cluster_address=gcomm://10.0.0.90,10.0.0.91,10.0.0.92 #ノードのリスト
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name="cluster_test"
wsrep_node_address="10.0.0.90"###ここは自分のIP
bind-address=0.0.0.0
#中身は↑#################

#SELinuxを止める(これやらないとエラーでる)
sudo setenforce 0

#一つ目のノードを起動
galera_new_cluster

これでとりあえず最初のノードが起動する。で、最初のノードは必ず最後の「galera_new_cluster」で上げないとダメっぽ。2個目以降は「sudo systemctl start mariadb」。 ここまでの状態で、「mysql」って打てば自分につながる。で、あとはmysqlのrootのパスワードとかの設定。やらなくても動くだろうけどとりあえず。

#Rootのパスワードとかの設定
#ちなみにこれは、最初の一個だけやれば他のノードは同期されてた。
sudo '/usr/bin/mysql_secure_installation'

#いちをmariadbにつないで「show status like 'wsrep_%';」でクラスタの設定が見れる。
#これの結果で wsrep_local_state_commentがSynced、
#wsrep_incoming_addressesに自分のアドレスが乗ってれば大丈夫らし。

#設定終わったらとりあえず一回止める(別にいらないかも)
sudo systemctl stop mariadb

後は、他のノードも同様に↓の感じでセットアップ。今回は他の2台。

#MariaDB レポジトリ設定
#https://mariadb.com/kb/en/library/mariadb-package-repository-setup-and-usage/

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

#MariaDB Serverのインストール(↓でMariaDB-client,galeraも入るらし)
sudo yum -y install MariaDB-server

#設定ファイル
sudo vi /etc/my.cnf.d/server.cnf
#中身は↓#################
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider='/usr/lib64/galera/libgalera_smm.so'
wsrep_cluster_address=gcomm://10.0.0.90,10.0.0.91,10.0.0.92 #ノードのリスト
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name="cluster_test"
wsrep_node_address="10.0.0.91"###ここは自分のIP
bind-address=0.0.0.0
#中身は↑#################

#SELinuxを止める(これやらないとエラーでる)
sudo setenforce 0

そしたら、最初に書いた通り、1個目のノードは「galera_new_cluster」、2個目以降のノードは「sudo systemctl start mariadb」でサービスを上げる。

ここまでで、クラスタ構成になる。適当なノードで「mysql -u root -p」でローカルのMariaDBにつなげて、「show status like 'wsrep_%';」すれば「wsrep_incoming_addresses」に3つIPが表示されてるはず。

次はMaxScaleのセットアップ。一か所で動かすけど、適当なノード(MariaDBが入ってるインスタンスとは別でもいい)で↓の感じ。

#インスト
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
sudo yum -y install maxscale

で、次にMariaDBにつないで、MaxSclae用のユーザを作る。

mysql -u root -p

#maxscaleというユーザでパスワードもmaxscaleにする
CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale';
GRANT SELECT ON mysql.user TO 'maxscale'@'%';
GRANT SELECT ON mysql.db TO 'maxscale'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';

#何か権限が↑じゃ足りないらしい。面倒なのでとりあえず丸ごとつける
GRANT ALL ON *.* to 'maxscale'@'%';

そしたら次は設定ファイル。

sudo vi /etc/maxscale.cnf

#中身は↓#################
[maxscale]
threads=auto

[server1]
type=server
address=10.0.0.90
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=10.0.0.91
port=3306
protocol=MariaDBBackend

[server3]
type=server
address=10.0.0.92
port=3306
protocol=MariaDBBackend

[Galera Service]
type=service
router=readconnroute
router_options=synced
servers=server1, server2, server3
user=maxscale
passwd=maxscale

[Galera Listener]
type=listener
service=Galera Service
protocol=MariaDBClient
port=4306

[Galera Monitor]
type=monitor
module=galeramon
servers=server1, server2, server3
user=maxscale
passwd=maxscale

[CLI]
type=service
router=cli
[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
socket=default
#中身は↑#################

Splitなんちゃらみたいなサービスとかリスナでやってるサンプルが多いですが、とりえず動かすなら↑で充分なのと、公式が上で書いてあるからそうした。で、アプリからつなぐときはリスナの「port=4306」につなげる必要あり。

そしたら、MaxScaleのサービスを上げて確認する。

#起動
sudo systemctl start maxscale
#確認用⇒ノードが3つ出て、マスタとかスレーブとか確認できればOK。
sudo maxadmin list services
sudo maxadmin list servers
sudo maxadmin list listeners

そしらた、mysqlのクライアントが入ってるインスタンス(mysqlってコマンド打って動くインスタンス)で↓のコマンド。

#maxscaleが動いてるホストの4306ポートに対してmaxsclaeというユーザで繋げる
mysql -h 10.0.0.90 --port 4306 -u maxscale

これで繋がれば、maxsclaeを通して各ノードにアクセス出来てる。

次はPythonから呼ぶ。っていっても↑のmaxscaleにつなげるやり方が分かってれば問題ないはず。

#とりあえずクライントはこれで
pip install mysql-connector-python

#インタプリタで試す
python

#下の感じ
import mysql.connector
conn = mysql.connector.connect(user='maxscale', password='maxscale', host='10.0.0.90', database='testhogedb')
cur = conn.cursor()
cur.execute("select * from hoge;")
for row in cur.fetchall():
    print(row[0])
cur.close
conn.close

これだけだと、あまり違いが分からないけど、マスタのサービス落としたり、他のノードでテーブル作ってインサートしてみたいなことしてもちゃんと動いてる事がわかると思う。

そいで、今一つ分かってない事が1個ある。色々見てるとクラスタは3ノードが最少構成で、3ノードの場合、ノードが残り1個になるとクエリを受け付け無くなるという習性がある。と色んな所で見る。でも、普通に動くんだよね。ノード1個でも。なんだろね。。。

謎。

MySQL(MariaDB)とPostgreSQLで文字コード指定で文字を作る(外字とか)

なんか久しぶりに書く気がする。

タイトル通り。サロゲとか外字の文字を文字コード指定でDBに入れたい時用。

MySQL(MariaDB)

insert testchar values ('外字',CHAR(0xEE8080 USING utf8mb4));
insert testchar values ('サロゲ',CHAR(0xF0A08083 USING utf8mb4));

サロゲを表現する場合、普通にIMEとかで見ると、「0xD840 , 0xDC03」って見えるけどこれを合わせた形式にする。

SELECT HEX(CONVERT(CHAR(0xD840DC03 USING utf16) USING utf8mb4));

上で出てきたコードをchar()に指定すればサロゲ文字が出る。あと、エンコード指定でutf8mb4を指定しないとダメ。サロゲ対応用の文字コードらし。

PostgreSQL

insert into testchar values('外字',chr('xE000' :: bit(16) :: int));
insert into testchar values('サロゲ','𠀃');--文字コード指定がわからん。

サロゲの文字コード指定が分かりませんでした。ごめんなさい。今度調べよ。

問合せ