Render.comに本番環境でmysqlを設定する(xserver)

Render.comのpostgresqlを利用すると、DBが90日で消えてしまうということなので、ちょうどxserverでwordpressを始めることもあり、そっちのmysqlを使おうと思って移行した。

xserver → mysql → wordpress
render.com → postgresql → 自分のアプリ

だったのでサーバー代もありどっちかに固める必要があった。

なので今回は自分のアプリをそのままrender.comでデプロイするけれど、DBだけxserverのmysqlを使うことにした。

手順

①xserverでSSH設定をONにして秘密鍵を発行する。

その前にxserverのMySql設定からアプリ用のDBと、アクセス権所有ユーザーを作っておき紐付けておく。

ssh設定

onに設定

パスフレーズを入れると秘密鍵が自動でダウンロードされる

②秘密鍵を使ってエックスサーバーにssh接続する。

sshディレクトリがなければ作成
→ sshディレクトリに秘密鍵を入れる
→ ファイルの名前をわかりやすく変更する
→.sshディレクトリと秘密鍵のパーミッションを変更
→sshログイン

#.sshディレクトリの作成
$ mkdir ~/.ssh
 #ファイルの移動 
$ mv ~/Downloads/hogehoge.key ~/.ssh/

#.sshに移動
$cd ~/.ssh/
 #名前の変更 
$mv hogehoge.key id_xserver_rsa

$ chmod 700 ~/.ssh
$ chmod 600 ~/.ssh/id_xserver_rsa
 #SSHログイン 
$ ssh -l サーバーID -i 秘密鍵ファイル名(今回はid_xserver_rsa) サーバーID.xsrv.jp -p 10022

①でmysqlとユーザーは紐づけているのでここまでできたら完了

エックスサーバーにssh接続する方法（ターミナル利用） - Qiita

③sshの情報とmysqlの情報をsettigsに書き込む

mysqlclientをインストールしておく

ここにかなり詰まって他にもいろいろ試してみたけど、sshtunnelを使ったらすぐにできた。

 #settings .py

from pathlib import Path
from dotenv import load_dotenv
import os
import MySQLdb as mydb
from sshtunnel import SSHTunnelForwarder

port_num = 10022  # エックスサーバーではデフォルトでポート番号が10022に設定されている
key_path = os.environ.get("keypath") #秘密鍵ファイルのパス 

ssh_tunnel = SSHTunnelForwarder(
     #サーバーID .xsrv.jp（xsから始まるお客様の初期ドメイン）
	 (os.environ.get("server_id"), port_num),
     #xserverのユーザー名 (サーバーIDの部分)
	 ssh_username= os.environ.get("ssh_username"),
     #秘密鍵を取得するときに入力したパスフレーズ 
	 ssh_password= os.environ.get("ssh_password"),
     ssh_pkey=key_path,
     #mysqlのホスト名 (xserverはlocalhost)とポート番号(xserverは3306)
	 remote_bind_address=('127.0.0.1', 3306),
)
 
ssh_tunnel.start()

DATABASES = {
    'default': {
            'ENGINE': 'django.db.backends.mysql',
            #dbの名前 (xserverはxsから始まる)
            'NAME': os.environ.get("name"),
            ##dbと紐付けたユーザー名(xserverはxsから始まる)
            'USER': os.environ.get("user"),
            ##パスワード(xserverはxsから始まる)
            'PASSWORD': os.environ.get("password"),
            'HOST': "127.0.0.1",
            'PORT': ssh_tunnel.local_bind_port,
            'OPTIONS':{
                'init_command':"SET sql_mode='STRICT_TRANS_TABLES'",
        },
    }
}

あとはマイグレーションをして完了

WindowsのDjangoのデータベースとしてエックスサーバーのMySQLをSSH接続で使う方法の解説 | 1978WORKS

④render.comのEnvironment Variablesに秘密にしたい値を書き込む

.envに保存したものをコピペしてもOK

secret_key=
allowed_hosts=*
DEBUG=True
keypath=/etc/secrets/id_xserver_rsa
server_id=xs〇〇.xsrv.jp
ssh_username=xs〇〇
ssh_password=
name=xs〇〇_〇〇
user=xs〇〇_〇〇
password=
settings=config.settings.base

次に、秘密鍵の中身をSecret Filesに追加する(秘密鍵はファイルのBEGINのところも含む全てをコピペしてください)

名前はid_xserver_rsaにする

⑤ついでに独自ドメインにも変更する

Render.comでデプロイしたwebアプリケーションに独自ドメインを当てる

⑥リブート対策をして投稿した画像が消えないようにする

Render.com無料プランのスリープ対策 - Qiita

詰まったところ

②や③でmysqlにアクセスしようとすると、1045 (28000): Access denied for user 'root'@'localhost' (using password: NO).のようなアクセス拒否のエラーが何回も出た。

マイグレーションやデプロイ時も度々このAccess deniedが出てきて、開発用のmysqlでは特に問題なかったし、時間をおいてもう一度試すとアクセスが通った。xserverかsshのどこかでエラーが発生しているのではと思った。

なお、以下のことを試したけど、全部Access deniedされた。

CREATE USER 'hogeuser'@'localhost' IDENTIFIED BY 'hogepassword';

grant all privileges on xs〇〇_〇〇.*  to 'xs〇〇_〇〇'@'localhost'
    -> with grant option;


SHOW GTANTS FOR  'xs〇〇_〇〇'@'localhost';

grant all privileges on xs〇〇_〇〇.* to 'xs〇〇_〇〇'@'localhost' IDENTIFIED BY 'hogepassword';

grant all privileges on xs〇〇_〇〇.* to 'xs〇〇_〇〇'@'localhost';

DROP USER 'xs〇〇_〇〇'@'localhost';

以上で完了です。デプロイしやすいrender.comを無料で利用しつつ、リブートやDBが90日で消えるなどの欠点はwordpressを使っているxserverのmysqlで補うことができます。速度的には微妙なのでガチ運用したいならまたいろいろ考えようと思います。