簡単にできると思っていたら、案外苦戦したので、今回はエックスサーバでシェルやPythonコードからMySQLを操作する方法について、備忘録も兼ねて、まとめたいと思います。
1. 今回の実験内容と流れ、諸設定など
今回は以下の2つについて実験し、その方法についてまとめています。
- エックスサーバのMySQLにmysqlシェルから操作
- エックスサーバのMySQLにpythonコードで操作
この実験内容の大まかな実行の流れは以下になります。
① エックスサーバのインフォパネルからMySQLのデータベース・ユーザを作成、データベースへのユーザ設定 ② エックスサーバへのSSH接続 ③ mysqlシェルからの操作 ④ Pythonコードからの操作 ※上記の①は実験内容の1,2の両方で必要で、②,③は実験内容1に相当します。そして、④は実験内容2に該当します。
また、今回の実験用のデータベースやユーザの設定は以下になります。
[USER] USER_NAME -> MY-DOMAIN-NAME_test USER_HOSTNAME -> sv~~.xserver.jp(※) PASSWORD -> xserver11 [DB] DB_NAME -> MY-DOMAIN_test DB_HOSTNAME -> mysql~~.xserver.jp(※) DB_USER -> 上記のユーザ ※エックスサーバのmysql設定ページに記載
2. エックスサーバのMySQLを操作
ここからが実行内容になります。2-1と2-2については良質なサイトがあるので、そちらを紹介しています。コマンドの大文字部分については、ご自身の値を入れて下さい。
2-1. エックスサーバでのDB・ユーザ作成、DBへのユーザ設定
ここではMySQLのDBとユーザを作成し、かつDBへのユーザ設定を行います。これについては、下記のエックスサーバの説明ページ通りでうまくいきますので、そちらを参照して頂ければと思います。
両方作成すると下記のような画面になると思います。
2-2. エックスサーバへのSSH接続
次はエックスサーバへのSSH接続についてです。これについては下記サイトの説明通りになります。
2-3. mysqlシェルからの操作
2-2を実行するとエックスサーバのターミナルに接続できたと思います。次は、mysqlシェルに接続します。エックスサーバのターミナルから以下のコマンドを実行します。
$ mysql -h DB_HOSTNAME -u USER_NAME -p上記コマンドを実行すると、パスワードの入力が促されるため、ユーザ作成時に設定したパスワードを入力します。mysql>というプロンプト(入力を促すもの)が出れば、接続成功です。mysqlシェルからmysqlのデータベースを軽く触ってみます。まずは、ユーザの権限確認です。
mysql> SHOW GRANTS FOR ‘MY-DOMAIN_test’@‘USER_HOSTNAME’;戻り値
+-------------------------------------------------------------------------------------+ | Grants for MY-DOMAIN_test@USER_HOSTNAME | +-------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘MY-DOMAIN_test’@‘USER_HOSTNAME’ | | GRANT ALL PRIVILEGES ON 'MY-DOMAIN\_test'.* TO ‘MY-DOMAIN_test’@‘USER_HOSTNAME’ | +-------------------------------------------------------------------------------------+
上記を見るとグローバルレベルでは、何の権限も持っていないことがわかります。これは、コマンドからユーザやデータベースを作成できないことを意味します。しかし、データベースレベルでは、全ての権限を持っており、自由にコマンドを実行できることがわかります。
次は、データベースの中にテーブルを作成してみます。
mysql> CREATE TABLE MY-DOMAIN_test.test(id INT NOT NULL, price DOUBLE(9,1) NOT NULL);上記コマンドでテーブルが作成されます。そのテーブルのカラム情報を取得してみます。
mysql> SHOW COLUMNS FROM MY-DOMAIN_test.test;戻り値
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | price | double(9,1) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
指定通りのカラム設定になっていることが確認できます。この他、INSERT文でデータを入力することももちろんできます。以上が、エックスサーバのMySQLをmysqlシェルから操作するまでの一連の方法です。
2-4. PythonコードからのMySQLの操作
次は、Pythonコードから操作してみます。スクリプトを作って実行でも良いですが、今回は対話型シェルでそのまま行います。エックスサーバにPython環境を構築する方法は、下記サイトが参考になります。
また、今回、PythonのMySQLドライバにはmysql-connector-pythonを利用しています。anacondaを利用したので、インストールにはcondaを利用しており、conda install mysql-connector-pythonコマンドを実行しました。上記コマンドでほぼ最新版のコマンドを入手することができます(2018年11月20日時点でver8.0.12)。
では、PythonコードからMySQLを操作してみます。まず、2-2に沿って、エックスサーバのターミナルに接続し、Python3系を呼び出します。>>>というプロンプトがでれば、接続成功です。そして以下のコードを順々に実行していきます。
>>>import mysql.connector
>>>con_obj = mysql.connector.connect(user=‘MY-DOMAIN_test',host=‘DB_HOSTNAME’, password='xserver11')
>>>cur_obj = con_obj.cursor()エラーが立たなければ、接続成功です。ホストネームについては、DB側のホストネームになる点が注意点です。先ほどと同じようにテーブルを作成してみます。
>>>sql = 'CREATE TABLE MY-DOMAIN_test.test2(id INT NOT NULL, price DOUBLE(9,1) NOT NULL);'
>>>cur_obj.execute(sql)上記で作成したテーブルのカラムをmysqlを用いて、確認してみます。
>>>SHOW COLUMNS FROM MY-DOMAIN_test.test2;戻り値
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | price | double(9,1) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
先ほど同じように、カラムが確認できました。以上のコードをスクリプトに落とし込むことも可能です。
3. まとめ
以上が、今回の学習内容になります。レンタルサーバで四苦八苦していると、そもそもさくらVPSサーバ使えよ、といったツッコミを受けそうですが、それは流します。笑 エックスサーバ関連の検索をすると、どうも私と同じような場所で苦戦している人を見かけることがあったので、今回私が得た知見を記事にまとめました。
どなたかの参考になれば幸いです。読んで頂いた方、どうもありがとうございました。